Roles could not be executed even after they were recreated

Problem:
Database Administrator grants privileges to a role, then grants this role to a user.  When THE user tries to execute those privileges through this role, it fails. However, user can execute privileges directly.

Error message:

ORA-00942: table or view does not exist

ORA-01031: insufficient privileges

Problem explanation:

In SYS.DBA_ROLE_PRIVS this role shows up but DEFAULT_ROLE column is set to 'N' therefore role can not be executed as it is not a default.

SYS.DBA_ROLE_PRIVS shows roles granted to users and roles:

SQL> desc dba_role_privs; Column Name                    Null?    Type ------------------------------ -------- ---- GRANTEE  VARCHAR2(30) GRANTED_ROLE                  NOT NULL VARCHAR2(30) ADMIN_OPTION                   VARCHAR2(3) DEFAULT_ROLE                   VARCHAR2(3) SQL> select * from dba_role_privs     2> where GRANTEE='AL'; GRANTEE                        GRANTED_ROLE                   ADM DEF ------------------------------ ------------------------------ --- --- AL                              ABC                           NO  NO AL                              ABCD                          NO  NO AL                              CONNECT                       NO  YES AL                              RESOURCE                      NO  NO 4 rows selected.

Therefore the only role user AL can execute is Connect and neither role ABC nor ABCD can not be executed.

Solution:

Issue the following statement to make all the roles granted to user as default roles :

-- Alter your username SQL> ALTER scott DEFAULT ROLE ALL;

Solution explanation:

If one of the roles was altered as default, that will be the only role showing as default.  To be able to execute a role it needs to appear as default in SYS.DBA_ROLE_PRIVS:

SQL> alter user scott default role all; SQL> select * from dba_role_privs     2> where GRANTEE='AL'; GRANTEE               GRANTED_ROLE                          ADM DEF ------------------------------ ---------------------------- --- --- AL                              ABC                         NO  YES AL                              ABCD                        NO  YES AL                              CONNECT                     NO  YES AL                              RESOURCE                    NO  YES 4 rows selected.

All roles show as default therefore all privileges that were granted to a role can now be executed not only directly.

I hope this helps.

Please feel free to leave your questions or suggest improvements to this section.