Auditing an Oracle database for security issues is very important. PeteFinnigan.com provides all of the information and tools that you will need Click here for details of PeteFinnigan.com Limited's detailed Oracle database security audit service Click here for details of PeteFinnigan.com Limited's Oracle Security Training Courses
There are 62 visitors online    
Cookie Policy:We only use essential cookies on small sections of this website. For details see here.


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:

	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 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:

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