Protecting Data with Data Redaction

 

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
 

Viewing the Data Prior to Redaction

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

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

    Click the Opportunities icon.

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

    Click the Logout link.

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

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

    Remain logged into the APEX application as sales-rep.

 

Creating and Applying a Data Redaction Policy

    Connect to pdb1 as sys.

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

    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;
    /

    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;
    /

 

Viewing the Data After Redaction

    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.

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

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

 

Testing "Create Table As Select" Against a Redacted Table

    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

    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."

    Now, connect to pdb1 as sys.

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

    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.

 

Dropping a Data Redaction Policy

    Connect to pdb1 as sys.

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

    Drop the redaction policy.

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

    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.

 

Summary

    In this tutorial, you have learned how to:

    • Create and apply a data redaction policy
    • Remove a data redaction policy

    Resources

    Credits

    • Contributor: Database Security PM team