In previous article we provide the brief introduction about Data Redaction on Oracle. Now in this post, we are going to configure different type of Data Redaction in Oracle 12c.

  • Full Data Redaction
  • Partial Data Redaction
  • Random Data Redaction
  • Data Redaction with REGEXP

For the demonstration on this I have a table that holds credit card information and I want to redact the card number in that table.

OBJECT_SCHEMA: REDACT_USER
OBJECT_TABLE: credit_card_detail
COLUMN_NAME: card_no

Now we are going to create user,  table and load some data on it.

Now Connect with ‘redact_user‘ user to create the table

Full Data Redaction

Mow we are going to add a data redaction policy to redact the CARD_NO column of CREDIT_CARD_DETAIL table.
I will make use of the FUNCTION_TYPE called DBMS_REDACT.
FULL to redact CARD_NO values to a static value, every time it is being queried using the data redact event EXPRESSION ‘1=1’.

Now connect with ‘redact_user‘ user to check the table.

In FULL data redact policy, the column data gets redacted to a static value (by default 0).We can view default static values for FULL redaction policy by querying REDACTION_VALUES_FOR_TYPE_FULL view.

Partial Data Redaction

Lets say I want to mask the first 9 characters of the 16 digit card number instead of completely making the data. We can opt for PARTIAL data redact policy for this purpose.

Lets alter the existing data redact policy for CARD_NO using ALTER_POLICY procedure of DBMS_REDACT package from FULL to PARTIAL.

Now connect with ‘redact_user‘ user to check the partial redaction.

As expected, in this partial redaction the first 9 digits in the CARD_NO are masked with the number 9.

Random Data Redaction

In RANDOM data redact policy, the column data would be redacted to a random value each time it is being queried. Lets alter our existing data redact policy from PARTIAL to RANDOM using ALTER_POLICY procedure of DBMS_REDACT package.

Now connect with ‘redact_user‘ user to check the random redaction.

Now we apply the redaction on other users only.

In this case we can exclude the redact_user from the policy.

Now connect with ‘redact_user‘ user to check the random redaction.

Now connect with ‘hr’ user to check the random redaction.

Add new column in Redaction Policy

In above situation , data is redacted on exp_date column for other users.

Now connect with ‘redact_user‘ user to check the random redaction.

Now connect with ‘hr’ user to check the random redaction.

Checking data redaction

Remove Data Redaction

Source

Comments

Leave a Reply