Non - Default Roles

While working on roles few days back, encountered an issue when all non default roles got changed to default when i missed adding the existing non-default roles in ALTER USER statement while assigning a new non-default role to a user.

Below is an illustration how a new NON_DEFAULT role should be assigned to user to avaoid any existing role changes.

DEFAULT ROLE

A default role means that the role is always enabled for the current session at logon. It is not necessary to issue the SET ROLE statement. To set a role as a DEFAULT role, you need to issue the ALTER USER statement.

NON_DEFAULT ROLE

Non default roles are the ones which are not enabled by default when a user logs into the database but must be enabled using SET ROLE statement.

STEPS TO BE FOLLOWED WHILE ASSIGNING NON_DEFAULT ROLES

1) Keep an output of existing state of roles assigned to the user in database.

SQL> select * from dba_role_privs where grantee='DUMMY' order by DEFAULT_ROLE desc,GRANTED_ROLE;

GRANTEE GRANTED_ROLE ADM DEF

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

DUMMY CSX_READ_ONLY_ROLE NO YES

DUMMY FDL_READ_ONLY NO YES

DUMMY CABOWNER_RO_ROLE NO YES

DUMMY PDD_CONNECT NO YES

DUMMY PDD_READ_ONLY_ROLE NO YES

DUMMY BSS_READ_ONLY_ROLE NO YES

DUMMY PROD_CATALOG_ROLE NO YES

DUMMY RPT_READ_ONLY_ROLE NO YES

DUMMY SUPPORT_EXECUTE_ROLE NO YES

DUMMY CDS_PROD_ROLE NO NO

DUMMY DATAFIX_ROLE NO NO

DUMMY CABOWNER_OPS_ROLE NO NO

DUMMY PDD_VIEW NO NO

The above output shows that user has some roles as NON_DEFAULT roles. We need to make sure that these NON_DEFAULT roles should not get affected after a new NON_DEFAULT role is assigned to user. Keep a note of all the NON_DEFAULT roles already assigned to user.

2) Grant the new NON_DEFAULT role to the user initially as DEFAULT role.

SQL> GRANT QMRC_DATAFIX TO DUMMY;

Grant succeeded.

3) Verify the status of new assigned role using

SQL> select * from dba_role_privs where grantee='DUMMY' order by DEFAULT_ROLE desc,GRANTED_ROLE;

GRANTEE GRANTED_ROLE ADM DEF

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

DUMMY CSX_READ_ONLY_ROLE NO YES

DUMMY FDL_READ_ONLY NO YES

DUMMY CABOWNER_RO_ROLE NO YES

DUMMY PDD_CONNECT NO YES

DUMMY PDD_READ_ONLY_ROLE NO YES

DUMMY BSS_READ_ONLY_ROLE NO YES

DUMMY PROD_CATALOG_ROLE NO YES

DUMMY QMRC_DATAFIX NO YES

DUMMY RPT_READ_ONLY_ROLE NO YES

DUMMY SUPPORT_EXECUTE_ROLE NO YES

DUMMY CDS_PROD_ROLE NO NO

DUMMY DATAFIX_ROLE NO NO

DUMMY CABOWNER_OPS_ROLE NO NO

DUMMY PDD_VIEW NO NO

From the output above, the new role has been assigned as DEFAULT ROLE.

4) Convert the newly assigned DEFAULT role to NON_DEFAULT role.

SQL> ALTER USER DUMMY DEFAULT ROLE ALL EXCEPT PDD_VIEW,DATAFIX_ROLE,CDS_PROD_ROLE,CABOWNER_OPS_ROLE,QMRC_DATAFIX;

User altered.

The point to note here is that we need to include all the existing NON_DEFAULT roles in the EXCEPT clause , otherwise the statement will change the existing NON_DEFAULT roles to DEFAULT.

5) Verify the state of new role as well as all the exiting NON_DEFAULT roles by using

SQL> select * from dba_role_privs where grantee='DUMMY' order by DEFAULT_ROLE desc,GRANTED_ROLE;

GRANTEE GRANTED_ROLE ADM DEF

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

DUMMY CSX_READ_ONLY_ROLE NO YES

DUMMY FDL_READ_ONLY NO YES

DUMMY CABOWNER_RO_ROLE NO YES

DUMMY PDD_CONNECT NO YES

DUMMY PDD_READ_ONLY_ROLE NO YES

DUMMY BSS_READ_ONLY_ROLE NO YES

DUMMY PROD_CATALOG_ROLE NO YES

DUMMY RPT_READ_ONLY_ROLE NO YES

DUMMY SUPPORT_EXECUTE_ROLE NO YES

DUMMY CDS_PROD_ROLE NO NO

DUMMY DATAFIX_ROLE NO NO

DUMMY CABOWNER_OPS_ROLE NO NO

DUMMY PDD_VIEW NO NO

DUMMY QMRC_DATAFIX NO NO

The above output shows the state of existing and newly granted NON_DEFAULT roles.