How to Find Users having DBA Role in Oracle

July 16, 2018
The default DBA role is automatically created during Oracle Database installation. This role contains most database system privileges. Therefore, the DBA role should be granted only to actual database administrators. If you want to know which users have been granted the dba role then you need to query the dba_role_privs in the SYS schema.

USERS_HAVING_DBA_ROLES

The DBA role does not include the SYSDBA or SYSOPER system privileges. These are special administrative privileges that allow an administrator to perform basic database administration tasks, such as creating the database and instance startup and shutdown.

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

DBA_ROLE_PRIVS describes the roles granted to all users and roles in the database.

USER_ROLE_PRIVS describes the roles granted to the current user.

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)

GRANTEE:               Name of the user or role receiving the grant
—————–
GRANTED_ROLE:   Granted role name
————————–
ADMIN_OPTION:   Indicates whether the grant was with the ADMIN OPTION (YES) or not(NO)
————————-
DEFAULT_ROLE:   Indicates whether the role is designated as a DEFAULT ROLE for the user (YES) or not (NO)

Using below query you can find users having DBA privileges

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

There are many situations arises where you wanted to revoke the DBA roles granted to the user for the security reasons. The above query will help you to find the users having DBA privileges. 

Must Read: