VPD虚拟专用数据库
VPD虚拟专用数据库（Virtual Private Database）
虚拟专用数据库（VPD）提供了角色和视图无法提供的行级访问控制， 如实现每个营销用户登录后只能访问销售表中自己的营销记录，具体的实现过程为：
1.先设置应用程序上下文A（应用程序上下文是一个数据库对象，在整个session周期内保存session的各项属性如登录数据名，类似于环境变量）用来保存用户登录时的登录名；
2.在用户登录时由触发器C执行设置定的程序单元B获取用户名并保存在应用程序上下文A中；
3.预设定好的审计规则E在监控到销售用户执行销售记录的查询语句时，由预设定好的程序单元D的用户执行的查询语句后隐身添加制定条件，如当销售记录执行select * from sales时系统自动装换为select * from sales where seller=“S001‘。
==============================================================
建立测试环境
SQL> create table scott.sales(product_id varchar(4),price number,qtys number,seller varchar(4));
SQL> insert into scott.sales values('0001',10000,1,'S001');
SQL> insert into scott.sales values('0002',20000,2,'S002');
SQL> create user S001 identified by S001;
SQL> create user S002 identified by S002;
SQL> create user MGR identified by MGR123;
SQL> grant connect,resource,select any table to S001;
SQL> grant connect,resource,select any table to S002;
SQL> grant connect,resource,select any table to MGR;
==============================================================
SQL>connect s001/s001
SQL> show user;
USER 为 "S001"
SQL> select * from scott.sales;
PRODUCT_ PRICE QTYS SELLER
-------- ---------- ---------- -------
0001 10000 1 S001
0002 20000 2 S002
SQL>connect s002/s002
SQL> show user;
USER 为 "S002"
SQL> select * from scott.sales;
PRODUCT_ PRICE QTYS SELLER
-------- ---------- ---------- -------
0001 10000 1 S001
0002 20000 2 S002
SQL>connect mgr/mgr123
SQL> show user;
USER 为 "MGR"
SQL> select * from scott.sales;
PRODUCT_ PRICE QTYS SELLER
-------- ---------- ---------- -------
0001 10000 1 S001
0002 20000 2 S002
测试3个用户能查到sales销售表中的所有数据，VPD需要实现mgr用户能查所有数据，而s001，s002只能查到自己的销售数据。
==============================================================
A.B. 建立应用程序上下文A（保存登录用户名）和存储过程B（获取登录用户名）
SQL> create or replace context vpd_context using scott.vpd_get_username;
建立应用程序上下文vpd_context，其中所保存的属性由存储过程scott.vpd_get_usrname（即存储过程）赋予。
SQL> create or replace procedure scott.vpd_get_username is
2 begin
3 dbms_session.set_context('vpd_context','seller',user);
4 end;
5 /
建立存储过程，为vpd_context建立属性名seller，用来保存属性user。
SQL> grant execute on scott.vpd_get_username to public;
将存储过程的执行权限赋给所有用户。
==============================================================
C.建立触发器C（自动获取用户名）
SQL> create or replace trigger get_username_on_logon
2 after logon on database
3 begin
4 scott.vpd_get_username;
5 end;
6 /
建立触发器，用于用户登录后自动执行存储过程vpd_context是否生效，其属性seller中是否包含用户名属性值。
==============================================================
D.建立函数D（在用户执行的语句后隐式添加指定条件）
SQL> create or replace function scott.vpd_add_condition
2 (p_schema_name varchar2,p_tab_name varchar2)
3 return varchar2 is
4 v_name varchar2(100):=upper(sys_context('vpd_context','seller'));
5 v_condition varchar2(2000);
6 begin
7 if v_name like 'S%' then v_condition:='seller='||''''||v_name||'''';
8 else v_condition:=null;
9 end if;
10 return v_condition;
11 end;
12 /
建立函数D，在用户执行的查询语句后隐式添加指定条件，如当销售用户执行用户select * from sales时系统自动转换成select * from sales where seller='S001';而当非销售用户MGR执行语句时，则不添加指定条件（null），其中p_schema_name和p_tab_name两变量值为FGAC规则函数必须的两个传入参数，分别用来表示对那个schema下的那个table添加FGAC规则。
FGAC即Fine_Grained Access Control，即对数据进行过滤的VPD，即提供行级安全性的VPD，即基于行的VPD。
==============================================================
E.建立审计规则E（监控销售表上执行的查询语句）
SQL> begin dbms_rls.add_policy
2 (object_schema=>'scott',
3 object_name=>'sales',
4 policy_name=>'fgac_vpd',
5 function_schema=>'scott',
6 policy_function=>'vpd_add_condition',
7 statement_types=>'select',
8 enable=>true);
9 end;
10 /
建立审计规则E，监控到sales销售表执行查询语句时，就调用vpd_add_condition函数进行处理。
==============================================================
测试结果：
SQL>connect s001/s001
SQL> select * from scott.sales;
PRODUCT_ PRICE QTYS SELLER
-------- ---------- ---------- --------
0001 10000 1 S001
SQL>connect s002/s002
PRODUCT_ PRICE QTYS SELLER
-------- ---------- ---------- -------
0002 20000 2 S002
SQL>connect mgr/mgr123
SQL> select * from scott.sales;
PRODUCT_ PRICE QTYS SELLER
-------- ---------- ---------- --------
0001 10000 1 S001
0002 20000 2 S002
测试3个用户登录时，两个销售用户只能查到自己的销售数据，而mgr用户则无限制。VPD实现完成。
SQL> exec dbms_rls.drop_policy('scott','sales','fgac_vpd');
SQL> drop function scott.vpd_add_condition;
==============================================================
基于列的VPD
仅有具有权限的用户才能访问敏感列，否则列数据被屏蔽。
SQL> create or replace function scott.vpd_col_condition
2 (p_owner varchar2,p_obj varchar2)
3 return varchar2 is
4 v_condition varchar2(2000);
5 begin
6 if (p_owner=user) then v_condition:=null;
7 else v_condition:='1=2';
8 end if;
9 return v_condition;
10 end;
11 /
建立规则函数，判断执行查询语句的用户是不是表的属主，是则不做限制，不是则添加条件1=2；
SQL> begin dbms_rls.add_policy
2 (object_schema=>'scott',
3 object_name=>'emp',
4 policy_name=>'fgac_col_vpd',
5 function_schema=>'scott',
6 policy_function=>'vpd_col_condition',
7 statement_types=>'select',
8 sec_relevant_cols=>'sal',
9 sec_relevant_cols_opt=>dbms_rls.all_rows);
10 end;
11 /
建立审计规则，监控到emp表上执行查询语句时，就调用vpd_col_condition函数判断执行查询语句的用户是不是表的属主，是则不做限制，不是则对所有的记录（由参数sec_relevant_cols_opt=>dbms_rls.all_rows指定）的sal列（sec_relevant_cols=>'sal'指定）单独添加条件1=2（即隐藏该列的数据）
SQL> connect scott/tiger
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- -
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- -
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
SQL>connect mgr/mgr123
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- -
7369 SMITH CLERK 7902 17-12月-80 20
7499 ALLEN SALESMAN 7698 20-2月 -81 300 30
7521 WARD SALESMAN 7698 22-2月 -81 500 30
7566 JONES MANAGER 7839 02-4月 -81 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 30
7782 CLARK MANAGER 7839 09-6月 -81 10
7788 SCOTT ANALYST 7566 19-4月 -87 20
7839 KING PRESIDENT 17-11月-81 10
7844 TURNER SALESMAN 7698 08-9月 -81 0 30
7876 ADAMS CLERK 7788 23-5月 -87 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- -
7900 JAMES CLERK 7698 03-12月-81 30
7902 FORD ANALYST 7566 03-12月-81 20
7934 MILLER CLERK 7782 23-1月 -82 10
测试结果，达到隐藏敏感列要求。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/27634619/viewspace-745807/ ，如需转载，请注明出处，否则将追究法律责任。
