DGRANT gets to see only his own row, because he does not manage anyone else in the company.
In the case of SMAVRIS, we see similar results from the query:
SQL> connect smavris/smavris702@dw;
Connected.
SQL> select employee_id, first_name, last_name,
2 email, job_id, salary, manager_id from hr.employees;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL JOB_ID SALARY MANAGER_ID
----------- ---------- ------------ ---------- ---------- ------- ----------
203 Susan Mavris SMAVRIS HR_REP 6500 101
1 row selected.
But wait, SMAVRIS is in the HR department and should be able to see all rows from the table.
In addition, SMAVRIS should be the only person to see the salary information for all employees.
326 Oracle Database 11g DBA Handbook
As a result, we need to change our policy function to give SMAVRIS and other employees in the
HR department full access to the HR.EMPLOYEES table; in addition, we can use column-level
restrictions in the policy assignment to return the same number of rows, but with the sensitive
data returned as NULL values.
To facilitate access to the HR.EMPLOYEES table by HR department employees, we first need
to change our mapping table to include the JOB_ID column. If the JOB_ID column has a value
of HR_REP, the employee is in the HR department. We will first disable the policy in effect and
create the new mapping table:
SQL> begin
2 dbms_rls.enable_policy(
3 object_schema => 'HR',
4 object_name => 'EMPLOYEES',
5 policy_name => 'EMP_SELECT_RESTRICT',
6 enable => FALSE
7 );
8 end;
PL/SQL procedure successfully completed.
Pages:
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538