One of the key requirements while masking data in large databases or multi
database environment is to consistently mask some columns, i.e. for a given input the output should always be the same. At the same time the masked output should not be predictable.
Deterministic masking also eliminates the need to spend enormous amount of time spent in identifying data relationships, i.e. parent and child relationships among columns defined in the application tables.
In this blog post I will explain different ways of consistently masking the data across databases using
Oracle Data Masking and Subsetting
The readers of post should have minimal knowledge on
Oracle Enterprise Manager
12c, Application Data Modeling, Data Masking concepts. For more information on these concepts, please refer to
Oracle Data Masking and Subsetting document
Oracle Data Masking and Subsetting
12c provides four methods using which users can consistently yet irreversibly mask their inputs.
1. Substitute
2. SQL Expression
3. Encrypt
4. User Defined Function
SUBSTITUTE
The substitute masking format replaces the original value with a value from a pre-created
database table. As the method uses a hash based algorithm in the back end the mappings are consistent.
For example consider DEPARTMENT_ID in EMPLOYEES table is replaced with FAKE_DEPARTMENT_ID from FAKE_TABLE. The substitute masking transformation that all occurrences of DEPARTMENT_ID say ‘101’ will be replaced with ‘502’ provided same substitution table and column is used , i.e. FAKE_TABLE.FAKE_DEPARTMENT_ID.
The following screen shot shows the usage of the Substitute masking format with in a masking definition:
Note that the uniqueness of the masked value depends on the number of columns being used in the substitution table i.e. if the original table contains 50000 unique values, then for the masked output to be unique and deterministic the substitution column should also contain 50000 unique values without which only consistency is maintained but not uniqueness.
SQL EXPRESSION
SQL Expression replaces an existing value with the output of a specified SQL Expression.
For example while masking an EMPLOYEES table the EMAIL_ID of an employee has to be in the format EMPLOYEE’s
[email protected] while FIRST_NAME and LAST_NAME are the actual column names of the EMPLOYEES table then the corresponding SQL Expression will look like %FIRST_NAME%||’.’||%LAST_NAME%||’@COMPANY.COM’.
The advantage of this technique is that if you are masking FIRST_NAME and LAST_NAME of the EMPLOYEES table than the corresponding EMAIL ID will be replaced accordingly by the masking scripts.
One of the interesting aspect’s of a SQL Expressions is that you can use sub SQL expressions, which means that you can write a nested SQL and use it as SQL Expression to address a complex masking business use cases.
SQL Expression can also be used to consistently replace value with hashed value using Oracle’s PL/SQL function ORA_HASH.
The following SQL Expression will help in the previous example for replacing the DEPARTMENT_IDs with a hashed number
ORA_HASH (%DEPARTMENT_ID%, 1000)
The following screen shot shows the usage of encrypt masking format with in the masking definition:
ORA_HASH takes three arguments:
1. Expression which can be of any data type except LONG, LOB, User Defined Type [nested table type is allowed]. In the above example I used the Original value as expression.
2. Number of hash buckets which can be number between 0 and 4294967295. The default value is 4294967295.
You can also co-relate the number of hash buckets to a range of numbers. In the above example above the bucket value is specified as 1000, so the end result will be a hashed number in between 0 and 1000.
3. Seed, can be any number which decides the consistency, i.e. for a given seed value the output will always be same. The default seed is 0. In the above SQL Expression a seed in not specified, so it to 0. If you have to use a non default seed then the function will look like.
ORA_HASH (%DEPARTMENT_ID%, 1000, 1234
The uniqueness depends on the input and the number of hash buckets used. However as ORA_HASH uses a 32 bit algorithm, considering birthday paradox or pigeonhole principle there is a 0.5 probability of collision after 232-1 unique values.
ENCRYPT
Encrypt masking format uses a blend of 3DES encryption algorithm, hashing, and regular expression to produce a deterministic and unique masked output. The format of the masked output corresponds to the specified regular expression. As this technique uses a key [string] to encrypt the data, the same string can be used to decrypt the data. The key also acts as seed to maintain consistent outputs for a given input.
The following screen shot shows the usage of encrypt masking format with in the masking definition:
Regular Expressions may look complex for the first time users but you will soon realize that it’s a simple language. There are many resources in internet,
oracle documentation,
oracle learning library, my
oracle support on writing a Regular Expressions, out of all the following My
Oracle Support document helped me to get started with Regular Expressions:
Oracle SQL Support for Regular Expressions[Video](Doc ID 1369668.1)
USER DEFINED FUNCTION [UDF]
User Defined Function or UDF provides flexibility for the users to code their own masking logic in PL/SQL, which can be called from masking Defintion.
The standard format of an UDF in
Oracle Data Masking and Subsetting is:
Function udf_func (rowid varchar2, column_name varchar2, original_value varchar2) returns varchar2;
Where
• rowid is the row identifier of the column that needs to be masked
• column_name is the name of the column that needs to be masked
• original_value is the column value that needs to be masked
You can achieve deterministic masking by using Oracle’s built in hash functions like, ORA_HASH, DBMS_CRYPTO.MD4, DBMS_CRYPTO.MD5, DBMS_UTILITY. GET_HASH_VALUE.Please refers to the
Oracle Database Documentation for more information on the
Oracle Hash functions.
For example the following masking UDF generate deterministic unique hexadecimal values for a given string input:
CREATE OR REPLACE FUNCTION RD_DUX (rid varchar2, column_name varchar2, orig_val VARCHAR2) RETURN VARCHAR2
DETERMINISTIC PARALLEL_ENABLE IS
stext varchar2 (26);
no_of_characters number(2);
BEGIN
no_of_characters:=6;
stext:=substr(RAWTOHEX(DBMS_CRYPTO.HASH(UTL_RAW.CAST_TO_RAW(text),1)),0,no_of_characters);
RETURN stext;
END;
The uniqueness depends on the input and length of the string and number of bits used by hash algorithm. In the above function MD4 hash is used [denoted by argument 1 in the DBMS_CRYPTO.HASH function which is a 128 bit algorithm which produces 2^128-1 unique hashed values , however this is limited by the length of the input string which is 6, so only 6^6 unique values will be generated. Also do not forget about the birthday paradox/pigeonhole principle mentioned earlier in this post.
An another example is to consistently replace characters or numbers preserving the length and special characters as shown below:
CREATE OR REPLACE FUNCTION RD_DUS(rid varchar2,column_name varchar2,orig_val VARCHAR2) RETURN VARCHAR2
DETERMINISTIC PARALLEL_ENABLE IS
stext varchar2(26);
BEGIN
DBMS_RANDOM.SEED(orig_val);
stext:=TRANSLATE(orig_val,'ABCDEFGHILKLMNOPQRSTUVWXYZ',DBMS_RANDOM.STRING('U',26));
stext:=TRANSLATE(stext,'abcdefghijklmnopqrstuvwxyz',DBMS_RANDOM.STRING('L',26));
stext:=TRANSLATE(stext,'0123456789',to_char(DBMS_RANDOM.VALUE(1,9)));
stext:=REPLACE(stext,'.','0');
RETURN stext;
END;
The following screen shot shows the usage of an UDF with in a masking definition:
To summarize,
Oracle Data Masking and Subsetting helps you to consistently mask data across databases using one or all of the methods described in this post. It saves the hassle of identifying the parent-child relationships defined in the application table.
Happy Masking
