set_context('HR_SECURITY','USERNAME',emp_login);
-- get employee id number, so manager rights can be established
-- but don't bomb out other DB users who are not in the
-- EMPLOYEES table
begin
select employee_id into emp_id_num
from hr.emp_login_map where login_acct = emp_login;
dbms_session.set_context('HR_SECURITY','EMP_ID',emp_id_num);
exception
when no_data_found then
dbms_session.set_context('HR_SECURITY','EMP_ID',0);
end;
-- Future queries will restrict rows based on emp_id
end; -- procedure
end; -- package body
A few things are worth noting about this procedure. We retrieve the user??™s schema by looking
in the USERENV context, which is enabled by default for all users, and assigning it to the variable
USERNAME in the newly created context HR_SECURITY. The other HR_SECURITY context
variable EMP_ID is determined by doing a lookup in the mapping table HR.EMP_LOGIN_MAP.
We don??™t want the procedure to terminate with an error if the logged-in user is not in the mapping
table; instead, we assign an EMP_ID of 0, which will result in no access to the table HR.EMPLOYEES
when the predicate is generated in the policy function.
In the next steps, we grant everyone in the database EXECUTE privileges on the package, and
we create a synonym for it to save a few keystrokes any time we need to call it:
grant execute on vpd.emp_access to PUBLIC;
create public synonym emp_access for vpd.emp_access;
Chapter 9: Database Security and Auditing 323
To ensure that the context is defined for each user when they log on, we will connect as
SYSTEM and create a logon trigger to set up the variables in the context:
connect system/nolongermanager@dw as sysdba;
create or replace trigger vpd.
Pages:
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534