Oracle VPD setup

This will be a quick guide on how to use the VPD feature in an Oracle database.

Start with creating a package that will fill your context.

create or replace package
user.mypackage
is
procedure set_user_id_context;
end;

Here comes the package body:

create or replace package body
user.mypackage
is
procedure set_user_id_context
is
sec_user_id number;
begin
--
-- in this case we have a table that holds my users and each user has a code
--
select
code
into
sec_user_id
from
usr
where
upper(id) =
upper(
sys_context(
'USERENV'
,'SESSION_USER'
)
);
--
-- set the context
--

dbms_session.set_context(
'MYCONTEXT_NAME'
, 'USER_ID'
, sec_user_id
);

exception

--
-- This is necessary in order to allow users that are
-- not in the usr table to login, as this procedure is
-- used in a logon trigger
--

when no_data_found then
dbms_session.set_context(
'MY_CONTEXT_NAME'
, 'USER_ID'
, NULL
);
end set_user_id_context;
end;



Then create a context:

connect user/password@sid


create or replace context my_context_name using user.mypackage;
/