| Security scripts? [message #19677] |
Wed, 03 April 2002 09:02  |
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  |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
