Refrain from granting DBA roles

                                                         

Recently application team requested DBA roles to perform some task.

In order to be complaint, we should refrain from granting DBA roles/SYSDBA privileges as far as possible. We can always narrow down to specific privileges .Alternatively , for one time activities this  can be handled by DBA  by screen sharing session and manually connecting as sys and input password.

In case, if we cannot opt any options from above then as a last resource we should have exceptions raised and approved.

Even though DBA role was granted, user was failing to query DBA level views, so additional privileges was granted on top of DBA role.

Question: Why did we need additional privileges when DBA role is granted?

Answer: DBA role has to be default role to exploit all privileges otherwise user fails to inherit permissions and additional privileges will be required in spite of DBA role. 

We should refrain from granting DBA roles/SYSDBA privileges as far as possible, however in case of exceptions follow below test case.

SQL> drop user systest cascade;

User dropped.

SQL> create user systest identified by xxxxxxxxxxx ;

User created.

•            Let us grant connect role

SQL> grant connect to systest;

Grant succeeded.

SQL> select * from dba_role_privs  WHERE GRANTEE='SYSTEST';

GRANTEE                        GRANTED_ROLE                   ADM DEL DEF COM

------------------------------ ------------------------------ --- --- --- ---

SYSTEST                        CONNECT                        NO  NO  YES NO  

-- Becomes default role 

SQL> select * from dba_sys_privs   WHERE GRANTEE='SYSTEST';

no rows selected

SQL> select * from dba_tab_privs   WHERE GRANTEE='SYSTEST';

no rows selected

SQL> select * from table_privileges WHERE GRANTEE='SYSTEST';

no rows selected

SQL> conn systest/xxxxxxxxxxx

Connected.

SQL> select * from session_privs;

PRIVILEGE

----------------------------------------

SET CONTAINER

CREATE SESSION

SQL> conn / as sysdba

Connected.

SQL> grant dba to systest;

Grant succeeded.

•   User SYSTEST not been granted any specific default role, so all roles granted are default, by default

SQL> select * from dba_role_privs  WHERE GRANTEE='SYSTEST';

GRANTEE                        GRANTED_ROLE                   ADM DEL DEF COM

------------------------------ ------------------------------ --- --- --- ---

SYSTEST                        DBA                            NO  NO  YES NO

SYSTEST                        CONNECT                        NO  NO  YES NO

SQL> conn systest/xxxxxxxxxxx

Connected.

SQL> select name from v$database ;

NAME

---------

MYTESTDB

SQL> select count(1) from session_privs;

  COUNT(1)

----------

       222    <<<<< Inherits all the privileges.

SQL> conn / as sysdba

Connected.

• Now let us specify the default role as CONNECT

SQL> alter user systest default role CONNECT;

User altered.

SQL> select * from dba_role_privs  WHERE GRANTEE='SYSTEST';

GRANTEE                        GRANTED_ROLE                   ADM DEL DEF COM

------------------------------ ------------------------------ --- --- --- ---

SYSTEST                        DBA                            NO  NO  NO  NO

SYSTEST                        CONNECT                        NO  NO  YES NO

SQL> conn systest/xxxxxxxxxxx

Connected.

SQL> select name from v$database ;

select name from v$database

                *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> select count(1) from session_privs;

  COUNT(1)

----------

         3 <<<<< Does not inherits all the privileges.

SQL> select * from session_privs;

PRIVILEGE

----------------------------------------

SET CONTAINER

UNLIMITED TABLESPACE

CREATE SESSION

SQL> conn / as sysdba

Connected.

•        Now let us specify the default role as DBA

SQL> alter user systest default role DBA;

User altered.

SQL> select * from dba_role_privs  WHERE GRANTEE='SYSTEST';

GRANTEE                        GRANTED_ROLE                   ADM DEL DEF COM

------------------------------ ------------------------------ --- --- --- ---

SYSTEST                        DBA                            NO  NO  YES NO

SYSTEST                        CONNECT                        NO  NO  NO  NO

SQL> conn systest/xxxxxxxxxxx

Connected.

SQL> select count(1) from session_privs;

  COUNT(1)

----------

       222 <<<< Inherits all the privileges.

SQL> select name from v$database ;

NAME

---------

MYTESTDB

SQL> exit

Summary: DBA role should be default to exploit.