RE: Privileges associated with Role
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).
RE: Privileges associated with Role
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_privslink 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_privslink 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_privslink c,
sys.dba_tab_privslink 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_privslink e,
sys.dba_col_privslink 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_privslink 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_privslink 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_privslink i,
sys.dba_sys_privslink 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.comSubject: 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
Re: Privileges associated with Role
I should have read your question more closely. The grants script that I provided will also work with a role instead of a userid. To get all the privileges granted on a table select * from dba_tab_privs where table_name = 'TABLE_YOU'RE_INTERESTED_IN'; Oracle DBA sami_resume To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @yahoo.com cc: Sent by: Subject: Privileges associated with Role ml-errors 09/09/2003 12:14 PM Please respond to 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: 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).
Re: Privileges associated with Role
dba_sys_privs role_sys_privs - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, September 09, 2003 21:44 Hi List How to look for all the privileges associated with particular Role(for example R1)? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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).
Re: Privileges associated with Role
Hi
I wrote script some time back that you use to find all privileges
granted to a user or role, system, object and role. It does this
hierarchically so that it shows privs granted to roles granted to roles
etc. It is available from http://www.petefinnigan.com/tools.htm its the
first script there:
A sample output is:
get user input
NAME OF USER TO CHECK [ORCL]: DBSNMP
OUTPUT METHOD [S/F]: S
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [/tmp]:
old 162:lv_file_or_screen:='output_method';
new 162:lv_file_or_screen:='S';
old 164:open_file('file_name','output_dir');
new 164:open_file('priv.lst','/tmp');
old 166:get_privs('user_to_find',lv_tabs);
new 166:get_privs('DBSNMP',lv_tabs);
...USER = DBSNMP has ROLE CONNECT which contains =
..SYS PRIV =ALTER SESSION grantable = NO
..SYS PRIV =CREATE CLUSTER grantable = NO
..SYS PRIV =CREATE DATABASE LINK grantable = NO
..SYS PRIV =CREATE SEQUENCE grantable = NO
..SYS PRIV =CREATE SESSION grantable = NO
..SYS PRIV =CREATE SYNONYM grantable = NO
..SYS PRIV =CREATE TABLE grantable = NO
..SYS PRIV =CREATE VIEW grantable = NO
...SYS PRIV =CREATE USER grantable = NO
...SYS PRIV =SELECT ANY DICTIONARY grantable = NO
PL/SQL procedure successfully completed.
SQL
Hope this helps
kind regards
Pete
--
Pete Finnigan
email:[EMAIL PROTECTED]
Web site: http://www.petefinnigan.com - Oracle security audit specialists
Book:Oracle security step-by-step Guide - see http://store.sans.org for details.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Pete Finnigan
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).
