|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
Listing privileges recursively for Oracle users
Note: I have started to move some scripts and source code from this website to GitHub.
I don't intend to maintain the scripts and or sources on this page any longer (so they might be outdated). But I will try to improve the code in the GitHub repository and accept Push Requests.
Users to roles and system privileges
This is a script that shows the hierarchical relationship between system privileges, roles and users.
It makes use of Oracles connect by SQL idiom.
Vladimir Gabzovski notifies me, that the following script doesn't run on 8.1 (ORA-01472). I want to thank him for pointing this out.
select lpad(' ', 2*level) || granted_role "User, his roles and privileges" from ( /* THE USERS */ select null grantee, username granted_role from dba_users where username like upper('%&enter_username%') /* THE ROLES TO ROLES RELATIONS */ union select grantee, granted_role from dba_role_privs /* THE ROLES TO PRIVILEGE RELATIONS */ union select grantee, privilege from dba_sys_privs ) start with grantee is null connect by grantee = prior granted_role;
System privileges to roles and users
This is also possible the other way round: showing the system privileges in relation to roles that have been granted this privilege and users that have been granted either this privilege or a role:
select lpad(' ', 2*level) || c "Privilege, Roles and Users" from ( /* THE PRIVILEGES */ select null p, name c from system_privilege_map where name like upper('%&enter_privliege%') /* THE ROLES TO ROLES RELATIONS */ union select granted_role p, grantee c from dba_role_privs /* THE ROLES TO PRIVILEGE RELATIONS */ union select privilege p, grantee c from dba_sys_privs ) start with p is null connect by p = prior c;
select case when level = 1 then own || '.' || obj || ' (' || typ || ')' else lpad (' ', 2*(level-1)) || obj || nvl2 (typ, ' (' || typ || ')', null) end from ( /* THE OBJECTS */ select null p1, null p2, object_name obj, owner own, object_type typ from dba_objects where owner not in ('SYS', 'SYSTEM', 'WMSYS', 'SYSMAN','MDSYS','ORDSYS','XDB', 'WKSYS', 'EXFSYS', 'OLAPSYS', 'DBSNMP', 'DMSYS','CTXSYS','WK_TEST', 'ORDPLUGINS', 'OUTLN') and object_type not in ('SYNONYM', 'INDEX') /* THE OBJECT TO PRIVILEGE RELATIONS */ union select table_name p1, owner p2, grantee, grantee, privilege from dba_tab_privs /* THE ROLES TO ROLES/USERS RELATIONS */ union select granted_role p1, granted_role p2, grantee, grantee, null from dba_role_privs ) start with p1 is null and p2 is null connect by p1 = prior obj and p2 = prior own;
A user can find his privileges also through the session_privs view.