12 Real Application Security HR Demo
12.1 Overview of the Security HR Demo
This Human Resources (HR) Demonstration shows how to use basic Real Application Security (RAS) features. This tutorial is an end-to-end use case scenario. PL/SQL scripts, a Java program source file, and log files can be found in Real Application Security HR Demo Files.
The HR demo secures the HR.EMPLOYEE
table by applying a data security policy that has three realms:
-
An employee's own record realm. The ACL,
EMP_ACL
controls this realm, which grants application roleemployee
privileges to access the realm, including theSALARY
column. -
All the records in the IT department realm. The ACL,
IT_ACL
controls this realm, which grants application roleit_engineer
privileges to access the realm, but excluding theSALARY
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 theSALARY
column.
The HR Demo defines two application users to demonstrate the effects of the policy:
-
DAUSTIN
, an application user in the IT department. He has application rolesemployee
andit_engineer
. So, he can access realm #1 and realm #2 mentioned previously; that is, he can view employee records in the IT department, but he cannot view theSALARY
column, except for his own salary record. -
SMAVRIS
, an application user in HR department. She has application rolesemployee
andhr_representative
. So, she can access realm #1 and realm #3 mentioned previously; that is, she can view and update all the employee records.
The HR Demo scripts show:
-
How to create Real Application Security objects: application user, application role, ACL, security class, and data security policy.
-
How to use the data security policy to secure rows (using realm constraints) and columns (using a column constraint) of a table.
-
How to directly logon to a database with application users (requiring a password), and how to create, attach, detach, and destroy a Real Application Security session.
-
How to enable and disable an application role in a Real Application Security session.
12.2 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
, andhr_representative
, and then granting the database roleDB_EMP
to each of these application roles. -
Granting application roles
employee
andit_engineer
to application userDAUSTIN
. -
Granting application roles
employee
andhr_representative
to application userSMAVRIS
. -
Creating the
VIEW_SALARY
privilege and creating thehr_privileges
security class in which to scope the privilege. -
Creating three ACLs:
EMP_ACL
,IT_ACL
, andHR_ACL
, in which:-
EMP_ACL
grants theemployee
role theSELECT
database privilege andVIEW_SALARY
application privilege to view an employee's own record, including theSALARY
column. -
IT_ACL
grants theit_engineer
role only theSELECT
database privilege to view the employee records in the IT department, but it does not grant theVIEW_SALARY
privilege that is required for access to theSALARY
column. -
HR_ACL
grants thehr_representative
roleSELECT
,INSERT
,UPDATE
, andDELETE
database privileges to view and update all employee's records, and granting theVIEW_SALARY
application privilege to view theSALARY
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 roleemployee
privileges to access the realm, including theSALARY
column. -
All the records in the IT department realm. The ACL,
IT_ACL
controls this realm, which grants application roleit_engineer
privileges to access the realm, but excluding theSALARY
column. -
All the employee records realm. The ACL,
HR_ACL
controls this realm, which grants application rolehr_representative
privileges to access the realm, including theSALARY
column. -
A column constraint that protects the
SALARY
column by requiring theVIEW_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
andxsdispatcher
to thisDISPATCHER
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 theSALARY
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 asemployee
,hr_representative
, andXSPUBLIC
for the current userSMAVRIS
. -
That
SMAVRIS
application user can view all records including all salary rows in theSALARY
column. -
Disabling the
hr_representative
and thus limiting application userSMAVRIS
to viewing only her own employee record. -
Enabling the
hr_representative
, thus allowingSMAVRIS
application user to view all records, including all salary rows in theSALARY
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.
12.3 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
12.3.1 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');
12.3.2 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; /
12.3.3 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; /
12.3.4 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;
12.3.5 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');
12.4 Running the Security HR Demo Using Direct Logon
To run the HR Demo, first connect as application user DAUSTIN
, who has only the EMPLOYEE
and IT_ENGINEER
application roles.
conn daustin/welcome1;
Customize how secured column values are to be displayed in SQL*Plus using the default indicator asterisks (*******) in place of column values.
SET SECUREDCOL ON UNAUTH *******
Perform a query to show that application user DAUSTIN
can view the records in the IT department, but can only view his own SALARY
column.
select email, first_name, last_name, department_id, manager_id, salary from employees order by email; SQL> select email, first_name, last_name, department_id, manager_id, salary 2 from employees order by email; EMAIL FIRST_NAME LAST_NAME DEPARTMENT_ID MANAGER_ID SALARY ---------- --------------- --------------- ------------- ---------- ------- AHUNOLD Alexander Hunold 60 102 ******* BERNST Bruce Ernst 60 103 ******* DAUSTIN David Austin 60 103 4800 DLORENTZ Diana Lorentz 60 103 ******* VPATABAL Valli Pataballa 60 103 ******* 5 rows selected.
Set to the default display for how secured column values are to be displayed in SQL*Plus by displaying null values in place of column values for application users without authorization, and in place of column values where the security level is unknown.
SET SECUREDCOL OFF
Perform an update operation to show that application user is not authorized to update the record.
update employees set manager_id = 102 where email = 'DAUSTIN'; SQL> update employees set manager_id = 102 where email = 'DAUSTIN'; 0 rows updated.
Perform a query to show that the record is unchanged.
select email, first_name, last_name, department_id, manager_id, salary from employees where email = 'DAUSTIN'; SQL> select email, first_name, last_name, department_id, manager_id, salary 2 from employees where email = 'DAUSTIN'; EMAIL FIRST_NAME LAST_NAME DEPARTMENT_ID MANAGER_ID SALARY ---------- --------------- --------------- ------------- ---------- ------- DAUSTIN David Austin 60 103 4800 1 row selected.
Connect as application user SMAVRIS
, who has both EMPLOYEE
and HR_REPRESENTATIVE
application roles.
conn smavris/welcome1;
Perform a query to show that application user SMAVRIS
can view all the records including SALARY
column.
select email, first_name, last_name, department_id, manager_id, salary from employees where department_id = 60 or department_id = 40 order by department_id, email; SQL> select email, first_name, last_name, department_id, manager_id, salary 2 from employees where department_id = 60 or department_id = 40 3 order by department_id, email; EMAIL FIRST_NAME LAST_NAME DEPARTMENT_ID MANAGER_ID SALARY ---------- --------------- --------------- ------------- ---------- ------- SMAVRIS Susan Mavris 40 101 6500 AHUNOLD Alexander Hunold 60 102 9000 BERNST Bruce Ernst 60 103 6000 DAUSTIN David Austin 60 103 4800 DLORENTZ Diana Lorentz 60 103 4200 VPATABAL Valli Pataballa 60 103 4800 6 rows selected.
Perform a query to show that application user SMAVRIS
can access all the records.
select count(*) from employees; SQL> select count(*) from employees; COUNT(*) ---------- 107 1 row selected.
Perform an update of the record to show that application user SMAVRIS
can update the record.
update employees set manager_id = 102 where email = 'DAUSTIN'; SQL> update employees set manager_id = 102 where email = 'DAUSTIN'; 1 row updated.
Perform a query to show that the record is changed.
select email, first_name, last_name, department_id, manager_id, salary from employees where email = 'DAUSTIN'; SQL> select email, first_name, last_name, department_id, manager_id, salary 2 from employees where email = 'DAUSTIN'; EMAIL FIRST_NAME LAST_NAME DEPARTMENT_ID MANAGER_ID SALARY ---------- --------------- --------------- ------------- ---------- ------- DAUSTIN David Austin 60 102 4800 1 row selected.
Update the record to change it back to its original state.
update employees set manager_id = 103 where email = 'DAUSTIN'; SQL> update employees set manager_id = 103 where email = 'DAUSTIN'; 1 row updated.
12.5 Running the Security HR Demo Attached to a Real Application Security Session
To run the demo attached to a Real Application Security session, the Real Application Security administrator must first create the session for an application user and attach to it. In the process, create a variable to remember the session ID.
connect hr_session/hr_session; var gsessionid varchar2(32); declare sessionid raw(16); begin sys.dbms_xs_sessions.create_session('SMAVRIS', sessionid); :gsessionid := rawtohex(sessionid); sys.dbms_xs_sessions.attach_session(sessionid, null); end ; /
Display the current user.
select xs_sys_context('xs$session','username') from dual; SQL> select xs_sys_context('xs$session','username') from dual; XS_SYS_CONTEXT('XS$SESSION','USERNAME') -------------------------------------------------------------------------------- SMAVRIS 1 row selected.
Display the enabled database and application roles for the current application user.
select role_name from v$xs_session_roles union select role from session_roles order by 1; SQL> select role_name from v$xs_session_roles union 2 select role from session_roles order by 1; ROLE_NAME -------------------------------------------------------------------------------- DB_EMP EMPLOYEE HR_REPRESENTATIVE XSPUBLIC 4 rows selected.
Perform a query to show that application user SMAVRIS
can view all the records including SALARY
column.
select email, first_name, last_name, department_id, manager_id, salary from employees where department_id = 60 or department_id = 40 order by department_id, email; SQL> select email, first_name, last_name, department_id, manager_id, salary 2 from employees where department_id = 60 or department_id = 40 3 order by department_id, email; EMAIL FIRST_NAME LAST_NAME DEPARTMENT_ID MANAGER_ID SALARY ---------- --------------- --------------- ------------- ---------- ------- SMAVRIS Susan Mavris 40 101 6500 AHUNOLD Alexander Hunold 60 102 9000 BERNST Bruce Ernst 60 103 6000 DAUSTIN David Austin 60 103 4800 DLORENTZ Diana Lorentz 60 103 4200 VPATABAL Valli Pataballa 60 103 4800 6 rows selected.
Perform a query to show that application user SMAVRIS
can access all the records.
select count(*) from employees; SQL> select count(*) from employees; COUNT(*) ---------- 107 1 row selected.
Disable the HR_REPRESENTATIVE
role. This will limit application user SMAVRIS
to only be able to see her own record.
exec dbms_xs_sessions.disable_role('hr_representative');
Perform a query
select email, first_name, last_name, department_id, manager_id, salary from employees where department_id = 60 or department_id = 40 order by department_id, email; SQL> select email, first_name, last_name, department_id, manager_id, salary 2 from employees where department_id = 60 or department_id = 40 3 order by department_id, email; EMAIL FIRST_NAME LAST_NAME DEPARTMENT_ID MANAGER_ID SALARY ---------- --------------- --------------- ------------- ---------- ------- SMAVRIS Susan Mavris 40 101 6500 1 row selected.
Enable the HR_REPRESENTATIVE
role so the application user can view all the records including SALARY
column.
exec dbms_xs_sessions.enable_role('hr_representative');
Perform a query to show that application user can view all the records including SALARY
column.
select email, first_name, last_name, department_id, manager_id, salary from employees where department_id = 60 or department_id = 40 order by department_id, email; SQL> -- SMAVRIS can view all the records again. SQL> select email, first_name, last_name, department_id, manager_id, salary 2 from employees where department_id = 60 or department_id = 40 3 order by department_id, email; EMAIL FIRST_NAME LAST_NAME DEPARTMENT_ID MANAGER_ID SALARY ---------- --------------- --------------- ------------- ---------- ------- SMAVRIS Susan Mavris 40 101 6500 AHUNOLD Alexander Hunold 60 102 9000 BERNST Bruce Ernst 60 103 6000 DAUSTIN David Austin 60 103 4800 DLORENTZ Diana Lorentz 60 103 4200 VPATABAL Valli Pataballa 60 103 4800 6 rows selected.
Perform a query to show that application user SMAVRIS
can access all the records.
select count(*) from employees; SQL> select count(*) from employees; COUNT(*) ---------- 107 1 row selected.
Detach and destroy the application session.
declare sessionid raw(16); begin sessionid := hextoraw(:gsessionid); sys.dbms_xs_sessions.detach_session; sys.dbms_xs_sessions.destroy_session(sessionid); end; /
12.6 Running the Security HR Demo Cleanup Script
After running the HR demo, you can run the clean up script to remove all of the Real Application Security components.
To start, connect as the Real Application Security Administrator and then begin removing components.
define passwd=&1 connect hr/hr;
Remove the data security policy from the EMPLOYEES
table.
begin xs_data_security.remove_object_policy(policy=>'employees_ds', schema=>'hr', object=>'employees'); end; /
Delete the security class and the ACLs.
exec sys.xs_security_class.delete_security_class('hrprivs', xs_admin_util.cascade_option); exec sys.xs_acl.delete_acl('emp_acl', xs_admin_util.cascade_option); exec sys.xs_acl.delete_acl('it_acl', xs_admin_util.cascade_option); exec sys.xs_acl.delete_acl('hr_acl', xs_admin_util.cascade_option);
Delete the data security policy.
exec sys.xs_data_security.delete_policy('employees_ds', xs_admin_util.cascade_option);
Connect as SYS/
user as SYSDBA
.
connect sys/&passwd as sysdba
Delete the application roles and application users.
exec sys.xs_principal.delete_principal('employee', xs_admin_util.cascade_option); exec sys.xs_principal.delete_principal('hr_representative', xs_admin_util.cascade_option); exec sys.xs_principal.delete_principal('it_engineer', xs_admin_util.cascade_option); exec sys.xs_principal.delete_principal('smavris', xs_admin_util.cascade_option); exec sys.xs_principal.delete_principal('daustin', xs_admin_util.cascade_option);
Delete the database role.
drop role db_emp;
Delete the Real Application Security session administrator.
drop user hr_session;
Delete the common user used to connect to the database.
drop user hr_common;
Delete the DISPATCHER
user used by the mid-tier.
exec sys.xs_principal.delete_principal('dispatcher', xs_admin_util.cascade_option);
12.7 Running the Security HR Demo in the Java Interface
See the Output section in "Human Resources Administration Use Case: Implementation in Java" for a description of the two queries that are returned from running the Security HR Demo in the Java interface.