Back
Some issues with password protected roles
This short article describes an issue with password protected roles and like a previous paper about clear text password leakage on SQL*Net when changing a users
password this paper also shows that SQL*Net sends the password in clear text when a SET ROLE command is issued. First let's look at an issue with password
protected roles.
First we connect as sys and create a test user. This user has only create session privilege to keep things simple:
Next create a password protected role and grant some arbitrary privilege to it. In this case I will use a select privilege on the dictionary view DBA_USERS. let's do this:
Next grant this password protected role to our test user ROLE_TEST and also alter the user so that he has no default roles set when he connects:
Connect as ROLE_TEST and make sure that no roles are set.
Next try to select from SYS.DBA_USERS just to prove we do not have access to it:
Now finally we set the password protected role and check that it is enabled:
OK now test the select again from SYS.DBA_USERS and show that we have this privilege now:
OK, that works as expected. Now let's show how the password protected role can be bypassed. First we will create a second
role that is not password protected.
Grant the password protected role to this new role and then grant the new role to our test user ROLE_TEST and again make sure
he has not got any default roles set:
Connect as ROLE_TEST and check that no roles are set yet:
Now set the role that is not password protected, obviously no password is used.
Check now what roles are set. You will see that the password protected role has been set as well and we didn't need to
supply the password for it!
Finally just check we can use our select privilege.
Hmmmmmmm. This is probably the behavior Oracle intended but not what I expected. If a role is password protected
then surely you should not be able to bypass giving the password. Using roles with passwords and not allowing any user
to have any roles set by default has long been a good way to control privileges. There is a downside to this though as
the application needs to send the "SET ROLE {BLAH} IDENTIFIED BY {BLAH}" command to the RDBMS. This means that the
password is usually embedded in the application code and also that at least one developer knows it. There are ways to
hide the password by splitting it up or obfuscating it in the binary but the idea is flawed. The more modern application roles
method is a better solution.
One more observation with this is that like issuing an ALTER USER command the password when passed via the SET ROLE command is
sent in clear text to the server. This can be seen using SQL*Net trace. I set the following values in my server $ORACLE_HOME/network/admin/sqlnet.ora
file:
Testing with the following commands in SQL*Plus:
results in the following entries in the SQL*Net trace file.
This is the same issue as covered in the ALTER USER paper Passwords in clear text.
Basically if you use the SET ROLE commands then you will need to ensure that the network cannot be sniffed to avoid giving out passwords (This is assuming you have protected the
password used in the application binary as well). The Oracle Advanced Security Option or something free like ssh could be used.
Going back to the password protected role bypass issue. If you want to use password protected roles you need to ensure two things:
In this case as you can see ROLE_TEST cannot grant this role to anyone else. It also may be prudent to check if a recipient of a password
protected role has the CREATE ROLE privilege. If they did and the ADMIN_OPTION was yes they have then the option of just creating a wrapper
role.
As you can see the non password role is shown as a grantee for the password protected one.
OK, that's it, remember that if you use password protected roles to ensure they cannot be sniffed or bypassed.
SQL> connect sys/change_on_install@sans as sysdba
Connected.
SQL> create user role_test identified by role_test;
User created.
SQL> grant create session to role_test;
Grant succeeded.
SQL>
SQL> create role pwd_role identified by pwd_role;
Role created.
SQL> grant select on dba_users to pwd_role;
Grant succeeded.
SQL>
SQL> grant pwd_role to role_test;
Grant succeeded.
SQL> alter user role_test default role none;
User altered.
SQL>
SQL> connect role_test/role_test@sans
Connected.
SQL> select * from session_roles;
no rows selected
SQL> select * from user_role_privs;
USERNAME GRANTED_ROLE ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
ROLE_TEST PWD_ROLE NO NO NO
1 row selected.
SQL>
SQL> select username from dba_users
2 where rownum=1;
select username from dba_users
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
SQL> set role pwd_role identified by pwd_role;
Role set.
SQL> select * from session_roles;
ROLE
------------------------------
PWD_ROLE
1 row selected.
SQL>
SQL> -- test our select again
SQL> select username from sys.dba_users
2 where username='ROLE_TEST';
USERNAME
------------------------------
ROLE_TEST
1 row selected.
SQL> -- OK that works fine
SQL> connect sys/change_on_install@sans as sysdba
Connected.
SQL> create role non_pwd_role;
Role created.
SQL>
SQL> grant pwd_role to non_pwd_role;
Grant succeeded.
SQL> grant non_pwd_role to role_test;
Grant succeeded.
SQL> alter user role_test default role none;
User altered.
SQL>
SQL> connect role_test/role_test@sans
Connected.
SQL> select * from session_roles;
no rows selected
SQL> select * from user_role_privs;
USERNAME GRANTED_ROLE ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
ROLE_TEST NON_PWD_ROLE NO NO NO
ROLE_TEST PWD_ROLE NO NO NO
2 rows selected.
SQL>
SQL> set role non_pwd_role;
Role set.
SQL>
SQL> select * from session_roles;
ROLE
------------------------------
NON_PWD_ROLE
PWD_ROLE
2 rows selected.
SQL>
SQL> select username from sys.dba_users
2 where username='ROLE_TEST';
USERNAME
------------------------------
ROLE_TEST
1 row selected.
SQL> -- excuse me!!
TRACE_FILE_SERVER=role.trc
TRACE_DIRECTORY_SERVER=c:\temp
TRACE_LEVEL_SERVER=SUPPORT
Connected to:
Personal Oracle9i Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> select * from session_roles;
no rows selected
SQL> set role pwd_role identified by pwd_role;
Role set.
SQL>
[13-MAR-2004 22:47:41:327] nsprecv: 00 00 00 00 28 73 65 74 |....(set|
[13-MAR-2004 22:47:41:327] nsprecv: 20 72 6F 6C 65 20 70 77 |.role.pw|
[13-MAR-2004 22:47:41:327] nsprecv: 64 5F 72 6F 6C 65 20 69 |d_role.i|
[13-MAR-2004 22:47:41:327] nsprecv: 64 65 6E 74 69 66 69 65 |dentifie|
[13-MAR-2004 22:47:41:327] nsprecv: 64 20 62 79 20 70 77 64 |d.by.pwd|
[13-MAR-2004 22:47:41:327] nsprecv: 5F 72 6F 6C 65 01 00 00 |_role...|
[13-MAR-2004 22:47:41:327] nsprecv: 00 01 00 00 00 00 00 00 |........|
Connected to:
Personal Oracle9i Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> desc dba_role_privs
Name Null? Type
----------------------------------------- -------- ----------------------------
GRANTEE VARCHAR2(30)
GRANTED_ROLE NOT NULL VARCHAR2(30)
ADMIN_OPTION VARCHAR2(3)
DEFAULT_ROLE VARCHAR2(3)
SQL> select * from dba_role_privs
2 where granted_role='PWD_ROLE';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
SYS PWD_ROLE YES YES
ROLE_TEST PWD_ROLE NO NO
NON_PWD_ROLE PWD_ROLE NO YES
SQL>
SQL> select p.grantee,p.granted_role,p.admin_option
2 from dba_role_privs p,
3 dba_roles r
4 where exists (select 'x'
5 from dba_roles d
6 where d.password_required='YES'
7 and d.role=p.granted_role)
8 and p.grantee=r.role
SQL> /
GRANTEE GRANTED_ROLE ADM
------------------------------ ------------------------------ ---
NON_PWD_ROLE PWD_ROLE NO
SQL>
Back