VPD (Virtual Private Database) & rls (row LEVEL security)

1. Establish the main user test table test data sub-user

--Master Data User

create user mainuser identified by oracle;

grant connect,resource to mainuser;

--wh sub-user

create user whuser identified by oracle;

grant connect,resource to whuser;

--SW Sub-users

create user swuser identified by oracle;

grant connect,resource to swuser;

--Test sheets and data

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);

--Table Assign Permission

grant select on mainuser.maintab to whuser;

grant select on mainuser.maintab to swuser;

2. establishing control function

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. Create rls strategy

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. verification

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