Proxy Authentication Alternatives

The previous architecture works in many cases, but there are situations when proxy authentication can’t be used. From a security perspective, proxy authentication is ideal because database security can be fully exploited with little or no application coding. Login triggers fire, database privileges and roles are automatically enabled, and auditing can be used to track the end user’s actions.

If proxy authentication can’t be used (both the proxy authentication requests and the EUS authentication introduce latency), the application must include directions for the database so that database security can also be used. This can be done by passing user information to the database prior to performing any database-related work within the application.

There are three popular ways to pass user information to the database: use a PL/SQL package, use the Oracle built-in Client Identifiers, and use an application context. I’ll discuss the first two ways here. Application contexts are similar to using a PL/SQL package and are covered in Chapter 9.

Application Directed Security

Recall from Chapter 4 that the Oracle implicit connection cache has a zero time associated with retrieving an already established connection from the connection pool. This is because both the physical connection and database session are already established. This connection pooling mechanism is highly desirable because it has eliminated the database connection and session creation time.

To use these connections securely, the application has to convey the user’s information to the database before any procedures or queries are executed. Additionally, the application has to reset the database state between different and subsequent user requests. Database object and system privileges will be stored in roles and selectively enabled and disabled for appropriate users. The application has to manage the process of knowing when to enable and disable roles for the users. Roles are discussed in Chapter 7.

All of this extra work is done to ensure that nothing about the previous user’s session will leak into the current user’s session. A frequent trick for storing information about a user in the database session is to use PL/SQL package variables with “getter” and “setter” functions. The package variables are private for each database session and can be easily cleared between user requests.

For this to work effectively, the web user must be bound to a database session for the duration of their request. The application will set information about the user by invoking a PL/SQL procedure. The database security functions (views, triggers, VPD, auditing, and so on) can then reference the values in the package.

A simple example shows how easy this technique can be. First, you’ll create a PL/SQL package to store the user information:

sec_mgr@KNOX10G> CREATE OR REPLACE PACKAGE user_info   2  AS   3    PROCEDURE set_name (p_name IN VARCHAR2);   4   5    FUNCTION get_name   6      RETURN VARCHAR2;   7  END;   8  / Package created. sec_mgr@KNOX10G> CREATE OR REPLACE PACKAGE BODY user_info   2  AS   3    g_name  VARCHAR2 (32767);   4   5    PROCEDURE set_name (p_name IN VARCHAR2)   6    AS   7    BEGIN   8      g_name := UPPER (p_name);   9    END;  10  11    FUNCTION get_name  12      RETURN VARCHAR2  13    AS  14    BEGIN  15      RETURN g_name;  16    END;  17  END;  18  / Package body created.

A table is created and populated based on the SCOTT.EMP table. Your application will access the table through a view that will filter the records returned based on the value in the PL/SQL package.

sec_mgr@KNOX10G> CREATE TABLE people_tab AS SELECT * FROM scott.emp; Table created. sec_mgr@KNOX10G> CREATE OR REPLACE VIEW person_view   2  AS   3    SELECT *   4      FROM people_tab   5     WHERE ename = user_info.get_name; View created.

The application is responsible for setting the user’s name prior to querying from the view. I’ll show the following examples in SQL*Plus, but the process is identical for Java or any other application.

sec_mgr@KNOX10G> -- set the user's identity sec_mgr@KNOX10G> EXEC user_info.set_name('SCOTT'); PL/SQL procedure successfully completed. sec_mgr@KNOX10G> -- get all rows for the user sec_mgr@KNOX10G> SELECT ename, job, sal   2    FROM person_view; ENAME      JOB              SAL ---------- --------- ---------- SCOTT      ANALYST         3000

The package variables are private to the database session. This is important because you have multiple database sessions connected to the same database schema. When the user request is finished, and prior to returning the connection to the connection pool, the application has to reset the values in the package:

sec_mgr@KNOX10G> -- reset all package variables sec_mgr@KNOX10G> EXEC dbms_session.reset_package; PL/SQL procedure successfully completed. sec_mgr@KNOX10G> -- show session state has been reset sec_mgr@KNOX10G> SELECT * FROM person_view; no rows selected

Object and system privileges are controlled by database roles. The application has to enable the role(s), set the user’s identity, do its work, reset the package state, and finally, disable the roles. Securing the privileges and roles is discussed in Chapter 7.

A convenient method for implementing this is to create an object that returns a user connection after calling all the initialization procedures. A complementary method would be used to reset the database state and close the connection (returning it to the connection pool). This is shown in the upcoming “Java Application Setup” section.

Application User Proxy—Client Identifiers

Oracle supports another method for conveying user identifying information to the database. This approach, known as Application User Proxy, uses something called Client Identifiers. The Client Identifier is string value that is stored in the user’s database session. Specifically, the Client Identifier is an attribute in the USERENV namespace that can be set by calling the SET_IDENTIFIER procedure in the publicly accessible DBMS_SESSION package.

The application user proxy capability was built to allow applications using connection pools to easily pass information about the real end user to the database. The Client Identifier can also be used when the user has authenticated to the database (either directly or via proxy authentication). Thus, you can use the Client Identifier for something other than just the user’s identity.

The biggest value Client Identifiers bring as opposed to using the PL/SQL package or application contexts is that the value is audited. In Chapter 8, you’ll see how to effectively audit user actions for accountability. When connecting users and applications to the database using an application-level connection pool (that is, not using proxy authentication), Client Identifiers allow the database auditing to preserve end user identity.

Using Client Identifiers

Setting and retrieving the Client Identifier is simple: you pass a string to the SET_IDENTIFIER procedure. The string can be anything, but it’s usually some useful identifying piece of information about the user, such as their username.

The following example shows how to set and retrieve the Client Identifier. You can optionally replace the view previously defined using the Client Identifier to determine the record returned.

sec_mgr@KNOX10G> EXEC dbms_session.set_identifier('SCOTT'); PL/SQL procedure successfully completed. sec_mgr@KNOX10G> COL username format a15 sec_mgr@KNOX10G> SELECT SYS_CONTEXT ('userenv',   2                      'client_identifier') username   3    FROM DUAL; USERNAME --------------- SCOTT sec_mgr@KNOX10G> -- replace view sec_mgr@KNOX10G> CREATE OR REPLACE VIEW person_view   2  AS   3    SELECT *   4      FROM people_tab   5     WHERE ename =   6             SYS_CONTEXT ('userenv',   7                          'client_identifier'); View created. sec_mgr@KNOX10G> -- get all rows for the user sec_mgr@KNOX10G> SELECT ename, job, sal   2    FROM person_view; ENAME      JOB              SAL ---------- --------- ---------- SCOTT      ANALYST         3000 

As with the PL/SQL package implementation, the application is responsible for setting the Client Identifier before any database work as well as resetting the value between requests. To clear the identifier value, invoke the CLEAR_IDENTIFIER procedure in the DBMS_SESSION package:

sec_mgr@KNOX10G> -- reset value sec_mgr@KNOX10G> EXEC dbms_session.clear_identifier; PL/SQL procedure successfully completed. sec_mgr@KNOX10G> SELECT * FROM person_view; no rows selected

Securing the Client Identifier

One particular challenge of Client Identifiers is that they can be set by anyone to anything. Recall that setting the Client Identifier is done by invoking a procedure in the DBMS_SESSION PL/SQL package. The privilege to execute the DBMS_SESSION package has been granted directly to PUBLIC, making it accessible to anyone in the database! There are good reasons for this; however, you should never base security on the Client Identifier value alone. It’s important to understand that Oracle’s decision to grant PUBLIC execute on DBMS_SESSION doesn’t create a security vulnerability. It’s our specific implementation and reliance on Client Identifiers for security that is risky.

Caution 

Don’t use Client Identifiers for security purposes until you have first secured the ability to set the value.

One approach to solving this problem is to create a wrapper procedure for setting and resetting the Client Identifier. First, revoke the execute privileges on DBMS_SESSION to PUBLIC; this has to be done as the SYS user. Next, grant execute privileges on the DBMS_SESSION to SEC_MGR.

Caution 

This is done for illustrative purposes; don’t run this on your production system(s) until you have finished reading this section and have tested on a test database!

sys@KNOX10g> REVOKE EXECUTE ON SYS.DBMS_SESSION FROM PUBLIC; Revoke succeeded. sys@KNOX10G> GRANT EXECUTE ON DBMS_SESSION TO sec_mgr; Grant succeeded. 

Next, create your wrapper package to set and reset the Client Identifier values. As illustrated here, wrapping is an effective way to implement and ensure that the least-privilege principle is being used.

sec_mgr@KNOX10G> CREATE OR REPLACE PACKAGE client_info_mgr   2  AS   3    PROCEDURE set_info (p_info IN VARCHAR2);   4   5    PROCEDURE clear_info;   6  END;   7  / Package created. sec_mgr@KNOX10G> CREATE OR REPLACE PACKAGE BODY client_info_mgr   2  AS   3    PROCEDURE set_info (p_info IN VARCHAR2)   4    AS   5    BEGIN   6      -- Optionally perform any validation   7      -- or verification prior to setting value   8      SYS.DBMS_SESSION.set_identifier (p_info);   9    END;  10  11    PROCEDURE clear_info  12    AS  13    BEGIN  14      SYS.DBMS_SESSION.clear_identifier;  15    END;  16  END;  17  / Package body created. sec_mgr@KNOX10G> -- grant execute privileges to our connection sec_mgr@KNOX10G> -- pool schema sec_mgr@KNOX10G> GRANT EXECUTE ON client_info_mgr TO app_public; Grant succeeded.

Revocation Risk

There is a caveat with this approach. While you may have strengthened the security, you have also introduced an issue with supportability. Three negative things may occur.

• The revocation of the execute privileges on DBMS_SESSION may break existing applications.

• Many applications may depend on other procedures in the DBMS_SESSION package, so you would have to either wrap every procedure needed, or re-create new, smaller packages that are subsets of DBMS_SESSION.

• Altering default grants and privileges is generally considered unsupported because it’s considered “tampering” with the Oracle internal mechanisms. Thus, this action may invalidate your support for either Oracle or some other applications.

Note the number of objects that goes invalid immediately upon the privilege revocation:

sys@KNOX10G> SELECT COUNT (*)   2    FROM all_objects   3   WHERE status = 'INVALID';   COUNT(*) ----------          0 sys@KNOX10G> REVOKE EXECUTE ON SYS.DBMS_SESSION FROM PUBLIC; Revoke succeeded. sys@KNOX10G> SELECT COUNT (*)   2    FROM all_objects   3   WHERE status = 'INVALID';   COUNT(*) ----------         24

You can view the objects that went invalid and recompile the SYS-owned objects. For objects not owned by SYS, you may need to grant direct execute privileges on DBMS_SESSION to allow them to be recompiled.

Note 

You should ensure that the direct grants to DBMS_SESSION are really needed. Perhaps some applications can use the CLIENT_INFO_MGR package instead.

I use the following code to dynamically build a script that I execute to recompile the objects. It’s good to edit and validate the script prior to executing it.

SET pages 999 SET lines 80 SET heading off SET feedback off SET termout off SPOOL recomp.sql SELECT    'Alter package SYS.'        || object_name        || ' compile;'   FROM all_objects  WHERE status = 'INVALID'    AND owner = 'SYS'    AND object_type = 'PACKAGE' UNION ALL SELECT    'Alter package SYS.'        || object_name        || ' compile body;'   FROM all_objects  WHERE status = 'INVALID'    AND owner = 'SYS'    AND object_type = 'PACKAGE BODY' UNION ALL SELECT DISTINCT    'grant execute on dbms_session to '                 || owner                 || ';'            FROM all_objects           WHERE status = 'INVALID'             AND owner != 'SYS' UNION ALL SELECT    'Alter package '        || owner        || '.'        || object_name        || ' compile body;'   FROM all_objects  WHERE status = 'INVALID'    AND owner != 'SYS'    AND object_type = 'PACKAGE BODY' UNION ALL SELECT    'Alter package SYS.'        || object_name        || ' compile body;'   FROM all_objects  WHERE status = 'INVALID'    AND owner = 'SYS'    AND object_type = 'PACKAGE BODY' / SPOOL off SET heading on SET feedback on SET termout on -- EDIT and VALIDATE script recomp.sql edit recomp.sql @recomp.sql SELECT object_name, object_type, owner   FROM all_objects  WHERE status = 'INVALID';

Client Identifiers Reinforced

If you are reluctant to revoke privileges on DBMS_SESSION, then consider using the PL/SQL security package (for example, USER_INFO) or an application context in conjunction with the Client Identifier. When doing this, you’re checking to ensure the user hasn’t modified the publicly available Client Identifier. You set the user information in both the Client Identifier and the secure PL/SQL program or application context. The Client Identifier will be audited, and you can trust the program or application context. Validating the values can be done simply. To do this, update the view you created earlier:

sec_mgr@KNOX10G> -- set username in both places sec_mgr@KNOX10G> EXEC user_info.set_name('SCOTT'); PL/SQL procedure successfully completed. sec_mgr@KNOX10G> EXEC dbms_session.set_identifier('SCOTT'); PL/SQL procedure successfully completed. sec_mgr@KNOX10G> COL client_id format a15 sec_mgr@KNOX10G> COL program_name format a15 sec_mgr@KNOX10G> -- show values are consistent sec_mgr@KNOX10G> SELECT SYS_CONTEXT ('userenv',   2                      'client_identifier')   3                                          client_id,   4         user_info.get_name program_name   5    FROM DUAL; CLIENT_ID       PROGRAM_NAME --------------- --------------- SCOTT           SCOTT sec_mgr@KNOX10G> -- Ensure Client ID and User info sec_mgr@KNOX10G> -- are consistent before returning rows sec_mgr@KNOX10G> CREATE OR REPLACE VIEW person_view   2  AS   3    SELECT *   4      FROM people_tab   5     WHERE ename = user_info.get_name   6       AND user_info.get_name =   7             SYS_CONTEXT ('userenv',   8                          'client_identifier'); View created. sec_mgr@KNOX10G> -- test database security sec_mgr@KNOX10G> SELECT ename, job, sal   2    FROM person_view; ENAME      JOB              SAL ---------- --------- ---------- SCOTT      ANALYST         3000 sec_mgr@KNOX10G> -- change client identifier sec_mgr@KNOX10G> EXEC dbms_session.set_identifier('KING'); PL/SQL procedure successfully completed. sec_mgr@KNOX10G> -- Note user cannot change user_info as sec_mgr@KNOX10G> -- it should be protected sec_mgr@KNOX10G> SELECT * FROM person_view; no rows selected

The database security is working in concert with the application security. The application has to set the values in the database. The database verifies that the values have been set and are consistent before returning any data. This same technique can be used with triggers, VPD, and secure application roles.

Leveraging Database Security with Anonymous Connection Pools

You now have an understanding of all the components needed to use the database security with “anonymous” connections. You’ll next see how to link a Java application into this database security.

Database Setup

There are a few steps to do within the database. First, grant access to the view to a password-protected database role and disable the role. This will provide the object-level access control for your view.

sec_mgr@KNOX10G> -- create synonym for View sec_mgr@KNOX10G> CREATE SYNONYM app_public.person_view   2             FOR sec_mgr.person_view; Synonym created. sec_mgr@KNOX10G> -- create a password protected role sec_mgr@KNOX10G> CREATE ROLE person_view_app_role   2    IDENTIFIED BY thisistherolepassword; Role created. sec_mgr@KNOX10G> -- grant privileges to select on view to role sec_mgr@KNOX10G> GRANT SELECT ON person_view TO person_view_app_role; Grant succeeded. sec_mgr@KNOX10G> -- grant role to connection pool (application) schema sec_mgr@KNOX10G> GRANT person_view_app_role TO app_public; Grant succeeded. sec_mgr@KNOX10G> -- disable role forcing application to set sec_mgr@KNOX10G> ALTER USER app_public DEFAULT ROLE NONE; User altered.

The most efficient way to set up the database security is to create a single procedure that performs all the steps—set the Client Identifier, set the username in the PL/SQL package, and enable the role. You are striving for efficiency, and one procedure call from the application will be faster than three.

sec_mgr@KNOX10G> CREATE OR REPLACE PROCEDURE enable_person_view (   2    p_user      IN  VARCHAR2,   3    p_password  IN  VARCHAR2)   4  AUTHID CURRENT_USER   5  AS   6  BEGIN   7    -- Set Client Identifier. Value can be audited.   8    client_info_mgr.set_info (p_user);   9    -- Set PL/SQL Package info for user.  10    -- This value can be used for fine grained access control  11    user_info.set_name (p_user);  12    -- Enable role which allows access to View  13    DBMS_SESSION.set_role  14          (   'person_view_app_role identified by '  15           || p_password);  16  END;  17  / Procedure created.

Grant execute privileges on the preceding procedure and set up a synonym for the application schema. The synonym is used so the application code doesn’t have to directly reference the SCHEMA.PROCEDURE when executing the call:

sec_mgr@KNOX10G> -- grant execute privileges to application schema sec_mgr@KNOX10G> GRANT EXECUTE ON enable_person_view TO app_public; Grant succeeded. sec_mgr@KNOX10G> -- create synonym for application sec_mgr@KNOX10G> CREATE SYNONYM app_public.enable_person_view   2             FOR sec_mgr.enable_person_view; Synonym created.

Finally, you need to create a complementary procedure that clears the database session state:

sec_mgr@KNOX10G> CREATE OR REPLACE PROCEDURE reset_state   2  AUTHID CURRENT_USER   3  AS   4  BEGIN   5    -- Clear the Client Identifier.   6    DBMS_SESSION.clear_identifier;   7    -- clear the PL/SQL package state   8    DBMS_SESSION.reset_package;   9    -- Disable roles  10    DBMS_SESSION.set_role ('none');  11  END;  12  / Procedure created. sec_mgr@KNOX10G> -- create synonym for application sec_mgr@KNOX10G> CREATE SYNONYM app_public.reset_state   2             FOR sec_mgr.reset_state; Synonym created. sec_mgr@KNOX10G> -- grant execute privileges to application role sec_mgr@KNOX10G> GRANT EXECUTE ON reset_state TO app_public; Grant succeeded.

Java Application Setup

An efficient way to handle the calls to the database for initialization and deinitialization of the database sessions is to create a new Java object. The object’s role is to setup and break down the database security for the reusable database sessions. You’ll create two methods. The first method returns a connection from that which has already been initialized; that is, the connection will be retrieved from the pool and will have executed the PL/SQL procedure ENABLE_PERSON_VIEW. The method requires the user’s name as a parameter.

The second method, the complementary method, clears the database session state and returns the connection to the pool.

package OSBD; import java.sql.*; import oracle.jdbc.pool.OracleDataSource;  public class ConnMgr  {   OracleDataSource ods;   public ConnMgr()   { // create connection pool       try {       ods = new OracleDataSource();       ods.setURL("jdbc:oracle:thin:@DKNOX:1521:KNOX10G");       ods.setUser("app_public");       ods.setPassword("tiarhpw2r");       ods.setConnectionCachingEnabled(true); // be sure set to true       java.util.Properties prop = new java.util.Properties();       prop.setProperty("InitialLimit", "3");        prop.setProperty("MinLimit", "10");       prop.setProperty("MaxLimit", "100");       ods.setConnectionCacheProperties (prop);     } catch (Exception e)    { System.out.println(e.toString()); }   }   public Connection getConnection(String userInfo)   { // return an initialized connection    Connection conn = null;       try {       conn = ods.getConnection("app_public", "tiarhpw2r");       // Initialize the database session.       // Use bind variables for performance and       // to guard against SQL Injection attacks       CallableStatement cstmt =          conn.prepareCall("{CALL enable_person_view(?,?)}");       cstmt.setString(1,userInfo);       cstmt.setString(2,"thisIsTheRolePassword");       cstmt.executeUpdate();       cstmt.close();           } catch (Exception e)    { return conn; }    return conn;   }   public void closeConnection(Connection conn)   { // reset database session and close connection     try {       // Reset the database session       CallableStatement cstmt = conn.prepareCall("{CALL reset_state}");       cstmt.close();             conn.close();     } catch (Exception e)    { System.out.println(e.toString()); }         } }

Next, test the setup for security and performance. To do this, use timings to check how long it takes to get an initialized connection. The test code creates an instance of the ConnMgr that defines the connection pool to the APP_PUBLIC user. The getConnection method returns the initialized connection. The first invocation will create your connection pool. Time this as well as a subsequent connection to a different user:

package OSBD; import java.sql.*; import oracle.jdbc.pool.OracleDataSource; public class FastConnect {   public static void main(String[] args)   {     long connectTime=0, connectionStart=0, connectionStop=0;     long connectTime2=0, connectionStart2=0, connectionStop2=0;     ConnMgr cm = new ConnMgr();     // time first connection. This connection initializes pool.     connectionStart = System.currentTimeMillis();     Connection conn = cm.getConnection("SCOTT");     connectionStop = System.currentTimeMillis();     String query = "select ename, job, sal from person_view";     try {       // show security by querying from View       Statement stmt = conn.createStatement();       ResultSet rset = stmt.executeQuery(query);       while (rset.next()) {         System.out.println("Name:   " + rset.getString(1));         System.out.println("Job:    " + rset.getString(2));         System.out.println("Salary: " + rset.getString(3));     }       stmt.close();       rset.close();       // close the connection which resets the database session       cm.closeConnection(conn);       // time subsequent connection as different user       connectionStart2 = System.currentTimeMillis();       conn = cm.getConnection("KING");       connectionStop2 = System.currentTimeMillis();       // ensure database can distinguish this new user       stmt = conn.createStatement();       rset = stmt.executeQuery(query);       while (rset.next()) {         System.out.println("Name:   " + rset.getString(1));         System.out.println("Job:    " + rset.getString(2));         System.out.println("Salary: " + rset.getString(3));       }       stmt.close();       rset.close();       cm.closeConnection(conn);     } catch (Exception e)    { System.out.println(e.toString()); }     // print timing results     connectTime = (connectionStop - connectionStart);     System.out.println("Connection time for Pool: " + connectTime + " ms.");     connectTime2 = (connectionStop2 - connectionStart2);     System.out.println("Subsequent connection time: " +                         connectTime2 + " ms.");   } } 

The results of executing this program show that the anonymous connection pool can maintain both database security and high performance (as measured by the subsequent connections from the pool):

Name:   SCOTT Job:    ANALYST Salary: 3000 Name:   KING Job:    PRESIDENT Salary: 5000 Connection time for Pool: 1071 ms. Subsequent connection time: 0 ms.