Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Nested roles - PL/SQL proc

Re: Nested roles - PL/SQL proc

From: FC <flavio_at_tin.it>
Date: Mon, 09 Jun 2003 14:56:19 GMT
Message-ID: <ne1Fa.64013$pR3.1371764@news1.tin.it>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US