Password-Protected Roles

Oracle created password-protected roles to help meet the security challenges just presented. Password-protected roles can’t be enabled unless the password is provided. Thus, you can prevent users from enabling roles by protecting the role via the password. The primary use case for password-protected roles is prohibiting users from gaining privileges unless they are accessing the database from a specific application. The application knows the role’s password, but the users do not. The password is used as a way to authenticate the application to the database.

Password-Protected Role Example

A quick example shows how easy these roles are to use. To create a password-protected role, simply add “identified by <password>” to the CREATE ROLE command. For example, to create a role with a password of “secpass”:

 sec_mgr@KNOX10g> CREATE ROLE pass_protected_role_a   2    IDENTIFIED BY secpass; Role created.

The syntax is similar to that of creating a user in that the password is followed by the phrase “identified by.” The next step is to grant the role to a user. Note that you want to explicitly disable the role; otherwise, it defeats the purpose of having a password-protected role:

sec_mgr@KNOX10g> GRANT pass_protected_role_a TO scott; Grant succeeded. sec_mgr@KNOX10g> ALTER USER scott DEFAULT ROLE ALL   2    EXCEPT pass_protected_role_a; User altered. 

To enable the role, the user or application has to supply the password via the SET ROLE command or by calling the DBMS_SESSION.SET_ROLE procedure. To enable the preceding role, the user or application could issue either of the following two statements:

scott@KNOX10g> -- Set role by command line. This method scott@KNOX10g> -- is usually done when running SQL*Plus scott@KNOX10g> SET ROLE pass_protected_role_a   2    IDENTIFIED BY secpass; Role set. scott@KNOX10g> -- Set role by procedure call. This method scott@KNOX10g> -- is typically done by applications. scott@KNOX10g> BEGIN   2    DBMS_SESSION.set_role   3                   (   'pass_protected_role_A'   4                    || ' identified by secPass');   5  END;   6  / PL/SQL procedure successfully completed.

Note the (unusual) syntax in the procedure call. One might expect the role name as one parameter and the password as a second parameter. This syntax is used because the procedure appends the parameter into the SET ROLE call.

Password-Protected Roles and Proxy Authentication

One limitation with password-protected roles is that they can’t be restricted in the proxy authentication DDL statements. You can’t control (that is, prevent or allow users from enabling) password-authenticated roles when connected via proxy authentication. For example, you can’t prevent the user SCOTT, who performs a proxy authentication via the APP_PUBLIC schema, from enabling the password-protected role PASS_PROTECTED_ROLE_A:

sec_mgr@KNOX10g> CREATE USER app_public IDENTIFIED BY oa8sfd8; User created. sec_mgr@KNOX10g> ALTER USER scott   2      GRANT CONNECT THROUGH app_public   3    WITH ROLE ALL EXCEPT pass_protected_role_a;   WITH ROLE ALL EXCEPT pass_protected_role_a                        * ERROR at line 3: ORA-28168: attempted to grant password-protected role

Not only can you not prevent a user from setting the roles, you can’t allow the role to be enabled via the connection, either. The gist is that, unlike standard roles, you can’t control enabling password-protected roles when using proxy authentication:

sec_mgr@KNOX10g> -- Allow role to be enabled when sec_mgr@KNOX10g> -- proxy authenticated. sec_mgr@KNOX10g> ALTER USER scott   2     GRANT CONNECT THROUGH app_public   3   WITH ROLE pass_protected_role_a;  WITH ROLE pass_protected_role_a            * ERROR at line 3: ORA-28168: attempted to grant password-protected role

Challenges to Securing the Password

The fundamental problem with password-protected roles is in keeping the password a secret. Keep in mind that passwords are considered by some to be “weak” authentication. Choosing a strong password is just as critical for roles as it is for users.

The real challenge with role passwords comes down to four fundamental issues. First, it’s difficult to secure the passwords in code. If the passwords are stored in the application code, they may be easily viewed by anyone who can access that code. If the application is a script, then the code is never compiled and the password can be easily seen. The compilation process tends to obfuscate the code, but even doing this may not be adequate protection from programs, such as the common “strings” application found on many UNIX systems.

Having users supply the password may defeat the purpose of having a password-protected role. Recall the requirement that restricts or limits database privileges to users accessing the database only through the application. If the user knows the password, then they can enable it not only from the application, but also from outside the application. One solution to this is to obtain the password via some other method, such as reading it from an encrypted file or from a secure LDAP account.

Second, unlike user passwords that are encrypted or hashed prior to sending them over the network, role passwords may travel in the clear. This will occur if the network traffic isn’t being encrypted. Network packet readers come embedded into many applications and can be easily downloaded from the Internet. Also, setting the trace level to ADMIN (16) in the SQLNET.ORA file will capture all SQL*Net network packets. The user can then obtain the role password that has been carefully hidden in the application by simply looking in the SQL*Net trace files. Sending any sensitive traffic over the network is a bad idea and may defeat the successful enforcement of password-protected roles. The solution to this is to use network encryption. The encryption capabilities of the Oracle Networking software provide this protection.

Third, for applications that need to enable the same role, the password would have to be shared. Sharing a password among many applications can be difficult, if not impossible. The password loses its ability to remain a secret when the entire development staff knows the password. Sharing passwords among applications is not easy, practical, or secure.

Fourth, the database still has no say as to whether the privileges are enabled or disabled. The application asks the database to enable the role and the database complies. Consequently, the security resides only in the application.

Tip 

Password-protected roles are a valuable tool, but there is a better way: secure application roles.