What Each Script Does

The Security HR demo use case runs the following set of PL/SQL scripts to set up components and run the demo:

• hrdemo_setup.sql: sets up the demo components by:

  • Creating a database user as the Real Application Security Administrator and then connecting as the Real Application Security Administrator to create the components.

  • Creating a database role, DB_EMP.

  • Creating an IT application user, DAUSTIN.

  • Creating an HR application user, SMAVRIS.

  • Creating application roles: employee, it_engineer, and hr_representative, and then granting the database role DB_EMP to each of these application roles.

  • Granting application roles employee and it_engineer to application user DAUSTIN.

  • Granting application roles employee and hr_representative to application user SMAVRIS.

  • Creating the VIEW_SALARY privilege and creating the hr_privileges security class in which to scope the privilege.

  • Creating three ACLs: EMP_ACL, IT_ACL, and HR_ACL, in which:

    • EMP_ACL grants the employee role the SELECT database privilege and VIEW_SALARY application privilege to view an employee’s own record, including the SALARY column.

    • IT_ACL grants the it_engineer role only the SELECT database privilege to view the employee records in the IT department, but it does not grant the VIEW_SALARY privilege that is required for access to the SALARY column.

    • HR_ACL grants the hr_representative role SELECT, INSERT, UPDATE, and DELETE database privileges to view and update all employee’s records, and granting the VIEW_SALARY application privilege to view the SALARY column.

  • The HR demo secures the HR.EMPLOYEE table by creating and applying the data security policy, EMPLOYEES_DS, that has the following three realms and column constraint:

    • An employee’s own record realm. The ACL, EMP_ACL controls this realm, which grants application role employee privileges to access the realm, including the SALARY column.

    • All the records in the IT department realm. The ACL, IT_ACL controls this realm, which grants application role it_engineer privileges to access the realm, but excluding the SALARY column.

    • All the employee records realm. The ACL, HR_ACL controls this realm, which grants application role hr_representative privileges to access the realm, including the SALARY column.

    • A column constraint that protects the SALARY column by requiring the VIEW_SALARY privilege to view its sensitive data.

  • Validating all the objects that have been created to ensure that all configurations are correct.

  • Setting up the mid-tier related configuration by creating a DISPATCHER user, setting the password for this user, and granting the roles, XSCONNECT and xsdispatcher to this DISPATCHER user.

• hrdemo.sql: runs the demo with direct logon, demonstrating:

  • That the IT application user, DAUSTIN, can view the records in the IT department, but can only view his own salary record, and cannot update his own record.

  • That the HR application user, SMAVRIS, can view all the records, including all salary rows in the SALARY column, and can update any record.

• hrdemo_session.sql: runs the demo creating and attaching to a Real Application Security session, demonstrating:

  • Connecting as the Real Application Security Administrator and creating an application session for application user SMAVRIS and attaching to it.

  • Displaying the current user as SMAVRIS.

  • Displaying the enabled database roles as DB_EMP and application roles as employee, hr_representative, and XSPUBLIC for the current user SMAVRIS.

  • That SMAVRIS application user can view all records including all salary rows in the SALARY column.

  • Disabling the hr_representative and thus limiting application user SMAVRIS to viewing only her own employee record.

  • Enabling the hr_representative, thus allowing SMAVRIS application user to view all records, including all salary rows in the SALARY column again.

  • Detaching from the application session.

  • Destroying the application session.

• hrdemo_clean.sql: performs a cleanup operation that removes: application roles, application users, ACLs, the data security policy, the database role, the Real Application Security administrative user, and the mid-tier dispatcher user.

• hrdemo.java: runs the HR Demo using the Java interface.

“Setting Up the Security HR Demo Components” describes in more detail how each of the Real Application Security components is created along with performing some other important tasks.