Home » SQL & PL/SQL » SQL & PL/SQL » ORA-30372: fine grain access policy conflicts with materialized view (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0)
ORA-30372: fine grain access policy conflicts with materialized view [message #689826] Fri, 10 May 2024 14:50 Go to next message
wtolentino
Messages: 420
Registered: March 2005
Senior Member
when attempting to create a materialized view that is based on a view for which the view is based on a table on a remote database. it is throwing an error "ORA-30372: fine grain access policy conflicts with materialized view". the table on the remote view has FGA that is currently disable yet it is still throwing "ORA-30372".

SQL> select object_name, policy_name, enable
  2  from dba_policies where object_name = 'WRT_COVID19_SE_STAGING';

OBJECT_NAME                    POLICY_NAME                    ENABLE
------------------------------ ------------------------------ --------------------
WRT_COVID19_SE_STAGING         CODE_WRT_COVID19_SE_STA_POL_00 NO

SQL>

CREATE MATERIALIZED VIEW PEBTOWNER.PEBT_AGENTAPP_PEBT4_MVW
REFRESH FORCE ON DEMAND 
AS 
SELECT *
FROM WRT_COVID19_SE_STAGING sestag
WHERE sestag.project_grp_nbr IN (1250,1251,1255,1260,1261,1265)
ORDER BY CHILD_NYSSIS_ID,SCHOOL_BED_TXT
Error report -
ORA-30372: fine grain access policy conflicts with materialized view
30372. 00000 -  "fine grain access policy conflicts with materialized view"
*Cause:    A fine grain access control procedure has applied a non-null policy
           to the query for the materialized view.

WRT_COVID19_SE_STAGING is a synonym for WRT_COVID19_SE_STAGING view. The view is based on a remote table "WRTDBA.WRT_COVID19_SE_STAGING@wrtd".
Re: ORA-30372: fine grain access policy conflicts with materialized view [message #689827 is a reply to message #689826] Fri, 10 May 2024 15:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
ORA-30372: fine grain access policy conflicts with materialized view
 *Cause:  A fine grain access control procedure has applied a non-null policy
          to the query for the materialized view.

 *Action: In order for the materialized view to work correctly, any fine
          grain access control procedure in effect for the query must
          return a null policy when the materialized view is being
          created or refreshed.  This may be done by ensuring that the
          usernames for the creator, owner, and invoker of refresh
          procedures for the materialized view all receive a null policy
          by the user-written fine grain access control procedures.
Re: ORA-30372: fine grain access policy conflicts with materialized view [message #689828 is a reply to message #689827] Fri, 10 May 2024 15:50 Go to previous messageGo to next message
wtolentino
Messages: 420
Registered: March 2005
Senior Member
FGA is currently disabled

SQL> select object_name, policy_name, enable
  2  from dba_policies where object_name = 'WRT_COVID19_SE_STAGING';

OBJECT_NAME                    POLICY_NAME                    ENABLE
------------------------------ ------------------------------ --------------------
WRT_COVID19_SE_STAGING         CODE_WRT_COVID19_SE_STA_POL_00 NO

SQL>
Re: ORA-30372: fine grain access policy conflicts with materialized view [message #689829 is a reply to message #689828] Sat, 11 May 2024 03:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Instead of disabling the policy, try the suggested action: return null from the policy function.

Re: ORA-30372: fine grain access policy conflicts with materialized view [message #689830 is a reply to message #689829] Sun, 12 May 2024 16:47 Go to previous message
wtolentino
Messages: 420
Registered: March 2005
Senior Member
apparently using this option fixed the issue.

REFRESH FORCE ON DEMAND using TRUSTED CONSTRAINTS
Previous Topic: PL SQL table generate auto index
Next Topic: Get the MetaData for FGA Policy
Goto Forum:
  


Current Time: Thu Nov 21 08:00:59 CST 2024