Masking PII columns in Oracle

Question

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

Answer 1

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.

Answer 2

VPD

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

• https://docs.oracle.com/cd/B28359_01/network.111/b28531/vpd.htm#DBSEG007
• https://oracle-base.com/articles/10g/database-security-enhancements-10g
• https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1855489000346413353

VPD-Like

For non-EE editions:

• CREATE CONTEXT
• 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 ($$)