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"
  • 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 '16 at 18:46
  • I can't see an edit? – Russell Dec 23 '16 at 18:50
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;
  • 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 '16 at 19:13
  • @Russell This is a new restriction starting with 11.2.0.4. Added this to my answer. – Balazs Papp Dec 23 '16 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 '16 at 19:26
  • @Russell Grant the secure role directly to the user, or use the mentioned hidden parameter as a workaround. – Balazs Papp Dec 23 '16 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 '16 at 19:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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