set_security_parameters
after logon on database
begin
vpd.emp_access.set_security_parameters;
end;
Because this trigger is fired for every user who connects to the database, it is vitally important that
the code be tested for every class of user, if not every user in the database! If the trigger fails with
an error, regular users cannot log in.
So far, we have our context defined, the procedure used to set up the context variables, and a
trigger that automatically calls the procedure. Logging in as one of our three users defined previously,
we can query the contents of the context:
SQL> connect smavris/smavris702@dw
Connected.
SQL> select * from session_context;
NAMESPACE ATTRIBUTE VALUE
------------------------ ------------------------- ---------------------
HR_SECURITY USERNAME SMAVRIS
HR_SECURITY EMP_ID 203
2 rows selected.
Notice what happens when SMAVRIS tries to impersonate another employee:
SQL> begin
2 dbms_session.set_context('HR_SECURITY','EMP_ID',100);
3 end;
begin
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 94
ORA-06512: at line 2
Only the package VPD.EMP_ACCESS is allowed to set or change variables in the context.
The final steps include defining the procedures that will generate the predicate and assigning
one or more of these procedures to the HR.EMPLOYEES table. As the user VPD, which already
owns the context procedures, we??™ll set up the package that determines the predicates:
connect vpd/vpd439@dw;
create or replace package vpd.
Pages:
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535