VPD example

Oracle Document

1. create policy function
create or replace package dm_pkg_vpd is
  function predicate (aSchema varchar2, aName varchar2) return varchar2;
end dm_pkg_vpd;
create or replace package body dm_pkg_vpd is
  function predicate (aSchema varchar2, aName varchar2) return varchar2 is
  begin
    if user = 'XXX' then
      return 'rownum <= '||to_char(trunc(dbms_random.value(500,2500)));
    else
      return null;
    end if;
  end;

begin
  null;
end dm_pkg_vpd;


2. add policy
要由有執行 dbms_rls權限的人執行，policy function也掛在此較方便例如以下的zzz

限制xxx.yyy被access的policy例子如下

begin dbms_rls.add_policy(
  object_schema => 'xxx',
  object_name => 'yyy',
  policy_name => 'TEST',
  policy_function => 'dm_pkg_vpd.predicate',
  function_schema=>'zzz'
);
end;
/