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 =