Once in a while you encounter an Oracle error you never seen before. Today I did and I want to share the solution. The Oracle error was ORA-30372: fine grain access policy conflicts with materialized view. The situation in which this occurred was a VPD policy I created on a table that was used in the materialized view.
Looking up the error the following description and solution is provided:
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.
Cause is clear, the VPD policy should return NULL when refreshing the materialized view, but the solution is not satisfactory. The invoker of the refresh is a user that should get restricted data access in other situations, so returning a null policy for this user is not the solution. Luckily there is another solution and it’s easy too. Put the following code at the start of the policy function:
if dbms_mview.i_am_a_refresh then return null; end if;
This way only the refresh of the materialized view returns a null policy.Refreshing a materialized view on a table with VPD,