My Oracle Support Banner

Example: How to Move a RLS Policy Between Databases Using Datapump (Doc ID 1637312.1)

Last updated on MAY 17, 2021

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 to 12.1.0.1 [Release 11.2 to 12.1]
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.

Goal

NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample or bulit-in schema(s), Public Documentation delivered with an Oracle database product or other training material.  Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.

The objective is to move the RLS policy between databases with data pump export and import.

This may be best illustrated with an example, where an RLS policy is created in the source database (SOURCEDB), exported with data pump export utility (expdp) and finally imported into the target database (TARGETDB) with the data pump import utility (impdp).

The policy will restrict access to records in the SCOTT.EMP table to rows WHERE ENAME = 'SMITH'. This of course is not a very good policy as it will restrict access to everyone, including SCOTT. But the purpose of this document is to illustrate how to export a policy, not how to write a good policy.
 

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution
 1. Note that there are 14 records in the SCOTT.EMP table.
 2. Create the policy (SMITH_POLICY) in the source database (SOURCEDB)
 2.1. Make sure the policy works
 3. Export the policy
 3.1. Prepare for the export
 3.2. Run the export job
 4. Import the policy into the target database (TARGETDB)
 4.1. Create user SCOTT
 4.2. Prepare for the import
 4.3. Run the import job
 5. Check that the policy has been imported
 6. Verify that the policy works
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.