Hi all!
I'm testing my function in the new relase 19c. I writed them in 11g database.
In 11g i've created 3 policies on 3 tables:
BEGIN DBMS_RLS.ADD_GROUPED_POLICY('"AU_DWH"','"ANAG_CLIENTI"','"GDPR"','"ANAG_CLIENTI_S"','"DBAVPD"','VPD_GDPR','SELECT',FALSE,TRUE,FALSE,dbms_rls.CONTEXT_SENSITIVE,FALSE,'"COGNOME","NUM_DOCUMENTO","NOME"',dbms_rls.ALL_ROWS); END;
BEGIN
DBMS_RLS.ADD_GROUPED_POLICY('"AU_DWH"','"ANAG_CODICI_CLIENTI"','"GDPR"','"ANAG_CODICI_CLIENTI_S"','"DBAVPD"','VPD_GDPR','SELECT',FALSE,TRUE,FALSE,dbms_rls.CONTEXT_SENSITIVE,FALSE,'"SYN_CLI"',dbms_rls.ALL_ROWS);
END;
BEGIN DBMS_RLS.ADD_GROUPED_POLICY('"AU_DBR"','"QDF_TBL_ANASOT_A1"','"GDPR"','"QDF_TBL_ANASOT_A1_S"','"DBAVPD"','VPD_GDPR','SELECT',FALSE,TRUE,FALSE,dbms_rls.CONTEXT_SENSITIVE,FALSE,'"COGNOME","NOME","COD_FISC_PART_IVA","TELEFONO","DOCUMENTO_NUM","COD_FISC","PART_IVA"',dbms_rls.ALL_ROWS);
END;
And the following query runs without problems:
MERGE INTO AU_DWH.ANAG_CLIENTI TMASTER
USING (
SELECT T2.IDE_CLI, MAX(T1.ATECO) AS ATECO
FROM AU_DBR.QDF_TBL_ANASOT_A1 T1,
AU_DWH.ANAG_CODICI_CLIENTI T2,
AU_DWH.ANAG_CLIENTI T3
WHERE T1.COD_FISC_PART_IVA = T2.SYN_CLI
AND T2.IDE_CLI = T3.IDE_CLI
AND T3.VALIDO_A = TO_DATE('31/12/2999','DD/MM/YYYY')
AND T1.ATECO IS NOT NULL
GROUP BY T2.IDE_CLI
) TDETT
ON (TMASTER.IDE_CLI = TDETT.IDE_CLI AND TMASTER.VALIDO_A =
TO_DATE('31/12/2999','DD/MM/YYYY'))
WHEN MATCHED THEN UPDATE SET TMASTER.COD_ATECO = TDETT.ATECO;
COMMIT;
In the 19c (with some VPD function, and users privilege) the query returns the error:
Errore SQL: ORA-28132: The MERGE INTO syntax does not support the security policy.
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 or the policy had
security-relevant column.
*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) or ensure that
the policy does not have a security-relevant column.
So I create the following policies:
BEGIN DBMS_RLS.ADD_GROUPED_POLICY('"AU_DWH"','"ANAG_CLIENTI"','"GDPR"','"ANAG_CLIENTI_I"','"DBAVPD"','VPD_GDPR','INSERT',FALSE,TRUE,FALSE,dbms_rls.CONTEXT_SENSITIVE,FALSE); END;
BEGIN DBMS_RLS.ADD_GROUPED_POLICY('"AU_DWH"','"ANAG_CLIENTI"','"GDPR"','"ANAG_CLIENTI_U"','"DBAVPD"','VPD_GDPR','UPDATE',FALSE,TRUE,FALSE,dbms_rls.CONTEXT_SENSITIVE,FALSE); END;
BEGIN DBMS_RLS.ADD_GROUPED_POLICY('"AU_DWH"','"ANAG_CLIENTI"','"GDPR"','"ANAG_CLIENTI_D"','"DBAVPD"','VPD_GDPR','DELETE',FALSE,TRUE,FALSE,dbms_rls.CONTEXT_SENSITIVE,FALSE); END;
The insert policy fails with error:
Report error -
ORA-28104: input value for statement_types is not valid
ORA-06512: at "SYS.DBMS_RLS_INT", line 240
ORA-06512: at "SYS.DBMS_RLS", line 273
ORA-06512: at line 1
28104. 00000 - "input value for %s is not valid"
*Cause: Input value for the argument is not valid
*Action: specify a valid argument value.
I don't understand why in 11g version it runs without problem and in 19c i can't run it!
If i drop only the SELECT policy on ANAG_CLIENTI the merge runs.