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 <[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
"dist cash"
<mccdba To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
@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:
1. how to check which object or role are grant to public (from DBA)?
(e.g. grant select on tax to public)
2. 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: [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).
