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

千家信息网最后更新 2024年11月22日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