Archive

Posts Tagged ‘check user privileges’

Oracle – Find users with DBA privilege

July 20, 2011 3 comments

If you wish to know which users have been granted the dba role then you need to query the dba_role_privs in the SYS schema.

This role tells you the grantee, granted_role, whether they have admin option granted, and whether the role is their default role:

SQL> desc dba_role_privs
Name         Null?    Type
------------ -------- ------------
GRANTEE               VARCHAR2(30)
GRANTED_ROLE NOT NULL VARCHAR2(30)
ADMIN_OPTION          VARCHAR2(3)
DEFAULT_ROLE          VARCHAR2(3)

To find a list of all users with DBA privilege execute the following code:

SQL> select * from dba_role_privs where granted_role='DBA';
GRANTEE   GRANTED_ROLE ADM DEF
--------- ------------ --- ---
SYS       DBA          YES YES
SYSTEM    DBA          YES YES

Further Reading

http://download.oracle.com/docs/cd/B14117_01/server.101/b10755/statviews_2291.htm

Oracle – Check User Privileges

July 10, 2011 1 comment

If you want to check that a user has been granted/revoked the correct privileges you can perform the following from SQLPLUS:

SQL> SELECT grantee
, privilege
FROM dba_sys_privs
WHERE grantee = '<username>'
ORDER BY privilege;

To check a group of similar users have the same number of privileges quickly you can perform a count of their privileges:

SQL> SELECT grantee
, COUNT(privilege)
FROM dba_sys_privs
GROUP BY grantee;