employees;
grant select on hr.emp_login_map to public;
Next, we will create a user account called VPD that has the privileges to create contexts and
maintains the policy functions:
create user vpd identified by vpd439;
grant connect, resource, create any context, create public synonym to vpd;
Connecting to the VPD schema, we will create a context called HR_SECURITY and define the
package and procedure used to set the context for the application:
connect vpd/vpd439@dw;
create context hr_security using vpd.emp_access;
create or replace package vpd.emp_access as
procedure set_security_parameters;
end;
Remember that the procedures in the package VPD.EMP_ACCESS are the only procedures
that can set the context variables. The package body for VPD.EMP_ACCESS follows:
create or replace package body vpd.emp_access is
--
-- At user login, run set_security_parameters to
-- retrieve the user login name, which corresponds to the EMAIL
-- column in the table HR.EMPLOYEES.
322 Oracle Database 11g DBA Handbook
--
-- context USERENV is pre-defined for user characteristics such
-- as username, IP address from which the connection is made,
-- and so forth.
--
-- for this procedure, we are only using SESSION_USER
-- from the USERENV context.
--
procedure set_security_parameters is
emp_id_num number;
emp_login varchar2(50);
begin
-- database username corresponds to email address in HR.EMPLOYEES
emp_login := sys_context('USERENV','SESSION_USER');
dbms_session.
Pages:
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533