Hierarchical result
The following query hierarchically selects, depending on what lines are commented, a
user's system or objects privileges:
with roles_of_user (
user_or_role,
lvl
)as (
select
'RENE' user_or_role,
0 lvl
from
dual
union all
select
drp.granted_role,
rou.lvl+1
from
roles_of_user rou join
dba_role_privs drp on rou.user_or_role = drp.grantee
)
search depth first by user_or_role set order_by
select
case when nvl( LAG( rou.order_by ) over (order by rou.order_by), -1 ) <> rou.order_by then lpad(' ', rou.lvl*3) || user_or_role end user_or_role,
prv.owner, prv.table_name, prv.privilege, prv.type, prv.grantor, prv.grantable
-- sys.privilege, sys.admin_option-- , sys.common, sys.inherited
from
roles_of_user rou left join
dba_tab_privs prv on rou.user_or_role = prv.grantee
-- dba_sys_privs sys on rou.user_or_role = sys.grantee
order by
rou.order_by
;
Selecting «granted role path»
The following query selects all «granted-role-paths» from tables via roles to a user. Each path is selected in one record.
The interesting tables are specified in tabs, the users in usrs.
with tabs as (
select
owner own,
object_name nam,
object_type typ
from
dba_objects
where
owner = 'RENE' and
object_name like 'OBJ%' and
object_type in ('VIEW', 'TABLE')
),
usrs as (
select 'USR1' nam from dual union all
select 'USR2' nam from dual union all
select 'USR3' nam from dual
),
roles_of_user (
user_or_role,
lvl,
path_,
usr
) as (
select
nam user_or_role,
0 lvl_,
nam path_,
nam usr
from
usrs
union all
select
drp.granted_role,
rou.lvl+1,
drp.granted_role || ' -> ' || rou.path_,
rou.usr
from
roles_of_user rou join
dba_role_privs drp on rou.user_or_role = drp.grantee
)
search depth first by user_or_role set order_by
select
tabs.own,
tabs.nam,
tabs.typ,
-- prvs.grantee,
rous.usr,
prvs.privilege,
rous.path_,
prvs.grantor,
prvs.grantable
from
tabs join
dba_tab_privs prvs on tabs.own = prvs.owner and
tabs.nam = prvs.table_name and
tabs.typ = prvs.type join
roles_of_user rous on prvs.grantee = rous.user_or_role
-- where
-- path_ is not null
order by
tabs.nam
;