Tuesday, December 15, 2015

ORA-28405

This post is an update to an earlier one, which I have now deleted. I tested the first part of it in an Oracle 11.1.0.6 database. First I created a role which was identified by a password:
 
SQL> conn / as sysdba
Connected.
SQL> create role low identified by secret_password
  2  /
 
Role created.
 
SQL>
 
I granted the role to a user and made sure it had no default roles:
 
SQL> grant create session, low
  2  to andrew identified by reid
  3  /
 
Grant succeeded.
 
SQL> alter user andrew default role none
  2  /
 
User altered.
 
SQL>
 
I connected as the user and tried to activate the role but this failed with an ORA-01979  as I had not supplied the password:
 
SQL> conn andrew/reid
Connected.
SQL> set role low
  2  /
set role low
*
ERROR at line 1:
ORA-01979: missing or invalid password for role 'LOW'
 
SQL> select role from session_roles
  2  /
 
no rows selected
 
SQL>
 
When I supplied the password, I was able to activate the role successfully:
 
SQL> set role low identified by secret_password
  2  /
 
Role set.
 
SQL> select role from session_roles
  2  /
 
ROLE
------------------------------
LOW
 
SQL>
 
I created another role without a password:
 
SQL> conn / as sysdba
Connected.
SQL> create role high
  2  /
 
Role created.
 
SQL>
 
… granted the first role to it:
 
SQL> grant low to high
  2  /
 
Grant succeeded.
 
SQL>
 
… and granted the 2nd role to the user:
 
SQL> grant high to andrew
  2  /
 
Grant succeeded.
 
SQL>
 
I then connected as the user and activated the 2nd role. This had the effect of also activating the 1st role without needing to supply the password. This has always seemed wrong to me:
 
SQL> conn andrew/reid
Connected.
SQL> select role from session_roles
  2  /
 
no rows selected
 
SQL> set role high
  2  /
 
Role set.
 
SQL> select role from session_roles
  2  /
 
ROLE
------------------------------
HIGH
LOW
 
SQL>
 
I went to a presentation given by Simon Pane from Pythian when I was at the UKOUG conference last week. He said that this behaviour changed in Oracle 11.2.0.4 so I decided to repeat the test in a database on this version:
 
SQL> conn / as sysdba
Connected.
SQL> create role low identified by secret_password
  2  /
 
Role created.
 
SQL> grant create session, low
  2  to andrew identified by reid
  3  /
 
Grant succeeded.
 
SQL> alter user andrew default role none
  2  /
 
User altered.
 
SQL> conn andrew/reid
Connected.
SQL> set role low
  2  /
set role low
*
ERROR at line 1:
ORA-01979: missing or invalid password for role 'LOW'
 
SQL> select role from session_roles
  2  /
 
no rows selected
 
SQL> set role low identified by secret_password
  2  /
 
Role set.
 
SQL> select role from session_roles
  2  /
 
ROLE
------------------------------
LOW
 
SQL> conn / as sysdba
Connected.
SQL> create role high
  2  /
 
Role created.
 
SQL>
 
It all worked as before until I tried to grant the 1st role to the 2nd role where this failed with an ORA-28405, which seems much more sensible to me:
 
SQL> grant low to high
  2  /
grant low to high
*
ERROR at line 1:
ORA-28405: cannot grant secure role to a role
 
SQL>
 
So when I granted the 2nd role to the user:
 
SQL> grant high to andrew
  2  /
 
Grant succeeded.
 
SQL> conn andrew/reid
Connected.
SQL> select role from session_roles
  2  /
 
no rows selected
 
SQL>
 
… he was able to activate the 2nd role but the 1st one, which had the password, was not activated:
 
SQL> set role high
  2  /
 
Role set.
 
SQL> select role from session_roles
  2  /
 
ROLE
------------------------------
HIGH
 
SQL>

1 comment:

Simon Pane said...

Yes it's good to see that Oracle finally fixed that as I always considered that a "hole" in the intended design. And not necessarily for malicious by-passing of security but rather security that could "accidentally" be by-passed by the DBAs by granting nested roles.

However surprisingly I see very few implementations of role security. Which I would consider a reasonably good mechanism for preventing end users or developers from circumventing the application and connecting to the databases directly using unauthorized tools.

Nice article showing exactly the problem and Oracle's solution. And thanks for attending my presentation at UKOUG.