If you use application logic to mask sensitive data now with 12c you have an option to accomplish the same effect at database level, but more efficient and secure. This is called Oracle Data Redaction. The solution is very simple, it modifies sensitive data based on user-defined policies. Important note to remember is that the policies that are defined does not modify data blocks at storage level. Instead, the data is modified on-the-fly before the results are returned to the user/application. This represents very fast and easy to implement solution for data masking.
We can mask characters, numbers and dates. Before redaction to take place we need to define policies which will determine the type of redaction, what to redact and how to redact.
There are several types from which we can choose: NONE , FULL, PARTIAL, RANDOM and REGEXP.
NONE – masking is not used.
FULL – data will be replaced with fixed value.
PARTIAL – we define what to be masked.
RANDOM – the system will generate random value which will be used to replace original data.
REGEXP – data is masked based on regular expression pattern.
In this part 1 I am going to show data redaction for characters, in the following part I’ll write for numbers and dates.
I have created the following table just for the purpose of this post (I haven’t included any architectural design principals).
SQL> conn iarsov@pdbx Enter password: Connected. SQL> create table mtab(user_id number, name varchar2(20), surname varchar2(50), account varchar2(15), created date); Table created. SQL> SQL> insert into mtab values(1, 'Steven', 'King', '123-4567-891234', sysdate); 1 row created. SQL> SQL> select user_id,name,surname,account,created from mtab; USER_ID NAME SURNAM ACCOUNT CREATED ---------- ------ ------ --------------- ---------- 1 Steven King 123-4567-891234 24.11.2014 SQL>
Lets define policy to redact/mask account column. The package that we need is called DBMS_REDACT.
We add policy with ADD_POLICY procedure. Required parameters are object_name, policy_name and expression. All other parameters have default values. For object_name we need to pass the object for which we want to create the policy, this can be table or view. For expression we need to specify expression that needs to evaluate to boolean value, if the expression evaluates to TRUE then the policy will be used. In this example I’ve set expression so that this policy is used for all users except “IARSOV” user.
FULL data redaction
For FULL type the system will use default values. We can determine which default values are used by querying REDACTION_VALUES_FOR_TYPE_FULL dictionary view. Also, those values can be changed/updated with UPDATE_FULL_REDACTION_VALUES procedure.
I’ve created the following policy for mtab table and account column with FULL type.
<pre>SQL> conn iarsov@pdbx Enter password: Connected. SQL> @redact_full.sql SQL> SQL> begin 2 3 DBMS_REDACT.ADD_POLICY ( 4 object_schema => USER, 5 object_name => 'mtab', 6 policy_name => 'mtab_account_full', 7 column_name => 'ACCOUNT', 8 function_type => DBMS_REDACT.FULL, 9 function_parameters => null, 10 expression => 'SYS_CONTEXT(''USERENV'',''CURRENT_USER'') <> ''IARSOV'''); 11 end; 12 / PL/SQL procedure successfully completed. SQL>
In order to test previously created policy I’ve created another user orax which has been granted SELECT privilege for iarsov.mtab table.
SQL> conn system@pdbx Enter password: Connected. SQL> SQL> SQL> create user orax identified by orax; User created. SQL> grant create session to orax; Grant succeeded. SQL> grant select on iarsov.mtab to orax; Grant succeeded. SQL> conn orax@pdbx Enter password: Connected. SQL> select name,surname,account from iarsov.mtab; NAME SURNAM ACCOUNT ----- ------ --------------- Steven King SQL>
The value for account column has been replaced with empty string which is default value for FULL data redaction.