I am using Oracle 11g R2 on Linux, and I have multiple tables in my database that has got columns that needs to be masked.

I have tried the OEM to mask the data as mentioned in the below hyperlink, it worked fine. http://www.oracle.com/webfolder/technetwork/tutorials/obe/em/emgc10205/data_masking/datamask.htm

But I couldn't find any blog that gives manual ways to mask data in oracle, like executing packages, functions and so on via SQLPLUS prompt.

Could someone please suggest me how else can this be achieved

  • What is PII an acronym for? – Colin 't Hart Aug 28 '18 at 15:25
  • Personally Identifiable Information, such as First Name, Last Name, Debit Card numbers, SSN etc. – sabarish jackson Aug 28 '18 at 15:29

If you want to mask data but still store it unmasked, and you don't want to use the Oracle provided tools (chargeable extra!) then you need to develop an API that does the masking for you.

You store the data as normal in the database.

Clients don't get any access to the tables directly. When they request data, use a "GET" call, which masks out the data to your spec, and when they insert data they use a "SET" call.

This all relies on access to the data being through this API though, so you need to carefully design that side of things. This means using one schema for storage of data and another user as the API owner/data access path.

There's lots of things to take into consideration here and it's not a small job.

| improve this answer | |


Column Masking in Oracle is done via Virtual Private Database (VPD) that is an included ($0) feature of the Oracle Enterprise Edition (EE) Database.


For non-EE editions:

  • CREATE VIEW on the underlying table
    • use SYS_CONTEXT() to determine if you should show the value
  • Create an INSTEAD OF TRIGGER on the VIEW
  • I would CREATE PACKAGE to hold all of the logic.
    • SQL*Developer can generate the Table API (TAPI) package
    • APEX can generate a TAPI also
    • there are template based code generators that can create TAPIs

Encrypt at Rest

If you have to follow some legal requirements, you'll probably need to encrypt the PII while it is at rest. Transparent Data Encryption (TDE) is THE recommended METHOD. However, it requires EE and an EE add-on ($$)

| improve this answer | |
  • HI, VPD just restricts the access to configured users against the columns, if I am right. But I would like to scramble the PII columns, every time I refresh the database. Suppose if FIRST_NAME column has SABARISH in PROD, when refreshing the non-prod envs, the SABARISH should be displayed as something else in lower environment For example "LISUYHBH". And the same applies for all the PII columns of a table. – sabarish jackson Aug 29 '18 at 9:28
  • @sabarisjackson - you're talking about data obfuscation. That is a different oracle product ($$$). – Michael Kutz Aug 29 '18 at 10:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.