测试VPD 一个小例子

首先创建一个vpd policy所用的function，一般情况下是要用application context，但是为了简便，就不用了，直接加个predicate就是了

create or replace function vpd_func1(schema_var in varchar2,table_var in varchar2)
return varchar2
is
return_var varchar2(80);
begin
	return_var:='col_a=160';
	return return_var;
end vpd_func1;
/

创建一个测试表，在pdbadmin shema下

17:26:50 SQL> create table t_vpd_1(col_a varchar2(20),col_b varchar2(20));

Table created.

Elapsed: 00:00:00.05

17:27:35 SQL> insert into t_vpd_1 values('160','wang');

1 row created.

Elapsed: 00:00:00.03
17:27:52 SQL> insert into t_vpd_1 values('170','cc');

1 row created.

Elapsed: 00:00:00.01
17:28:03 SQL> insert into t_vpd_1 values('160','aa');

1 row created.

Elapsed: 00:00:00.00
17:28:17 SQL> insert into t_vpd_1 values('888','li');

1 row created.

Elapsed: 00:00:00.01
17:28:28 SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
17:28:30 SQL> select * from t_vpd_1;

COL_A		     COL_B
-------------------- --------------------
160		     wang
170		     cc
160		     aa
888		     li

Elapsed: 00:00:00.01
17:28:34 SQL>

使用policy将function和table关联起来

17:39:59 SQL>  exec dbms_rls.add_policy(object_schema=>'pdbadmin',object_name=>'t_vpd_1',policy_name=>'vpd_policy_t_vpd_1',function_schema=>'sys',policy_function=>'vpd_func1',policy_type=>dbms_rls.static);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02

测试vpd的效果

sys用户不受vpd的影响，可以看到所有的数据
17:40:05 SQL> select * from pdbadmin.t_vpd_1;

COL_A		     COL_B
-------------------- --------------------
160		     wang
170		     cc
160		     aa
888		     li

Elapsed: 00:00:00.00
17:42:03 SQL> show user;
USER is "SYS"

这个表的owner也会受到vpd的影响，只能看到部分数据
17:28:34 SQL> select * from t_vpd_1;

COL_A		     COL_B
-------------------- --------------------
160		     wang
160		     aa

Elapsed: 00:00:00.03
17:40:46 SQL> show user;
USER is "PDBADMIN"

别的用户也会受到影响，只能看到部分数据
17:41:34 SQL> select * from pdbadmin.t_vpd_1;

COL_A		     COL_B
-------------------- --------------------
160		     wang
160		     aa

Elapsed: 00:00:00.03
17:41:51 SQL> show user;
USER is "HR_MANAGER"


更新也收到vpd的影响
17:45:33 SQL> update t_vpd_1 set col_b='000' ;

2 rows updated.

Elapsed: 00:00:00.00
17:45:48 SQL> commit;

Commit complete.

Elapsed: 00:00:00.00

17:46:00 SQL> select * from t_vpd_1;

COL_A		     COL_B
-------------------- --------------------
160		     000
160		     000

Elapsed: 00:00:00.00