While moving the data from production to staging or development test environment, what is the best way to mask some columns of some tables?

The two option I can think of are:
(A) Apply redaction policy as it is in built in oracle 12c
(B) Export data then apply script on columns the move the data to test environment.

Which of the two ways will be better?

If I go with (A) then I apply the redact.add_policy. But will this be automatically applied on the exported data?
Chandan PratihastAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chandan PratihastAuthor Commented:
Redaction I have read and the second link talks about normal export. What I was looking for, whether the masking policy will be automatically applied when I do expdp, datapump export?
slightwv (䄆 Netminder) Commented:
I don't believe you can export the data in the redacted state.

If you import the data then redact, the real data will be in the other environments which will likely violate some policies which is why you need to redact in the first place.

I'm not sure what you mean by option-B:  If you export it, how will you run a script on it before importing?

There are issues with dbms_redact and export:

Data Redaction and Data Pump

 The role DATAPUMP_EXP_FULL_DATABASE includes the EXEMPT REDACTION POLICY system privilege. This way, tables can be exported using Data Pump without being redacted.

If an attempt to export a table is made by a user that has not been granted the EXEMPT REDACTION POLICY system privilege, the following error will be displayed:
ORA-28081: Insufficient privileges - the command references a redacted object.

To export the metadata related to the policies of the Oracle Data Redaction, you can use the following parameters of the expdp utility:



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chandan PratihastAuthor Commented:
Thank you for reply. So we cannot export data in redacted state.

Then how can we ensure it is redacted in the first place?
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Chandan PratihastAuthor Commented:
To export the metadata related to the policies of the Oracle Data Redaction, you can use the following parameters of the expdp utility:  CONTENT=METADATA_ONLY  INCLUDE=RADM_FPTM,RADM_POLICY  , so this way we can redact the exported data. But the original data exported.
slightwv (䄆 Netminder) Commented:
>>But the original data exported.

I believe the original data will remain un-redacted and the import will then import the policy to show it as redacted to the unprivileged users but you should need to do a quick test to confirm this.

>>Then how can we ensure it is redacted in the first place?

Query it as a non-privileged user?  Oracle doesn't redact in place.  The original data is still there.  It just masks it a query time for users that don't have permission to see it.
Chandan PratihastAuthor Commented:
Thank you very much for reply.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.