ORACLE 安全访问策略VPD与ORA-28132
今天下午，开发同事反馈，某地市的医保oracle数据库执行存储过程报错，报错信息是ORA-28132：
ERROR IS:ORA-28132: MERGE INTO 语法不支持安全策略
相关的sql语句如下：
MERGE /*+ parallel(4) */
INTO DW_BILL B
USING (SELECT T1.HIS_ID, SUM(T1.DETAIL_DEDUCT_AMOUNT) AMOUNT
FROM (SELECT DISTINCT T.HIS_ID,
T.DETAIL_ID,
T.DETAIL_DEDUCT_AMOUNT
FROM TMP_DEDUCTIONPLAN_BILLDETAIL T) T1
GROUP BY T1.HIS_ID) T1
ON (B.HISID = T1.HIS_ID)
WHEN MATCHED THEN
UPDATE
SET B.BMI_NOPAY =
(CASE
WHEN T1.AMOUNT >= B.BMI_NOPAY THEN
0
ELSE
B.BMI_NOPAY - T1.AMOUNT
END),
B.RULETYPE = 0
WHERE B.TABLE_PAR >= I_TABLEPAR_BEGIN
AND B.TABLE_PAR < I_TABLEPAR_END;
查看oracle联机文档解释：
[oracle@se31 ~]$ oerr ora 28132
28132, 00000, "The MERGE INTO syntax does not support the security policy."
// *Cause: The MERGE INTO syntax did not support a security policy on the
// destination table, because the policy's statement_types did
// not include each of INSERT, UPDATE and DELETE.
// *Action: If you do not have the privilege to modify the security policy,
// then instead of MERGE INTO, use the INSERT, UPDATE and DELETE DML
// statements on the table that has a security policy defined on it.
// If you have the privilege to modify the security policy, then
// redefine it in such a way that statement_types includes all of
// the DML statement types (INSERT, UPDATE and DELETE).
//
[oracle@se31 ~]$
根据oracle联机文档解释是merge into语句涉及的目标表上有安全访问控制策略，oracle MOS官方文档（ID 2258901.1）也提示确认merge into目标表上是否有安全控制策略。
select OBJECT_OWNER,OBJECT_NAME,POLICY,PREDICATE from v$vpd_policy;
使用如下sql语句确定是否有安全访问控制策略：
select distinct object_owner,object_name,policy,predicate
from v$vpd_policy
where object_name in('DW_BILLDETAIL','DW_BILL') order by 1,2,3;
经确认，merge into的sql语句涉及的目标表上的确添加了访问控制策略：ACLINSERT、ACLQUERY、ACLUPDATE，
但是，merge into语句使用要求insert、delete、update同时支持，所以merge into 语句执行报错ORA-28132。
解决方法：
1、为merge into涉及的目标表添加ACLDELETE策略:
BEGIN
DBMS_RLS.add_policy (object_schema => 'BMI',
object_name => 'DW_BILLDETAIL',
policy_name => 'ACLDELETE',
function_schema => 'BMI',
policy_function => 'F_LIMITED_DELETE_DW_BILLDETAIL');--需要根据需求自定义
END;
/
2、删除merge into涉及的目标表上添加的访问控制策略：
BEGIN
DBMS_RLS.drop_policy (object_schema => 'BMI',
object_name => 'DW_BILLDETAIL',
policy_name => 'ACLUPDATE');
END;
/
BEGIN
DBMS_RLS.drop_policy (object_schema => 'BMI',
object_name => 'DW_BILLDETAIL',
policy_name => 'ACLINSERT');
END;
/
另外：oracle vpd即虚拟专用数据库
所谓虚拟专用数据库(VPD)指的是，通过在数据库里进行配置，从而让不同的用户只能查看某 个表里的部分数据。
VPD分为以下两个级别：
行级别：在该级别下，可以控制某些用户只能查看到某些数据行。比如，对于销售数据表sales 来说，每个销售人员只能检索出他自己的销售数据，
不能查询其他销售人员的销售数据。
列级别：在该级别下，可以控制某些用户不能检索某个表的某个列的值。比如用户HR 下的 employees 表中，含有工资(salary)列，由于该列比较敏感，
因此不让其他用户查询该列的值。 其他用户检索该列时，会发现其值全都为空(null )。
