Home » RDBMS Server » Security » How to extract privilege report from Oracle?
How to extract privilege report from Oracle? [message #418041] Wed, 12 August 2009 12:32 Go to next message
Smith_X
Messages: 56
Registered: January 2007
Member
Hello

I want to extract privilege from Oracle Database. As far as I understand, privilege in Oracle keep in DBA_something_PRIVS view

- SYS.DBA_ROLE_PRIVS
- SYS.DBA_SYS_PRIVS
- SYS.DBA_TAB_PRIVS
- SYS.DBA_COL_PRIVS

My assignment is to makesure that any privilege assign to any user is commensurate to their job function. (i.e. only DBA should have SELECT ANY TABLE privilge while programmer can select only some table. ==> set at DBA_TAB_PRIVS instead of granted ANY TABLE in DBA_SYS_PRIVS)

First, I query all privilege from DBA_SYS_PRIVS. It shows like 20~ roles.

Next step, I cross check with DBA_ROLE_PRIVS and found that some role is a member of another role and so on. This happen because Oracle allow to group privilege as role. and allow to assign role to another role.

I spend about 2 hour to mapping it in Excel and it seems very far to finish. There are 2 more view I did not touch it yet. Are there any better way to do that, please?
Re: How to extract privilege report from Oracle? [message #418042 is a reply to message #418041] Wed, 12 August 2009 12:35 Go to previous messageGo to next message
BlackSwan
Messages: 22688
Registered: January 2009
Senior Member
clues can be found in 1 or more of the views below

DBA_AQ_AGENT_PRIVS
DBA_COL_PRIVS
DBA_PRIV_AUDIT_OPTS
DBA_REPGROUP_PRIVILEGES
DBA_ROLE_PRIVS
DBA_RSRC_CONSUMER_GROUP_PRIVS
DBA_RSRC_MANAGER_SYSTEM_PRIVS
DBA_SYS_PRIVS
DBA_TAB_PRIVS
DBA_WM_SYS_PRIVS
DBA_WORKSPACE_PRIVS
Re: How to extract privilege report from Oracle? [message #418462 is a reply to message #418042] Sat, 15 August 2009 16:16 Go to previous messageGo to next message
Smith_X
Messages: 56
Registered: January 2007
Member
Anybody have SQL script to extract Role/Privilege in report format please?

I found this one from http://www.psoug.org/reference/system_privs.html

Determine User Privs

This query will list the system privileges assigned to a user 	SELECT LPAD(' ', 2*level) || granted_role "USER PRIVS"
FROM (
  SELECT NULL grantee,  username granted_role
  FROM dba_users
  WHERE username LIKE UPPER('%&uname%')
  UNION
  SELECT grantee, granted_role
  FROM dba_role_privs
  UNION
  SELECT grantee, privilege
  FROM dba_sys_privs)
START WITH grantee IS NULL
CONNECT BY grantee = prior granted_role;

or

SELECT path
FROM (
  SELECT grantee,
    sys_connect_by_path(privilege, ':')||':'||grantee path
  FROM (
    SELECT grantee, privilege, 0 role
    FROM dba_sys_privs
    UNION ALL
    SELECT grantee, granted_role, 1 role
    FROM dba_role_privs)
  CONNECT BY privilege=prior grantee
  START WITH role = 0)
WHERE grantee IN (
  SELECT username
  FROM dba_users
  WHERE lock_date IS NULL
  AND password != 'EXTERNAL'
  AND username != 'SYS')
OR grantee='PUBLIC'
/


If anyone have SQL script which extract Role / Privilege from Oracle in simplify format please help. Thanks in advance.

[Updated on: Sat, 15 August 2009 16:23]

Report message to a moderator

Re: How to extract privilege report from Oracle? [message #418463 is a reply to message #418041] Sat, 15 August 2009 16:43 Go to previous messageGo to next message
BlackSwan
Messages: 22688
Registered: January 2009
Senior Member
http://www.orafaq.com/scripts/security/badsysprivs.txt
Re: How to extract privilege report from Oracle? [message #418477 is a reply to message #418463] Sun, 16 August 2009 03:20 Go to previous message
Smith_X
Messages: 56
Registered: January 2007
Member
Thank you very much BlackSwan

I found some more script at Pete's website in Tool Section. But I did not test it yet.

http://www.petefinnigan.com/tools.htm

If I found something more, I will also update here.
Previous Topic: Keeping OS_AUTHENTICATION_PREFIX a null, Is this a security risk ??
Next Topic: Enterprise User/Role
Goto Forum:
  


Current Time: Thu Aug 21 21:11:23 CDT 2014

Total time taken to generate the page: 0.09223 seconds