Here is a real treat: my good friend and colleague, Oracle ACE Director Oded Raz has agreed to publish couple of his high-end content about database security in my blog.
This time, Oded tells us about Data Redaction.
Enjoy!
Zohar

Sensitive data is everywhere in our organization’s systems. Today, organizations are required to protect sensitive data; these requirements come from regulations, laws and the necessity of the organization to protect its own data and customer’s information from falling to the wrong hands.

Data masking can dynamically or statically protect sensitive data by replacing it with fictitious data that looks realistic to prevent data loss in different use cases, understanding the difference between Static Data Masking – SDM and Dynamic Data Masking – DDM is crucial for implementing the right solution for each situation.

Dynamic vs. Static data masking

Dynamic Data Masking
Changing the returned result set on the fly living the original data intact. This approach is mainly used in production or training environments where original data can’t be changed but there is a need to hide sensitive data from one or more data consumer. Starting Oracle 12c (now available in 11.2.0.8 also) a built in DDM capability was introduced by Oracle – Data reduction

Pros:

• Easy to implement
• No effect on actual data – can be implemented in production environments
• Data can be masked per IP address, per user, or per  application

Cons:

• Actual data is not changed – strong users such as DBA or data owners can access original data
• Limited masking functionality can be applied
• No values persistency

Static Data Masking

Replacing the accrual data inside the database with fictitious data that looks realistic, in most cases after static data masking original data can’t be retrieved. This approach is used when production database or subset of the production database is being copied for non-production use like development QA and testing. Oracle deliver static data masking using grid control – Data Masking Pack.

Pros:

• Original data can’t be retrieved – best practice preventing data leakage to non-production environments
• Provides more comprehensive masking capabilities
• Allow organizations to share data with external companies

Cons:

• Actual data is changed, original data can’t be accessed if needed.
• Masking takes time, in large databases it can take a while
• More complicated to design and implement correctly

Oracle Data Redaction – By Example

Data Redaction provides selective, on-the-fly redaction of sensitive data in SQL query results prior to display by applications so that unauthorized users cannot view the sensitive data. It enables consistent redaction of database columns across application modules accessing the same database information. Data Redaction minimizes changes to applications because it does not alter actual data in internal database buffers, caches, or storage, and it preserves the original data type and formatting when transformed data is returned to the application.

Let’s understand how it works, first we need to create a redaction policy. In the first example I have created a policy on EMPLOYEE table of HR schema, I have used DBMS_REDACT.FULL to completely replace the original data in commission_pct field with nulls or 0. All create policy statements has been issued by SYS:

BEGIN
 DBMS_REDACT.ADD_POLICY(
  object_schema => 'HR',
  object_name => 'employees',
  column_name => 'commission_pct',
  policy_name => 'redact_com_pct',
  function_type => DBMS_REDACT.FULL,
  expression => '1=1');
END;
/

And not let’s connect as HR and retrieve some data from employees including the redacted column
“commission_pct”:

As you can see original data was replaced with zero values, if the redacted column was string, null was returned from redaction policy.

It is time do more complicated redaction and return part of the original value but mask all the rest, the below policy is used to redact credit_card column of employees table. Notice that I have used ” DBMS_REDACT.PARTIAL” function

with the following parameters – ‘VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,,1,12′  –  for every row that credit_card data matches this pattern – “VVVVFVVVVFVVVVFVVVV”, replace it with this pattern “VVVV-VVVV-VVVV-VVVV” and replace the first 12 characters with ““.

 DBMS_REDACT.ADD_POLICY(
  object_schema => 'HR',
  object_name => 'employees',
  column_name => 'credit_card',
  policy_name => 'redact_credit_card',
  function_type => DBMS_REDACT.PARTIAL,
  function_parameters => 'VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,*,1,12',
  expression => '1=1');
END;
/

And this is how it looks like when trying to query credit_card column:

Up until now we have used “expression => ‘1=1’” on all of our redaction policies, this means that data will be reduced whenever the redacted column is accessed. Oracle data redaction can conditionally redact data making original data available for some and redacted for all others. The below policy replaces phone_number column with random values only for database user – REDACT.

BEGIN
 DBMS_REDACT.ADD_POLICY(
  object_schema => 'HR',
  object_name => 'employees',
  column_name => 'phone_number',
  policy_name => 'redact_cust_rand_vals',
  function_type => DBMS_REDACT.RANDOM,
  expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''REDACT''');
END;
/

Notice that when data is queried using user HR original data is retrieved and when REDACT user queries the table data is redacted.

Last but not least, DBMS_REDACT provide ALTER_POLICY capabilities to alter existing policies, I can’t show this online but you have to trust me on this one DBMS_REDACT.ALTER_POLICY changes the policy on the fly, next time the redacted object will be accessed new redaction policy will be active, no need to reconnect sessions or restart the data base.

BEGIN
 DBMS_REDACT.ALTER_POLICY(
  object_schema => 'HR',
  object_name => 'employees',
  policy_name => 'redact_cust_rand_vals',
  action => DBMS_REDACT.MODIFY_EXPRESSION,
  expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''REDACT''');
END;
/

Summery

Data redaction was introduced on Oracle 12c as a great addition to Oracle database security features providing us the capability to display different data to different people and prevent access to sensitive data on the fly with near zero performance overhead. Until next time, be smart be careful and don’t be afraid to use Oracle database security features.

Oded Raz
Oracle ACE Director