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