A secure role in Oracle is a role protected by a password. You can use this feature for security reasons. The idea is that you cannot set the role without knowing its password.
Lately we ran in ORA-28405 on granting roles during an application upgrade from Oracle RDBMS 220.127.116.11 to 18.104.22.168, so I got noticed for a change how Oracle manages secure roles.
Let’s suppose that role A1 is granted to a role B and role A1 is not protected by a password.
Before Oracle 22.214.171.124 a user that has a role B could set the role B only if he (or she) knows it’s password, but he (or she) could set role A1 without a password:
This was actually not exactly, how we expected it to work. To avoid the problem, we had to protect also role A1 with a password. After that the user was not able to set any of the roles A1 or B without a password.
The Oracle behavior changes in 126.96.36.199 and 12.1: our user that has role B can now only set role B, if this is a secure role. The user is not able to directly set the role A1, granted to role B:
If you now set a password for role A1 you will not be able to grant it to a secure role B anymore. You will receive:
ORA-28405: cannot grant secure role to a role
You will find bugs 17243886 und 17420290 in MOS saying that this is an expected behavior.
The documentation for Oracle Releases 11.2 and 12.1 says now: „… You cannot grant a secure role (that is, an IDENTIFIED BY role, IDENTIFIED USING role, or IDENTIFIED EXTERNALLY role) to a non-secure role. You can use the SET ROLE statement to enable the secure role for the session.“
If you would like to work with secure roles, you have to do this in a different way in Oracle RDBMS 188.8.131.52 and Oracle RDBMS 184.108.40.206/12.1:
Till 220.127.116.11 (including 18.104.22.168) you will need to setup the roles on all levels as secure roles and protect them with a password. From version 22.214.171.124 on you have to set only the roles granted directly to the user as protected roles.