Home » RDBMS Server » Security » Security scripts?
Security scripts? [message #19677] Wed, 03 April 2002 09:02 Go to next message
Jill
Messages: 6
Registered: August 1999
Junior Member
I have been trying to write a query to pull off information on users, roles and object access. I need to report on roles and their permissions, and what users have these roles granted to them. I cannot seem to get this data out of the data dictionary tables in any way that makes sense. Has anyone tried anything like this and have something I could look at?

Thanks!
Re: Security scripts? [message #19681 is a reply to message #19677] Wed, 03 April 2002 09:57 Go to previous messageGo to next message
Grant
Messages: 578
Registered: January 2002
Senior Member
set echo off
set verify off
set pages 200
col granted_role form a20
col owner form a12
col table_name form a27
col privilege form a27

ACCEPT username prompt 'Enter Username : '

spool privs_&username..lst

PROMPT Roles granted to user

SELECT granted_role,admin_option,default_role
FROM dba_role_privs
WHERE grantee=UPPER('&username');

PROMPT Table Privileges granted to a user through roles

SELECT granted_role, owner, table_name, privilege
FROM ( SELECT granted_role
FROM dba_role_privs WHERE grantee=UPPER('&username')
UNION
SELECT granted_role
FROM role_role_privs
WHERE role in (SELECT granted_role
FROM dba_role_privs WHERE grantee=UPPER('&username')
)
) roles, dba_tab_privs
WHERE granted_role=grantee;

PROMPT System Privileges assigned to a user through roles

SELECT granted_role, privilege
FROM ( SELECT granted_role
FROM dba_role_privs WHERE grantee=UPPER('&username')
UNION
SELECT granted_role
FROM role_role_privs
WHERE role in (SELECT granted_role
FROM dba_role_privs WHERE grantee=UPPER('&username')
)
) roles, dba_sys_privs
WHERE granted_role=grantee;

PROMPT Table privileges assigned directly to a user

SELECT owner, table_name, privilege
FROM dba_tab_privs
WHERE grantee=UPPER('&username');

PROMPT System privileges assigned directly to a user

SELECT privilege, admin_option
FROM dba_sys_privs
WHERE grantee=UPPER('&username');

spool off
Re: Security scripts? [message #19683 is a reply to message #19677] Wed, 03 April 2002 11:46 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Another interesting solution:
http://asktom.oracle.com/pls/ask/f?p=4950:8:134219::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:853427230099,
Previous Topic: Oracle Security
Next Topic: revoke create table does not work for dba - Urgent
Goto Forum:
  


Current Time: Thu May 26 16:13:27 CDT 2022