Wednesday , October 6 2021

Oracle Data Redaction 19c Examples

This article contain information about Oracle Data Redaction in 19c Example. If you wonder about What is Oracle Data Redaction and methods, you may want to read the below article.

What is Oracle Data Redaction

Important Note: Oracle Data Redaction comes with separate licensing (Oracle Advanced Security). You should not use this feature without license.

I will perform this example with the HR scheme, which is one of the sample schemes. You can work on a table on a different scheme or create the HR user as follows.

  1. Set a password for the HR user.
  2. Set tablespace for HR user, if you pass it blank it gets default tablespace.
  3. Set temp tablespace for HR user, if you pass it blank, it gets default temp tablespace.
  4. Go blank.
  5. Set the Log location for the HR user, if you pass it blank, it gets the default location.
Now that our user has been created, we can start practicing. I will do my work on the EMPLOYEES table in HR user. This table stores personnel information and especially salary field.

Let’s Select the whole table with a classic query.

We will perform the operations in the order I explained at the beginning of the article.

Full Oracle Data Redaction Example

First, we start with Full Redaction. First of all, I will do it for the SALARY field. When the code block below runs, the HR user will see this field as it should, while other users will see it as “0”.

I select the table with my HR user. As you can see in the screenshot, everything is fine.

Now I’m running the same query with a different user. Other users see it as “0” as it is stuck in the Data Redaction process.

As we explained in the first part, there is a piece of information that I would like to remind.

  • The default value of Full Redaction is “0”. If the data type in the column appears as NUMBER, it is replaced with “0”, if the data type is a character, it is replaced with a space.
  • Users with SYSDBA and DBA privileges are not affected by these rules.

So what if we run same code block for FIRST_NAME?

As you can see, SALARY field with NUMBER value returned as “0”, while VARHCAR “FIRST_NAME” field returned as blank.

Partial Oracle Data Redaction Example

Another model is Partial. With this redaction, we can print digit from left to right. For example, let’s make the first three digits of a 6-digit salary “1”

Everything is normal with our HR user. Let’s try it with a different user.

 

As can be seen, other users see the first three digits as “1” and the remaining digits as normal.

Random Oracle Data Redaction Example

Let’s take a look at the Random Redaction feature. This time we will make a change and work in the PHONE_NUMBER field. Our sample code block is as follows.

I am looking with my HR user. Everything is normal.

I’m running the same query again with a different user. As you can see, PHONE_NUMBER field returned as random character.

As you can see, it is very simple and most importantly, it is a feature that does not bring additional load for our database.

Hope to see you in other articles,

Buğra PARLAYAN
Author: Buğra PARLAYAN

Burgra Parlayan is an experienced Database and Weblogic Administrator. After completing his technical / relevant training he has got involved with a serious amount of projects. He successfully managed database upgrade, database migration, database performance tuning projects for various public institutions.Currently he has been employed by one of the leading financial institutions called Turkiye Hayat & Emeklilik as responsible administrator for Oracle Database and Oracle Middleware. He has been sharing his experience and knowledge by face to face training, personal blog and various social networking accounts to support the Oracle ecosystem continuously since 2010.

About Buğra PARLAYAN

Burgra Parlayan is an experienced Database and Weblogic Administrator. After completing his technical / relevant training he has got involved with a serious amount of projects. He successfully managed database upgrade, database migration, database performance tuning projects for various public institutions.Currently he has been employed by one of the leading financial institutions called Turkiye Hayat & Emeklilik as responsible administrator for Oracle Database and Oracle Middleware. He has been sharing his experience and knowledge by face to face training, personal blog and various social networking accounts to support the Oracle ecosystem continuously since 2010.

Leave a Reply

Your email address will not be published. Required fields are marked *