Oracle-L: Re: [Q] how to check object or role grant to public?

From: Thomas Day <tday6_at_csc.com>
Date: Tue, 11 Feb 2003 10:09:57 -0800
Message-ID: <F001.00549A5A.20030211100957@fatcity.com>

This asks for two parameters, the userid and the db_link. If you leave the db_link blank then it looks for the userid on the present database. To check for public give auserid of public. Slice and dice to fit your needs.

Posted by Jacques Kilchoer <Jacques.Kilchoer_at_quest.com> 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

                                                                                                                                       
                      "dist cash"                                                                                                      
                      <mccdba                  To:      Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>                    
                      @hotmail.com>            cc:                                                                                     
                      Sent by: root            Subject: [Q] how to check object or role grant to public?                               
                                                                                                                                       
                                                                                                                                       
                      02/11/2003 12:49                                                                                                 
                      PM                                                                                                               
                      Please respond                                                                                                   
                      to ORACLE-L

e have ORACLE 8.1.7 on SUN erver. My question are:

how to check which object or role are grant to public (from DBA)? (e.g. grant select on tax to public)
How to check which object or role are grant to which users?

Thanks.

STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: dist cash
  INET: mccdba_at_hotmail.com

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: 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.net
-- 
Author: Thomas Day
  INET: tday6_at_csc.com

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: 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).

Received on Tue Feb 11 2003 - 12:09:57 CST