vpd安全策略的使用

1.首先我们创建用户vpd,并给与一定的权限

create user　vpd identified by 123456
grant resource, connect to vpd;
grant execute on dbms_rls to vpd;
grant select any dictionary to vpd;
ALTER USER vpd QUOTA UNLIMITED ON USERS;--对表空间users无权限

2.使用刚创建的vpd用户创建一个test表

create table test as select * from dba_objects;
select * from test

3.创建策略函数 ，如果是vpd用户则可以查看test表，否则不能查询到test表中数据

create or replace function f_limit_access ( vc_schema varchar2, vc_object varchar2 ) return varchar2 as
vc_userid varchar2(100);
begin
    select SYS_CONTEXT('USERENV','SESSION_USER') into vc_userid from dual;
    if (trim(vc_userid)='vpd')
      then
        return '1=1';
        else
          return '1=0';
          end if;
end;

4，应用策略函数

begin
  dbms_rls.add_policy(object_schema => 'vpd',
  object_name => 'test',policy_name => 'VPD_TEST',
  function_schema => 'vpd',
  policy_function => 'F_LIMIT_ACCESS');
end;

5.查看策略函数返回的条件

select policy_name, SEL, INS, UPD, DEL, IDX, CHK_OPTION, ENABLE from user_policies;

6.授权给sym用户查看test表的权限，并且登录sym后查询该表，结果为无法获取该表数据，说明安全策略成功应用

grant select on test to sym;

select * from vpd.test

7.删除安全策略后让sym再查询一遍 ,这时sym就可以查询到test表中的数据了

begin 

  sys.dbms_rls.drop_policy(object_schema => 'vpd',  

                           object_name => 'test',  

                           policy_name => 'VPD_TEST');  

end;

select * from vpd.test