Real Application Security: Putting It All Together

This section puts all the Real Application Security concepts together in order to define a basic data security policy. It builds upon the HR scenario example introduced in “Scenario: Security Human Resources (HR) Demonstration of Employee Information”.

The section includes the following topic that discusses each implementation task described in the scenario with the help of an example.

This section includes the following topics:

Basic HR Scenario: Implementation Tasks

The following implementation tasks are discussed:

Connecting as User SYS to Create Real Application Security Users and Roles

To create Real Application Security users and roles, you need only to connect as user SYS.

Example: Connecting as User SYS

SQL> connect sys/&passwd as sysdba
Connected.

Creating Roles and Application Users

Creating the Database Role

Create the database role DB_EMP and grant this role the necessary table privileges. This role is used to grant the required object privileges to application users.

Creating the Application Roles

Grant the DB_EMP Database Role to the Application Roles

Grant the DB_EMP database role to the three application roles, so they each have the required object privilege to access the table.

Create the Application Users

Create application user DAUSTIN (in the IT department) and grant this user application roles EMPPLOYEE and IT_ENGINEER.

In this example:

Note:

To make logins easier, you can create the name in upper case. That way, the user can omit the quotation marks when logging in or connecting to SQL*Plus. For example:

sqlplus DAUSTIN

See Also: “Creating a Simple Application User Account” for information about how case sensitivity affects database logins for application users

Create application user SMAVRIS (in the HR department) and grant this user application roles EMPLOYEE and HR_REPRESENTATIVE.

Grant the HR User the Policy Administration Privilege ADMIN_ANY_SEC_POLICY

Grant the HR user the ADMIN_ANY_SEC_POLICY privilege.

Example: Creating the DB_EMP Role

SQL> create role db_emp;

Role created.

SQL> grant select, insert, update, delete on hr.employees to db_emp;

Grant succeeded.

Example: Creating the Application Role EMPLOYEE for Common Employees

SQL> exec sys.xs_principal.create_role(name => 'employee', enabled => true);

PL/SQL procedure successfully completed.

Example: Creating the Application Role IT_ENGINEER for the IT Department

SQL> exec sys.xs_principal.create_role(name => 'it_engineer', enabled => true);

PL/SQL procedure successfully completed.

Example: Creating the Application Role HR_REPRESENTATIVE for the HR Department

SQL> exec sys.xs_principal.create_role(name => 'hr_representative', enabled => true);

PL/SQL procedure successfully completed.

Example: Granting DB_EMP Database Role to Each Application Role

SQL> grant db_emp to employee;

Grant succeeded.

SQL> grant db_emp to it_engineer;

Grant succeeded.

SQL> grant db_emp to hr_representative;

Grant succeeded.

Example: Creating Application User DAUSTIN

SQL> exec  sys.xs_principal.create_user(name => 'daustin', schema => 'hr');

PL/SQL procedure successfully completed.

SQL> exec  sys.xs_principal.set_password('daustin', 'password');

PL/SQL procedure successfully completed.

SQL> exec  sys.xs_principal.grant_roles('daustin', 'XSCONNECT');

PL/SQL procedure successfully completed.

SQL> exec  sys.xs_principal.grant_roles('daustin', 'employee');

PL/SQL procedure successfully completed.

SQL> exec  sys.xs_principal.grant_roles('daustin', 'it_engineer');

PL/SQL procedure successfully completed.

Example: Creating Application User SMAVRIS

SQL> exec  sys.xs_principal.create_user(name => 'smavris', schema => 'hr');

PL/SQL procedure successfully completed.

SQL> exec  sys.xs_principal.set_password('smavris', 'password');

PL/SQL procedure successfully completed.

SQL> exec  sys.xs_principal.grant_roles('daustin', 'XSCONNECT');

PL/SQL procedure successfully completed.

SQL> exec  sys.xs_principal.grant_roles('smavris', 'employee');

PL/SQL procedure successfully completed.

SQL> exec  sys.xs_principal.grant_roles('smavris', 'hr_representative');

PL/SQL procedure successfully completed.

Example: Granting the HR User the Policy Administration Privilege ADMIN_ANY_SEC_POLICY

SQL> exec sys.xs_admin_util.grant_system_privilege('ADMIN_ANY_SEC_POLICY','HR');

PL/SQL procedure successfully completed.

Creating the Security Class and ACLS

Creating the Security Class

Create a security class HR_PRIVILEGES based on the predefined DML security class. HR_PRIVILEGES has a new privilege VIEW_SALARY, which controls access to the SALARY column.

Creating the ACls

Create three ACLs, EMP_ACL, IT_ACL, and HR_ACL to grant privileges for the data security policy to be defined later.

In this example:

Lines 11 through 13: Creates the EMP_ACL and grants EMPLOYEE the SELECT and VIEW_SALARY privileges.
Lines 21 through 23: Creates the IT_ACL and grants IT_ENGINEER the SELECT privileges.
Lines 30 through 33: Creates the HR_ACL and grants HR_REPRESENTATIVE the 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.

Example: Creating the HRPRIVS Security Class

SQL> declare
  2  begin
  3    xs_security_class.create_security_class(
  4      name        => 'hr_privileges',
  5      parent_list => xs$name_list('sys.dml'),
  6      priv_list   => xs$privilege_list(xs$privilege('view_salary')));
  7  end;
  8  /

PL/SQL procedure successfully completed.

Example: Creating ACLs: EMP_ACL, IT_ACL, and HR_ACL

SQL> declare
  2    aces xs$ace_list := xs$ace_list();
  3  begin
  4    aces.extend(1);
  5
  6    -- EMP_ACL: This ACL grants EMPLOYEE role the privileges to view an employee's
  7    --          own record including SALARY column.
  8    aces(1) := xs$ace_type(privilege_list => xs$name_list('select','view_salary'),
  9                           principal_name => 'employee');
 10
 11    sys.xs_acl.create_acl(name      => 'emp_acl',
 12                      ace_list  => aces,
 13                      sec_class => 'hr_privileges');
 14
 15    -- IT_ACL:  This ACL grants IT_ENGINEER the privilege to view the employee
 16    --          records in IT department, but it does not grant the VIEW_SALARY
 17    --          privilege that is required for access to SALARY column.
 18    aces(1) := xs$ace_type(privilege_list => xs$name_list('select'),
 19                           principal_name => 'it_engineer');
 20
 21    sys.xs_acl.create_acl(name      => 'it_acl',
 22                      ace_list  => aces,
 23                      sec_class => 'hr_privileges');
 24
 25    -- HR_ACL:  This ACL grants HR_REPRESENTATIVE the privileges to view and update all
 26    --          employees' records including SALARY column.
 27    aces(1):= xs$ace_type(privilege_list => xs$name_list('select', 'insert',
 28                                              'update', 'delete', 'view_salary'),
 29                          principal_name => 'hr_representative');
 30
 31    sys.xs_acl.create_acl(name      => 'hr_acl',
 32                      ace_list  => aces,
 33                      sec_class => 'hr_privileges');
 34  end;
 35  /

PL/SQL procedure successfully completed.

Creating the Data Security Policy

Create the data security policy for the EMPLOYEES table. The policy defines three data realm constraints and a column constraint that protects the SALARY column.

In this example:

Lines 7 through 23: Defines the three data realm constraints.
Lines 27 through 30: Defines the column constraint requiring the VIEW_SALARY application privilege to view the SALARY column.
Lines 32 through 35: Creates the EMPLOYEES_DS data security policy encompassing the three data realm constraints and the column constraint.

Applying the Data Security Policy to the Table

Apply the data security policy to the EMPLOYEES table.

Example: Creating the EMPLOYEES_DS Data Security Policy

SQL> declare
  2    realms   xs$realm_constraint_list := xs$realm_constraint_list();
  3    cols     xs$column_constraint_list := xs$column_constraint_list();
  4  begin
  5    realms.extend(3);
  6
  7    -- Realm #1: Only the employee's own record.
  8    --           EMPLOYEE role can view the realm including SALARY column.
  9    realms(1) := xs$realm_constraint_type(
 10      realm    => 'email = xs_sys_context(''xs$session'',''username'')',
 11      acl_list => xs$name_list('emp_acl'));
 12
 13    -- Realm #2: The records in the IT department.
 14    --           IT_ENGINEER role  can view the realm excluding SALARY column.
 15    realms(2) := xs$realm_constraint_type(
 16      realm    => 'department_id = 60',
 17      acl_list => xs$name_list('it_acl'));
 18
 19    -- Realm #3: All the records.
 20    --           HR_REPRESENTATIVE role can view and update the realm including SALARY column.
 21    realms(3) := xs$realm_constraint_type(
 22      realm    => '1 = 1',
 23      acl_list => xs$name_list('hr_acl'));
 24
 25    -- Column constraint protects SALARY column by requiring VIEW_SALARY
 26    -- privilege.
 27    cols.extend(1);
 28    cols(1) := xs$column_constraint_type(
 29      column_list => xs$list('salary'),
 30      privilege   => 'view_salary');
 31
 32    sys.xs_data_security.create_policy(
 33      name                   => 'employees_ds',
 34      realm_constraint_list  => realms,
 35      column_constraint_list => cols);
 36  end;
 37  /

PL/SQL procedure successfully completed.

Example: Applying the EMPLOYEES_DS Security Policy to the EMPLOYEES Table

SQL> begin
  2    sys.xs_data_security.apply_object_policy(
  3      policy => 'employees_ds',
  4      schema => 'hr',
  5      object =>'employees');
  6  end;
  7  /

PL/SQL procedure successfully completed.

Validating the Real Application Security Objects

After you create these Real Application Security objects, validate them to ensure they are all properly configured.

Example: Validating the Real Application Security Objects

SQL> set serveroutput on;
SQL> begin
  2    if (xs_diag.validate_workspace()) then
  3      dbms_output.put_line('All configurations are correct.');
  4    else
  5      dbms_output.put_line('Some configurations are incorrect.');
  6    end if;
  7  end;
  8  /
All configurations are correct.

PL/SQL procedure successfully completed.

SQL> -- XS$VALIDATION_TABLE contains validation errors if any.
SQL> -- Expect no rows selected.
SQL> select * from xs$validation_table order by 1, 2, 3, 4;

no rows selected

Disabling a Data Security Policy for a Table

Example: Disabling a Data Security Policy for a Table shows the complementary operation of disabling data security for table HR.EMPLOYEES.

Example: Disabling a Data Security Policy for a Table

BEGIN
  SYS.XS_DATA_SECURITY.DISABLE_OBJECT_POLICY(policy => 'EMPLOYEES_DS', schema => 'HR', object => 'EMPLOYEES');
END;
/

Running the Security HR Demo

The Security HR Demo is run in two ways:

Using direct logon first as application user DAUSTIN and later as application user SMAVRIS.

In each case, each user performs queries on the HR.EMPLOYEES table to demonstrate what each can access or cannot access to view employee records and the SALARY column. See “Running the Security HR Demo Using Direct Logon” for a description of this demonstration.
Attached to a Real Application Security session

In this demonstration, the Real Application Security Administrator creates a Real Application Security session for an application user to attach to. See “Running the Security HR Demo Attached to a Real Application Security Session” for a description of this demonstration.