How To Implement A VPD Policy Working With Materialized Views to Avoid ORA-30372 (Doc ID 386755.1)

Last updated on MARCH 29, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.8 to 11.2.0.3 [Release 9.2 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 21-Feb-2013***

Symptoms

Comment:

This document describes a method to avoid encountering an ORA-30372 error when attempting to create a materialized view on a table which is also managed by fine grain access control.

 

You receive the following error when you commit an insert to a table:

SQL> insert into mytab values (100,'100');
1 row created.
SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-30372: fine grain access policy conflicts with materialized view

Changes

You have both a restrictive VPD policy defined on the table and a materialized view with the attribute REFRESH FAST ON COMMIT, for example:

create table APP.MyTab(
    MyID  number,
    MyVal varchar2( 10 ),
    constraint XPKMyTab primary key( MyID )
) ;
grant select, insert, update, delete on APP.MyTab to APPUSR ;
create materialized view log on APP.MyTab with sequence,
primary key( MyVal ) including new values ;

create materialized view APP.VwMVMyTab
REFRESH FAST ON COMMIT
as
select MyID,
MyVal
from APP.MyTab ;
create or replace function APP.GetPredicate
( p_cOwner varchar2, p_cObjName varchar2 )
return varchar2
as
begin
return 'myid <>123' ;
end GetPredicate ;
/
begin
  SYS.DBMS_RLS.Add_Policy( Object_Schema   => 'APP',
                           Object_Name     => 'MYTAB',
                           Function_Schema => 'APP',
                           Policy_Function => 'GETPREDICATE',
                           Policy_Name     => 'MYTABPOLICY',
                           STATEMENT_TYPES => 'SELECT, INSERT, UPDATE, DELETE' ) ;
end ;
/

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms