The Oracle 10g database offers many security technologies to safeguard your data. You have undoubtedly heard of the Advanced Security Option, Virtual Private Database, and perhaps even Oracle Label Security. In addition to these technologies, stored data encryption and advanced auditing techniques help to further strengthen one’s security posture. My book, Effective Oracle Database 10g Security By Design (McGraw-Hill/Osborne 2004), details many examples of how to build a defense in depth structure to secure your data.

One of the least talked-about mechanisms, Secure Application Roles (SARs), is an excellent tool for preventing unwanted data access, and is the focus of this article.

Secure Application Roles

Secure application roles act as database object and system privilege guardians. These are normal database roles in the sense that object and system privileges are granted to the roles as opposed to being granted to users. Even other database roles can be granted to SARs, thus making it more convenient to administer the privilege inheritance model.

Normally, a user has a set of roles granted to them. By default, some are enabled while others are disabled. Using the “SET ROLE” command, a user or application can ask the database to enable a role or set or roles. SARs, however, differ from standard roles in two significant ways.

First, the roles can only be enabled from within a single, defined PL/SQL program. When the SET ROLE command is issued, the database checks the environment call stack to ensure that the SET ROLE command is being executed from within the defined PL/SQL program. (SARs are, therefore, similar to Application Context, which also restricts manipulations to a uniquely-defined PL/SQL program.) The role of the program (pun intended) is to verify and validate that everything is as it should be before the role is set. Once the role is set, the user will have access to all the privileges granted to the role. Running through a list of checks prior to setting the role (and thus privileges) is a good idea.

The second difference between SARs and standard database roles is that the secure application roles do not have to be granted directly to the database users who will use them. If the user has execute privileges on the PL/SQL program defined to activate the role, then they may get the role. I am using the word “may,” since the point to having the role activated by a procedure is to do so only selectively after certain security checks have been performed.

Secure roles are predominantly used to ensure that users are operating in a least-privilege environment. For example, a user accessing a database directly should not have the same privilege set as when they are accessing the database through a Web application. Many security policies are written to prevent the user from accessing the database through random or ad-hoc means. Secure application roles are an excellent tool for facilitating such requirements, as the PL/SQL program can be used to detect the access path and then, and only then, enable the appropriate role and subsequent privileges.

Setting the Roles

Implementing a secure application role requires the administrator to develop a PL/SQL program to make the SET ROLE call. The procedure has a significant requisite: it has to be created with the Invoker Rights mode.

Definer and Invoker Rights

Just for review, 10g supports two types of PL/SQL modes: Definer Rights programs and Invoker Rights. The Definer Rights model is the default mode for PL/SQL programs. This mode allows the PL/SQL program to be created in a schema that has all the necessary object privileges required for the program to execute. However, the invoker of the procedure does not require privileges on the objects the program touches. The invoker only requires the EXECUTE privilege on the PL/SQL program. This allows a developer to shield access to critical underlying database tables and procedures and ensures guided access to those objects by way of the procedure(s).

The Invoker Rights model is the opposite of Definer Rights. It requires the invoker of the PL/SQL program to have privileges on all the underlying database objects that the program touches. In a sense, this model defers the privilege checking to runtime. A program is considered an Invoker Rights program when the string, AUTHID CURRENT_USER is place after the program’s definition.

One of the greatest benefits of the Invoker Rights program is that it allows the use of privileges granted to database roles. Definer Rights programs disable all roles during execution; therefore, with Definer Rights, any privileges granted to any roles are lost during compilation and execution of the PL/SQL program. If you are heavily dependent on roles for privilege management and you are writing PL/SQL programs, then Invoker Rights is an appetizing option to consider.

Defining Secure Application Roles

Creating a SAR is the natural first step to using them. The syntax is similar to creating a standard role, but an IDENTIFIED USING <PL/SQL program> is added, as seen here:

CREATE ROLE sar_role1 IDENTIFIED USING sec_mgr.sar_guard;

The program unit defined here is SAR_GUARD, and exists within the SEC_MGR schema. This could be a function, procedure, or package — the database does not care. The program does not even have to exist prior to issuing the previous statement to create the role. After you create a role, you can immediately assign privileges to that role. Normally, you would next grant the role to your users. As mentioned before, you do not have to do this with secure application roles. While you don't have to grant the roles to the users, I often do this anyway as it allows me to later see who has access to the role’s privileges by checking the data dictionary to see who has been granted the role.

Enabling the Secure Application Role

Inside the program defined in the role creation DDL, the administrator/developer places an execute call to the DBMS_SESSION.SET_ROLE procedure to enable the database role. (Security checks and validations are to be done prior to making this call.)

In the following example, the SAR_MGR.SAR_GUARD program performs a check to validate that the application has passed a secure token (or password), that the database session is coming from a specific IP address, and the connection has been made through the proxy user account SHARED_CONN_POOL_SCHEMA.

CREATE OR REPLACE PACKAGE sar_guard AUTHID CURRENT_USER
AS
PROCEDURE set_role (p_key IN VARCHAR2 DEFAULT NULL);
END;
/
CREATE OR REPLACE PACKAGE BODY sar_guard
AS
----------------------------------------------------------
FUNCTION isvalidpassword (p_pass1 IN VARCHAR2)
RETURN BOOLEAN
AS
l_password VARCHAR2 (20) := 'secretRolePassword';
BEGIN
RETURN (p_pass1 = l_password);
END;
----------------------------------------------------------
FUNCTION isauthorizedipadress
RETURN BOOLEAN
AS
BEGIN
RETURN (SYS_CONTEXT ('USERENV', 'IP_ADDRESS') =
'127.0.0.1');
END;
----------------------------------------------------------
FUNCTION isconnectedthroughapplication
RETURN BOOLEAN
AS
BEGIN
-- check user against list of valid users
RETURN (SYS_CONTEXT ('USERENV', 'PROXY_USER') =
'SHARED_CONN_POOL_SCHEMA');
END;
----------------------------------------------------------
PROCEDURE set_role (p_key IN VARCHAR2 DEFAULT NULL)
AS
BEGIN
IF ( isValidPassword (p_key)
AND isConnectedThroughApplication
AND isAuthorizedIPAdress)
THEN
DBMS_SESSION.set_role ('sar_role1');
END IF;
EXCEPTION
WHEN OTHERS
THEN
-- Fail silently. Potentially write audit to
-- DB table or file.
NULL;
END;
----------------------------------------------------------
END;
/

Any one of these single checks may not be sufficient. Using all of them together is a good defense-in-depth strategy, as it forces a would-be hacker to spoof or bypass multiple factors to gain access to the role.

Enabling the role SAR_ROLE1 simply involves executing the SET_ROLE procedure previously mentioned. This now only requires execute privileges on the SAR_GUARD package. If all the checks are satisfied, the procedure makes the SET ROLE call, and enables the role.

Note that if the package referenced any other database objects, the user would also have to have access to those objects since the package runs in Invoker Rights mode.

The following code shows how the role would be enabled. (The actual security checks have been removed to allow this example to work through SQL*Plus. The schema executing the code is displayed to the let of the “>”.)

sec_mgr> GRANT EXECUTE ON sar_guard TO scott;
Grant succeeded.
sec_mgr> CONN scott/tiger
Connected.
scott> -- Show current roles
scott> SELECT * FROM session_roles;
ROLE
------------------------------
CONNECT
RESOURCE
scott> -- Enable SAR
scott> EXEC SEC_MGR.sar_guard.set_role('secretRolePassword');
PL/SQL procedure successfully completed.
scott> SELECT * FROM session_roles;
ROLE
------------------------------
SAR_ROLE1

Tips, Tricks, and Gotchas

There are a few things you should consider when using SARs.

SET ROLE

You may have noticed in the previous example that the SET ROLE call in the database enables the roles passed as a parameter to it. Be aware of this, because it may inadvertently disable other roles that may have already been set. Notice that the default CONNECT and RESOURCE roles are disabled after the invocation of the SAR_GUARD.SET_ROLE procedure.

To ensure this does not happen, you will have to build a list of all currently enabled roles and then add the new role, passing the entire string to the SET ROLE command. The following code shows one way of doing this:

PROCEDURE enable_role (p_role IN VARCHAR2)
AS
l_role_list VARCHAR2 (2000) := UPPER (p_role);
BEGIN
-- Enable all other current roles
FOR rec IN (SELECT ROLE
FROM session_roles)
LOOP
l_role_list := l_role_list || ', ' || rec.ROLE;
END LOOP;
-- Reset the roles.
DBMS_SESSION.set_role (l_role_list);
END;

The caveat with the above procedure is that it will not work with other SARs or password protected roles.

Application-only Privileges

If the role is designed to restrict privilege access to an application, then the goal is simple: Create tests that can determine whether or not the user is accessing the data by way of application.

Generally, an application authentication mechanism has to be developed. Using a password to authenticate the application, as seen in the previous example, is one way to do this. Checking how the user connected and from where they connected may also validate that the user is accessing the database from the application running on the application server machine. This is the defense-in-depth strategy at work.

Automatic Role Setup

Unfortunately, SARs cannot be enabled automatically by the database. You can grant the role to users by default, but this defeats the purpose of the “secure” role since the user will always have the role, and, thus, the privileges. You might want to use a database logon trigger, but this would not work. While the code may compile and appear to execute, the role(s) is not enabled.

The only way to enable the SAR is to call it directly from your application before you need the privileges that the role provides. This is usually done in a constructor program that the application calls at the beginning of each user session.

Connection Pools

SARs are great for limiting privileges when using shared database connections often seen with connection pools. With SARs, the initial connection would have the SAR disabled. Then, the application would execute the PL/SQL to enable the role, and, thus, enable the privileges. It is popular to use this approach on these shared connections to provide separate privileges for separate users. The correct method for SARs, then, is to ensure you disable the SAR when a connection is returned to the connection pool so that the privileges do not leak from user to user.

Wrap the Code

As with many security-related PL/SQL programs, it is a very good idea to wrap the code using the Oracle-provided wrap programs. This will ensure that a privileged (and malicious) user does not retrieve your security algorithms by fetching the source code from the SOURCE$ table, which can be accessed from the ALL_SOURCE, USER_SOURCE and DBA_SOURCE views.

This is not bullet-proof, but it is nonetheless a good idea to obfuscate the checks your SAR will be performing.

Audit Invocations

Another nice feature of SARs is that you can audit how the program executes that sets the role. Standard database auditing will do this for you.

AUDIT EXECUTE ON sec_mgr.sar_guard BY SESSION WHENEVER
SUCCESSFUL;

Additionally, you can augment the code to audit the data yourself. This is especially valuable when setting the role fails because an audit may indicate why it failed as well as whether the failure is the result of an unauthorized person trying to pry into your data.

Fail Silently

The final tip I’ll leave with you is to “fail silently.” An often-used hacker technique is to purposefully cause an application or operation to fail, and when the application or operation does so, it forfeits a lot of valuable information.

In the SAR_GUARD example previously cited, the exception handling is in place to prevent any failures from reaching the user. In case you do want to know why the program failed (such as when you are debugging), you can write the failure messages to an auxiliary database table or even to the file system (do not pass the data to the user or application).

Conclusion

Secure application roles are a powerful layer of defense that should be employed with your database applications whenever possible.

The roles can be safely guarded by anything you can program from PL/SQL including the time of day, network information, database user information, LDAP queries, and so on. Don’t forget that PL/SQL can call Java and Web services, too. This enables you to make “Go-NoGo” decisions on roles on practically anything that you can think of — or at least code.

--

David Knox is the chief engineer for Oracle’s Information Assurance Center and is one of the premier educators worldwide on Oracle security. While at Oracle he has worked on wide-ranging security programs for various customers including the U.S. Department of Defense, intelligence agencies, financial services companies, and healthcare organizations.