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;
/