Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Nested roles - PL/SQL proc
The problem with hierarchical queries is in that they cannot be run on certain views containing distinct clauses and other amenities. The data dictionary views Dba_role_privs is no exception to this rule. However you can work around this problem by using a temporary table as you see below:
create global temporary table tmp_role_privs on commit delete rows
as select a.*, b.privilege from dba_role_privs a, dba_sys_privs b
where 1=2;
create index tmp_idx_on_grantee on tmp_role_privs(grantee);
truncate table tmp_role_privs;
insert into tmp_role_privs
select a.*, b.privilege from dba_role_privs a, dba_sys_privs b
where a.granted_role = b.grantee (+);
accept username char prompt 'User name?';
select distinct level, grantee, granted_role, privilege from tmp_role_privs a
start with grantee = upper('&username')
connect by grantee = prior granted_role
union all (select 0, c.grantee, '<none>', c.privilege from dba_sys_privs c
where c.grantee = upper('&username'))
order by 1, 2, 4, 3;
I presume you can tailor the script to your needs.
Bye,
Flavio Received on Mon Jun 09 2003 - 09:56:19 CDT