At this note I will show to you how you can mask data according to the role of users by using Data Redaction. Actually I will not explain Data Redaction in detail , I assume that you already know about Data Redaction. But in the future I will give detailed information about Oracle Advanced Security and Data Redaction.
Now in this note we will use Data Redaction to mask data according to session roles. Virtual Private database can be used instead of Data Redaction. I will show it in the another note.
I will explain this subject with a sample. In the sample we have an user which holds data (rep_user) , application user ( app_user which can see all data ) and inq_user ( data will be masked for this user) . At the example; Only users which have a special role (redact_role) can not be affected by data redaction policy.
Let’s build up the environment and create users.
First ; Create rep_user
Create inq_user and redact_role
We simple create a table with one column and we mask this column with data redaction.
Insert some data to this table and grant this table to app_user and inq_user
Now app_user and inq_user can select this table as below
Now create Data Redaction policy to hide data
And according to our policy only users with redact_role can not be affected by this policy.
To do this grant redact_role to app_user;
And now app_user can see the masked data but other users( inq_user) can not reach this data.
Lets’s test it
Masked numeric data can be seen as 0 in Data Redaction ( on default).
As you see we can hide data according to user’s role by using SYS_CONTEXT function. You can change this case according to your needs.
you can reach all scripts from github