ORA-30372解决方法

ORA-30372: Fine Grain Access Policy Conflicts With Materialized View

ORA-30372: 精细粒度访问策略与实体化视图冲突

---

修改时间 23-MAR-2010     类型 HOWTO     状态 PUBLISHED

---

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.6
Information in this document applies to any platform.

Goal

Trying to create a materialized view in 11g against a table located in a remote database table which has VPD enabled crashes with: ORA-30372.

Solution

The issue is described in:
BUG 6952718 - FGAC + MVIEW GIVE ORA-30372 ERRORS EVEN IF USING 10G WORK-AROUNDS
This bug, closed as not being a bug, but the expected behavior is about:
1. table + VPD policies enabled in 10g database
2. remote materialized view in 11g database created against the 10g table.
This always leads to:
ORA-30372 errors
no matter what is the workarounds that used to work in the previous releases.
This is the intended behavior in the 11g release and the error is expected - this follows a security enhancement.
In 11g it was initially not possible create or use a materialized view against a remote table with VPD enabled on it. When trying to workaround by applying the VPD after the materialized view creation would also lead to later failures, when refreshing the materialized view.
This behavior has been changed following enhancement request:
Bug 7016507 - ENH: ALLOW REMOTE MATERIALIZED VIEWS TO BE CREATED AFTER FGAC IS SETUP
implemented in the 11.2 release (no patch needed in this release)
This is also backported on 11.1.0.7:
Bug 8338443 - NEED BACKPORT BLR FOR ENHANCEMENT REQUEST Bug 7016507 IN 11.1.0.7
The patch must be installed on both source and destination if both are 11.1 release.
The solution consists in the expansion the of this MV clause " USING TRUSTED CONSTRAINTS". By using this clause, MVs can be defined on remote tables with VPD enabled, eg:

connect user1@inst1
create materialized view mv1
refresh fast with rowid USING TRUSTED CONSTRAINTS
as
select * from table2@inst2;


Another solution for this situation is to workaround as follows:
Supposing that table A is the remote table and materialized view MV is the object that is desired in the local database:

1. create a materialized view MV1 in the remote database, following the exact logic of the MV materialized view.
2. create the local materialized view M as a simple select * from MV1@ ;

---

< > FOLDER:ST.Server.Security.DBSecurity TOPIC:Fine Grained Access Control DOCUMENT-ID:604046.1 ALIAS: SOURCE:AWIZ 6794460.993 DOCUMENT-TYPE:HOWTO ZCXTECH TITLE:Ora-30372: Fine Grain Access Policy Conflicts With Materialized View IMPACT:LOW SKILL-LEVEL:NOVICE STATUS:PUBLISHED DISTRIBUTION:EXTERNAL ZCXPUBLIC ZCXCURRENT AUTHOR:DANGHEL.RO ERROR:ORA-30372 KEYWORD:ACCESS~POLICY KEYWORD:FGAC KEYWORD:MATERIALIZED~VIEW PRODID-5 COMPONENT:RDBMS.SECURITY MINVER:11.1 MAXVER: PORTID-0 FDRSEG-797 FDRSEG-1262 FDRSEG-773 FDRSEG-465

相关的

---

产品

---

• Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition

关键字

---

ACCESS POLICY; VPD; MATERIALIZED VIEW; FGAC

错误

---

ORA-30372; 30372 ERROR; 530372 ERROR

 

 

总结

版本如果 是11.1 需要打补丁

版本如果 是11.2 则只需要在物化视图脚本中添加（USING TRUSTED CONSTRAINTS）即可，如下所示：

create materialized view mv1
refresh fast with rowid USING TRUSTED CONSTRAINTS
as
select * from table2@inst2;