Redacting Sensitive Data in Oracle 12c using Dbms_Redact

Since Oracle 10g it has been possible to hide data from specific users or under a set of specified conditions. This can be entire rows using Virtual Private Database (VPD) policies or specific columns in a table. In this post I’m going to look at the latter and how that has been enhanced in Oracle 12c. First, let’s take a pre-12c example. Suppose I have the following table of data.

Create Table emps As   Select '12345' emp_no, 'Alice' emp_name, '01234 567890' tel_no From Dual Union All   Select '67890' emp_no, 'Bob' emp_name, '07800 123456' tel_no From Dual Union All   Select '09876' emp_no, 'Charlie' emp_name, '07989 989898' tel_no From Dual Union All   Select '54321' emp_no, 'Dave' emp_name, '01987 654321' tel_no From Dual Union All   Select '13579' emp_no, 'Erin' emp_name, '07909 101010' tel_no From Dual; 

Now suppose we have a requirement to hide the telephone from all users as we deem that to be sensitive information. We could of course put whatever logic we wanted in our function, however let’s just keep it simple for the purposes of demonstration.

Create or Replace Function check_auth(p_owner In Varchar2, p_name In Varchar2) Return Varchar2 Is Begin   Return Null; End; / 

Then we create a policy against our emps table as follows.

Begin   Dbms_Rls.Add_Policy(     object_schema=>'KEYMEJ',     object_name=>'EMPS',     policy_name=>'Emp_Auth',     function_schema=>user,     policy_function=>'check_auth',     sec_relevant_cols=>'tel_no',     sec_relevant_cols_opt=>Dbms_Rls.All_Rows   ); End; / 

Now look what happens when I select from my table emps as user KEYMEJ.

EMP_N EMP_NAM TEL_NO ----- ------- ------ 12345 Alice          67890 Bob            09876 Charlie        54321 Dave           13579 Erin       

I can also still insert data, I just can’t read what I’ve inserted!

Insert Into emps(emp_no, emp_name, tel_no) Values ('24680','Frank','07909 090909');  Select * From emps Where emp_no='24680';  1 row inserted.  EMP_N EMP_NAM TEL_NO ----- ------- ------ 24680 Frank          

Now that is all well and good, however wouldn’t it be useful for say a customer support representative to at least be able to verify the data? I.e. What if they could just see the last four characters of the phone number? Prior to 12c we would have done something like this:

Create or Replace View emps_v As Select emp_no, emp_name, RPad('x',Length(tel_no)-1,'x') || Substr(tel_no,Length(tel_no)-4,4) tel_no, tel_no t2   From emps; 

…and have all all users select from that instead. However that leads us into other areas of complication in terms of managing grants, insert/update capability and so on.
However as of 12c, we have Data Redaction functionality, provided by the database package Dbms_Redact. Let’s just jump straight in with an example.

Begin   Dbms_Redact.Add_Policy(     object_schema=>'KEYMEJ',     object_name=>'EMPS',     column_name=>'TEL_NO',     policy_name=>'Emp_Auth',     function_type=>Dbms_Redact.Partial,     function_parameters=>'VVVVVFVVVVVV,VVVVV VVVVVV,X,1,7',     expression=>'1=1'   ); End; / 

The above call is pretty self-explanatory, perhaps with the exception of the function_parameters parameter. This 5 segment string takes on the following format.

1. Input Format
2. Output Format
3. Masking Character
4. Mask From Position
5. Mask To Position

The V‘s and F‘s represent a character that is redactable and a character that is fixed (respectively). You may also notice the expression 1=1? That is a SQL expression when to apply this policy, a bit like the function we used above. For the purposes of this example I have just used 1=1 (apply always) because you need to code up the conditions slightly different with dbms_redact (using Sys_Context() rather than a function ).
Anyway, now let’s select from our table as KEYMEJ.

EMP_N EMP_NAM TEL_NO      ----- ------- ------------ 12345 Alice   XXXXX XX7890 67890 Bob     XXXXX XX3456 09876 Charlie XXXXX XX9898 54321 Dave    XXXXX XX4321 13579 Erin    XXXXX XX1010 24680 Frank   XXXXX XX0909   6 rows selected  

This is an example of partial redaction on a column. We can of course redact the entire column, or we can choose to replace a value with completely random data. So if it was decided now that nobody should be allowed to view an employees real name, it’s as easy as this:

Begin   Dbms_Redact.Alter_Policy(     object_schema=>'KEYMEJ',     object_name=>'EMPS',     column_name=>'EMP_NAME',     policy_name=>'Is_DBA',     function_type=>Dbms_Redact.Random,     action=>Dbms_Redact.Add_Column   ); End; / 

When we select from our table now:

EMP_N EMP_NAM TEL_NO      ----- ------- ------------ 12345 TspJL   XXXXX XX7890 67890 )/_     XXXXX XX3456 09876 p2N- .% XXXXX XX9898 54321 E(h`    XXXXX XX4321 13579 J$\]    XXXXX XX1010 24680 |^w~B   XXXXX XX0909   6 rows selected   

The great thing about this though is that the redaction is applied by the database just before the data is returned to the client. So the CBO still uses the correct underlying data in its cardinality calculations etc. We don’t get skews on artificially hidden data or indexes not being used because of functions around columns.
In a lot of ERP systems data is obfuscated or deleted when a production environment is cloned to development/test – but perhaps this offers an alternative approach that doesn’t compromise the underlying data. It’s usually relatively easy to implement data redaction within your application however the challenge has always been redacting sensitive data from your developers. For even further security you can use Oracle Data Vault which allows redaction of sensitive information even from DBA users.
That’s all for now though, hope you found the above useful.