SQL> alter session set container=SALESPDB;

Session altered.

SQL> create or replace context emp_ctx using emp_ctx_pkg;

Context created.

# 2. 创建上下文管理包
SQL> create or replace package emp_ctx_pkg is
procedure set_dept_id(p_dept_id in number);
function get_dept_id return number;
end emp_ctx_pkg;
/

Package created.

SQL> create or replace package body emp_ctx_pkg is
procedure set_dept_id(p_dept_id in number) is
begin
dbms_session.set_context(‘EMP_CTX’, ‘DEPT_ID’, p_dept_id);
end set_dept_id;

function get_dept_id return number is
v_dept_id number;
begin
v_dept_id := sys_context(‘EMP_CTX’, ‘DEPT_ID’);
return v_dept_id;
end get_dept_id;
end emp_ctx_pkg;
/

Package body created.

# 3. 创建测试表
SQL> create user hr_admin identified by hr_password;

User created.

SQL> grant create session, connect, resource to hr_admin;

Grant succeeded.

SQL> create table hr_admin.employees (
id number primary key,
name varchar2(100),
email varchar2(100),
dept_id number,
salary number,
hire_date date
);

Table created.

# 4. 插入测试数据
SQL> insert into hr_admin.employees values (1, ‘风哥1号’, ‘zhangsan@fgedu.net.cn’, 10, 10000, sysdate – 365);

1 row created.

SQL> insert into hr_admin.employees values (2, ‘风哥2号’, ‘lisi@fgedu.net.cn’, 10, 12000, sysdate – 300);

1 row created.

SQL> insert into hr_admin.employees values (3, ‘王五’, ‘wangwu@fgedu.net.cn’, 20, 15000, sysdate – 200);

1 row created.

SQL> insert into hr_admin.employees values (4, ‘赵六’, ‘zhaoliu@fgedu.net.cn’, 20, 18000, sysdate – 100);

1 row created.

SQL> insert into hr_admin.employees values (5, ‘钱七’, ‘qianqi@fgedu.net.cn’, 30, 20000, sysdate – 50);

1 row created.

SQL> commit;

Commit complete.

# 5. 创建VPD策略函数
SQL> create or replace function hr_admin.emp_dept_vpd_func(
p_schema in varchar2,
p_object in varchar2
) return varchar2 is
v_dept_id number;
v_predicate varchar2(200);
begin
v_dept_id := sys_context(‘EMP_CTX’, ‘DEPT_ID’);

if v_dept_id is null then
v_predicate := ‘1=0’;
else
v_predicate := ‘dept_id = ‘ || v_dept_id;
end if;

return v_predicate;
end emp_dept_vpd_func;
/

Function created.

# 6. 创建VPD策略
SQL> begin
dbms_rls.add_policy(
object_schema => ‘HR_ADMIN’,
object_name => ‘EMPLOYEES’,
policy_name => ‘EMP_DEPT_POLICY’,
function_schema => ‘HR_ADMIN’,
policy_function => ‘EMP_DEPT_VPD_FUNC’,
statement_types => ‘SELECT,INSERT,UPDATE,DELETE’,
enable => true
);
end;
/

PL/SQL procedure successfully completed.

# 7. 验证配置
SQL> select object_schema, object_name, policy_name, policy_function, enabled
from dba_policies
where object_schema = ‘HR_ADMIN’ and object_name = ‘EMPLOYEES’;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME POLICY_FUNCTION ENABLED
————- ———— —————– —————— ——–
HR_ADMIN EMPLOYEES EMP_DEPT_POLICY EMP_DEPT_VPD_FUNC YES

# 8. 测试VPD策略
SQL> conn hr_admin/hr_password@SALESPDB

Connected.

SQL> select * from employees;

no rows selected

SQL> exec emp_ctx_pkg.set_dept_id(10);

PL/SQL procedure successfully completed.

SQL> select * from employees;

ID NAME EMAIL DEPT_ID SALARY HIRE_DATE
———- ———- ———————- ———- ———- ———
1 风哥1号 zhangsan@fgedu.net.cn 10 10000 31-MAR-25
2 风哥2号 lisi@fgedu.net.cn 10 12000 04-JUN-25

Connected.

SQL> select * from employees;

no rows selected

# 分析步骤

# 1. 检查VPD策略
SQL> conn / as sysdba

SQL> alter session set container=SALESPDB;

Session altered.

SQL> select object_schema, object_name, policy_name, enabled
from dba_policies
where object_schema = ‘HR_ADMIN’ and object_name = ‘EMPLOYEES’;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME ENABLED
————- ———— —————– ——–
HR_ADMIN EMPLOYEES EMP_DEPT_POLICY YES

# 2. 检查上下文
SQL> select namespace, attribute, value from v$context where namespace = ‘EMP_CTX’;

no rows selected

# 3. 发现上下文未设置

# 4. 解决方案：设置上下文
SQL> conn hr_admin/hr_password@SALESPDB

Connected.

SQL> exec emp_ctx_pkg.set_dept_id(10);

PL/SQL procedure successfully completed.

# 5. 验证上下文已设置
SQL> select sys_context(‘EMP_CTX’, ‘DEPT_ID’) as dept_id from dual;

DEPT_ID
———-
10

# 6. 再次测试查询
SQL> select * from employees;

ID NAME EMAIL DEPT_ID SALARY HIRE_DATE
———- ———- ———————- ———- ———- ———
1 风哥1号 zhangsan@fgedu.net.cn 10 10000 31-MAR-25
2 风哥2号 lisi@fgedu.net.cn 10 12000 04-JUN-25

# 7. 预防措施
# – 配置自动设置上下文
# – 监控VPD策略状态
# – 定期测试访问控制

Connected.

SQL> select * from employees;

select * from employees
*
ERROR at line 1:
ORA-28112: failed to execute policy function

# 分析步骤

# 1. 检查策略函数
SQL> select object_name, object_type, status from user_objects where object_name = ‘EMP_DEPT_VPD_FUNC’;

OBJECT_NAME OBJECT_TYPE STATUS
——————– ————- ——-
EMP_DEPT_VPD_FUNC FUNCTION VALID

# 2. 测试策略函数
SQL> select hr_admin.emp_dept_vpd_func(‘HR_ADMIN’, ‘EMPLOYEES’) as predicate from dual;

PREDICATE
———–
dept_id = 10

# 3. 检查策略函数逻辑
SQL> select text from user_source where name = ‘EMP_DEPT_VPD_FUNC’ order by line;

TEXT
——————————————————————————–
function emp_dept_vpd_func(
p_schema in varchar2,
p_object in varchar2
) return varchar2 is
v_dept_id number;
v_predicate varchar2(200);
begin
v_dept_id := sys_context(‘EMP_CTX’, ‘DEPT_ID’);

if v_dept_id is null then
v_predicate := ‘1=0’;
else
v_predicate := ‘dept_id = ‘ || v_dept_id;
end if;

return v_predicate;
end emp_dept_vpd_func;

# 4. 发现函数逻辑正常

# 5. 检查VPD策略配置
SQL> select object_schema, object_name, policy_name, policy_function, enabled
from dba_policies
where object_schema = ‘HR_ADMIN’ and object_name = ‘EMPLOYEES’;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME POLICY_FUNCTION ENABLED
————- ———— —————– —————— ——–
HR_ADMIN EMPLOYEES EMP_DEPT_POLICY EMP_DEPT_VPD_FUNC YES

# 6. 尝试重新创建策略
SQL> begin
dbms_rls.drop_policy(
object_schema => ‘HR_ADMIN’,
object_name => ‘EMPLOYEES’,
policy_name => ‘EMP_DEPT_POLICY’
);
end;
/

PL/SQL procedure successfully completed.

SQL> begin
dbms_rls.add_policy(
object_schema => ‘HR_ADMIN’,
object_name => ‘EMPLOYEES’,
policy_name => ‘EMP_DEPT_POLICY’,
function_schema => ‘HR_ADMIN’,
policy_function => ‘EMP_DEPT_VPD_FUNC’,
statement_types => ‘SELECT,INSERT,UPDATE,DELETE’,
enable => true
);
end;
/

PL/SQL procedure successfully completed.

# 7. 再次测试查询
SQL> conn hr_admin/hr_password@SALESPDB

Connected.

SQL> exec emp_ctx_pkg.set_dept_id(10);

PL/SQL procedure successfully completed.

SQL> select * from employees;

ID NAME EMAIL DEPT_ID SALARY HIRE_DATE
———- ———- ———————- ———- ———- ———
1 风哥1号 zhangsan@fgedu.net.cn 10 10000 31-MAR-25
2 风哥2号 lisi@fgedu.net.cn 10 12000 04-JUN-25

# 8. 预防措施
# – 定期检查策略函数状态
# – 测试策略函数逻辑
# – 监控VPD策略错误

if v_username = ‘HR_ADMIN’ then
v_predicate := ‘1=1’;
else
v_predicate := ‘salary <= 15000';
end if;

return v_predicate;
end emp_salary_vpd_func;
/

Function created.

SQL> begin
dbms_rls.add_policy(
object_schema => ‘HR_ADMIN’,
object_name => ‘EMPLOYEES’,
policy_name => ‘EMP_SALARY_POLICY’,
function_schema => ‘HR_ADMIN’,
policy_function => ‘EMP_SALARY_VPD_FUNC’,
statement_types => ‘SELECT’,
enable => true
);
end;
/

PL/SQL procedure successfully completed.

# 2. 验证多个VPD策略
SQL> select object_schema, object_name, policy_name, policy_function, enabled
from dba_policies
where object_schema = ‘HR_ADMIN’ and object_name = ‘EMPLOYEES’;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME POLICY_FUNCTION ENABLED
————- ———— ——————– ——————- ——–
HR_ADMIN EMPLOYEES EMP_DEPT_POLICY EMP_DEPT_VPD_FUNC YES
HR_ADMIN EMPLOYEES EMP_SALARY_POLICY EMP_SALARY_VPD_FUNC YES

# 3. 配置VPD审计
SQL> audit policy oracle_secure_config;

Audit succeeded.

# 4. 监控VPD策略
SQL> select object_schema, object_name, policy_name, enabled
from dba_policies
where object_schema = ‘HR_ADMIN’;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME ENABLED
————- ———— ——————– ——–
HR_ADMIN EMPLOYEES EMP_DEPT_POLICY YES
HR_ADMIN EMPLOYEES EMP_SALARY_POLICY YES

# 5. 创建VPD监控脚本
$ vi /home/oracle/scripts/monitor_vpd.sh

#!/bin/bash
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# VPD监控脚本

export ORACLE_HOME=/oracle/app/oracle/product/19c/dbhome_1
export ORACLE_SID=FGEDUDB
export PATH=$ORACLE_HOME/bin:$PATH

# 检查VPD策略状态
POLICY_COUNT=$(sqlplus -s / as sysdba << EOF set heading off feedback off pagesize 0 alter session set container=SALESPDB;
select count(*) from dba_policies where enabled = ‘NO’;
exit;
EOF
)

if [ “$POLICY_COUNT” -gt 0 ]; then
echo “WARNING: $POLICY_COUNT VPD policies are disabled”
# 发送告警
echo “$POLICY_COUNT VPD policies are disabled” | mail -s “VPD Policy Alert” admin@fgedu.net.cn
fi

# 检查VPD函数状态
FUNCTION_COUNT=$(sqlplus -s / as sysdba << EOF set heading off feedback off pagesize 0 alter session set container=SALESPDB;
select count(*) from user_objects where object_type = ‘FUNCTION’ and status = ‘INVALID’;
exit;
EOF
)

if [ “$FUNCTION_COUNT” -gt 0 ]; then
echo “WARNING: $FUNCTION_COUNT VPD functions are invalid”
# 发送告警
echo “$FUNCTION_COUNT VPD functions are invalid” | mail -s “VPD Function Alert” admin@fgedu.net.cn
fi

echo “VPD monitoring completed at $(date)”

# 6. 设置定期监控
$ crontab -e

# 每小时检查VPD状态
0 * * * * /home/oracle/scripts/monitor_vpd.sh >> /home/oracle/scripts/monitor_vpd.log 2>&1