Granting password protected role to another role problem in Oracle

oraclepasswordrole

I can grant a role to another role unless the granted role is password protected, even when I know the password. I keep getting "ORA-00990: missing or invalid privilege" The system privileges the Schema Owner of the roles has are RESOURCE, DBA and CONNECT. 

grant OBLAH_DEE IDENTIFIED BY "15gx9FR7GG" to "OBLAH_DAH"

Best Answer

First of all, your command is syntactically incorrect. It should be:

grant OBLAH_DEE to "OBLAH_DAH" IDENTIFIED BY "15gx9FR7GG";

The problem with that: you can not grant password protected roles to another role. See https://support.oracle.com/epmos/faces/DocContentDisplay?id=1663997.1 for details.

ERROR
----------------------- 
ORA-28405: cannot grant secure role to a role

...

Starting With Oracle 11.2.0.4.0 and 12c granting secure or password
protected role to any role  (normal or password protected) we raise
this expected error.

grant::=

enter image description here

grant_system_privileges::= enter image description here

grantee_identified_by:

enter image description here

As you can see in the 3rd picture, when using the IDENTIFIED BY clasue, the grantee can be only an user, but not a role. (By the way, that clasue is used for something else.) Using the regular grantee_clause, this is the error you would receive:

SQL> create role r1;

Role created.

SQL> create role r2 identified by 1;

Role created.

SQL> grant r2 to r1;
grant r2 to r1
*
ERROR at line 1:
ORA-28405: cannot grant secure role to a role

And what you tried:

SQL> grant r2 to r1 identified by 1;
grant r2 to r1 identified by 1
*
ERROR at line 1:
ORA-01920: user name 'R1' conflicts with another user or role name

As a workaround, the below parameter can be set to revert to the old behaviour:

 alter system set "_grant_secure_role"=true scope=spfile;

You must restart the database for the change to take effect.

Related Question