Question: How
do I export a have that has a VPD (fined grained access
control policy applied against the table?
Answer:
It is the VPD policy filter that prevents the rows
from being exported with exp or expdp.
To avoid export errors on tables with VPD, you have two
choices::
1: Grant the exempt
access policy privilege to the exporting user ID.
2: Temporarily disable the VPD
policy.
There are two types of
errors associated with exporting when you have VPD FGAC
protection on a table:
- ORA-39181 :only partial table data may be
exported due to fine grain access control.
- EXP-79 "Data in table %s is protected.
Conventional path may only be exporting partial table."
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 (VPD)
policy enabled against it. When expdp is run
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.
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.
