ExamplePutting the Pieces Together

Example—Putting the Pieces Together

It’s often helpful to show an example that uses several technology components together in a complementary way. That is the objective of this example. The context is based on a J2EE application connecting to the database from an application server. The application uses proxy authentication for identity preservation, connection pools for performance, Enterprise Users for manageability, and secure application roles for strict privilege management.

The discussion focuses on the last part—establishing privileges using the secure application role. You’ll base your security policy on four separate checks. This design is based on a limited trust model between the database and the application. In this particular example, the application is being trusted to correctly identify itself along with the acting end user.

Application Authentication

The first step in establishing privileges is to ensure the user is accessing the data objects via the application. To do this, first check the static IP address of the application server. The connection to the database will be from the application server, so you can use the USERENV application context to retrieve this. Assuming your application server has an IP address of 138.2.2.20, your check would simply be as follows. Note the code shown here is the function that will be encapsulated in your PL/SQL package that is registered as the guardian for your secure application role.

FUNCTION is_authorized_ip   RETURN BOOLEAN AS BEGIN   RETURN (sys_context ('userenv', 'ip_address') = '138.2.2.20'); END;

Next, check the user authentication path. The connection to the database is supposed to happen through a connection pool that establishes its first connection to the APP_PUBLIC schema. Your check simply verifies that this connection was made with the proxy authenticated user. Note that the APP_PUBLIC schema should require strong authentication, so it would be difficult for someone to fake this.

FUNCTION is_proxied_through_app_account   RETURN BOOLEAN BEGIN   RETURN (sys_context ('userenv', 'proxy_user') = 'APP_PUBLIC'); END;

For your third check, use the little trick that is the basis of password-protected roles—a password. Use it because it helps to enforce that the application is really your application and not some other application running from the same application server. That is, someone could deploy an application to your application server that is able to use the same connections and would have the same IP address. Force the application to pass a secure token (a password) for this very scenario. Your database procedure will take the token presented from the application and then verify it. Your function ensures they are identical:

FUNCTION is_valid_password (p_pass1 IN VARCHAR2,                              p_pass2 IN VARCHAR2)   RETURN BOOLEAN AS BEGIN   RETURN (p_pass1 = p_pass2); END;

Note that the problems presented in the password-protected roles section may also be resident here. That’s okay, because this is only one in a series of checks. Also, the Java and PL/SQL code can be obfuscated to mitigate the risk of someone viewing the source code potentially figuring out the secure token. An alternate implementation would be for both the application and the database program to obtain the password from an encrypted file on the file system.

Verifying the User

In environments where single sign-on is prevalent, there is often a concern that users will be able to access applications that they aren’t supposed to because of the single sign-on. This concern is grounded in the belief that as part of the single sign-on infrastructure, an application may not be able to control, at a user level, who is accessing the application. While this is not necessarily true at the application server, within the database, this may be a legitimate concern.

Therefore, you could keep a list of authorized users for your application. The belief is that as owner and administrator of an application and database, you want to maintain ultimate control over who is getting or not getting access. If you suspect someone is either up to no good, or perhaps just issues bad SQL constantly, and you want to prevent them from hitting your database application, you will screen them out using this check. Note that you may not be able to get the person removed from the single sign-on application environment—or maybe not even removed as a database user. As such, you need something at the application level within the database that authorizes the user.

For this task, you have a table of authorized users. You could easily have a table of unauthorized users instead. The table acts as an access control list for our application. Take the user’s name and compare it to the list of users in your table. Because your user is an Enterprise User acting within a shared schema, you have to first build a helper function. This function will return the user’s nickname by parsing the external name (distinguished name), which is available from the USERENV.

You’ll create this as a standalone function because it also has utility in other places within the database. The function takes a parameter, defaulted to the external name, which allows us to test the parsing capability. This is shown below the function. Our nickname is defined as the common name. Because the string for the external name always starts with “cn=<username>, …”, you can parse based on the following code:

sec_mgr@KNOX10g> CREATE OR REPLACE FUNCTION eus_username (   2    p_ext_name  IN  VARCHAR2   3        DEFAULT SYS_CONTEXT ('userenv',   4                             'external_name'))   5    RETURN VARCHAR2   6  AS   7  BEGIN   8    RETURN UPPER (SUBSTR (p_ext_name,   9                          4,  10                          INSTR (p_ext_name, ',')  11                          - 4));  12  END;  13  / Function created. sec_mgr@KNOX10g> -- Test function sec_mgr@KNOX10g> VAR eus_id varchar2(40) sec_mgr@KNOX10g> BEGIN   2    :eus_id :=   3         'cn=Knox,cn=users,dc=iac,dc=oracle,dc=com';   4  END;   5  / PL/SQL procedure successfully completed. sec_mgr@KNOX10g> COL name FORMAT a10 sec_mgr@KNOX10g> SELECT eus_username (:eus_id) name   2    FROM DUAL; NAME ---------- KNOX

Now that you know the user’s name, you can verify that against your lookup table of authorized users. First, create your table:

sec_mgr@KNOX10g> CREATE TABLE auth_users   2    (username VARCHAR2(30)); Table created.

Your function will check to see if the user exists in this table. If so, the function returns TRUE. The function is

FUNCTION is_authorized_user     RETURN BOOLEAN   AS    l_return_val  BOOLEAN := FALSE; BEGIN    FOR rec IN (SELECT username                   FROM auth_users                  WHERE username = eus_username)   LOOP      l_return_val := TRUE;   END LOOP;   RETURN l_return_val; END;

For inserting records into the AUTH_USERS table, use a trigger to ensure the username is stored in uppercase.

Setting the Secure Application Role

You need to create a master procedure for your application to call. The only parameter you need from your application is the secure token (password). Once you verify that everything is as it should be, set the secure application role. Note that this procedure resides in a package that has to be created with invoker rights.

  PROCEDURE set_role (p_key IN VARCHAR2 DEFAULT NULL)   AS     l_password   VARCHAR2(20) := 'HideTheRolePassword';   BEGIN     IF (is_valid_password (p_key, l_password)          AND is_authorized_user          AND is_proxied_through_app_account          AND is_authorized_ip          )       THEN          DBMS_SESSION.set_role ('sec_app_role');       END IF;    END;

Securing the Source

As with many security-based PL/SQL programs, it’s a good idea to obscure the source code for the programs that guard the secure application roles. In the preceding example, wrapping is absolutely necessary to prevent someone in the database (with the requisite privileges) from obtaining the password used in the first verification routine. Hiding the overall verification process also helps to ensure that the implementation remains secure. For example, if an attacker can determine that you are checking for a specific IP address, then they know they have to spoof that IP address.

The WRAP program in Oracle Database 10g has been enhanced to obfuscate all code, including strings. Prior to this release, strings were left in the clear. You would then have to derive the string by using functions that would construct the strings utilizing various tricks, such as referring to letters by their CHR values. Assuming you placed the preceding code in a file named Priv_Mgr.sql, you would wrap it by issuing the following:

wrap iname=Priv_Mgr.sql

The program generates an output file named Priv_Mgr.plb. Searching the .plb file for the password key, note that there are no signs of this. Listing the contents of the file also shows that the source is now secure:

C:\> grep -i password Priv_Mgr.plb C:\> cat Priv_Mgr.plb CREATE OR REPLACE PACKAGE priv_mgr wrapped a000000 b2 abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd 9 66 9e y9VKcTgEdb2qWsTj0Oeg5Vw3jNEwg7KXf8tqyi82Z7E04NkaLh78Nv39iAuguKcJCLUP2 AzC0YCFDEkETpk4twUZ90x51GSZwAKZyvma3bX12Xuqoh3kc5KjqaOhTEyJa5OycN/xjN 7DxDKKlsqy0= / CREATE OR REPLACE PACKAGE BODY priv_mgr wrapped a000000 b2 abcd abcd abcd abcd bcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd b 4d9 2b9 dPKBxn1Rob61kqiwWncuQP6yGdgwg/BeLiCMfC9Vxz+VaKY+GPHYoINi/K83qFRsYUPQo lHnIoyaDog5zkv72HWPIGyetyqUwGTT+yWDIXUrY8yt6VTfI9fZ49q2oJ+HIiKcRl4hpA DiaUoiYS0V7n6U7tppdwDJHxtr5urNm++aCI2ThgXdMUJ+9Y1 

This wrapped code is then compiled in the database. The resulting stored PL/SQL source is obfuscated even from the privileged database users who can access the stored source in DBA_SOURCE.