Post date: 19-Jan-2011 14:01:53
http://www.adp-gmbh.ch/ora/misc/recursively_list_privilege.html
select
lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
(
/* THE USERS */
select
null grantee,
username granted_role
from
where
username like upper('%&enter_username%')
/* THE ROLES TO ROLES RELATIONS */
union
select
grantee,
granted_role
from
/* THE ROLES TO PRIVILEGE RELATIONS */
union
select
grantee,
privilege
from
)
start with grantee is null
connect by grantee = prior granted_role;