使用vpd进行行级控制

在系统用户下：

1.创建vpd用户 

create user　vpd identified by 123456
grant resource, connect to vpd;
grant execute on dbms_rls to vpd;
grant select any dictionary to vpd;
ALTER USER vpd QUOTA UNLIMITED ON USERS;

1. 再创建三个用户实现不同用户查询相应的数据

create tablespace jiami datafile 'F:\jiami.dbf' size 200M;
drop tablespace t_user including contents and datafiles;
create user test1 IDENTIFIED BY 123456 default tablespace jiami
temporary tablespace TEMP profile DEFAULT;

create user test2 IDENTIFIED BY 123456 default tablespace jiami
temporary tablespace TEMP profile DEFAULT;
  
create user test3 IDENTIFIED BY 123456 default tablespace jiami
temporary tablespace TEMP profile DEFAULT;

2.授权用户可以查询该表hj_test

grant connect ，resource to test1;
grant connect ，resource to test2;
grant connect ，resource to test3; 

在vpd用户下：

3.授权这三个用户访问hj_test的权限

grant select on hj_test to test1;
grant select on hj_test to test2;
grant select on hj_test to test3;

4.创建hj_test表,作为查询测试表

CREATE TABLE hj_test(ID NUMBER,NAME VARCHAR2(50),tag VARCHAR2(20));

 INSERT INTO hj_test VALUES(1,'aa','011');
 INSERT INTO hj_test VALUES(2,'bb','022');
 INSERT INTO hj_test VALUES(3,'cc','033');


select * from hj_test

5.创建用户权限表，即用户对应可以查询的数据

CREATE TABLE rls_users(ID NUMBER,username VARCHAR2(50),usertag VARCHAR2(20));

INSERT INTO rls_users VALUES(1,'test1','011');
INSERT INTO rls_users VALUES(2,'test2','022');
INSERT INTO rls_users VALUES(3,'test3','033');

6.创建rls函数，函数返回的结果为对应表的where条件

CREATE OR REPLACE FUNCTION f_select_data_security(p_user VARCHAR2,p_table VARCHAR2) RETURN VARCHAR2 IS
   results VARCHAR2(255);
BEGIN
   --SYS_CONTEXT('USERENV','SESSION_USER') 获取session_user
   --或者直接用输入的参数p_user
      if (sys_context('userenv','session_user')='vpd') then
      results := NULL;
      else
    results := 'tag IN (SELECT usertag FROM vpd.rls_users WHERE upper(username)=SYS_CONTEXT(''USERENV'',''SESSION_USER''))';
      end if;  
  RETURN results;
END;

7.验证函数是否能正确返回

SELECT f_select_data_security('test1','hj_test') from dual;

8.对表hj_test添加rls安全策略

grant execute on  dbms_rls to vpd;

BEGIN
   dbms_rls.add_policy(object_schema   => 'vpd',
                       object_name     => 'hj_test',
                       policy_name     => 'SELECT_DATA_SECURITY',
					   STATEMENT_TYPES => 'select',
                       policy_function => 'F_SELECT_DATA_SECURITY');
END;

9.查看是否已经加上rls安全策略，注意VPD,HJ_TEST要大写，因为库里面是大写的

SELECT * FROM dba_policies WHERE object_owner='VPD' AND object_name='HJ_TEST';

10. 接下来登录test1，test2, test3来进行测试

select * from vpd.hj_test

select * from vpd.hj_test

。。。。。。。