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,
Whitehorses is specialized in succesfully implementing Oracle SOA solutions: BPEL, OSB, WebLogic & BPM
{ 5 comments… read them below or add one }
Hi Peter,
can You tell me where to “put” this little code fragment as mentioned above?
Many thanks in advance.
Regards,
Peter
Hi Peter,
You put this code at the start of your VPD policy function. If you look at the VPD tutorial in the Oracle documentation:
http://download-uk.oracle.com/docs/cd/B28359_01/network.111/b28531/vpd.htm#insertedID4
At step 2 the function is created, in this function you would put the code in the line after the BEGIN.
Please try to bypass the policy for the user
grant EXEMPT ACCESS POLICY to USERNAME;
and try again to create MV view.
Thanks
Manish
or you can add “USING TRUSTED CONSTRAINTS” in your CREATE MVIEW command.
thank you tim
{ 1 trackback }