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:
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>
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:
SQL> create role pwd_role identified by pwd_role; Role created. SQL> grant select on dba_users to pwd_role; Grant succeeded. SQL>
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:
SQL> grant pwd_role to role_test; Grant succeeded. SQL> alter user role_test default role none; User altered. SQL>
Connect as ROLE_TEST and make sure that no roles are set.
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>
Next try to select from SYS.DBA_USERS just to prove we do not have access to it:
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>
Now finally we set the password protected role and check that it is enabled:
SQL> set role pwd_role identified by pwd_role; Role set. SQL> select * from session_roles; ROLE ------------------------------ PWD_ROLE 1 row selected. SQL>
OK now test the select again from SYS.DBA_USERS and show that we have this privilege now:
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
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.
SQL> connect sys/change_on_install@sans as sysdba Connected. SQL> create role non_pwd_role; Role created. SQL>
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:
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>
Connect as ROLE_TEST and check that no roles are set yet:
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>
Now set the role that is not password protected, obviously no password is used.
SQL> set role non_pwd_role; Role set. SQL>
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!
SQL> select * from session_roles; ROLE ------------------------------ NON_PWD_ROLE PWD_ROLE 2 rows selected. SQL>
Finally just check we can use our select privilege.
SQL> select username from sys.dba_users 2 where username='ROLE_TEST'; USERNAME ------------------------------ ROLE_TEST 1 row selected. SQL> -- excuse me!!
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 tohide 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:
TRACE_FILE_SERVER=role.trc TRACE_DIRECTORY_SERVER=c:\temp TRACE_LEVEL_SERVER=SUPPORT
Testing with the following commands in SQL*Plus:
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>
results in the following entries in the SQL*Net trace file.
[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 |........|
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:
- That the password protected role cannot be granted on by the recipient of the
role, otherwise he could grant it to any other role he had access to and use the
same technique as above to gain the privileges without the password. For example:
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>
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.
- The other thing to check is for password protected roles that have been granted
to non password protected roles. If you find any revoke the password protected role
from the non password role. The following SQL illustrates:
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>
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.
Back
