Chapter 6. Transparent Sensitive Data Protection

In this chapter, we will cover the following tasks:

• Creating a sensitive type

• Determining sensitive columns

• Creating transparent sensitive data protection policy

• Associating transparent sensitive data protection policy with sensitive type

• Enabling, disabling, and dropping policy

• Altering transparent sensitive data protection policy

Introduction

Oracle Transparent Sensitive Data Protection (TSDP) is a new security feature, introduced in Oracle Database 12c (available only in Enterprise Edition). TSDP provides a way to create classes of sensitive data and enables more centralized control of how sensitive data is protected. In database versions 12.1.0.1 and 12.1.0.2, it leverages two Oracle security mechanisms:

• Oracle Virtual Private Database (VPD), described in Chapter 4 , Virtual Private Database

• Oracle Data Redaction, explained in Chapter 5 , Data Redaction

To implement TSDP, you should complete steps shown in Figure 1:

Figure 1 - Steps to implement TSDP

For all recipes in this chapter, we assume that the database is up and running and each user has at least a create session privilege. In this chapter, it is assumed that user c##zoran has a DBA role and it executes privileges on the following packages:

• DBMS_TSDP_MANAGE

• DBMS_TSDP_PROTECT

• DBMS_RLS

• DBMS_REDACT

Recipes are tested on Oracle Database 12.1.0.2 in multitenant and...

Creating a sensitive type

To create a sensitive type, you can use Oracle Enterprise Manager or a command-line interface. In this recipe, you'll use the command-line interface to execute a PL/SQL procedure. You decided that you want to protect e-mail addresses stored in your database, so first you are going to create sensitive type email_type.

Getting ready

To complete this recipe, you'll need an existing user who can create a sensitive type (for example, c##zoran).

How to do it...

1. Connect to the database (for example, pdb1) as a user who has appropriate privileges (for example, c##zoran):

   $ sqlplus c##zoran@pdb1
   

2. Create a sensitive type (for example, email_type):

   SQL> BEGIN 
    DBMS_TSDP_MANAGE.ADD_SENSITIVE_TYPE ( 
    sensitive_type => '<your_type>', 
    user_comment=> '<description>');
    END;
    /
   

   

   Figure 2 - Creating a sensitive type

How it works...

In step 2, you created a sensitive type (for example, email_type), which you can use to consistently mask (protect), in our case, e-mail...

Determining sensitive columns

After you decide which data is sensitive, you'll need to find all places where that data is stored. Once you do that, you'll classify the data (associate sensitive columns with sensitive types). In this recipe, you'll associate two sensitive columns (from two tables) with sensitive type you created in the previous recipe.

Getting ready

To complete this recipe, create a user challengezoran, create table T1, and insert several values into the table (see Figure 1) or use your own table. Also, you'll need an existing user who has an execute privilege on dbms_tsdp_manage package (for example, c##zoran).

Figure 4 - Creating table T1

How to do it...

1. Connect to the database (for example, pdb1) as a user who has appropriate privileges (for example, c##zoran user):

   $ sqlplus c##zoran@pdb1
   

2. Associate a sensitive column (for example, schema CHALLENGEZORAN, table T1, column EMAIL_ADDRESS) with sensitive type you created in the previous recipe (for example, email_type)

   

   Figure 5...

Creating transparent sensitive data protection policy

This step defines the way you want to protect sensitive data. You can use Data Redaction or VPD settings for your TSDP policy. In this recipe, you'll use regular expression redaction to protect previously defined sensitive data.

Getting ready

To complete this recipe, you'll need an existing user who has the execute privilege on the dbms_tsdp_protect package (for example, c##zoran).

How to do it...

1. Connect to the database (for example, pdb1) as a user who has appropriate privileges (for example, c##zoran user):

   $ sqlplus c##zoran@pdb1
   

2. Create TSDP policy using Data Redaction.

   

   Figure 7 - TSDP policy using Oracle Data Redaction

How it works...

In step 2, lines 2 and 3 define variables redact_features_options and policy_conditions. Data redaction settings, for TSPD policy, are defined by using redact_features_options variable that holds parameter-value pairs that correspond with the parameters in DBMS_REDACT.ADD_POLICY procedure (lines 4-8). Line...

Associating transparent sensitive data protection policy with sensitive type

In this recipe, you'll associate TSDP policy and sensitive type you created in the previous recipes.

Getting ready

To complete this recipe, you'll need an existing user who has the execute privilege on the dbms_tsdp_protect package (for example, c##zoran).

How to do it...

1. Connect to the database as a user (for example, pdb1) who has appropriate privileges (for example, c##zoran user):

   $ sqlplus c##zoran@pdb1
   

2. Associate TSDP policy with sensitive type:

   

There's more...

To verify that you successfully associated the TSDP policy and the sensitive type, execute the following query:

SQL> SELECT POLICY_NAME, SENSITIVE_TYPE FROM DBA_TSDP_POLICY_TYPE;

See also

• Creating a sensitive type

• Determining sensitive columns

• Creating transparent sensitive data protection policy

Enabling, disabling, and dropping policy

In this recipe, you'll learn to enable, disable, and drop transparent sensitive data protection policies.

Getting ready

To complete this recipe, you'll need two existing users-one to manage TSDP policies and the other to view sensitive data.

How to do it...

1. Connect to the database (for example, pdb1) as a user who has the SELECT privilege on the HR.EMPLOYEES table and the CHALLENGEZORAN.T1 table or the SELECT ANY TABLE privilege (for example, maja).

   $ sqlplus maja@pdb1
   

2. View sensitive data by executing the following two queries:

   SELECT EMAIL FROM HR.EMPLOYEES FETCH FIRST 10 ROWS ONLY;
   

   

   Figure 9 - Before enabling the policy

   SELECT EMAIL_ADDRESS FROM CHALLENGEZORAN.T1;
   

   

   Figure 10 - Before enabling the policy

3. Connect to the database (for example, pdb1) as a user who can manage TSDP policies (for example, c##zoran). Enable the TSDP policy:

   

4. Repeat step 2 as user maja.

   

   Figure 12 - Sensitive data is protected

5. Result of the second query is shown in Figure 13:

   

   Figure...

Altering transparent sensitive data protection policy

In this recipe, you'll alter policy you created in recipe Creating transparent sensitive data protection policy and enable it.

Getting ready

To complete this recipe, you'll need two existing users (for example, c##zoran and maja). Also, update the table hr.employees, as shown in Figure 20:

Figure 20 - Set new e-mail addresses in the hr.employees table

How to do it...

1. Connect to the database (for example, pdb1) as a user who can manage TSDP policies (for example, c##zoran):

   $ sqlplus c##zoran@pdb1
   

2. If the policy is enabled, disable it for all columns (for instructions how to disable the TSDP policy, see recipe Enabling, disabling, and dropping policy).

3. Connect to the database (for example, pdb1) as a user who can view sensitive data (for example, maja). Execute the following queries:

   SELECT EMAIL FROM HR.EMPLOYEES FETCH FIRST 10 ROWS ONLY;
   

   

   Figure 21 - Before altering and enabling the policy

          SELECT EMAIL_ADDRESS FROM CHALLENGEZORAN.T1;...