测试VPD的policy group

最新推荐文章于 2022-09-08 17:08:56 发布

wgz7747147820

最新推荐文章于 2022-09-08 17:08:56 发布

阅读量253

点赞数

分类专栏： orale 文章标签： oracle

本文链接：https://blog.csdn.net/wgz7747147820/article/details/112884215

版权

orale 专栏收录该内容

203 篇文章 0 订阅

订阅专栏

首先创建一个测试表，插入一些测试数据

23:49:47 SQL> create table t_policy_group_1(group_a_col1 varchar2(20),group_a_col2 varchar2(20),group_b_col1 varchar2(20),group_b_col2 varchar2(20));

Table created.

Elapsed: 00:00:00.05

23:50:41 SQL> insert into t_policy_group_1 values('group_a_row1','group a 20210120','group_b_row1','group b 202101221522');

1 row created.

Elapsed: 00:00:00.01

23:53:33 SQL> insert into t_policy_group_1 values('group_a_row2','group a 1553','group_b_row2','group b 1554');

1 row created.

Elapsed: 00:00:00.00
23:54:03 SQL> commit;

Commit complete.

Elapsed: 00:00:00.00

23:56:54 SQL>  exec dbms_rls.create_policy_group(object_schema=>'pdbadmin',object_name=>'t_policy_group_1',policy_group=>'policy_group_a');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
23:56:59 SQL>  exec dbms_rls.create_policy_group(object_schema=>'pdbadmin',object_name=>'t_policy_group_1',policy_group=>'policy_group_b');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

create or replace function vpd_group_a(schema_var varchar2,table_var varchar2) 
return varchar2
is
predicate_var varchar2(80);
begin
	if sys_context('userenv','session_user') = 'PDBADMIN' then
		predicate_var := '1=2';
	elsif sys_context('userenv','session_user') = 'HR_MANAGER' then
		predicate_var :='1=2';
	end if;
	return predicate_var;
end vpd_group_a;
/


create or replace function vpd_group_b(schema_var varchar2,table_var varchar2) 
return varchar2
is
predicate_var varchar2(80);
begin
	if sys_context('userenv','session_user') = 'PDBADMIN' then
		predicate_var := '1=2';
	elsif sys_context('userenv','session_user') = 'HR_MANAGER' then
		predicate_var :='1=2';
	end if;
	return predicate_var;
end vpd_group_b;
/

如果像上面这样创建两个policy function，则最终的测试结果是这样

06:30:24 SQL> select * from pdbadmin.t_policy_group_1;

G G G G
- - - -



Elapsed: 00:00:00.01

create or replace function vpd_group_a(schema_var varchar2,table_var varchar2) 
return varchar2
is
predicate_var varchar2(80);
begin
	if sys_context('userenv','session_user') = 'PDBADMIN' then
		predicate_var := '1=2';

	end if;
	return predicate_var;
end vpd_group_a;
/


create or replace function vpd_group_b(schema_var varchar2,table_var varchar2) 
return varchar2
is
predicate_var varchar2(80);
begin
	if sys_context('userenv','session_user') = 'HR_MANAGER' then
		predicate_var :='1=2';
	end if;
	return predicate_var;
end vpd_group_b;
/

像上面这样创建的policy function，则功能就是正常的

06:32:04 SQL> select * from pdbadmin.t_policy_group_1;

GROUP_A_COL1	     GROUP_A_COL2	  G G
-------------------- -------------------- - -
group_a_row1	     group a 20210120
group_a_row2	     group a 1553

Elapsed: 00:00:00.01

00:15:00 SQL> create or replace context vpd_group_ctx using vpd_group_ctx_pkg;

Context created.

Elapsed: 00:00:00.01

create or replace package vpd_group_ctx_pkg is
procedure set_group;
end vpd_group_ctx_pkg;
/

create or replace package body vpd_group_ctx_pkg is
procedure set_group
as
begin
	case sys_context('userenv','session_user')
		when 'PDBADMIN' then
			dbms_session.set_context('vpd_group_ctx','pgroup','policy_group_a');
		when 'HR_MANAGER' then
			dbms_session.set_context('vpd_group_ctx','pgroup','policy_group_b');
	end case;

end set_group;
end vpd_group_ctx_pkg;
/

00:31:13 SQL>  exec dbms_rls.add_policy_context(object_schema=>'pdbadmin',object_name=>'t_policy_group_1',namespace=>'vpd_group_ctx',attribute=>'pgroup');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02

添加这种grouped policy的时候不能使用static，应该使用context sensitive类型的policy

00:42:13 SQL>  exec dbms_rls.add_grouped_policy(object_schema=>'pdbadmin',object_name=>'t_policy_group_1',policy_group=>'policy_group_a',policy_name=>'policy_a',function_schema=>'sys',policy_function=>'vpd_group',policy_type=>dbms_rls.static,sec_relevant_cols=>'group_b_col1,group_b_col2',sec_relevant_cols_opt=>dbms_rls.all_rows,namespace=>'vpd_group_ctx',attribute=>'pgroup');
BEGIN dbms_rls.add_grouped_policy(object_schema=>'pdbadmin',object_name=>'t_policy_group_1',policy_group=>'policy_group_a',policy_name=>'policy_a',function_schema=>'sys',policy_function=>'vpd_group',policy_type=>dbms_rls.static,sec_relevant_cols=>'group_b_col1,group_b_col2',sec_relevant_cols_opt=>dbms_rls.all_rows,namespace=>'vpd_group_ctx',attribute=>'pgroup'); END;

*
ERROR at line 1:
ORA-28146: invalid operation for policy "POLICY_A"
ORA-06512: at "SYS.DBMS_RLS_INT", line 296
ORA-06512: at "SYS.DBMS_RLS", line 287
ORA-06512: at line 1


Elapsed: 00:00:00.06

00:50:16 SQL>  exec dbms_rls.add_grouped_policy(object_schema=>'pdbadmin',object_name=>'t_policy_group_1',policy_group=>'policy_group_a',policy_name=>'policy_a',function_schema=>'sys',policy_function=>'vpd_group',policy_type=>dbms_rls.context_sensitive,sec_relevant_cols=>'group_b_col1,group_b_col2',sec_relevant_cols_opt=>dbms_rls.all_rows,namespace=>'vpd_group_ctx',attribute=>'pgroup');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05


00:51:13 SQL>  exec dbms_rls.add_grouped_policy(object_schema=>'pdbadmin',object_name=>'t_policy_group_1',policy_group=>'policy_group_b',policy_name=>'policy_b',function_schema=>'sys',policy_function=>'vpd_group',policy_type=>dbms_rls.context_sensitive,sec_relevant_cols=>'group_a_col1,group_a_col2',sec_relevant_cols_opt=>dbms_rls.all_rows,namespace=>'vpd_group_ctx',attribute=>'pgroup');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00


create or replace function vpd_group_a(schema_var varchar2,table_var varchar2) 
return varchar2
is
predicate_var varchar2(80);
begin
	if sys_context('userenv','session_user') = 'PDBADMIN' then
		predicate_var := '1=2';
	elsif sys_context('userenv','session_user') = 'HR_MANAGER' then
		predicate_var :='1=2';
	end if;
	return predicate_var;
end vpd_group_a;
/


create or replace function vpd_group_b(schema_var varchar2,table_var varchar2) 
return varchar2
is
predicate_var varchar2(80);
begin
	if sys_context('userenv','session_user') = 'PDBADMIN' then
		predicate_var := '1=2';
	elsif sys_context('userenv','session_user') = 'HR_MANAGER' then
		predicate_var :='1=2';
	end if;
	return predicate_var;
end vpd_group_b;
/


07:41:10 SQL> select * from t_policy_group_1;

G G G G
- - - -



Elapsed: 00:00:00.01
07:41:31 SQL> select sql_hash,sql_id,object_owner,object_name,policy_group,policy,policy_function_owner,predicate,con_id from v$vpd_policy;

  SQL_HASH SQL_ID	 OBJECT_OWN OBJECT_NAME 	 POLICY_GROUP	 POLICY 		    POLIC PREDICATE			     CON_ID
---------- ------------- ---------- -------------------- --------------- -------------------------- ----- ------------------------------ ----------
3402380368 6u8n4am5csd2h PDBADMIN   T_VPD_1		 SYS_DEFAULT	 VPD_POLICY_CONTEXT_T_VPD_1 SYS   col_a = '160' 			 12
 300597836 9rs92q08yphkc PDBADMIN   T_VPD_1		 SYS_DEFAULT	 VPD_POLICY_CONTEXT_T_VPD_1 SYS 					 12
 431025690 af8qz18cv1vhu PDBADMIN   T_VPD_1		 SYS_DEFAULT	 VPD_POLICY_CONTEXT_T_VPD_1 SYS   col_a = '160' 			 12
2508661550 bvvtchfasf8tf PDBADMIN   T_POLICY_GROUP_1	 POLICY_GROUP_A  POLICY_A		    SYS   1=2					 12
2508661550 bvvtchfasf8tf PDBADMIN   T_POLICY_GROUP_1	 POLICY_GROUP_B  POLICY_B		    SYS   1=2					 12
 716255473 b3tn5v0pb2c7j PDBADMIN   T_POLICY_GROUP_1	 POLICY_GROUP_A  POLICY_A		    SYS 					 12
 716255473 b3tn5v0pb2c7j PDBADMIN   T_POLICY_GROUP_1	 POLICY_GROUP_B  POLICY_B		    SYS 					 12

7 rows selected.

Elapsed: 00:00:00.15

######################################################
上面和下面作为对比
######################################################


create or replace function vpd_group_a(schema_var varchar2,table_var varchar2) 
return varchar2
is
predicate_var varchar2(80);
begin
	if sys_context('userenv','session_user') = 'PDBADMIN' then
		predicate_var := '1=2';

	end if;
	return predicate_var;
end vpd_group_a;
/


create or replace function vpd_group_b(schema_var varchar2,table_var varchar2) 
return varchar2
is
predicate_var varchar2(80);
begin
	if sys_context('userenv','session_user') = 'HR_MANAGER' then
		predicate_var :='1=2';
	end if;
	return predicate_var;
end vpd_group_b;
/



07:41:42 SQL> select * from t_policy_group_1;

GROUP_A_COL1	     GROUP_A_COL2	  G G
-------------------- -------------------- - -
group_a_row1	     group a 20210120
group_a_row2	     group a 1553

Elapsed: 00:00:00.01
07:44:27 SQL> select sql_hash,sql_id,object_owner,object_name,policy_group,policy,policy_function_owner,predicate,con_id from v$vpd_policy;

  SQL_HASH SQL_ID	 OBJECT_OWN OBJECT_NAME 	 POLICY_GROUP	 POLICY 		    POLIC PREDICATE			     CON_ID
---------- ------------- ---------- -------------------- --------------- -------------------------- ----- ------------------------------ ----------
3402380368 6u8n4am5csd2h PDBADMIN   T_VPD_1		 SYS_DEFAULT	 VPD_POLICY_CONTEXT_T_VPD_1 SYS   col_a = '160' 			 12
 300597836 9rs92q08yphkc PDBADMIN   T_VPD_1		 SYS_DEFAULT	 VPD_POLICY_CONTEXT_T_VPD_1 SYS 					 12
 431025690 af8qz18cv1vhu PDBADMIN   T_VPD_1		 SYS_DEFAULT	 VPD_POLICY_CONTEXT_T_VPD_1 SYS   col_a = '160' 			 12
2508661550 bvvtchfasf8tf PDBADMIN   T_POLICY_GROUP_1	 POLICY_GROUP_A  POLICY_A		    SYS   1=2					 12
2508661550 bvvtchfasf8tf PDBADMIN   T_POLICY_GROUP_1	 POLICY_GROUP_B  POLICY_B		    SYS 					 12
 716255473 b3tn5v0pb2c7j PDBADMIN   T_POLICY_GROUP_1	 POLICY_GROUP_A  POLICY_A		    SYS 					 12
 716255473 b3tn5v0pb2c7j PDBADMIN   T_POLICY_GROUP_1	 POLICY_GROUP_B  POLICY_B		    SYS 					 12

7 rows selected.

Elapsed: 00:00:00.15

wgz7747147820

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
测试VPD的policy group

首先创建一个测试表，插入一些测试数据23:49:47 SQL> create table t_policy_group_1(group_a_col1 varchar2(20),group_a_col2 varchar2(20),group_b_col1 varchar2(20),group_b_col2 varchar2(20));Table created.Elapsed: 00:00:00.0523:50:41 SQL> insert into t_policy_group_1
复制链接

扫一扫