Human Resources Administration Use Case: Implementation in Java

This section describes how to verify data security related application privileges at the middle tier. This Java example is based on the Security Human Resources (HR) scenario described in “Real Application Security: Putting It All Together”. It uses the EMPLOYEES table in the sample HR schema. The example uses two Real Application Security application users DAUSTIN and SMAVRIS to illustrate Real Application Security concepts. The example can be divided into the following modules:

• Setting Up the Mid-Tier Related Configuration

• Setting up the Connection and Initializing the Middle Tier

• Setting up the Session and Authorizing with Middle-Tier API

• Running a Query on the Database

• Performing Cleanup Operations

• The main Method

Setting Up the Mid-Tier Related Configuration

To set up the mid-tier configuration involves creating a DISPATCHER user and password and granting this user the xscacfeadmin and xsessionadmin Real Application Security administrator privileges.

exec xs_principal.create_user(name=>'dispatcher', schema=>'HR');
exec sys.xs_principal.set_password('dispatcher', 'password');

exec xs_principal.grant_roles('dispatcher', 'xscacheadmin');
exec xs_principal.grant_roles('dispatcher', 'xssessionadmin');

Setting up the Connection and Initializing the Middle Tier

This example uses the setupConnection method to create the connection to the database. The setupConnection method accepts a String array as argument, where:

args[0]=Database user

args[1]=Password

args[2]=Host

This method also initializes the middle tier by calling the getSessionManager method of the oracle.security.xs.XSSecurityManager class.

public static void setupConnection(String[] args) throws Exception {
  mgrConnection =
      DriverManager.getConnection(args[2], "dispatcher", "password");

  mgr = XSSessionManager.getSessionManager(mgrConnection, 30, 2048000);

  appConnection = DriverManager.getConnection(args[2], args[0], args[1]);
}

Setting up the Session and Authorizing with Middle-Tier API

This example uses queryAsUser method to set up the session and authorize with the middle-tier checkAcl method. This example creates a session and attaches the session, and then calls the queryEmployees method. The queryEmployees method in “Running a Query on the Database” checks the ACL for the UPDATE privilege, and if TRUE, it allows the update; it checks the ACL again for the VIEW_SALARY application privilege, and if TRUE, it allows access to the SALARY column and displays all the employees records including the sensitive data in the SALARY column. Then after displaying the employees records, it detaches the session, and destroys the session.

private static void queryAsUser(String user) throws SQLException {

  System.out.println("\nQuery HR.EMPLOYEES table as user \"" + user + "\"");

  try {
    Session lws = mgr.createSession(appConnection, user, null,null);
    mgr.attachSession(appConnection, lws, null, null, null, null, null);

    queryEmployees(lws);

    mgr.detachSession(lws);
    mgr.destroySession(appConnection, lws);
  } catch (Exception e) {
    e.printStackTrace();
  }
}

Running a Query on the Database

This example uses the queryEmployees method to run a query on the HR database.

  public static void queryEmployees(Session lws) throws SQLException {

    Connection conn = lws.getConnection();
    String query =
      " select email, first_name, last_name, department_id, salary, ora_get_aclids(emp) from hr.employees emp where department_id in (40, 60, 100) order by email";

    Statement stmt = null;
    ResultSet rs = null;

    System.out.printf("  EMAIL  | FIRST_NAME | LAST_NAME  | DEPT | SALARY | UPDATE | VIEW_SALARY\n");

    try {

      stmt = conn.createStatement();
      rs = stmt.executeQuery(query);

      while (rs.next()) {

        String email = rs.getString("EMAIL");
        String first_name = rs.getString("FIRST_NAME");
        String last_name = rs.getString("LAST_NAME");
        String department_id = rs.getString("DEPARTMENT_ID");
        String salary;

        if (((OracleResultSet)rs).getAuthorizationIndicator("SALARY") == AuthorizationIndicator.NONE) {
          salary = rs.getString("SALARY");
        }
        else {
          salary = "*****";
        }

        byte[] aclRaw = rs.getBytes(6);
        String update, viewSalary;
        if (XSAccessController.checkAcl(lws, aclRaw, "UPDATE")) {
          update = "true";
        }
        else {
          update = "false";
        }

        if (XSAccessController.checkAcl(lws, aclRaw, "VIEW_SALARY")) {
          viewSalary = "true";
        }
        else {
          viewSalary = "false";
        }

        System.out.printf("%9s|%12s|%12s|%6s|%8s|%8s|%8s\n", email,
                          first_name, last_name, department_id,
                          salary, update, viewSalary);
       }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try { if (rs != null) rs.close(); } catch (Exception e) {};
      try { if (stmt != null) stmt.close(); } catch (Exception e) {};
    }
  }
}

The queryEmployees method is run for both application users DAUSTIN and SMAVRIS.

Performing Cleanup Operations

This examples uses the cleanup method for system cleanup operations.

public static void cleanupConnection() throws Exception {
  mgrConnection.close();
  appConnection.close();

}

The main Method

This section contains the main method for the Java example discussed. This section also contains the different packages that you must import to run the program.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


import java.util.ArrayList;
import java.util.List;
import oracle.jdbc.OracleDriver;
import oracle.jdbc.OracleResultSet;
import oracle.jdbc.OracleResultSet.AuthorizationIndicator;

import oracle.security.xs.Role;
import oracle.security.xs.Session;
import oracle.security.xs.XSAccessController;
import oracle.security.xs.XSSessionManager;

/**
 * HR demo java version, check data security related privilege at mid-tier
 */
public class HRDemo {

  static Connection mgrConnection = null;
  static Connection appConnection = null;
  static XSSessionManager mgr = null;
  static String user = null;

  public static void main(String[] args)  {

    try {
      DriverManager.registerDriver(new OracleDriver());


      if (args.length >=3) {
        user = args[0];
      } else {
        System.out.println("Usage HRDemo user pwd dbURL");
        System.exit(1);
      }

      setupConnection(args);

      queryAsUser("DAUSTIN");
      queryAsUser("SMAVRIS");

      cleanupConnection();

    } catch (Exception e1) {
      e1.printStackTrace();
    }
  }

1. Running the Security HR demo in Java assumes that the set up script described in “Setting Up the Security HR Demo Components” has been run to set up the Real Application Security components.

2. Compile the Java code.

   $ORACLE_HOME/jdk6/bin/javac -classpath $ORACLE_HOME/rdbms_ho/jlib/xs.jar:$ORACLE_HOME/dbjava/lib/ojdbc6.jar HRdemo.java

   Note: You must use JDK 6 with xs.jar and ojdbc6.jar, which are located in the Oracle home directory. Different jars and JDK may not work.

3. Run the Java code.

   $ORACLE_HOME/jdk6/bin/java -classpath $ORACLE_HOME/rdbms_ho/jlib/xs.jar:$ORACLE_HOME/dbjava/lib/ojdbc6.jar
   
   HRdemo db_hr db_hr jdbc:oracle:thin:@myserver:myport:mysid

Output

Running the Security HR demo in Java assumes that the set up script described in “Setting Up the Security HR Demo Components” has been run to set up the Real Application Security components. When you run the Security HR demo, results of two queries are returned.

The first query runs with application user DAUSTIN, who has application roles EMP_ROLE and IT_ROLE, so he can view employee records in the IT department, but he cannot view the SALARY column except for his own salary record. The results of the query are as follows:

Query HR.EMPLOYEES table as user "DAUSTIN"
  EMAIL  | FIRST_NAME | LAST_NAME  | DEPT | SALARY | UPDATE | VIEW_SALARY
  AHUNOLD|   Alexander|      Hunold|    60|   *****|   false|   false
   BERNST|       Bruce|       Ernst|    60|   *****|   false|   false
  DAUSTIN|       David|      Austin|    60|    4800|   false|    true
 DLORENTZ|       Diana|     Lorentz|    60|   *****|   false|   false
 VPATABAL|       Valli|   Pataballa|    60|   *****|   false|   false

Note that application user DAUSTIN can only view the SALARY column data for his own record, and no others.

The second query runs with application user SMAVRIS, who has application roles EMP_ROLE and HR_ROLE, so she can view and update all the employee records. The results of the query are as follows:

Query HR.EMPLOYEES table as user "SMAVRIS"
  EMAIL  | FIRST_NAME | LAST_NAME  | DEPT | SALARY | UPDATE | VIEW_SALARY
  AHUNOLD|   Alexander|      Hunold|    60|    9000|    true|    true
   BERNST|       Bruce|       Ernst|    60|    6000|    true|    true
  DAUSTIN|       David|      Austin|    60|    4800|    true|    true
  DFAVIET|      Daniel|      Faviet|   100|    9000|    true|    true
 DLORENTZ|       Diana|     Lorentz|    60|    4200|    true|    true
 ISCIARRA|      Ismael|     Sciarra|   100|    7700|    true|    true
    JCHEN|        John|        Chen|   100|    8200|    true|    true
  JMURMAN| Jose Manuel|       Urman|   100|    7800|    true|    true
    LPOPP|        Luis|        Popp|   100|    6900|    true|    true
 NGREENBE|       Nancy|   Greenberg|   100|   12008|    true|    true
  SMAVRIS|       Susan|      Mavris|    40|    6500|    true|    true
 VPATABAL|       Valli|   Pataballa|    60|    4800|    true|    true

Note that application user SMAVRIS can view all the employee records, including all data in the SALARY column.