测试vpd 和 application context

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>

17:42:09 SQL> create or replace context application_context_test_1 using application_context_test_1_pkg;

Context created.

Elapsed: 00:00:00.01

19:27:35 SQL> select * from dba_context where namespace='APPLICATION_CONTEXT_TEST_1';

NAMESPACE		       SCHEMA			      PACKAGE				       TYPE		      ORIGIN_CON_ID TRA
------------------------------ ------------------------------ ---------------------------------------- ---------------------- ------------- ---
APPLICATION_CONTEXT_TEST_1     SYS			      APPLICATION_CONTEXT_TEST_1_PKG	       ACCESSED LOCALLY 		 12 YES

Elapsed: 00:00:00.02

create or replace package application_context_test_1_pkg 
is
procedure set_col_a;
end;
/

create or replace package body application_context_test_1_pkg is
procedure set_col_a
as
col_a_var varchar2(80) :='999';
begin
	if sys_context('userenv','session_user') = 'PDBADMIN' then
		col_a_var := '160';
	elsif sys_context('userenv','session_user') = 'HR_MANAGER' then
		col_a_var := '170';
	end if;
	dbms_session.set_context('application_context_test_1','col_a_c',col_a_var);
end set_col_a;
end;
/

create trigger logon_after after logon on database
begin
	sys.application_context_test_1_pkg.set_col_a;
end;
/


创建policy所用的policy function

create or replace function vpd_policy_context_t_vpd_1 (schema_var in varchar2,table_var in varchar2) 
return varchar2
is
predicate_var varchar2(80);
begin
	predicate_var := 'col_a = '''||sys_context('application_context_test_1','col_a_c')||'''';
	return predicate_var;

end vpd_policy_context_t_vpd_1;
/

20:04:35 SQL>  exec dbms_rls.add_policy(object_schema=>'pdbadmin',object_name=>'t_vpd_1',policy_name=>'vpd_policy_context_t_vpd_1',function_schema=>'sys',policy_function=>'vpd_policy_context_t_vpd_1',policy_type=>dbms_rls.static);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01