SQL> drop table hr.emp_login_map;
Table dropped.
SQL> create table hr.emp_login_map (employee_id, login_acct, job_id)
2 as select employee_id, email, job_id from hr.employees;
Table created.
SQL> grant select on hr.emp_login_map to public;
Grant succeeded.
The procedure we??™re using to set up the context variables, VPD.EMP_ACCESS, needs another
context variable added that indicates the security level of the user accessing the table. We will
change the SELECT statement and make another call to DBMS_SESSION.SET_CONTEXT, as follows:
. . .
emp_job_id varchar2(50);
. . .
select employee_id, job_id into emp_id_num, emp_job_id
from hr.emp_login_map where login_acct = emp_login;
dbms_session.set_context('HR_SECURITY','SEC_LEVEL',
case emp_job_id when 'HR_REP' then 'HIGH' else 'NORMAL' end );
. . .
Whenever the employee has a job title of HR_REP, the context variable SEC_LEVEL is set to
HIGH instead of NORMAL. In our policy function, we need to check for this new condition as
follows:
create or replace package body vpd.get_predicates is
function emp_select_restrict
Chapter 9: Database Security and Auditing 327
(owner varchar2, object_name varchar2) return varchar2 is
ret_predicate varchar2(1000); -- part of WHERE clause
begin
-- only allow employee to see their row or immediate subordinates,
-- unless they have high security clearance
if sys_context('HR_SECURITY','SEC_LEVEL') = 'HIGH' then
ret_predicate := ''; -- no restrictions in WHERE clause
else
ret_predicate := 'EMPLOYEE_ID = ' ||
sys_context('HR_SECURITY','EMP_ID') ||
' OR MANAGER_ID = ' ||
sys_context('HR_SECURITY','EMP_ID');
end if;
return ret_predicate;
end emp_select_restrict;
end; -- package body
Because the policy is dynamic, the predicate is generated each time a SELECT statement is
executed, so we don??™t have to do a policy refresh.
Pages:
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539