Listing privileges recursively for Oracle users

kxcquzwdjp

　　Users to roles and system privileges

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

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;

 
 Object privileges 
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;