1

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"
2
  • Hey Russell, can you please review my edit and make any additional changes to your question to add more detail that you think may be relevant?
    – jcolebrand
    Dec 23, 2016 at 18:46
  • I can't see an edit?
    – Russell
    Dec 23, 2016 at 18:50

2 Answers 2

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

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.

6
  • Is this a new restriction in 12c? Because in 11g this is exactly how it was set up. The lower level role with insert and update object privileges and password protection was granted to a higher level role with additional delete privileges on objects. It too was password protected.
    – Russell
    Dec 23, 2016 at 19:13
  • @Russell This is a new restriction starting with 11.2.0.4. Added this to my answer. Dec 23, 2016 at 19:18
  • Any suggestions? Do I create an un-secure role to replicate the original secure role that I want in my cascading hierarchy? Or do I now have to just add all the object privileges into my secure role that is the elevated role. The entire purpose of the cascading roles was to reduce the amount of potential error by granting something in one role and failing to grant it in the higher level role.
    – Russell
    Dec 23, 2016 at 19:26
  • @Russell Grant the secure role directly to the user, or use the mentioned hidden parameter as a workaround. Dec 23, 2016 at 19:28
  • That is the problem sort of. The secure role is already granted to thousands of users and invoked in the application with a set_session_role. the higher level roles that are also secure are assigned to far fewer but still there are hundreds of object grants that would have to be replicated and maintained. I tried the alteration and the system said it succeeded but it is still not letting me make the grant.
    – Russell
    Dec 23, 2016 at 19:39
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.