15 Masking Sensitive Data

15.1.1 Data Masking Concepts

Data masking (also known as data scrambling and data anonymization) is the process of replacing sensitive information copied from production databases to test non-production databases with realistic, but scrubbed, data based on masking rules. Data masking is ideal for virtually any situation when confidential or regulated data needs to be shared with non-production users. These users may include internal users such as application developers, or external business partners such as offshore testing companies, suppliers and customers. These non-production users need to access some of the original data, but do not need to see every column of every table, especially when the information is protected by government regulations.

Data masking enables organizations to generate realistic and fully functional data with similar characteristics as the original data to replace sensitive or confidential information. This contrasts with encryption or Virtual Private Database, which simply hides data, and the original data can be retrieved with the appropriate access or key. With data masking, the original sensitive data cannot be retrieved or accessed.

Names, addresses, phone numbers, and credit card details are examples of data that require protection of the information content from inappropriate visibility. Live production database environments contain valuable and confidential data—access to this information is tightly controlled. However, each production system usually has replicated development copies, and the controls on such test environments are less stringent. This greatly increases the risks that the data might be used inappropriately. Data masking can modify sensitive database records so that they remain usable, but do not contain confidential or personally identifiable information. Yet, the masked test data resembles the original in appearance to ensure the integrity of the application.

15.1.2 Security and Regulatory Compliance

Masked data is a sensible precaution from a business security standpoint, because masked test information can help prevent accidental data escapes. In many cases, masked data is a legal obligation. The Enterprise Manager Data Masking Pack can help organizations fulfill legal obligations and comply with global regulatory requirements, such as Sarbanes-Oxley, the California Database Security Breach Notification Act (CA Senate Bill 1386), and the European Union Data Protection Directive.

The legal requirements vary from country to country, but most countries now have regulations of some form to protect the confidentiality and integrity of personal consumer information. For example, in the United States, The Right to Financial Privacy Act of 1978 creates statutory Fourth Amendment protection for financial records, and a host of individual state laws require this. Similarly, the U.S. Health Insurance Portability and Accountability Act (HIPAA) created protection of personal medical information.

15.1.3 Roles of Data Masking Users

The following types of users participate in the data masking process for a typical enterprise:

• Application database administrator or application developer

  This user is knowledgeable about the application and database objects. This user may add additional custom database objects or extensions to packaged applications, such as the Oracle E-Business suite.

• Information security administrator

  This user defines information security policies, enforces security best practices, and also recommends the data to be hidden and protected.

15.1.4 Related Oracle Security Offerings

Besides data masking, Oracle offers the following security products:

• Virtual Private Database or Oracle Label Security — Hides rows and data depending on user access grants.

• Transparent Data Encryption — Hides information stored on disk using encryption. Clients see unencrypted information.

• DBMS_CRYPTO — Provides server packages that enable you to encrypt user data.

• Database Vault — Provides greater access controls on data.

15.1.5 Agent Compatibility for Data Masking

Data Masking supports Oracle Database 9i and newer releases. If you have a version prior to 11.1, you can use it by implementing the following work-around.

Replace the following file...

AGENT_HOME/sysman/admin/scripts/db/reorg/reorganize.pl

... with this file:

OMS_HOME/sysman/admin/scripts/db/reorg/reorganize.pl

15.1.6 Supported Data Types

The list of supported data types varies by release.

• Grid Control 10g Release 5 (10.2.0.5), Database 11g Release 2 (11.2), and Cloud Control 12c Release 1 (10.2.0.1)

  • Numeric Types

    The following Numeric Types can use Array List, Delete, Fixed Number, Null Value, Post Processing Function, Preserve Original Data, Random Decimal Numbers, Random Numbers, Shuffle, SQL Expression, Substitute, Table Column, Truncate, Encrypt, and User Defined Function formats:

    • NUMBER

    • FLOAT

    • RAW

    • BINARY_FLOAT

    • BINARY_DOUBLE

  • String Types

    The following String Types can use Array List, Delete, Fixed Number, Fixed String, Null Value, Post Processing Function, Preserve Original Data, Random Decimal Numbers, Random Digits, Random Numbers, Random Strings, Shuffle, SQL Expression, Substitute, Substring, Table Column, Truncate, Encrypt, and User Defined Function formats:

    • CHAR

    • NCHAR

    • VARCHAR2

    • NVARCHAR2

  • Date Types

    The following Date Types can use Array List, Delete, Null Value, Post Processing Function, Preserve Original Data, Random Dates, Shuffle, SQL Expression, Substitute, Table Column, Truncate, Encrypt, and User Defined Function formats:

    • DATE

    • TIMESTAMP

• Grid Control 11g Release 1 (11.1) and Cloud Control 12c Release 1 (10.2.0.1)

  • Large Object (LOB) Data Types

    The following Data Types can use Fixed Number, Fixed String, and Null Value formats:

    • BLOB

    • CLOB

    • NCLOB

15.5.1 Creating New Masking Formats

This section describes how to create new masking formats using Enterprise Manager.

To create a masking format in the format library: 

1. From the Enterprise menu, select Quality Management, then Data Masking Formats. Alternatively, if you are in the Database home page, select Data Masking Format Library from the Schema menu.

   The Format Library appears with predefined formats that Oracle Enterprise Manager provides.

2. Click Create.

   The Create Format page appears, where you can define a masking format.

   Tip:

   For information on page user controls, see the online help for the Format page.

3. Provide a required name for the new format, select a format entry type from the Add list, then click Go.

   A page appears that enables you to provide input for the format entry you have selected. For instance, if you select Array List, the subsequent page enables you to enter a list of values, such as New York, New Jersey, and New Hampshire.

4. Continue adding additional format entries as needed.

5. When done, provide an optional user-defined or post-processing function (see "Providing User-defined and Post-processing Functions"), then click OK to save the masking format.

   The Format Library page reappears with your newly created format displayed in the Format Library table. You can use this format later to mask a column of the same sensitive type.

   Tip:

   For information on page user controls, see the online help for the Format Library and Create Format pages.

15.5.2 Using Oracle-supplied Predefined Masking Formats

Enterprise Manager provides several out-of-box predefined formats. All predefined formats and built-in formats are random. The following sections discuss the various Oracle-supplied format definitions and how to modify them to suit your needs:

• Patterns of Format Definitions

• Category Definitions

• Installing the DM_FMTLIB Package

15.5.3 Providing a Masking Format to Define a Column

When you create a masking definition ("Masking with an Application Data Model and Workloads"), you will be either importing a format or selecting one from the available types in the Define Column Mask page. Format entry options are as follows:

• Array List

  The data type of each value in the list must be compatible with that of the masked column. Uniqueness must be guaranteed if needed. For example, for a unique key column that already has 10 distinct values, the array list should also contain at least 10 distinct values.

• Delete

  Deletes the specified rows as identified by the condition clauses. If a column includes a delete format for one of its conditions, a foreign key constraint or a dependent column cannot refer to the table.

• Encrypt

  Encrypts column data by specifying a regular expression. The column values in all the rows must match the regular expression. This format can be used to mask data consistently across databases. That is, for a given value it always generates the same masked value.

  For example, the regular expression [(][1-9][0-9]{2}[)][_][0-9]{3}[-][0-9]{4} generates U.S. phone numbers such as (123) 456-7890.

  This format supports a subset of the regular expression language. It supports encrypting strings of fixed widths. However, it does not support * or + syntax of regular expressions.

  If a value does not match the format specified, the encrypted value may no longer produce one-to-one mappings. All non-confirming values are mapped to a single encrypted value, thereby producing a many-to-one mapping.

• Fixed Number

  The type of column applicable to this entry is a NUMBER column or a STRING column. For example, if you mask a column that has a social security number, one of the entries can be Fixed Number 900. This format is combinable.

• Fixed String

  The type of column applicable to this entry is a STRING column. For example, if you mask a column that has a License Plate Number, one of the entries can be Fixed String CA. This format is combinable.

• Null Value

  Masks the column using a value of NULL. The column must be nullable.

• Post-Processing Function

  This is a special function that you can apply to the mask value that the masking engine generates. This function takes the mask value as input and returns the actual mask value to be used for masking.

  The post-processing function is called after the mask value is generated. You can use it, for instance, to add commas or dollar signs to a value. For example, if a mask value is a number such as 12000, the post processing function can modify this to $12,000. Another use is for adding checksums or special encodings for the mask value that is produced.

  In the following statement:

  Function post_proc_udf_func (rowid varchar2, column_name varchar2, mask_value varchar2) returns varchar2;
  

  • rowid is the min (rowid) of the rows that contains the value mask_value 3rd argument.

  • column_name is the name of the column being masked.

  • mask_value is the value being masked.

• Preserve Original Data

  Retains the original values for rows that match the specified condition clause. This is used in cases where some rows that match a condition do not need to be masked.

• Random Dates

  The uniqueness of the Date column is not maintained after masking. This format is combinable.

• Random Digits

  This format generates unique values within the specified range. For example, for a random digit with a length of [5,5], an integer between [0, 99999] is randomly generated, left padded with '0's to satisfy the length and uniqueness requirement. This is a complementary type of random number, which will not be padded. When using random digits, the random digit pads to the appropriate length in a string. It does not pad when used for a number column. This format is combinable.

  Data masking ensures that the generated values are unique, but if you do not specify enough digits, you could run out of unique values in that range.

• Random Numbers

  If used as part of a mixed random string, these have limited usage for generating unique values. This format generates unique values within the specified range. For example, a starting value of 100 and ending value of 200 generates an integer number ranging from 100 to 200, both inclusive. Note that Oracle Enterprise Manager release 10.2.0.4.0 does not support float numbers. This format is combinable.

• Random Strings

  This format generates unique values within the specified range. For example, a starting length of 2 and ending length of 6 generates a random string of 2 - 6 characters in length. This format is combinable.

• Shuffle

  This format randomly shuffles the original column data. It maintains data distribution except when a column is conditionally masked and its values are not unique.

  For more information, see "Using the Shuffle Format".

• Substitute

  This format uses a hash-based substitution for the original value and always yields the same mask value for any given input value. Specify the substitution masking table and column. This format has the following properties:

  • The masked data is not reversible. That is, this format is not vulnerable to external security breaches because the original value is replaced, so it is not possible to retrieve the original value from the mask value.

  • Masking multiple times with a hash substitute across different databases yields the same mask value. This characteristic is valid across multiple databases or multiple runs assuming that the same substitution values are used in the two runs. That is, the actual rows and values in the substitution table do not change. For example, suppose the two values Joe and Tom were masked to Henry and Peter. When you repeat the same mask on another database using the same substitution table, if there were Bob and Tom, they might be replaced with Louise and Peter. Notice that even though the two runs have different data, Tom is always replaced with Peter.

  • This format does not generate uniqueness.

• Substring

  Substring is similar to the database substr function. The start position can be either a positive or a negative integer. For example, if the original string is abcd, a substring with a start position of 2 and length of 3 generates a masked string of bcd. A substring with start position of -2 and length of 3 generates a masked string of cd. This format is combinable.

• Table Column

  A table column enables you to select values from the chosen column as the replacement value or part thereof. The data type and uniqueness must be compatible. Otherwise, a failure occurs when the job runs. This format is combinable.

• Truncate

  Truncates all rows in a table. If one of the columns in a table is marked as truncated, the entire table is truncated, so no other mask formats can be specified for any of the other columns. If a table is being truncated, it cannot be referred to by a foreign key constraint or a dependent column.

• User Defined Function

  The data type and uniqueness of the output values must be compatible with the original output values. Otherwise, a failure occurs when the job runs.

  In the following statement:

  Function udf_func (rowid varchar2, column_name varchar2, original_value varchar2) returns varchar2;
  

  • rowid is the min (rowid) of the rows that contain the value original_value 3rd argument.

  • column_name is the name of the column being masked.

  • original_value is the value being masked.

15.5.4 Deterministic Masking Using the Substitute Format

You may occasionally need to consistently mask multiple, distinct databases. For instance, if you run HR, payroll, and benefits that have an employee ID concept on three separate databases, the concept may be consistent for all of these databases, in that an employee's ID can be selected to retrieve the employee's HR, payroll, or benefits information. Based on this premise, if you were to mask the employee's ID because it actually contains his/her social security number, you would have to mask this consistently across all three databases.

Deterministic masking provides a solution for this problem. You can use the Substitute format to mask employee ID column(s) in all three databases. The Substitute format uses a table of values from which to substitute the original value with a mask value. As long as this table of values does not change, the mask is deterministic or consistent across the three databases.

15.6.1 Adding Dependent Columns

Dependent columns are defined by adding them to the Application Data Model. The following prerequisites apply for the column to be defined as dependent:

• A valid dependent column should not already be included for masking.

• The column should not be a foreign key column or referenced by a foreign key column.

• The column data should conform to the data in the parent column.

If the column does not meet these criteria, an "Invalid Dependent Columns" message appears when you attempt to add the dependent column.

15.6.2 Masking Dependent Columns for Packaged Applications

The following procedure explains how to mask data across columns for packaged applications in which the relationships are not defined in the data dictionary.

To mask dependent columns for packaged applications: 

1. Go to Data Discovery and Modeling and create a new Application Data Model (ADM) using metadata collection for your packaged application suite.

   When metadata collection is complete, edit the newly created ADM.

2. Manually add a referential relationship:

   1. From the Referential Relationships tab, open the Actions menu, then select Add Referential Relationship.

      The Add Referential Relationship pop-up window appears.

   2. Select the requisite Parent Key and Dependent Key information.

   3. In the Columns Name list, select a dependent key column to associate with a parent key column.

   4. Click OK to add the referential relationship to the ADM.

      The new dependent column now appears in the referential relationships list.

3. Perform sensitive column discovery.

   When sensitive column discovery is complete, review the columns found by the discovery job and mark them sensitive or not sensitive as needed.

   When marked as sensitive, any discovery sensitive column also marks its parent and the other child columns of the parent as sensitive. Consequently, it is advisable to first create the ADM with all relationships. ADM by default, or after running drivers, may not contain denormalized relationships. You need to manually add these.

   For more information about sensitive column discovery, see step 6.

4. Go to Data Masking and create a new masking definition.

5. Select the newly created ADM and click Add, then Search to view this ADM's sensitive columns.

6. Select columns based on your search results, then import formats for the selected columns.

   Enterprise Manager displays formats that conform to the privacy attributes.

7. Select the format and generate the script.

8. Execute the masking script.

   Enterprise Manager executes the generated script on the target database and masks all of your specified columns.

15.6.3 Selecting Data Masking Advanced Options

The following options on the Masking Definitions page are all checked by default, so you need to uncheck the options that you do not want to enable:

• Data Masking Options

• Random Number Generation

• Pre- and Post-mask Scripts

variable sts_task  VARCHAR2(64);

/*Step :1 Create the staging table for statistics*/

exec dbms_stats.create_stat_table(ownname=>'SCOTT',stattab=>'STATS');
 
/* Step 2: Export the table statistics into the staging table. Cascade results in
all index and column statistics associated with the specified table being exported
as well. */
 
exec
dbms_stats.export_table_stats(ownname=>'SCOTT',tabname=>'EMP',
partname=>NULL,stattab=>'STATS',statid=>NULL,cascade=>TRUE,statown=>'SCOTT');
exec
dbms_stats.export_table_stats(ownname=>'SCOTT',tabname=>'DEPT',
partname=>NULL,stattab=>'STATS',statid=>NULL,cascade=>TRUE,statown=>'SCOTT');

/* Step 3: Create analysis task */
3. exec :sts_task := DBMS_SQLPA.create_analysis_task(sqlset_name=>
'scott_test_sts',task_name=>'SPA_TASK', sqlset_owner=>'SCOTT');
 
/*Step 4: Execute the analysis task before masking */
exec DBMS_SQLPA.execute_analysis_task(task_name => 'SPA_TASK', 
execution_type=> 'explain plan', execution_name  => 'pre-mask_SPA_TASK'); 

*Step 1: Import the statistics from the staging table to the dictionary tables*/
 
exec
dbms_stats.import_table_stats(ownname=>'SCOTT',tabname=>'EMP',
partname=>NULL,stattab=>'STATS',statid=>NULL,cascade=>TRUE,statown=>'SCOTT');
exec
dbms_stats.import_table_stats(ownname=>'SCOTT',tabname=>'DEPT',
partname=>NULL,stattab=>'STATS',statid=>NULL,cascade=>TRUE,statown=>'SCOTT');
 
/* Step 2: Drop the staging table */
 
exec dbms_stats.drop_stat_table(ownname=>'SCOTT',stattab=>'STATS');

/*Step 3: Execute the analysis task before masking */
exec DBMS_SQLPA.execute_analysis_task(task_name=>'SPA_TASK', 
execution_type=>'explain plan', execution_name=>'post-mask_SPA_TASK');
 
/*Step 4: Execute the comparison task */
exec DBMS_SQLPA.execute_analysis_task(task_name =>'SPA_TASK', 
execution_type=>'compare', execution_name=>'compare-mask_SPA_TASK'); 

15.6.4 Cloning the Production Database

When you clone and mask the database, a copy of the masking script is saved in the Enterprise Manager repository and then retrieved and executed after the clone process completes. Therefore, it is important to regenerate the script after any schema changes or modifications to the production database.

To clone and optionally mask the masking definition's target database: 

1. From the Data Masking Definitions page, select the masking definition you want to clone, select Clone Database from the Actions list, then click Go.

   The Clone Database: Source Type page appears.

   The Clone Database wizard appears, where you can create a test system to run the mask. You can also access this wizard by clicking the Clone and Mask button from the Script Generation Results page.

2. Specify the type of source database backup to be used for the cloning operation, then click Continue.

3. Proceed through the wizard steps as you ordinarily would to clone a database. For assistance, refer to the online help for each step.

4. In the Database Configuration step of the wizard, add a masking definition, then select the Run SQL Performance Analyzer option as well as other options as desired or necessary.

5. Schedule and then run the clone job.

To work around the Mask Data step limitation: 

• Add a post-clone script and copy the workload contents to the destination directory. (Refer to the Database Configuration step discussed in "Using Cloning and Masking for Evaluation".)

  Before copying, ensure that the destination directory matches the correct mapping of the destination directory object. If the source database has a directory object of CAPTURE_DIR with a path of /net/sourcehost/scratch/aime/capture_dir/, the cloned database will have the same directory object, but with a path of /net/desthost/scratch/aime/capture_dir/, as shown in the following command you can add to the post-clone script:

  host cp -r 
  /net/sourcehost/path/to/capture_dir /net/desthost/path/to/capture_dir
  

  • The Clone user interface contains a field to add this information to the Clone operation.

  • Note that cloning the database also clones the directory objects. However, you can configure the location of the directory object on the destination host during cloning.

Alternatively, If the disks are not remotely mounted, you can perform the copy procedure with the RCP command, or by initiating a ZIP host command, then using FTP.

15.6.5 Importing a Data Masking Template

You can import and re-use a previously exported data masking definition template, including templates for Fusion Applications, saved as an XML file to the current Enterprise Manager repository.

Note the following advisory information:

• The XML file format must be compliant with the masking definition XML format.

• Verify that the name of the masking definition to be imported does not already exist in the repository.

• Verify that the target name identifies a valid Enterprise Manager target.

To import a data masking template: 

1. From the Data Masking Definitions page, click Import.

   The Import Masking Definition page appears.

2. Specify the ADM associated with the template. The Reference Database is automatically provided.

3. Browse for the XML file, or specify the name of the XML file, then click Continue.

The Data Masking Definitions Page reappears and displays the imported definition in the table list for subsequent viewing and masking.

15.7.1 Using Only Masking for Evaluation

To use only masking to evaluate performance: 

1. From the Data Masking Definitions page, select the masking definition to be analyzed, then click Schedule Job.

   The Schedule Data Masking Job page appears.

2. At the top of the page, provide the requisite information.

   The script file location pertains to the masking script, which also contains the pre- and post-masking scripts you created in "Pre- and Post-mask Scripts".

3. In the Encryption Seed section, provide a text string that you want to use for encryption.

   This section only appears for masking definitions that use the Substitute or Encrypt formats. The seed is an encryption key used by the encryption/hash-based substitution APIs, and makes masking more deterministic instead of being random.

4. In the Workloads section:

   1. Select the Mask SQL Tuning Sets option, if desired.

      If you use a SQL Tuning Set that has sensitive data to evaluate performance, it is beneficial to mask it for security, consistency of data with the database, and to generate correct evaluation results.

   2. Select the Capture Files option, if desired, then select a capture directory.

      When you select this option, the contents of the directory is masked. The capture file masking is executed consistently with the database.

5. In the Detect SQL Plan Changes Due to Masking section, leave the Run SQL Performance Analyzer option unchecked.

   You do not need to enable this option because the pre- and post-masking scripts you created, referenced in step 2, already execute the analyzer.

6. Provide credentials and scheduling information, then click Submit.

   The Data Masking Definitions page reappears, and a message appears stating that the Data Masking job has been submitted successfully.

   During masking of any database, the AWR bind variable data is purged to protect sensitive bind variables from leaking to a test system.

7. When the job completes successfully, click the link in the SQL Performance Analyzer Task column to view the executed analysis tasks and Trial Comparison Report, which shows any changes in plans, timing, and so forth.

15.7.2 Using Cloning and Masking for Evaluation

Using both cloning and masking to evaluate performance is very similar to the procedure described in the previous section, except that you specify the options from the Clone Database wizard, rather than from the Schedule Data Masking Job page.

To use both cloning and masking to evaluate performance: 

1. Follow the steps described in "Cloning the Production Database".

2. At step 4, the format of the Database Configuration step appears different from the Schedule Data Masking Job page discussed in "Using Only Masking for Evaluation", but select options as you would for the Schedule Data Masking Job page.

3. Continue with the wizard steps to complete and submit the cloning and masking job.