Oracle Data Redaction
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.
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 188.8.131.52 also) a built in DDM capability was introduced by Oracle – Data reduction
- 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
- 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.
- 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
- 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
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; /
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.
Oracle ACE Director
Thanks for the nice article.
Great explanation to implement redaction in to DB applications.
If i want the data in a column to be viewed by 2 or more users but not all users of the database in Data REDACTION for a certain
REDACTION policy. How can i achive it?
Thank you such a nice article,
Can one redact the based on IP address of connected session using SYS_CONTEXT(‘USERENV’,’IP_ADDRESS’)
i am using this policy to redact the data if IP address 184.108.40.206
I am not able to get it work , if connected from different IP address .
object_schema => ‘HR’,
object_name => ‘CUSTOMERS_DATA’,
column_name => ‘EMAILADR’,
policy_name => ‘REDACT_CUSTOMER_DATA_POLICY’,
function_type => DBMS_REDACT.FULL,
expression => ‘SYS_CONTEXT(”USERENV”,”IP_ADDRESS”) = ”220.127.116.11”’
Nice Post, I am using RANDOM policy and want to restrict multiple users . Can you please advise how can we achieve this requirement ?
function_type => DBMS_REDACT.RANDOM,
expression => ‘SYS_CONTEXT(”USERENV”,”SESSION_USER”) = ”SH”’);
I can see only one schema is possible. IS there any way we can achieve this requirement ?