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