Granting password protected role to another role problem in Oracle

Question

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"

Answer 1

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::=

grant_system_privileges::=

grantee_identified_by:

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.

Answer 2

You cannot grant a password-protected Role to another Role.

Consider this (which doesn't work):

create role ROLE_1 identified by abc123 ; 
create role ROLE_2 identified by bcd234 ; 
grant ROLE_2 to ROLE_1 ; 

set ROLE ROLE_1 identified by abc123 ; 

Question: How do you provide the password for ROLE_2? It has to have one, because it's password-protected, but the SET ROLE syntax doesn't provide for it.
That's why you can't do it.

The same applies to granting Roles by default, "setting" them automatically at login. Again, where would the password come from? That's why you can't do that either.

It's been this way since at least 11.1.