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 a UKOUG conference. 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>
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.
ReplyDeleteHowever 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.