Home » SQL & PL/SQL » SQL & PL/SQL » Error: ORA-30372: fine grain access policy conflicts with material
Error: ORA-30372: fine grain access policy conflicts with material [message #433587] Thu, 03 December 2009 02:48 Go to next message
dyahav
Messages: 32
Registered: March 2009
Member
Hi all,
I have a table, named A, that a policy is defined on it.
On table A I have a view, named A_view, that select several columns.
So far, it works properly.

I try to define a materilized view on the view A_view but I get the following error:
Error: ORA-30372: fine grain access policy conflicts with material.

I have read few posts regarding this error and I found a post that says to add to following to
the policy function:
IF dbms_mview.i_am_a_refresh THEN
v_predicate:=NULL;
else....

But it doesn't work and I still get the error mentioned above.
Does anybody know how to solve this error?
Thanks
dyahav


Re: Error: ORA-30372: fine grain access policy conflicts with material [message #433589 is a reply to message #433587] Thu, 03 December 2009 03:06 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
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.

The solution you posted should work.
Be sure the policy was reevaluated after your modifications: drop and recreate it.
You have to post the code if you want more help.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel

[Updated on: Thu, 03 December 2009 03:06]

Report message to a moderator

Re: Error: ORA-30372: fine grain access policy conflicts with material [message #433592 is a reply to message #433589] Thu, 03 December 2009 03:23 Go to previous messageGo to next message
dyahav
Messages: 32
Registered: March 2009
Member
Hi,
Thanks for the quick response.

The problem is that the following doesn't work:
IF dbms_mview.i_am_a_refresh THEN
v_predicate:=NULL;
else....


I changed the policy function to retrun NULL anyway and
the MV was created successfully. It means that the dbms_mview.i_am_a_refresh doesn't work properly.

Do you have any suggestion how to solve it?
Thanks
dyahav
Re: Error: ORA-30372: fine grain access policy conflicts with material [message #433595 is a reply to message #433592] Thu, 03 December 2009 03:42 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
It means that the dbms_mview.i_am_a_refresh doesn't work properly.

If you say so.

Quote:
Do you have any suggestion how to solve it?

No one without the full version number.

Regards
Michel

[Updated on: Thu, 03 December 2009 03:43]

Report message to a moderator

Re: Error: ORA-30372: fine grain access policy conflicts with material [message #433596 is a reply to message #433595] Thu, 03 December 2009 03:45 Go to previous messageGo to next message
dyahav
Messages: 32
Registered: March 2009
Member
Hi,
I use oracle 11.1.0.6.0.
Thanks
dyahav
Re: Error: ORA-30372: fine grain access policy conflicts with material [message #433597 is a reply to message #433596] Thu, 03 December 2009 03:54 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search on MOS for bug 6952718.

Regards
Michel
Re: Error: ORA-30372: fine grain access policy conflicts with material [message #433598 is a reply to message #433596] Thu, 03 December 2009 03:56 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Explainations in note 604046.1

Regards
Michel
Re: Error: ORA-30372: fine grain access policy conflicts with material [message #433599 is a reply to message #433598] Thu, 03 December 2009 04:21 Go to previous messageGo to next message
dyahav
Messages: 32
Registered: March 2009
Member
Could you please let me know where can I find the note 604046.1?
Where can I find bug 6952718?
Thanks
dyahav
Re: Error: ORA-30372: fine grain access policy conflicts with material [message #433612 is a reply to message #433599] Thu, 03 December 2009 05:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
metalink.oracle.com
Re: Error: ORA-30372: fine grain access policy conflicts with material [message #433616 is a reply to message #433599] Thu, 03 December 2009 05:19 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now: https://support.oracle.com

Regards
Michel
Re: Error: ORA-30372: fine grain access policy conflicts with material [message #433617 is a reply to message #433612] Thu, 03 December 2009 05:20 Go to previous messageGo to next message
dyahav
Messages: 32
Registered: March 2009
Member
Hi,

I only need to know if it's a bug. If so, Is there a patch
or something else that can solve the problem?
In which version the feature works?
Thanks
dyahav
Re: Error: ORA-30372: fine grain access policy conflicts with material [message #433618 is a reply to message #433617] Thu, 03 December 2009 05:24 Go to previous message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I only need to know if it's a bug. If so, Is there a patch
or something else that can solve the problem?

To know this you have to connect to the site we gave you and read the notes we indicated.
If you can't because you have no access anyway you can't get the patch if it exists.

Regards
Michel
Previous Topic: sql restictions of 4000 charactes in in clause
Next Topic: Sql query for display client version.
Goto Forum:
  


Current Time: Sun Sep 25 14:32:03 CDT 2016

Total time taken to generate the page: 0.25527 seconds