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:

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:

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:

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: