CODEWITHSUNDEEP
oracle-databasedatabase-partitioningdatabase-permissions
Steve
Steve

Reputation: 551

Cannot Exchange partitions between Oracle schemas. ALTER TABLE EXCHANGE restricted by fine-grained security. Realm violation...EXEMPT ACCESS POLICY

I have a working Oracle procedure that does the following:

  1. Chooses a partition from a Source table
  2. Inserts records from this partition into a Swap table. (design/structure of Swap table matches Source)
  3. Modifies records in the Swap table.
  4. Does an "exchange partition" to swap the records from the Swap Table back to the Source.

This works perfectly when the procedure and all tables are in the same schema: (INBOUND)

However... the next step is to have it use Source tables that exist in another Schema: WAREHOUSE

It now fails on the Exchange Partition step: 

Alter Table WAREHOUSE.ITEM_FCT Exchange Partition SYS_ABCD WITH TABLE INBOUND.ITEM_SWP Including     
Indexes Without Validation;

SQL Error: ORA-14136: ALTER TABLE EXCHANGE restricted by fine-grained security 14136. 00000 - "ALTER TABLE EXCHANGE restricted by fine-grained security" *Cause: User doing exchange does not have full table access due to VPD policies. *Action: Grant exempt priviliges to this user.

I attempted to add the permissions needed, based on google research: 

grant ALL on INBOUND.ITEM_SWP TO WAREHOUSE;
grant EXEMPT ACCESS POLICY to INBOUND;

When I try the grant exempt access policy, it says: 

grant EXEMPT ACCESS POLICY to INBOUND
Error report -
SQL Error: ORA-47410: Realm violation for GRANT on EXEMPT ACCESS POLICY

I would greatly appreciate any guidance. Placing everything in the WAREHOUSE schema would be an absolute last resort.

Thanks!

Upvotes: 2

Views: 4752

Answers (3)

Bhuwan Gautam
Bhuwan Gautam

Reputation: 1269

I had the same issue but the simply grant solved my issue.

-- On Partition Table Schema

 GRANT ALL ON <partition-table> TO <source-table-schema-name>;

-- On Source Table Schema

GRANT ALL ON <source-table> TO <partition-table-schema-name>;

-- Final Query on Partition Table Schema

ALTER TABLE <partition-table> EXCHANGE PARTITION p_all WITH TABLE <source-table-schema-name>.<source-table> INCLUDING INDEXES WITHOUT VALIDATION;

I hope this helps for you too. Cheers!

Upvotes: 1

Varun Bahl
Varun Bahl

Reputation: 21

We dont need to disable the policy in order to carry out the exchange partition operation if you have EXEMPT_ACCESS_ROLE granted. The user above might have faced the problem even after granting the role because he is trying to exchange partition between 2 different schemas but granted the role to only one schema.

Upvotes: 2

Rusty
Rusty

Reputation: 2138

You can't apply exchange partition operation for table with FGAC policy enabled. This restriction is described in Oracle reference. You need to disable policy, apply operation and re-enable it again. However this is not ideal cause for some short period of time your data will be not protected by policy.

Upvotes: 2

Related Questions