12c: Transparent Sensitive Data Protection

TSDP is the another new security feature of Oracle 12c , basically a complement to Data Redaction and Virtual Private Database. It allows to categorize sensitive columns across database and then to set a policy to each defined category of columns for example telephone number columns' policy to redact into a random number.

The packages DBMS_TSDP_MANAGE and DBMS_ TSDP_PROTECT are used to manage the categories and policies.

Example:

1- Create a Sensitive Type
The sensitive type is a class of data that you designate as sensitive. For example, you can create a tel_num_type sensitive type for all telephone numbers.

C:\Users\ibukhary>sqlplus /@pdb2 as sysdba

SQL> BEGIN

  2   DBMS_TSDP_MANAGE.ADD_SENSITIVE_TYPE (

  3    sensitive_type  => 'tel_num_type', --case sensitive

  4    user_comment    => 'Type for telephone Num columns using a number data type');

  5  END;

  6  /

PL/SQL procedure successfully completed.

You can find existing sensitive types by querying theDBA_SENSITIVE_COLUMN_TYPES data dictionary view

select * from DBA_SENSITIVE_COLUMN_TYPES

2- Add sensitive columns to the type

-- Create Test table with test data

SQL> CREATE TABLE RD.TSDP_TEST(ID NUMBER,TELNUM1 NUMBER,TELNUM2 NUMBER);

Table created.

SQL> insert into tsdp_test values(1,540033123,560033123);

1 row created.

SQL> insert into tsdp_test values(1,550033123,570033123);

1 row created.

SQL> commit;

Commit complete.

Execute the DBMS_TSDP_MANAGE.ADD_SENSITIVE_COLUMN procedure to associate the sensitive type with a table column.

SQL> BEGIN
2 DBMS_TSDP_MANAGE.ADD_SENSITIVE_COLUMN(
3 schema_name => 'RD',
4 table_name => 'TSDP_TEST',
5 column_name => 'TELNUM1',
6 sensitive_type => 'tel_num_type',
7 user_comment => 'Sensitive column addition of tel_num_type');
8 END;
9 /

PL/SQL procedure successfully completed.

3- Create the Transparent Sensitive Data Protection Policy

To create the transparent sensitive data protection policy, you must configure it for the Virtual Private Database or Oracle Data Redaction settings that you want to use, and then apply these settings to a transparent sensitive data protection policy defined by theDBMS_TSDP_PROTECT.ADD_POLICY procedure. I'll be using a data redaction policy.

To create the policy, use the DBMS_TSDP_PROTECT.ADD_POLICY procedure

SQL> DECLARE

  2    redact_feature_options DBMS_TSDP_PROTECT.FEATURE_OPTIONS;

  3    policy_conditions DBMS_TSDP_PROTECT.POLICY_CONDITIONS;

  4  BEGIN

  5    redact_feature_options ('expression') :=    'SYS_CONTEXT(''USERENV'',''CLIENT_INFO'') =''RED'''; -- Wri

tes the Data Redaction policy settings

  6    redact_feature_options ('function_type') := 'DBMS_REDACT.PARTIAL';

  7    redact_feature_options ('function_parameters') := '0,1,6';

  8    policy_conditions(DBMS_TSDP_PROTECT.DATATYPE) := 'NUMBER';

  9    --policy_conditions(DBMS_TSDP_PROTECT.LENGTH) := '16';

 10   DBMS_TSDP_PROTECT.ADD_POLICY ('redact_partial_telnum', DBMS_TSDP_PROTECT.REDACT,  redact_feature_options

, policy_conditions); -- creates the redact_partial_telnum TSDP policy

 11  END;

 12  /

PL/SQL procedure successfully completed.

4- Associate the Policy with a Sensitive Type

After you have created the transparent sensitive data protection policy, use the DBMS_TSDP_PROTECT.ASSOCIATE_POLICY procedure to associate the policy with a sensitive type.

Find the sensitive type that you want to use.

SQL> SELECT NAME FROM DBA_SENSITIVE_COLUMN_TYPES ORDER BY NAME;

NAME
--------------------------------------------------------------------
tel_num_type

Run the DBMS_TSDP_PROTECT.ASSOCIATE_POLICY procedure to associate the policy with a sensitive column type.

SQL> BEGIN

  2   DBMS_TSDP_PROTECT.ASSOCIATE_POLICY(

  3   policy_name        => 'redact_partial_telnum',

  4   sensitive_type     => 'tel_num_type',

  5   associate          => true);

  6  END;

  7  /

PL/SQL procedure successfully completed.

Check tel_num_type is now associated with the redact_partial_telnum policy.

SQL> SELECT POLICY_NAME, SENSITIVE_TYPE FROM DBA_TSDP_POLICY_TYPE ORDER BY SENSITIVE_TYPE;

POLICY_NAME

--------------------------------------------------------------------------------

SENSITIVE_TYPE

--------------------------------------------------------------------------------

REDACT_AUDIT

tel_num_type

redact_partial_telnum

tel_num_type

5- Enable the Transparent Sensitive Data Protection Policy

You can enable the transparent sensitive data protection policy for the current database in a protected source, for a specific table column, or for a specific column type. If you enable transparent sensitive data protection for a specific table column, you can enable only one policy per table.

-- Enabling TSDP Protection for a Table Column

SQL> BEGIN

  2   DBMS_TSDP_PROTECT.ENABLE_PROTECTION_COLUMN(

  3    schema_name          => 'RD',

  4    table_name           => 'TSDP_TEST',

  5    column_name          => 'TELNUM1',

  6    policy               => 'redact_partial_telnum');

  7  END;

  8  /

PL/SQL procedure successfully completed.

-- Enabling TSDP Protection for a Column Type

BEGIN

 DBMS_TSDP_PROTECT.ENABLE_PROTECTION_TYPE(

  sensitive_type           => 'tel_num_type');

END;

If an ORA-45622: warnings generated during policy enforcement error appears, then check the configuration of the policy.

6- Test Transparent Sensitive Data Protection

SQL> conn rd/rd@pdb2

SQL> select * from tsdp_test;

        ID    TELNUM1    TELNUM2

---------- ---------- ----------

         1  540033123  560033123

         1  550033123  570033123

SQL> exec dbms_application_info.set_client_info('RED');

PL/SQL procedure successfully completed.

SQL>  select * from tsdp_test;

        ID    TELNUM1    TELNUM2

---------- ---------- ----------

         1        123  560033123

         1        123  570033123

-- Add the other tel column 

C:\Users\ibukhary>sqlplus /@pdb2 as sysdba

SQL> BEGIN

  2   DBMS_TSDP_MANAGE.ADD_SENSITIVE_COLUMN(

  3   schema_name        => 'RD',

  4   table_name         => 'TSDP_TEST',

  5   column_name        => 'TELNUM2',

  6   sensitive_type     => 'tel_num_type',

  7   user_comment       => 'Sensitive column addition of tel_num_type');

  8  END;

  9  /

SQL> BEGIN

  2   DBMS_TSDP_PROTECT.ENABLE_PROTECTION_COLUMN(

  3    schema_name          => 'RD',

  4    table_name           => 'TSDP_TEST',

  5    column_name          => 'TELNUM2',

  6    policy               => 'redact_partial_telnum');

  7  END;

  8  /

PL/SQL procedure successfully completed.

SQL> conn rd/rd@pdb2

SQL> select * from tsdp_test;

        ID    TELNUM1    TELNUM2

---------- ---------- ----------

         1        123        123

         1        123        123

7- Other maintenance Tasks

-- Altering a Transparent Sensitive Data Protection Policy

SQL> DECLARE
2 redact_feature_options DBMS_TSDP_PROTECT.FEATURE_OPTIONS;
3 policy_conditions DBMS_TSDP_PROTECT.POLICY_CONDITIONS;
4 BEGIN
5 redact_feature_options ('expression') := 'SYS_CONTEXT(''USERENV'',''CLIENT_INFO'') =''RED'''; -- Wri
tes the Data Redaction policy settings
6 redact_feature_options ('function_type') := 'DBMS_REDACT.PARTIAL';
7 redact_feature_options ('function_parameters') := '0,1,6';
8 policy_conditions(DBMS_TSDP_PROTECT.DATATYPE) := 'NUMBER';
9 policy_conditions(DBMS_TSDP_PROTECT.LENGTH) := '38';
10 DBMS_TSDP_PROTECT.ALTER_POLICY ('redact_partial_telnum', redact_feature_options, policy_conditions); --
Alters the redact_partial_telnum TSDP policy
11 END;
12 /

-- Disabling Transparent Sensitive Data Protection Policies
Find the protected columns and their associated transparent sensitive data protection policies.
SQL> SELECT COLUMN_NAME, TSDP_POLICY FROM DBA_TSDP_POLICY_PROTECTION WHERE TABLE_NAME = 'TSDP_TEST';

SQL> BEGIN

  2   DBMS_TSDP_PROTECT.DISABLE_PROTECTION_COLUMN(

  3    schema_name          => 'RD',

  4    table_name           => 'TSDP_TEST',

  5    column_name          => 'TELNUM2',-- You can use the % wildcard in this procedure to specify multiple items.

  6    policy               => 'redact_partial_telnum');-- To disable all transparent sensitive data protection policies for a table, you can omit the policy parameter.

  7  END;

  8  /

PL/SQL procedure successfully completed.

Query the table to verify it is disabled or not

SQL>  select * from tsdp_test;

        ID    TELNUM1    TELNUM2

---------- ---------- ----------

         1        123  560033123

         1        123  570033123

-- Dropping Transparent Sensitive Data Protection Policies

Find the policy that you want to drop

SQL> SELECT POLICY_NAME FROM DBA_TSDP_POLICY_FEATURE; -- SELECT_CATALOG_ROLE role required for this view

POLICY_NAME

--------------------------------------------------------------------------------

REDACT_AUDIT

redact_partial_telnum

Find the sensitive column that is associated with this policy.

SQL> SELECT COLUMN_NAME FROM DBA_TSDP_POLICY_PROTECTION WHERE TSDP_POLICY = 'redact_partial_telnum';

COLUMN_NAME

--------------------------------------------------------------------

TELNUM1

Drop this sensitive column.

BEGIN

 DBMS_TSDP_MANAGE.DROP_SENSITIVE_COLUMN (

   schema_name        => 'RD',

   table_name         => 'TSDP_TEST',

   column_name        => 'TELNUM1');

END;

Find the sensitive type that is associated with this policy.

SQL> SELECT SENSITIVE_TYPE FROM DBA_TSDP_POLICY_TYPE WHERE POLICY_NAME = 'redact_partial_telnum';

SENSITIVE_TYPE

--------------------------------------------------------------------------------

tel_num_type

Drop this sensitive type.

BEGIN

 DBMS_TSDP_MANAGE.DROP_SENSITIVE_TYPE (   sensitive_type     => 'tel_num_type');

END;

/

Drop the policy.

BEGIN

 DBMS_TSDP_PROTECT.DROP_POLICY(

   policy_name     => 'redact_partial_telnum');

END;

/

Note:

If you want to export or import the policy to or from another database, then you must use Oracle Data Pump to perform a full export or import of the database that contains the policy.

Remember that the export and import operations apply to the entire database, not just the transparent sensitive data protection policy.