5.1 Protecting Data with Data Redaction

«« Previous
Next »»

1. Overview


Purpose

This tutorial covers how to use data redaction to protect sensitive data.

Time to Complete

Approximately 10 minutes

Introduction

Data redaction can be used to dynamically hide sensitive data. Redaction policies can be set to specify the table columns to be protected, and from which user accounts the data should be hidden.

Scenario

This tutorial uses an Application Express application to display the data. In the tutorial, two user accounts are used to demonstrate data access as defined in a data redaction policy. The sales-vp account has access to all data, and the sales-rep account does not have access to the deal_probability and deal_amount columns. Once the policy is applied, the sales-rep account sees zeroes instead of the actual values of the deal_probability and deal_amount columns.

Prerequisites

Before starting this tutorial, you should:
  • Have Oracle Database 12c installed.
  • Installed Oracle Application Express (APEX) in a pluggable database, with the Sales Forecasting application created in an APEX workspace.
The environment used in the development of this tutorial is as follows:
  • ORACLE_HOME: /u01/app/oracle/product/12.1.0
  • TNS Listener port: 1521
  • Container databases:
    • SID: cdb1
    • SID: cdb2
  • Pluggable databases (in cdb1):
    • pdb1
    • pdb2
  • Non-CDB SID: noncdb

2. Viewing the Data Prior to Redaction


1. Open the browser, and go to the APEX application at http://localhost:8080/apex/f?p=101.

2. At the login screen, enter sales-vp for username, and oracle for password.

Protecting Data with Data Redaction

3. Click the Opportunities icon.

Protecting Data with Data Redaction

4. The Prob. and Amount column values should display values greater than zero.

Protecting Data with Data Redaction

5. Click the Logout link.

Protecting Data with Data Redaction

6. At the login screen, enter sales-rep for username, and oracle for password.

Protecting Data with Data Redaction

7. The Prob. and Amount column values should display values greater than zero.

Protecting Data with Data Redaction

Remain logged into the APEX application as sales-rep.

3. Creating and Applying a Data Redaction Policy


1. Connect to pdb1 as sys.

sqlplus sys/oracle@localhost:1521/pdb1 as sysdba

2. Create the redaction policy. Notice the expression parameter defines which user account(s) do not have access to the actual values of the redacted column. According to this policy, if the user account is NOT sales-vp, or is not defined, the deal_probability table column value should be redacted. This means sales-rep will see zeroes in place of the actual values in this column.

BEGIN
DBMS_REDACT.add_policy(object_schema => 'DEMO'
,object_name => 'EBA_SALES_DEALS'
,policy_name => 'Deal Detail Redaction'
,expression => 'SYS_CONTEXT(''USERENV'',''CLIENT_INFO'') NOT LIKE ''SALES-VP%'' OR SYS_CONTEXT(''USERENV'',''CLIENT_INFO'') IS NULL'
,column_name => 'DEAL_PROBABILITY'
,function_type => dbms_redact.FULL
);
END;
/

3. Add the deal_amount table column to the redaction policy.

BEGIN
DBMS_REDACT.alter_policy(object_schema => 'DEMO'
,object_name => 'EBA_SALES_DEALS'
,policy_name => 'Deal Detail Redaction'
,action => dbms_redact.ADD_COLUMN
,column_name => 'DEAL_AMOUNT'
,function_type => dbms_redact.FULL
);
END;
/

4. Viewing the Data After Redaction


1. Go back to the APEX application, and refresh the browser window. Because you are already logged in as sales-rep, you should now see zeroes in the Prob. and Amount columns.

Protecting Data with Data Redaction

Notice the Weighted column shows zero because it's a calculated column showing the product of Prob. times Amount.

Protecting Data with Data Redaction

2. Logout of the APEX application, and re-login as sales-vp. You should now see the actual values in the Prob. and Amount columns.

Protecting Data with Data Redaction


5. Testing "Create Table As Select" Against a Redacted Table


1. Test if you can issue a "Create Table As Select" (CTAS) command against a redacted table. Connect to pdb1 as demo.

connect demo/oracle@localhost:1521/pdb1

2. Issue a CTAS on the demo.eba_sales_deals table to create a new table called demo.test.

create table demo.test as select * from demo.eba_sales_deals;

You should see an this error: "ORA-28081: Insufficient privileges - the command references a redacted object."

3. Now, connect to pdb1 as sys.

connect sys/oracle@localhost:1521/pdb1 as sysdba

4. Issue the CTAS statement again to create demo.test.
create table demo.test as select * from demo.eba_sales_deals;

Why did the CTAS succeed this time?
Answer: The sys and system users are able to bypass any existing Oracle Data Redaction policies.


6. Dropping a Data Redaction Policy


1. Connect to pdb1 as sys.

connect sys/oracle@localhost:1521/pdb1 as sysdba

2. Drop the redaction policy.

BEGIN
DBMS_REDACT.drop_policy(object_schema => 'DEMO'
,object_name => 'EBA_SALES_DEALS'
,policy_name => 'Deal Detail Redaction'
);
END;
/

3. Verify that sales-rep can now view the actual table column values for deal_probability and deal_amount. Logout of the APEX application, and re-login as sales-rep. You should now see the actual values in the Prob. and Amount columns.

Protecting Data with Data Redaction

«« Previous
Next »»