About Default And Password Protected Roles

There was a conversation between me and an auditor:

Auditor: What is the default role for the database?
Me:        What do you mean by default role for the database? !!!!!!!!!!!
Auditor: We found this output in the script log we asked you to run for us:

GRANTEE       GRANTED_ROLE                     ADM  DEFAULT_ROLE
--------------      ---------------------                          ---        ------------
SYSTEM       AQ_ADMINISTRATOR_ROLE  YES      YES
SYSTEM       TTXLY_SUDI_ACCESS              YES      YES
.....

Now let me explain:

Firstly, forget the auditor words about the database default role !

So what does column DEFAULT_ROLE represents in dba_role_privs view?

By default Oracle set the roles assigned to any user as a default role for him, to get rid of the headache of setting the roles manually every time the user try to use his roles.

This means the user HR doesn't need to explicitly set the "RESOURCE" role using "set role resource;" command each time he tries to create a table, because "RESOURCE" role is already been set as a DEFAULT role for him.

The following example will give you a clear picture:

Now I’ll set the role “resource” for user HR as a non-default role to see what will happen:

SQL> sho user
USER is "SYS"

SQL> alter user hr default role all except resource;
User altered.

SQL>  select *from dba_role_privs where grantee='HR';
GRANTEE                   GRANTED_ROLE              ADM  DEFAULT_ROLE
---------------------------- ------------------------------        -------    -----
HR                                 RESOURCE                         NO        NO
HR                                 XXX                                       NO       YES

Now I’ll login with HR user and try to create a new table:

SQL> conn hr/hr
Connected.

SQL> create table asd as select * from employees;
create table asd as select * from employees                                 
ERROR at line 1:
ORA-01031: insufficient privileges

This is what will happen when you set a role as a non default role, to use a non default role you have to explicitly enable the role “resource” using this command:

SQL> set role resource;
Role set.

Now user HR can create the table after enabling the "RESOURCE" role:

SQL> create table asd as select * from employees;
Table created.
                                                                           
Conclusion:
Oracle gets the task of setting user's roles a hassle free one by automatically setting any role assigned to the user as a DEFAULT role unless the administrator set it as a non default role.


Here are some useful command:

To check how many roles are allowed to be  "DEFAULT ROLE" for each user in the the database:

SQL> sho parameter max_enabled_roles
NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
max_enabled_roles     integer 150

To make a role as a NON-DEFAULT role:

SQL> alter user HR default role all except RESOURCE;

To make all roles assigned to a user default roles:

SQL> alter user HR default role all;

To check the default and non default roles assigned to a user:

SQL>  select *from dba_role_privs where grantee='HR';
GRANTEE                    GRANTED_ROLE        ADM    DEF
------------------------------ ------------------------------    ---        ---
HR                                 RESOURCE                      NO     YES
HR                                 XXX                                   NO     YES

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)


Now, What about password protected Roles:
When you grant a user a role protected by a password although it will be automatically set as a DEFAULT ROLE, the user must execute "set role  identified by " command, in order to be able to use that role:

Here is an example:

SQL> sho user
USER is "SYS"

SQL> create role xxx identified by 123;
Role created.

SQL> grant select on scott.emp to xxx;
Grant succeeded.

SQL> grant xxx to hr;
Grant succeeded.

SQL> select *from dba_role_privs where grantee='HR';
GRANTEE                        GRANTED_ROLE               ADM   DEF
------------------------------ ------------------------------   ---        ---
HR                                 RESOURCE                        NO     YES
HR                                XXX                                     NO     YES

As we can observe xxx role is a default_role by default.

Now can we use "xxx" role before setting it? let's try

SQL> conn hr/hr
Connected.

SQL> desc scott.emp
ERROR:
ORA-04043: object aa.ss does not exist

To use the password protected role "xxx" you have to explicitly set it using the following command:

SQL> set role xxx identified by 123;
Role set.

Now, "xxx" role is ready to be used by the user:

SQL> desc scott.emp
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)