Chapter 5. Data Redaction

In this chapter, we will cover the following tasks:

• Creating a redaction policy when using full redaction

• Creating a redaction policy when using partial redaction

• Creating a redaction policy when using random redaction

• Creating a redaction policy when using regular expression redaction

• Using Oracle Enterprise Manager Cloud Control 12c to manage redaction policies

• Changing the function parameters for a specified column

• Adding a column to the redaction policy

• Enabling, disabling, and dropping a redaction policy

• Exempting users from data redaction policies

Introduction

Oracle Data Redaction is a new security feature, introduced in Oracle Database 12c. From a licensing viewpoint, it is part of the Advanced Security Option (only available as an option for Oracle Database Enterprise Edition). However, afterwards, Oracle decided to make it available in Oracle Database 11g as well (only in version 11.2.0.4). The main idea behind this feature is to mask (hide/redact) some (sensitive) data from end-users. Having this in mind, it is logical that you will primarily use this security solution in a production environment.

Note

Oracle Data Redaction and Oracle Data Masking are both used to mask sensitive data, but these solutions are completely different—from the way they are designed (how they work) to their target implementation use cases. Oracle Data Masking enables organizations to use production data in development and test environments by changing production data with realistic data (transformation is done by using masking rules).

Oracle Data Redaction...

Creating a redaction policy when using full redaction

In this recipe, you will create a redaction policy on the income_level column (on the income_level column on the CUSTOMERS table in the sample schema OE), find the default values (for full redaction) for different data types, and change the default value for the varchar2 data type.

Note

You may consider data about customer address to be sensitive. Unfortunately, you can't create a redaction policy on the CUST_ADDRESS column (the table CUSTOMERS in the sample schema OE) because its data type is not supported (its data type is TYPE, which is not a literal value, so it can't be redacted). If you try to create a redaction policy, you will receive the following error: ORA-28073. The column CUST_ADDRESS has an unsupported data type.

Getting ready

To complete this recipe, you'll need the following:

• An existing user who can view data in OE.CUSTOMERS sample table but doesn't have exempt redaction policy privilege (for example, oe)

• To connect as a SYS...

Creating a redaction policy when using partial redaction

In this recipe, you will implement partial redaction on columns of two different types: Number and Varchar2. Partial redaction means that only part (hence the name partial) of the data in a specified column will be masked (redacted), whereas the other part of the data will be visible to the user - for instance, the first 12 digits of credit card number will be redacted, whereas other 4 digits will be visible.

How to do it...

1. Log in to database as a user who has a DBA role (for instance, zoran):

   $ sqlplus zoran/oracle
   

2. Create a test table and insert some data in it:

   SQL> create table tbl (a number);
   
   SQL> insert into tbl values (123456);
   
   SQL> insert into tbl values (234567);
   
   SQL> insert into tbl values (345678);
   
   SQL> commit;

3. Create role (that is going to be used in redaction policy) and user usr1 as the first test user:

   SQL> create role myrole;
   
   
   SQL> create user usr1 identified by oracle1;
   
   
   SQL...

Creating a redaction policy when using random redaction

Random redaction type is usually used for the number and date-time data types because for these data types, it is hard to make a distinction between the redacted (random) and real data. In this recipe, you will create redaction policy EMP_POL using random redaction type on hr.employees table, column salary, by using SQL*Plus. In the Changing redaction policy recipe, you will modify the EMP_POL redaction policy.

Getting ready

To complete this recipe, you'll need:

• An existing user who can view data in the HR.EMPLOYEES sample table but doesn't have an exempt redaction policy privilege (for example, hr)

• The secmgr user created in the Creating a redaction policy using full redaction recipe or another user who can create redaction policies (has execute on the dbms_redact package)

How to do it...

1. Connect to the database as a user who has the SELECT privilege on the HR.EMPLOYEES table or the SELECT ANY TABLE privilege (for example, hr user):

   $ sqlplus...

Creating a redaction policy when using regular expression redaction

A regular expression redaction type enables you to create and implement flexible redaction rules. You define patterns that will be used in order to match and replace data, as well as some other parameters of the search. In this recipe, you will create the redaction policy SHORT_POL, which will be used to mask customers' phone numbers.

Getting ready

To complete this recipe, you'll need:

• An existing user who can view data in the SH.CUSTOMERS sample table but doesn't have an exempt redaction policy privilege (for example, sh)

• The secmgr user you created in the Creating redaction policy using full redaction recipe or another user who can create redaction policies (has execute on dbms_redact package)

How to do it...

1. Connect to the database as a user who has the SELECT privilege on the SH.CUSTOMERS table or the SELECT ANY TABLE privilege (for example, the sh user):

   $ sqlplus sh
   

2. Verify that the user (for example, the user sh) can view...

Using Oracle Enterprise Manager Cloud Control 12c to manage redaction policies

In this recipe, you will perform several tasks with Data Redaction policies using Oracle Enterprise Manager Cloud Control 12c, including creation, modification, and deletion. Many tasks from other recipes, described in this chapter, can be done very easily using Enterprise Manager.

Getting ready

To complete this recipe, you need Enterprise Manager Cloud Control 12c and HR sample schema in the database.

How to do it...

1. Log in to Oracle Enterprise Manager Cloud Control at https://hostname:port/em.

2. Go to a Database home page (if it is a container database, you should go to a home page of PDB that contains sample schemas).

3. On menu, select Security | Data Redaction (see Figure 16).

   

   Figure 16 - Select Data Redaction

4. On the Data Redaction screen, select Create (Figure 17).

   

   Figure 17 - Creating a redaction policy

5. Set Schema as HR and the table as  EMPLOYEES . Enter SAL_POLICY as a policy name. Click on the Add button, to add column...

Changing the function parameters for a specified column

There are several ways in which you can change an existing redaction policy. In this recipe and the next one, you will:

• Change the function parameters for a specified column (the a_tbl_partial policy, which you created in the recipe Creating a redaction policy when using partial redaction)

• Add a column (commission_pct in the hr.employees table) to the redaction policy EMP_POL (you defined it in the Creating a redaction policy when using random redaction recipe)

Also, it is possible to remove column from the redaction policy, alter the policy expression, and modify the type of redaction for a specified column.

You concluded that the  a_tbl_partial redaction policy doesn't satisfy the requirements for your application anymore because it redacts first four digits with 0 and leading zeros are not displayed in the application. You decide to alter the a_tbl_partial policy. You want all digits to be displayed and to have them redacted with some...

Add a column to the redaction policy

You have to modify the existing redaction policy in order to redact more than one column in the table. In the table HR.EMPLOYEES, besides the column SALARY, you want to redact the column COMMISSION_PCT. You will modify the redaction policy EMP_POL. You decide that you want to use full redaction type for the column COMMISSION_PCT.

Note

Note that in the same redaction policy (in this case, EMP_POL) the different "protected" columns can use different redaction types (in this case, random and full redaction).

Getting ready

Before doing this recipe, you should have completed the Creating redaction policy when using random redaction recipe. You will use the secmgr user you created in the Creating redaction policy when using full redaction recipe.

How to do it...

1. Connect to the database as the secmgr user and alter the EMP_POL policy:

   $ sqlplus secmgr
   SQL> BEGIN
     2  DBMS_REDACT.ALTER_POLICY(
     3  object_schema   => 'HR',
     4  object_name     => 'EMPLOYEES...

Enabling, disabling, and dropping redaction policy

In this recipe, you will perform the three basic tasks: enabling, disabling, and dropping the same redaction policy (CUST_POL), which you defined in the Creating a redaction policy when using full redaction recipe using SQL*Plus. Also, you will check which redaction policies exist in the database and whether they are enforced (enabled).

To minimize dependence on the previous recipes in this chapter, a result shown after querying data dictionary view is equivalent to the one you would get if you completed only the Creating a redaction policy when using full redaction recipe before starting to do this recipe. The only difference you may see in the result is the number of existing redaction policies in the database.

Getting ready

Before doing this recipe, you should have completed the Creating a redaction policy when using full redaction recipe.

How to do it...

To complete the tasks, you will use procedures in the dbms_redact package (disable_policy...

Exempting users from data redaction policies

In this recipe, you will create a user and then exempt that user from Data Redaction. This user will be exempted from all redaction policies in the database.

Getting ready

Before doing this recipe, you should have completed the Creating a redaction policy when using the partial redaction recipe.

How to do it...

1. Connect to the database as a user who has a DBA role (for example, user zoran):

   $ sqlplus zoran/oracle
   

2. Create a new user (for example, vipuser) and grant him the create session privilege and select privilege on table customers in schema zoran:

   SQL> create user vipuser identified by oracle;
   
   
   SQL> grant create session to vipuser;
   
   
   SQL> grant select on zoran.customers to vipuser;
   

3. Connect as a newly created user and try to select from the zoran.customers table:

   SQL> connect vipuser/oracle
   
   
   SQL> select * from zoran.customers;
   
   
   NAME             CREDIT_CARD
   ---------------- --------------...