This was posted here a while ago. I modified it to work through a database
link but if you leave the database link blank it will work on your current
database. It gives you all the privs (sys, role, and tab) granted directly
to the userid as well as all the privs granted to it via roles.
-- Posted by Jacques Kilchoer <[EMAIL PROTECTED]> on ORACLE-L
set linesize 200
set pages 500
set verify off
column sort_id noprint
column priv_type format a31
column priv format a59
column grantable heading "ADM" format a3
column default_role heading "DEF" format a3
select
1 as sort_id,
'ROLE' as priv_type,
a.granted_role as priv,
a.admin_option as grantable,
a.default_role as default_role
from
sys.dba_role_privs&&link a
where
grantee = upper('&&enter_username')
union
select
2 as sort_id,
'SYS PRIV' as priv_type,
b.privilege as priv,
b.admin_option as grantable,
null as default_role
from
sys.dba_sys_privs&&link b
where
grantee = upper('&&enter_username')
union
select
5 as sort_id,
'TAB PRIV (ROLE "' || c.granted_role || '")' as priv_type,
d.privilege || ' on "' || d.owner ||
'"."' || d.table_name || '"'
as priv,
d.grantable as grantable,
c.default_role as default_role
from
sys.dba_role_privs&&link c,
sys.dba_tab_privs&&link d
where
c.grantee = upper('&&enter_username')
and d.grantee = c.granted_role
union
select
7 as sort_id,
'COL PRIV (ROLE "' || e.granted_role || '")' as priv_type,
f.privilege || ' on "' || f.owner ||
'"."' || f.table_name || '" ("' || f.column_name || '")'
as priv,
f.grantable as grantable,
e.default_role as default_role
from
sys.dba_role_privs&&link e,
sys.dba_col_privs&&link f
where
e.grantee = upper('&&enter_username')
and f.grantee = e.granted_role
union
select
4 as sort_id,
'TAB PRIV' as priv_type,
g.privilege || ' on "' || g.owner ||
'"."' || g.table_name || '"'
as priv,
g.grantable as grantable,
null as default_role
from
sys.dba_tab_privs&&link g
where
g.grantee = upper('&&enter_username')
union
select
6 as sort_id,
'COL PRIV' as priv_type,
h.privilege || ' on "' || h.owner ||
'"."' || h.table_name || '" ("' || h.column_name || '")'
as priv,
h.grantable as grantable,
null as default_role
from
sys.dba_col_privs&&link h
where
h.grantee = upper('&&enter_username')
union
select
3 as sort_id,
'SYS PRIV (ROLE "' || i.granted_role || '")' as priv_type,
j.privilege as priv,
j.admin_option as grantable,
i.default_role as default_role
from
sys.dba_role_privs&&link i,
sys.dba_sys_privs&&link j
where
i.grantee = upper('&&enter_username')
and j.grantee = i.granted_role
order by 1, 2, 3 ;
undefine enter_username
undefine link
clear columns
set linesize 80
set verify on
"Rothouse,
Michael" To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
<mrothouse cc:
@fcg.com> Subject: RE: Privileges associated with
Role
Sent by:
ml-errors
09/09/2003 01:54
PM
Please respond
to ORACLE-L
Select * from dba_sys_privs where grantee='R1';
Select * from dba_tab_privs where grantee='R1';
Select * from dba_tab_privs where owner='<schema>' and
table_name='<table_name>';
-----Original Message-----
Sent: Tuesday, September 09, 2003 12:14 PM
To: Multiple recipients of list ORACLE-L
Hi List
How to look for all the privileges associated with
particular Role(for example R1)?
Alos does anyone have a query to identify ALL(either
directly or thru role) the privileges granted on
particular tables
TIA
Sami
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Oracle DBA
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from). You may also send the HELP
command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rothouse, Michael
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Thomas Day
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
