exempt access policy权限 VPD predicate

SELECT predicate FROM V$VPD_POLICY;
The predicate column is empty for the DB user for which the VPD policies are not getting applied. T
he DB user is able to view the rows which are protected by VPD, but as per the VPD policy definition the rows shouldn't be viewable by this DB user.

CAUSE
'EXEMPT ACCESS POLICY' privilege was granted to the DB user and so any of the VPD policies were not applied to this DB user session.
 

SOLUTION
SQL> revoke exempt access policy from ;

SELECT * FROM V$VPD_POLICY;
 

   

--------验证权限

1. Execute the following Query

select * from dba_sys_privs where privilege like 'EXEMPT%'
2. If the Results of the above query consists of a Record with Grantee value as HDOWNER , this indicates that Exempt Access Policy is granted for HDOWNER

3. Revoke the Exempt Access Policy on HDOWNER, this should be performed by System DBA.

-----EXP

GOAL

When using Data Pump Export, or legacy Export, warnings are noticed in the log output, e.g.

Data Pump Export:

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

Legacy Export:

EXP-00079: Data in table "TAB" is protected. Conventional path may only be exporting partial table.

If EXEMPT ACCESS POLICY is granted to the user performing the export, no such warning is received. How can it be predicted if the EXEMPT ACCESS POLICY privilege is needed prior to the export occurring?

SOLUTION

select *From dba_policies

select *From system_privilege_map    -235

EXEMPT ACCESS POLICY is needed to export table data when there is a row level security policy on the table data, the policy restricts what users can select on the table, and the policy is enabled.

If DBA_POLICIES.SEL = YES and DBA_POLICIES.ENABLE = YES, then without the EXEMPT ACCESS POLICY privilege, data will be left out of the dump file. However, a warning will be given in either legacy export or Data Pump if there is any sort of policy defined on a table being exported, regardless if the policy does not apply to selects and regardless if the policy is disabled. It is only a warning, cautioning that data *may* be lost due to the presence of a policy on table, but there is no check to see if the policy applies to the SELECT privilege or if the policy is disabled.

Therefore if DBA_POLICIES.SEL = NO, or DBA_POLICIES.ENABLE = NO, we will still receive this warning if EXEMPT ACCESS POLICY is not granted.

To make sure that all data is exported, a check would need to be done in DBA_POLICIES to see if there are any policies defined with SEL=YES and ENABLE=YES.
If so, then EXEMPT ACCESS POLICY would be needed to export all of the data. If there are no such policies meeting both criteria, then EXEMPT ACCESS POLICY is not needed, but there still will be warnings during the export session if it is not granted.