Redacting Sensitive Data in Oracle SQL Developer Data Modeler 4.0

Overview

    Purpose

    This tutorial shows you how to redact sensitive data during the modeling process and how to create templates for applying the same masking pattern to other parts of your design. 

    Time to Complete

    Approximately 20 minutes

    Introduction

    In this tutorial, you learn how to create a Redaction Policy and apply it to sensitive data and how to create Masking Templates using SQL Developer Data Modeler 4.0 (Data Modeler).

    Prerequisites

    Before starting this tutorial, you should:

    • Have the latest Oracle SQL Developer Data Modeler installed. Support for Data Redaction starts from version 4.0 and above. Download it here.
    • Have an Oracle 12c database with a table containing sensitive data suitable for redacting data (for example, this tutorial uses the Employees table containing salary information).
    • Understand Oracle Database concepts. More information is available in the Oracle Database 12c Documentation and on the Oracle Technology Network.

Setting Up the Environment

    For this section, you will open the HR Relational Model by importing the HR schema from the data dictionary.

    Double click on the SQL Developer Data Modeler desktop icon or the datamodeler.exe in the installed Data Modeler folder.


    The Oracle SQL Developer Data Modeler window is displayed.

    Click File > Import > Data Dictionary.


    In the Data Dictionary Import Wizard, click Add.


    In the New / Select Database Connection dialog box, enter or select the following values, then click Connect.

    Name: HR
    Username: HR
    Password: Enter the password for HR
     Save Password: Select this check box
    Connection Type: Basic
    Role: default
    Hostname: localhost
    Port: 1521
    SID or Service Name: Enter SID or Service Name

    Your screen should look similar to this.

    The new connection will appear. Click on the HR connection name and click Next.


    Select HR schema to import. Click Next.

    Click Select All icon to import all the Tables. Click Next.


    Click Finish.

    The View Log will be displayed showing all objects have been imported successfully. Click Close.

    The HR Relational Model will open.


Redacting Sensitive Data

    Data Modeler enables you to set up the redaction of sensitive information as early as during the design of the database. To redact the data, you first create a redaction policy.

    Note: This feature is also available in Oracle Database Release 11gR2.

    In the HR Relational Model, double-click the HR.EMPLOYEES table to view its table properties.


    The General table properties are displayed.

    In the navigation tree, select Redaction Policy.


    The Redaction Policy dialog box is displayed.


    For the policy name, enter Emp Policy.

    Confirm that the Enabled and Generate in DDL check boxes are selected and that the Expression text box is empty (expression defaults to true).

    Your screen should look similar to this:


    In the navigation tree, select Columns.


    Click the Security tab and select SALARY.

     

    Click the cell corresponding to the SALARY row and the Contains PII column. (PII stands for "Personally Identifiable Information.")

    Click the null selection (the blank selection above YES).

    In the same row, click the Sensitive Info column and select YES.

    In the same row, click the Masking Type column and select FULL. 


    The next column, Mask Template, is not required for a fully masked column, but it enables you to specify the masking details for partially masked columns. You will use this column later in the tutorial.


    Click Apply.

    Click OK.

    In the EMPLOYEES table, SALARY is displayed in red font to indicate that it is a secure field.

Creating a Masking Template

    You can save data masking formats as templates and reuse (and modify) them by using Mask Templates Administration.

    Select Tools > Mask Templates Administration.


    The Mask Templates Administration dialog box is displayed.

    Click Add.


    Enter Numeric Mask Template for the name.


    Select PARTIAL for the function type.


    Select Numeric for the data type.


    Enter an asterisk (*) for the masking character. Mask from 1 (the first number) through 6 (the sixth number).

    Your screen should look like this:


    Click Apply.

    Click Save.


    Click Close to return to the model diagram.

    Right-click the HR.EMPLOYEES table and select Properties.

    Select Columns.


    Click the Security tab.


    Click the cell corresponding to the SALARY row and the Masking Type column.


    Select PARTIAL for the masking type.


    Click the cell corresponding to the SALARY row and Mask Template column and select Numeric Mask Template.


    The template is assigned to the SALARY column.

    Click Apply.


    Click OK.

    In the model diagram, right-click the EMPLOYEES table and select DDL Preview to view the DDL for the security changes.

    In the preview, examine the DDL for your security changes. Here you can see the policy name and column to which the security is being added, as well as the type of masking and parameters for the masking function.


    Click OK.

    The template is saved. Click Close to return to the table properties.


    Congratulations! You have finished the tutorial.


Summary

    In this tutorial, you learned how to use SQL Developer Data Modeler 4.0 to:

    • Import a Relational Model from the Data Dictionary
    • Define a Redaction Policy to redact sensitive data
    • Create a Masking Template and applied it to sensitive data

    Resources

    Additional information about Oracle and Oracle technologies is available at the following sites:

    Credits

    • Lead Curriculum Developer: Pete DeHaan
    • Other Contributors: Ashley Chen

To navigate this Oracle by Example tutorial, note the following:

Hide Header Buttons:
Click the title to hide the buttons in the header. To show the buttons again, click the title again.
Topic List:
Click a topic to navigate to that section.
Expand All Topics:
Click the button to show or hide the details for the sections. By default, all topics are collapsed.
Hide All Images:
Click the button to show or hide the screenshots. By default, all images are displayed.
Print:
Click the button to print the content. The content that is currently displayed or hidden is printed.

To navigate to a particular section in this tutorial, select the topic from the list.