ORA-30372解决方法

来源：互联网发布：java中settitle大小编辑：程序博客网时间：2024/05/16 18:02

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;