A closer look at "SYSDBA" and "DBA"

SYSDBA and DBA are often misunderstood. In short, SYSDBA is a system privilege whereas DBA is a role.

The DBA role does not include the SYSDBA or SYSOPER system privileges. SYSDBA and SYSOPER are special administrative privileges that allow an administrator to perform basic database administration tasks, such as creating the database, instance startup and shutdown, drop a database, open and mount a database, place database in archivelog mode or remove it from archivelog mode.

About SYSDBA system privilege

The SYSDBA and SYSOPER system privileges allow access to a database instance even when the database is not open. Control of these privileges is totally outside of the database itself. The SYSDBA and SYSOPER privileges can also be thought of as types of connections that enable you to perform certain database operations for which privileges cannot be granted in any other fashion. For example, if you have the SYSDBA privilege, you can connect to the database by specifying CONNECT AS SYSDBA.

Query users having SYSDBA or SYSOPER privileges:

SQL> column sysdba format a10
SQL> column sysoper format a10
SQL> select * from v$pwfile_users;

USERNAME SYSDBA SYSOPER
------------------------------ ---------- ----------
SYS TRUE TRUE
SCOTT TRUE FALSE

About DBA Role

A predefined role, named "DBA", is automatically created with every Oracle database. This role contains all database system privileges but SYSDBA and SYSOPER are excluded. Therefore, it is very powerful and should be granted only to fully functional database administrators.

Query all roles that exist in the database:

SQL> select role from dba_roles;

ROLE
------------------------------
CONNECT
RESOURCE
DBA
:
:

Query all users to whom DBA role is granted:

SQL> select * from dba_role_privs where granted_role = 'DBA';

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
SYS DBA YES YES
SYSMAN DBA NO YES
LETS DBA NO YES
SYSTEM DBA YES YES

Additionally, you may not grant SYSDBA privilege to a role. ORA-01931 error is raised when you try to grant it to a role.

SQL> grant sysdba to dba;
grant sysdba to dba
*
ERROR at line 1:
ORA-01931: cannot grant SYSDBA to a role

A database user can be granted both DBA and SYSDBA privilege.

SQL> select * from dba_role_privs where granted_role = 'DBA';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
SYS                            DBA                            YES YES
SCOTT                          DBA                            NO  YES
SYSMAN                         DBA                            NO  YES
LETS                           DBA                            NO  YES
SYSTEM                         DBA                            YES YES

Happy reading !!!