Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
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.
- Set a password for the HR user.
- Set tablespace for HR user, if you pass it blank it gets default tablespace.
- Set temp tablespace for HR user, if you pass it blank, it gets default temp tablespace.
- Go blank.
- Set the Log location for the HR user, if you pass it blank, it gets the default location.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | $ sqlplus / as sysdba SQL> @?/demo/schema/human_resources/hr_main.sql specify password for HR as parameter 1: Enter value for 1: specify default tablespace for HR as parameter 2: Enter value for 2: specify temporary tablespace for HR as parameter 3: Enter value for 3: specify password for SYS as parameter 4: Enter value for 4: specify log path as parameter 5: Enter value for 5: PL/SQL procedure successfully completed. User created. . . . . . . PL/SQL procedure successfully completed. |
Let’s Select the whole table with a classic query.
1 | SQL> SELECT * FROM HR.EMPLOYEES |
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”.
1 2 3 4 5 6 7 8 9 | BEGIN DBMS_REDACT.add_policy (object_schema => 'HR', object_name => 'EMPLOYEES', column_name => 'SALARY', policy_name => 'SALARY_KOLONO_ICIN_REDACTION', function_type => DBMS_REDACT.full, expression => '1=1'); END; / |
I select the table with my HR user. As you can see in the screenshot, everything is fine.
1 | SQL> SELECT * FROM HR.EMPLOYEES |
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.
1 | SQL> SELECT * FROM HR.EMPLOYEES |
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?
1 2 3 4 5 6 7 8 9 | BEGIN DBMS_REDACT.alter_policy (object_schema => 'HR', object_name => 'HR.EMPLOYEES', column_name => 'FIRST_NAME', policy_name => 'SALARY_KOLONO_ICIN_REDACTION', function_type => DBMS_REDACT.full, expression => '1=1'); END; / |
As you can see, SALARY field with NUMBER value returned as “0”, while VARHCAR “FIRST_NAME” field returned as blank.
1 | SQL> SELECT * FROM HR.EMPLOYEES |
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”
1 2 3 4 5 6 7 8 9 10 | BEGIN DBMS_REDACT.alter_policy (object_schema => 'HR', object_name => 'EMPLOYEES', policy_name => 'SALARY_KOLONO_ICIN_REDACTION', action => DBMS_REDACT.modify_column, column_name => 'SALARY', function_type => DBMS_REDACT.partial, function_parameters => '1,1,3'); END; / |
Everything is normal with our HR user. Let’s try it with a different user.
1 | SQL> SELECT * FROM HR.EMPLOYEES |
As can be seen, other users see the first three digits as “1” and the remaining digits as normal.
1 | SQL> SELECT * FROM HR.EMPLOYEES |
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.
1 2 3 4 5 6 7 8 9 | BEGIN DBMS_REDACT.alter_policy (object_schema => 'HR', object_name => 'EMPLOYEES', policy_name => 'SALARY_KOLONO_ICIN_REDACTION', action => DBMS_REDACT.add_column, column_name => 'PHONE_NUMBER', function_type => DBMS_REDACT.RANDOM); END; / |
I am looking with my HR user. Everything is normal.
1 | SQL> SELECT * FROM HR.EMPLOYEES |
I’m running the same query again with a different user. As you can see, PHONE_NUMBER field returned as random character.
1 | SQL> SELECT * FROM HR.EMPLOYEES |
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,
