ORA-01979 Missing or Invalid Password Error for a Role

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.

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'

SQL>  


2. Impact:  The user cannot access and use the roles assigned and granted to him which is affecting his performance and production.

3. Solution: When a user is granted a role which is password protected and he is enabling the role using the set role command then he must always provide the password which has already been set and specified. This is mandatory and if the user does not specify the password while enabling the role then he would encounter a ORA-01979 Missing or Invalid Password for a role Error.

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

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.

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