Missing or Invalid Password Error for a Role:
1. Problem: The user gets an ORA-01979 error when he tries to enable the role assigned to him by using the set role command. The role is protected by a password. The Error encountered by the user is given below:
Logged in as a SYS or a DBA user and create a role and then grant that role to the user:
SQL> CREATE role usrrole1 identified by abcd1;
Role created.
SQL> GRANT usrrole1 to newusr10;
Grant succeeded.
Role created.
SQL> GRANT usrrole1 to newusr10;
Grant succeeded.
SQL>
Now when the user tries to set the role without the password then he encounters the following error:
SQL> SET ROLE usrrole1;
SET ROLE usrrole1
*
ERROR at line 1:
ORA-01979: missing or invalid password for role 'USRROLE1'
SET ROLE usrrole1
*
ERROR at line 1:
ORA-01979: missing or invalid password for role 'USRROLE1'
2. Impact: The user cannot access and use the roles assigned and granted to him which is affecting his performance and production.
First to be sure that a particular role is password protected, the user can run the following select. Dba_roles is a Data dictionary view.
SQL> SELECT * from dba_roles where role='USRROLE1';
ROLE PASSWORD
------------------------------ --------
USRROLE1 YES
ROLE PASSWORD
------------------------------ --------
USRROLE1 YES
SQL>
The role is password protected. Now Login as the user newusr10 and execute the command along with the password to enable the role.
SQL> SET role usrrole1 identified by abcd1;
Role set.
Role set.
SQL>
Run a select as below to verify that the role has been enabled by the command above. Session_roles is a Data dictionary view.
SQL> SELECT * FROM session_roles;
ROLE
------------------------------
USRROLE1
SQL> SELECT * FROM session_roles;
ROLE
------------------------------
USRROLE1