1024programmer Blog expdp ORA-39181:Only Partial Table Data Exported Due To Fine Grain Access Control_aicon’s blog

expdp ORA-39181:Only Partial Table Data Exported Due To Fine Grain Access Control_aicon’s blog

Applies to:

Oracle Server – Enterprise Edition – Version 10.2.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 20-SEP-2012***

>

Symptoms

ERROR:
ORA-39181 :only partial table data may be exported due to fine grain access control

This error appears trying to datapump export a table with FGAC policy enabled against it.

When Expdp runs as the schema owner, if fine grained policies are discovered on tables and if the exporting user has unrestricted access, then the entire table data is exported.

Changes

A table with FGAC policy enabled against it.

Scenario 1 (unprivileged user)
———————– ——-
table: ABC_TAB
partition=yes
table_owner= X_USER
data= 500GB

when expdp X_USER/password parameter=exp_ABC_TAB.par
==>
Getting ORA-39181: Only partial table data may be exported due to fine grain access control

Scenario 2 (privileged user)
—————————–
table: ABC_TAB
partition=yes
table_owner= X_USER

when expdp oradba/password parameter=exp_ABC_TAB.par
==>
Getting exported “X_USER”.”ABC_TAB”:”ABC_TAB_AA”12340 KB 1000 rows
Master table “ORADBA”.”ABC_TAB_AA” successfully loaded/unloaded

Cause

This is expected behavior.

ORA-39181 is caused by an unprivileged user who tries to export a table with a fine grain access control policy applied. The table owner is subject to access control and may not be able to export all rows in the table. Only the rows that can be seen by that user are exported. In order to preserve integrity of the table, the user importing the table should have enough privilege to recreate the table with the security policies at import time.

Action: It is strongly recommended that the database administrator handles the export of this table.

This as an informational message.

VPD and Oracle Label Security are not enforced during DIRECT path export and similarly, database users granted the EXEMPT ACCESS POLICY privilege, either directly or through a database role, are exempt from VPD enforcements. However, the following policy enforcement options remain in effect even when EXEMPT ACCESS POLICY is granted:

* INSERT_CONTROL, UPDATE_CONTROL, DELETE_CONTROL , WRITE_CONTROL, LABEL_UPDATE, and LABEL_DEFAULT

EXEMPT ACCESS POLICY is a strong privilege and must be granted with care. For example , grant it to the role exp_full_database role as this role is granted to admin users only.

Solution

To avoid this:

Grant the privilege EXEMPT ACCESS POLICY to the exporting user

-or-

Disable the VPD policy.

References

NOTE:304137.1 – ORA-12406 When Updating a Table With an OLS Policy Though Granted EXEMPT ACCESS POLICY Privilege
NOTE:174799.1 – How to Bypass Fine-Grained Security Enforcement

———————————————- ————————————————–

———————————————- ————————————————–

[dbuser@xpli expdpbak]$ expdp system/aaaaa dumpfile=11gfull.dmp DIRECTORY=dpdata1 SCHEMAS=SYSMAN_OPSS,SYSMAN,SYSMAN_APM,SYSMAN_MDS,MGMT_VIEW,SYS,SYSTEM parallel=2 logfile=11gfulldp 1. log

[dbuser@xpli expdpbak]$ cat 11gfulldp1.log |grep ORA-
ORA-39181: Only partial table data may be exported due to fine grain access control on “SYSMAN”.”EM_EVENT_SEQUENCES”:”OPEN”
ORA-39181: Only partial table data may be exported due to fine grain access control on “SYSMAN”.”EM_EVENT_SEQUENCES”:”P201301″
ORA-39181: Only partial table data may be exported due to fine grain access control on “SYSMAN”.”EM_ISSUES_INTERNAL”:”P201301″
ORA-39181: Only partial table data may be exported due to fine grain access control on “SYSMAN”.”EM_ISSUES_INTERNAL”:”OPEN”
ORA -39181: Only partial table data may be exported due to fine grain access control on “SYSMAN_MDS”.”MDS_ATTRIBUTES”
ORA-39181: Only partial table data may be exported due to fine grain access control on “SYSMAN”.”EM_SU_ENTITIES”
ORA-39181: Only partial table data may be exported due to fine grain access control on “SYSMAN_MDS”.”MDS_COMPONENTS”
ORA-39181: Only partial table data may be exported due to fine grain access control on “SYSMAN”.”EM_COMPLIANCE_GROUP”
ORA-39181: Only partial table data may be exported due to fine grain access control on “SYSMAN_MDS”.”MDS_PATHS”
ORA-39181: Only partial table data may be exported due to fine grain access control on “SYSMAN”.”EM_MANAGEABLE_ENTITIES”
ORA-39181: Only partial table data may be exported due to fine grain access control on “SYSMAN”.”EM_NC_CREDS”
ORA -39181: Only partial table data may be exported due to fine grain access control on “SYSMAN”.”MGMT_IP_REPORT_DEF”
ORA-39181: Only partial table data may be exported due to fine grain access control on “SYSMAN”.” MGMT_JOB”

SQL> show user;
USER is “SYS”
SQL> select owner,object_name,object_type,status from dba_objects where object_name like ‘%INSERT_CONTROL%’;

no rows selected

SQL> GRANT EXEMPT ACCESS POLICY to system;

Grant succeeded.

SQL>

[dbuser@xpli expdpbak]$ expdp system/aaaaa dumpfile=11gfull.dmp DIRECTORY=dpdata1 SCHEMAS=SYSMAN_OPSS,SYSMAN,SYSMAN_APM,SYSMAN_MDS,MGMT_VIEW,SYS,SYSTEM parallel=2 logfile=11gfulldp 1. log
[dbuser @xpli expdpbak]$ cat 11gfulldp1.log |grep ORA-
[dbuser@xpli expdpbak]$

This article is from the internet and does not represent1024programmerPosition, please indicate the source when reprinting:https://www.1024programmer.com/expdp-ora-39181only-partial-table-data-exported-due-to-fine-grain-access-control_aicons-blog/

author: admin

Previous article
Next article

Leave a Reply

Your email address will not be published. Required fields are marked *

Contact Us

Contact us

181-3619-1160

Online consultation: QQ交谈

E-mail: 34331943@QQ.com

Working hours: Monday to Friday, 9:00-17:30, holidays off

Follow wechat
Scan wechat and follow us

Scan wechat and follow us

Follow Weibo
Back to top
首页
微信
电话
搜索