|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
Listing privileges recursively for Oracle users
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.