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.