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
网站栏目：VPD(虚拟专用数据库)&rls(rowLEVELsecurity)
当前网址：
http://bzwzjz.com/article/phdcho.html
