VPD(虚拟专用数据库) & rls(row LEVEL security)

1.建立主用户  测试表 测试数据 分用户


--主数据用户
create user mainuser identified by oracle;
grant connect,resource to mainuser;


--wh分用户
create user whuser identified by oracle;
grant connect,resource to whuser;




--sw分用户
create user swuser identified by oracle;
grant connect,resource to swuser;


--测试表与数据
create table mainuser.maintab(name varchar2(20),salary number(8,2),DEP_ID number(8,2));  


insert into mainuser.maintab values('whuser',5000,1);  
insert into mainuser.maintab values('swuser',3000,2); 


--表赋权限
grant select on mainuser.maintab to whuser;
grant select on mainuser.maintab to swuser;




2.建立控制函数


create or replace function main_fun
(owner varchar2,objname varchar2) return varchar2
is
  v_where_clause varchar2(2000);
begin
  v_where_clause :=' upper(name)=' || '''' || sys_context('userenv','session_user') || '''';
  return v_where_clause;
end;
/




3.建立rls策略




BEGIN  
  dbms_rls.add_policy(object_schema => 'mainuser',  
  object_name => 'maintab',  
  policy_name => 'main_rlw',  
  function_schema =>'mainuser',  
  policy_function => 'main_fun',  
  statement_types  =>'select',  
  sec_relevant_cols=>'salary');  
END; 
/


4.验证




sql> connect swuser/oracle
Connected.
SQL> select * from mainuser.maintab;


NAME                     SALARY     DEP_ID
-------------------- ---------- ----------
swuser                     3000          2


SQL> connect whuser/oracle
Connected.
SQL> select * from mainuser.maintab;


NAME                     SALARY     DEP_ID
-------------------- ---------- ----------
whuser                     5000          1