Re: how to find privileges granted to a role

From: <tday6_at_csc.com>
Date: Thu, 20 Sep 2001 11:44:42 -0700
Message-ID: <F001.00394128.20010920115522@fatcity.com>

You'll probably want to pare this down. It shows all privileges granted to a particular role or user. It takes one parameter, the role or privilege being queried for. It's not mine, but unfortunately I didn't document where I picked it up.

--spool c:\project\listings\grantsfor&&1

set linesize 132 pagesize 66 verify off
ttitle "List Grants for User or Role "&&1 skip 2 column name heading 'Name'
column role heading 'Role'
column tname heading 'Table'
column priv heading 'Privilege'
select
substr(o.username,1,8) name,

o.granted_role role,
t.table_name tname,
t.privilege priv

from user_role_privs o,
user_tab_privs t
where
o.granted_role=t.grantee
order by 1,3,2
/
select o.granted_role role,
s.privilege priv
from
role_sys_privs s,
user_role_privs o
where
o.granted_role=s.role
/
select username name,
privilege priv
from
user_sys_privs
/

--spool off

                    "Davydov,                                                          
    
                    Vitaliy"             To:     Multiple recipients of list ORACLE-L  
    
                    <vdavydov_at_all        <ORACLE-L_at_fatcity.com>                        
    
                    data.com>            cc:                                           
    
                    Sent by:             Subject:     how to find privileges granted 
to a  
                    root_at_fatcity.        role                                          
    
                    com                                                                
    
                                                                                       
    
                                                                                       
    
                    09/20/2001                                                         
    
                    03:05 PM                                                           
    
                    Please                                                             
    
                    respond to                                                         
    
                    ORACLE-L

I'd like to find out what object privileges are granted to a particular role.
Tried to query role_tab_privs, but no luck. Thank you.
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Davydov, Vitaliy
INET: vdavydov_at_alldata.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (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.com
--

Author:
INET: tday6_at_csc.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message