Setting Up the Security HR Demo Components

Before you can create Real Application Security components, you must first connect as SYS/ user as SYSDBA.

define passwd=&1
connect sys/&passwd as sysdba

This sections includes the following topics:

• About Creating Roles and Application Users

• About Creating the Security Class and ACLs

• About Creating the Data Security Policy

• About Validating the Real Application Security Objects

• About Setting Up the Mid-Tier Related Configuration

About Creating Roles and Application Users

Create the application roles EMPLOYEE, IT_ENGINEER, and HR_REPRESENTATIVE, and the database role DB_EMP. The DB_EMP role is used to grant the required object privileges to the two application users that are created, DAUSTIN and SMAVRIS. Finally, grant the HR user the policy administration privilege, ADMIN_ANY_SEC_POLICY.

Connect as SYS/ user as SYSDBA.

define passwd=&1
connect sys/&passwd as sysdba

Create the application role EMPLOYEE for common employees.

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

Create an application role IT_ENGINEER for the IT department.

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

Create an application role HR_REPRESENTATIVE for the HR department.

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

Create the database role, DB_EMP, for object privilege grants.

create role db_emp;

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

grant db_emp to employee;
grant db_emp to it_engineer;
grant db_emp to hr_representative;

Create the application users.

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

exec  sys.xs_principal.create_user(name => 'daustin', schema => 'hr');
exec  sys.xs_principal.set_password('daustin', 'welcome1');
exec  sys.xs_principal.grant_roles('daustin', 'XSCONNECT');
exec  sys.xs_principal.grant_roles('daustin', 'employee');
exec  sys.xs_principal.grant_roles('daustin', 'it_engineer');

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

exec  sys.xs_principal.create_user(name => 'smavris', schema => 'hr');
exec  sys.xs_principal.set_password('smavris', 'welcome1');
exec  sys.xs_principal.grant_roles('smavris', 'XSCONNECT');
exec  sys.xs_principal.grant_roles('smavris', 'employee');
exec  sys.xs_principal.grant_roles('smavris', 'hr_representative');

Grant the HR user the policy administration privilege, ADMIN_ANY_SEC_POLICY.

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

About Creating the Security Class and ACLs

First, grant the necessary table privileges to the DB_EMP role.

Next, 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. Finally, create the three ACLs, EMP_ACL, IT_ACL, and HR_ACL.

Connect as the HR user.

connect hr/hr;

Grant the necessary object privileges to the DB_EMP role. This role is used to grant the required object privileges to application users.

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

declare
begin
  sys.xs_security_class.create_security_class(
    name        => 'hr_privileges',
    parent_list => xs$name_list('sys.dml'),
    priv_list   => xs$privilege_list(xs$privilege('view_salary')));
end;
/

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

declare
  aces xs$ace_list := xs$ace_list();
begin
  aces.extend(1);

  -- EMP_ACL: This ACL grants EMPLOYEE role the privileges to view an employee's
  --          own record including SALARY column.
  aces(1) := xs$ace_type(privilege_list => xs$name_list('select','view_salary'),
                         principal_name => 'employee');

  sys.xs_acl.create_acl(name      => 'emp_acl',
                    ace_list  => aces,
                    sec_class => 'hr_privileges');

  -- IT_ACL:  This ACL grants IT_ENGINEER role the privilege to view the employee
  --          records in IT department, but it does not grant the VIEW_SALARY
  --          privilege that is required for access to SALARY column.
  aces(1) := xs$ace_type(privilege_list => xs$name_list('select'),
                         principal_name => 'it_engineer');

  sys.xs_acl.create_acl(name      => 'it_acl',
                    ace_list  => aces,
                    sec_class => 'hr_privileges');

  -- HR_ACL:  This ACL grants HR_REPRESENTITIVE role the privileges to view and update all
  --          employees' records including SALARY column.
  aces(1):= xs$ace_type(privilege_list => xs$name_list('select', 'insert',
                                          'update', 'delete', 'view_salary'),
                        principal_name => 'hr_representative');

  sys.xs_acl.create_acl(name      => 'hr_acl',
                    ace_list  => aces,
                    sec_class => 'hr_privileges');
end;
/

About Creating the Data Security Policy

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

declare
  realms   xs$realm_constraint_list := xs$realm_constraint_list();
  cols     xs$column_constraint_list := xs$column_constraint_list();
begin
  realms.extend(3);

  -- Realm #1: Only the employee's own record.
  --           The EMPLOYEE role can view the realm including SALARY column.
  realms(1) := xs$realm_constraint_type(
    realm    => 'email = xs_sys_context(''xs$session'',''username'')',
    acl_list => xs$name_list('emp_acl'));

  -- Realm #2: The records in the IT department.
  --           The IT_ENGINEER role can view the realm excluding SALARY column.
  realms(2) := xs$realm_constraint_type(
    realm    => 'department_id = 60',
    acl_list => xs$name_list('it_acl'));

  -- Realm #3: All the records.
  --           The HR_REPRESENTATIVE role can view and update the realm including SALARY column.
  realms(3) := xs$realm_constraint_type(
    realm    => '1 = 1',
    acl_list => xs$name_list('hr_acl'));

  -- Column constraint protects SALARY column by requiring VIEW_SALARY
  -- privilege.
  cols.extend(1);
  cols(1) := xs$column_constraint_type(
    column_list => xs$list('salary'),
    privilege   => 'view_salary');

  sys.xs_data_security.create_policy(
    name                   => 'employees_ds',
    realm_constraint_list  => realms,
    column_constraint_list => cols);
end;
/

Apply the data security policy to the EMPLOYEES table.

begin
  sys.xs_data_security.apply_object_policy(
    policy => 'employees_ds',
    schema => 'hr',
    object =>'employees');
end;
/

About Validating the Real Application Security Objects

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

begin
  if (sys.xs_diag.validate_workspace()) then
    dbms_output.put_line('All configurations are correct.');
  else
    dbms_output.put_line('Some configurations are incorrect.');
  end if;
end;
/
-- XS$VALIDATION_TABLE contains validation errors if any.
-- Expect no rows selected.
select * from xs$validation_table order by 1, 2, 3, 4;

About Setting Up the Mid-Tier Related Configuration

Set up the mid-tier configuration to be used later. This involves creating a session administrator, hr_session, who only has Real Application Security administrative privileges (XS_SESSION_ADMIN and CREATE SESSION), but no data privileges. The session administrator is responsible for managing the Real Application Security session for each application user. In addition, it involves creating a DISPATCHER user and password and granting this user the XSCONNECT and XSDISPATCHER Real Application Security administrator privileges.

grant xs_session_admin, create session to hr_session identified by hr_session;
grant create session to hr_common identified by hr_common;

Create a dispatcher user for the Java demo to set up a session for the application user.

exec sys.xs_principal.create_user(name=>'dispatcher', schema=>'HR');
exec sys.xs_principal.set_password('dispatcher', 'welcome1');
exec sys.xs_principal.grant_roles('dispatcher', 'XSCONNECT');
exec sys.xs_principal.grant_roles('dispatcher', 'xsdispatcher');