Orace VPD快速入门

参考文章

https://blog.csdn.net/sunansheng/article/details/52587044
https://blog.csdn.net/finefun/article/details/17918433
http://www.wfuyu.com/datay/26995.html
https://blog.csdn.net/u011109042/article/details/51910444
http://www.itpub.net/thread-1101586-1-1.html
https://wenku.baidu.com/view/c1b7fa956bec0975f465e2f3.html

体会用户上下文

create or replace package ni_app_pkg is
  procedure set_ni_context;
end;

create or replace package body ni_app_pkg is
  procedure set_ni_context is
  begin
    dbms_session.set_context('ni_ctx', 'ni_user_id', user);
  end;
end;

grant execute on ni_app_pkg to public;

begin
  ni_app_pkg.set_ni_context;
end;

select sys_context('userenv', 'ip_address') "IP",
       sys_context('userenv', 'db_name') "DB"
  from dual;
select sys_context('ni_ctx', 'ni_user_id') from dual;

体会dbms_rls.add_policy

create or replace package ni_app_pkg is
  procedure set_ni_context;
  function where_condition(p_schema_name varchar2, p_tab_name varchar2)
    return varchar2;
end;

create or replace package body ni_app_pkg is
  procedure set_ni_context is
  begin
    dbms_session.set_context('ni_ctx', 'ni_user_id', user);
  end;
  function where_condition(p_schema_name varchar2, p_tab_name varchar2)
    return varchar2 is
    v_where_condition varchar2(2000);
  begin
    v_where_condition := '1=2';
    return v_where_condition;
  end;
end;

grant select on ni_serv_simp_info to public;

begin
  dbms_rls.add_policy(OBJECT_SCHEMA   => 'NETINFO',
                      OBJECT_NAME     => 'NI_SERV_SIMP_INFO',
                      POLICY_NAME     => 'NI_SERV_SIMP_INFO_FGAC',
                      FUNCTION_SCHEMA => 'NETINFO',
                      POLICY_FUNCTION => 'ni_app_pkg.where_condition',
                      STATEMENT_TYPES => 'select,update',
                      UPDATE_CHECK    => true,
                      ENABLE          => true);
end;

select * from NI_SERV_SIMP_INFO ;

体会VPD

create or replace package ni_app_pkg is
  procedure reset_ni_context;
  procedure set_ni_context;
  function where_condition(p_schema_name varchar2, p_tab_name varchar2)
    return varchar2;
end;
create or replace package body ni_app_pkg is
  procedure reset_ni_context is
  begin
    dbms_session.set_context('ni_ctx', 'ni_user_id', null);
  end;
  procedure set_ni_context is
  begin
    dbms_session.set_context('ni_ctx', 'ni_user_id', user);
  end;
  function where_condition(p_schema_name varchar2, p_tab_name varchar2)
    return varchar2 is
    v_user_id         varchar2(100) := upper(sys_context('ni_ctx',
                                                         'ni_user_id'));
    v_where_condition varchar2(2000);
  begin
    if upper(v_user_id) = 'NETINFO' then
      v_where_condition := '1 = 1';
    else
      v_where_condition := '1 =2';
    end if;
    return v_where_condition;
  end;
end;

begin
  ni_app_pkg.set_ni_context;
end;
select sys_context('ni_ctx', 'ni_user_id') from dual;
select * from NI_SERV_SIMP_INFO ;
begin
  ni_app_pkg.reset_ni_context;
end;
select sys_context('ni_ctx', 'ni_user_id') from dual;
select * from NI_SERV_SIMP_INFO ;