Scenario: Security Human Resources (HR) Demonstration of Employee Information

This section presents an example policy that provides a high-level overview of Real Application Security. It is a simple scenario aimed at explaining the basic Real Application Security concepts. You should be familiar with the following concepts, introduced in “Data Security Concepts Used in Real Application Security”:

• Principals – application users and application roles

• Security classes and application privileges

• Access control lists and entries (ACLs and ACEs)

• Data security policy

This same scenario appears throughout the book, to illustrate different components of Real Application Security. It is also described in detail in Real Application Security HR Demo and Real Application Security HR Demo Files to demonstrate how to use advanced concepts of Real Application Security to handle a more complex policy.

This section includes the following topics:

• Basic Security HR Demo Scenario: Description and Security Requirements

• Basic HR Scenario: Implementation Overview

Basic Security HR Demo Scenario: Description and Security Requirements

Susan Mavris (SMAVRIS) is an employee in the Human Resources department. Her job title is Human Resources Representative. In this capacity, she is in charge of managing the human resources information for all employees, including department 60 (IT). She can view and update all the employee records, including the SALARY column.

David Austin (DAUSTIN) is an employee in the IT department. His job title is Assistant Department Manager. In this capacity, he can view employee records in the IT department, but he cannot view the SALARY column, except for his own salary record.

Secure authorization requires defining which application users and application roles can have access to which data, to perform which kinds of operations. These three security dimensions must be defined: protected data, principals, and application privileges. (see “About Data Security with Oracle Database Real Application Security”).

In this basic scenario:

• The data to be protected is employee information and it is protected in three ways:

  • Access to an employee’s own record, including the SALARY column.

  • Access to all the records in the IT department, excluding the SALARY column.

  • Access to all employee records, including the SALARY column.

• Users are allowed access to employee data in the following ways:

  • Each user can view their own record, including the SALARY column.

  • Application user DAUSTIN in his role as Assistant Department Manager is allowed to view all the records in the IT department, excluding the SALARY column.

  • Application user SMAVRIS in her role as human-resources representative is allowed to view and update all employee records, including the SALARY column.

• Database role DB_EMP is created and granted SELECT, INSERT, UPDATE, and DELETE privileges on HR.EMPLOYEES.

• Application roles are created as follows:

  • EMPLOYEE role is granted to both application users DAUSTIN and SMAVRIS. Database role DB_EMP is granted to EMPLOYEE role.

  • IT_ENGINEER role is granted to only application user DAUSTIN. Database role DB_EMP is granted to IT_ENGINEER role.

  • HR_REPRESENTATIVE role is granted to only application user SMAVRIS. Database role DB_EMP is granted to HR_REPRESENTATIVE role.

• The VIEW_SALARY application privilege is created to control access to the SALARY column. The HR_PRIVILEGES security class is created in which to scope the VIEW_SALARY application privilege.

• ACLs are created to define the degree of access to employee records in the following ways:

  • 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 data 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.

Basic HR Scenario: Implementation Overview

To implement the basic human-resources security scenario, in addition to identifying the protected data, the principals, and the application privileges, you must define the following:

• A database user as the Real Application Security Administrator and then connect as the Real Application Security Administrator to create the components.

• How the principals connect with the database to access the data.

• The access control lists (ACLs) that grant the application privilege and any database privileges to the principals.

• A data security policy that associates the ACLs with the particular data (rows) that the principals need to access.

In this basic scenario, application users SMAVRIS and DAUSTIN connect to the database directly as the principals.

The application user account that is created for application users SMAVRIS and DAUSTIN are principals in this scenario. Each application user account is granted application roles that, ultimately, has the SELECT privilege on the database table that contains the employee information. The application role is a principal in this scenario.

A database role, DB_EMP serves as intermediary between the application role and the database privilege because database privileges can be granted only to database users and roles. That is, the necessary database privileges are granted to a database role, and that role is granted to each application role (the principal).

The database SELECT privilege applies to the entire table. The principal must also be granted an Real Application Security application privilege such as the DML SELECT privilege, which can be restricted to certain rows of the database table. This restriction is implemented using an access control list (ACL) and a data security policy.

The HR scenario requires the following components for the security model:

• Protected data: Employee information is stored in the table EMPLOYEES of the sample database schema HR (delivered with Oracle Database).

• Application role: Application roles, EMPLOYEE, IT_ENGINEER, and HR_REPRESENTATIVE are created for performing tasks. The application roles are defined with the XS_PRINCIPAL.CREATE_ROLE procedure.

• Application user: Application users, SMAVRIS and DAUSTIN, are created and defined. SMAVRIS is granted the application roles EMPLOYEE and HR_REPRESENTATIVE. DAUSTIN is granted the application roles EMPLOYEE and IT_ENGINEER.

• Database access: Application users SMAVRIS and DAUSTIN are given a database password for direct database login. In order to grant SELECT, INSERT, UPDATE, and DELETE privileges on table EMPLOYEES to application roles EMPLOYEE, IT_ENGINEER, HR_REPRESENTATIVE a database role, DB_EMP, is created and granted these database privileges. The application roles are then granted this database role.

• Application Privilege: A single security class, HR_PRIVILEGES, is created which defines a single custom application privilege, VIEW_SALARY. Through inheritance, the predefined application privilege SELECT is also available in this security class. These application privileges will be used in connection with a data security policy to allow read access to employee information. The security class is created by the XS.SECURITY_CLASS.CREATE_SECURITY_CLASS procedure.

• ACL: The SELECT and VIEW_SALARY privileges are granted to application role EMPLOYEE by the access control list (ACL), EMP_ACL that is created by XS_ACL.CREATE_ACL procedure. The SELECT privilege is granted to application role IT_ENGINEER by the ACL, IT_ACL that is created by XS_ACL.CREATE_ACL procedure. The SELECT, INSERT, UPDATE, and DELETE privileges are granted to application role HR_REPRESENTATIVE by the ACL, HR_ACL that is created by XS_ACL.CREATE_ACL procedure to view and update all employee’s records, and granting the VIEW_SALARY application privilege to view the SALARY column. .

• Data Security Policy: The data security policy is defined and created with the XS_DATA_SECURITY.CREATE_POLICY procedure. This data security policy defines three data realms (an employee’s own record realm that can view the realm including the SALARY column, all the records in the IT department realm that can view the IT department excluding the SALARY column, and all the employee records realm that can view the realm including the SALARY column) and a column constraint. The data security policy associates the ACLs EMP_ACL, IT_ACL, and HR_ACL with its respective data realm.

Introducing this example in this chapter provides an overview of the requirements for implementing a policy using Real Application Security. Actual implementation of these tasks requires a systematic understanding of all the Real Application Security concepts introduced in this chapter, and further discussed in subsequent chapters. The complete example, including implementation details, appears in “Real Application Security: Putting It All Together”.