Shulou(Shulou.com)06/01 Report--

This afternoon, the developer colleague reported that the stored procedure of the medical insurance oracle database of a certain city reported an error, and the error message was ORA-28132:

ERROR IS:ORA-28132: MERGE INTO syntax does not support sql statements related to security policy as follows: 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 = > 'FairLIMITEDD deleted DWG BILLDETAIL')-- it needs to be customized according to the requirements

END

/

2. Delete the access control policy added to the target table involved in 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

/

In addition: oracle vpd is a virtual private database

The so-called virtual private database (VPD) refers to the configuration in the database so that different users can only view part of the data in a table.

VPD is divided into two levels:

Row level: at this level, you can control that some users can only see certain rows of data. For example, for the sales data table sales, each salesperson can only retrieve his own sales data

You cannot query the sales data of other salespeople.

Column level: at this level, you can control that some users cannot retrieve the value of a column in a table. For example, the employees table under user HR contains a salary column, because this column is more sensitive

Therefore, other users are not allowed to query the value of the column. When other users retrieve the column, they will find that its values are all null.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.