Transparent sensitive data protection is a way to find and classify table columns that hold sensitive information.

This feature enables you to quickly find the table columns in a database that hold sensitive data, classify this data, and then create a policy that protects this data as a whole for a given class. Examples of this type of sensitive data are credit card numbers or Social Security numbers.

The TSDP policy then protects the sensitive data in these table columns by using either Oracle Data Redaction or Oracle Virtual Private Database settings. The TSDP policy applies at the column level of the table that you want to protect, targeting a specific column data type, such as all NUMBER data types of columns that contain credit card information. You can create a uniform TSDP policy for all of the data that you classify, and then modify this policy as necessary, as compliance regulations change. Optionally, you can export the TSDP policies for use in other databases.

The benefits of TSDP policies are enormous: You easily can create and apply TSDP policies throughout a large organization with numerous databases. This helps auditors greatly by enabling them to estimate the protection for the data that the TSDP policies target. TSDP is particularly useful for government environments, in which you may have a lot of data with similar security restrictions and you must apply a policy to all of this data consistently. The policy could be to redact it, encrypt it, control access to it, audit access to it, and mask it in the audit trail. Without TSDP, you would have to configure every redaction policy, column-level encryption configuration, and Virtual Private Database policy column by column.

To use TSDP with Oracle Data Redaction, you must follow a set of general steps.

1. Create a sensitive type to classify the types of columns that you want to protect.

   For example, you can create a sensitive type for classify all Social Security numbers or credit card numbers. To create the sensitive type, either use the DBMS_TSDP_MANAGE.ADD_SENSITIVE_TYPE PL/SQL procedure or use an Enterprise Manager Cloud Control Application Data Model. To add multiple sensitive types in one operation from an Application Data Model, you can use the DBMS_TSDP_MANAGE.IMPORT_SENSITIVE_TYPES procedure.

2. Identify a list of sensitive columns that are associated with the sensitive types.

   To determine and generate this list, you can use either of the following methods:

   • The DBMS_TSDP_MANAGE.ADD_SENSITIVE_COLUMN procedure individually identifies sensitive columns.

   • An Oracle Enterprise Manager Cloud Control Application Data Model enables you to identify a group of sensitive columns. It then prepares this list of sensitive columns in XML format, which you then import into your database.

3. If you used an Application Data Model for Step 2, then import the list of sensitive columns from the Application Data Model into your database by using the DBMS_TSDP_MANAGE.IMPORT_DISCOVERY_RESULT procedure.
4. Create the TSDP policy by using the DBMS_TSDP_PROTECT.ADD_POLICY procedure within an anonymous block that defines the Data Redaction or Virtual Private Database settings that you want to use.
5. Associate the TSDP policy with one or more sensitive types by using the DBMS_TSDP_PROTECT.ASSOCIATE_POLICY procedure.
6. Enable the TSDP policy protections by using the DBMS_TSDP_PROTECT.ENABLE_PROTECTION_SOURCE, DBMS_TSDP_PROTECT.ENABLE_PROTECTION_COLUMN, or the DBMS_TSDP_PROTECT.ENABLE_PROTECTION_TYPE procedure.
7. Optionally, export the TSDP policy to other databases by using Oracle Data Pump to perform a full database export. (You cannot individually export TSDP policies.)

Transparent sensitive data protection has several benefits.

These benefits are as follows:

• You configure the sensitive data protection once, and then deploy this protection as necessary. You can configure transparent sensitive data protection policies to designate how a class of data (for example, credit card columns) must be protected without actually having to specify the target data. In other words, when you create the transparent sensitive data protection policy, you do not need to include references to the actual target columns that you want to protect. The transparent sensitive data protection policy finds these target columns based on a list of sensitive columns in the database and the policy's associations with the specified sensitive types. This can be useful when you add more sensitive data to your databases after you have created the transparent sensitive data protection policies. After you create the policy, you can enable protection for the sensitive data in a single step (for example, enable protection based on the entire source database). The sensitive type of the new data and the sensitive type and policy associations determine how the sensitive data is protected. In this way, as new sensitive data is added, you do not need to configure its protection, as long as it meets the current transparent sensitive data protection policy's requirements.

• You can manage protection of multiple sensitive columns. You can enable or disable protection for multiple sensitive columns based on a suitable attribute (such as the source database of the identification, the sensitive type itself, or a specific schema, table, or column). This granularity provides a high level of control over data security. The design of this feature enables you to manage data security based on specific compliance needs for large data sets that fall under the purview of these compliance regulations. You can configure data security based on a specific category rather than for each individual column. For example, you can configure protection for credit card numbers or Social Security numbers, but you do not need to configure protection for each and every column in the database that contains this data.

• You can protect the sensitive columns identified using the Oracle Enterprise Manager Cloud Control Application Data Modeling (ADM) feature. You can use the Cloud Control ADM feature to create sensitive types and discover a list of sensitive columns. Then you can import this list of sensitive columns and their corresponding sensitive types into your database. From there, you can create and manage transparent sensitive data protection policies using this information.

To use transparent sensitive data protection, you must have the EXECUTE privilege for several PL/SQL packages.

These privileges are as follows:

• DBMS_TSDP_MANAGE, which enables you to import and manage sensitive columns and sensitive types into your database. The procedures in this package execute with invoker's rights. Typically, an application database administrator will be granted privileges for this package.

• DBMS_TSDP_PROTECT, which you use to create the TSDP policy. The procedures in this package execute with invoker's rights. Typically, a security database administrator will be granted privileges for this package.

• DBMS_REDACT, if you plan to create Data Redaction policies. Typically, a security database administrator will be granted privileges for this package.

• DBMS_RLS, if you plan to incorporate Oracle Virtual Private Database functionality into your TSDP policies. Typically, a security database administrator will be granted privileges for this package.

For better separation of duty, these packages are designed so that either an application database administrator has control over one area of the TSDP policy creation (as in the case of the DBMS_TSDP_MANAGE package) or a security database administrator (for the DBMS_TSDP_PROTECT, DBMS_REDACT, and DBMS_RLS packages).

You can apply Transparent Sensitive Data Protection policies to the current PDB or current application PDB only.

If you are using Enterprise Manager Cloud Control Application Data Model, then you can find sensitive columns that belong to both local and common application objects (that is, common objects that are visible and accessible in the current PDB) inside the PDB. This enables you to use a TSDP policy to protect both local objects to the PDB and common objects that are accessible from the PDB.

In an application root:

• For application containers in general:

  • When you create scripts for application install, upgrade, patch, or uninstall operations, you can include SQL statements within the ALTER PLUGGABLE DATABASE app_name BEGIN INSTALL and ALTER PLUGGABLE DATABASE app_name END INSTALL blocks to perform various operations. If you include TSDP statements within these blocks, then the TSDP statements will fail. You can, however, include TSDP statements outside these blocks in the script.

• In the application root:

  • You can perform TSDP operations in both application common objects and application root local objects.

  • A TSDP policy that is defined in the application root container behaves as if it is a local policy to the application root. That is, the policy is effective only in the application root container.

In an application PDB:

• The security policies that protect an application PDB apply to TSDP operations that are performed on local application objects.

• The security policies that protect an application PDB apply to TSDP operations that are performed on application common objects that are accessed from the PDB. However, access to the application common object outside the application PDB is not governed by the security policy that protects the application PDB.

You can find a listing of TSDP policies and the security features that are associated with them by querying the DBA_TSDP_POLICY_FEATURE data dictionary views. To find all PDBs, query the DBA_PDBS view.

You must create a sensitive type, find the sensitive columns to be protected, and then import these columns from ADM into your database.

• Step 1: Create a Sensitive Type
  The sensitive type is a class of data that you designate as sensitive.
• Step 2: Identify the Sensitive Columns to Protect
  After you define a sensitive column, you are ready to identify the columns to protect.
• Step 3: Import the Sensitive Columns List from ADM into Your Database
  Next, you are ready to import the sensitive columns list from ADM into your database.
• Step 4: Create the Transparent Sensitive Data Protection Policy
  After you have created the list of sensitive columns and imported this list into your database, you can create the transparent sensitive data protection policy.
• Step 5: Associate the Policy with a Sensitive Type
  The DBMS_TSDP_PROTECT.ASSOCIATE_POLICY procedure associates a TSDP policy with a sensitive type.
• Step 6: Enable the Transparent Sensitive Data Protection Policy
  You can enable the TSDP policy for the current database in a protected source, a specific table column, or a specific column type.
• Step 7: Optionally, Export the Policy to Other Databases
  You can export or import the policy to or from another database.

The DBMS_TSDP_PROTECT.ALTER_POLICY procedure can alter a TSDP policy.

• To alter a transparent sensitive data protection policy, use the DBMS_TSDP_PROTECT.ALTER_POLICY procedure.
  For example, to alter an existing transparent sensitive data protection policy:

  DECLARE
    redact_feature_options SYS.DBMS_TSDP_PROTECT.FEATURE_OPTIONS;
    policy_conditions SYS.DBMS_TSDP_PROTECT.POLICY_CONDITIONS;
    BEGIN
    redact_feature_options ('expression') := 
     'SYS_CONTEXT(''USERENV'',''SESSION_ USER'') =''APPUSER''';
    redact_feature_options ('function_type') := 'DBMS_REDACT.PARTIAL';
    redact_feature_options ('function_parameters') := '9,1,6';
    policy_conditions(DBMS_TSDP_PROTECT.DATATYPE) := 'NUMBER';
    policy_conditions(DBMS_TSDP_PROTECT.LENGTH) := '22';
   DBMS_TSDP_PROTECT.ALTER_POLICY ('redact_partial_cc',  
    redact_feature_options, policy_conditions);
  END;
  /

  In this example:

  • redact_feature_options SYS.DBMS_TSDP_PROTECT.FEATURE_OPTIONS creates the variable redact_feature_options, which uses the FEATURE_OPTIONS data type.

  • policy_conditions SYS.DBMS_TSDP_PROTECT.POLICY_CONDITIONS creates the variable policy_conditions, which uses the POLICY_CONDITIONS data type.

  • redact_feature_options ... redact_feature_options writes the Data Redaction policy settings to the redact_feature_option variable. This example applies the Data Redaction policy to the user APPUSER, defines the policy as a partial data redaction for number data types.

  • policy_conditions ... policy_conditions writes the TSDP policy conditions to the policy_conditions variable (that is, the data type and length) for the protected NUMBER data type column.

  • DBMS_TSDP_PROTECT.ALTER_POLICY ... executes the DBMS_TSDP_PROTECT.ALTER_POLICY procedure, which alters the redact_partial_cc TSDP policy to use the definitions set in the redact_feature_options and policy_conditions variables.

The DBMS_TSDP_PROTECT.DISABLE_PROTECTION_COLUMN procedure disables one or all TSDP policies.

1. Query the DBA_TSDP_POLICY_PROTECTION data dictionary view to find the protected columns and their associated transparent sensitive data protection policies.

   For example:

   SELECT COLUMN_NAME, TSDP_POLICY FROM DBA_TSDP_POLICY_PROTECTION WHERE TABLE_NAME = 'CUST_CC';
   
   COLUMN_NAME   TSDP_POLICY
   ------------  ------------------
   CREDIT_CARD   redact_partial_cc
   

2. Run the DBMS_TSDP_PROTECT.DISABLE_PROTECTION_COLUMN procedure.

   For example, to disable the redact_partial_cc policy on the CREDIT_CARD column of the CUST_CC table:

   BEGIN
    DBMS_TSDP_PROTECT.DISABLE_PROTECTION_COLUMN(
     schema_name          => 'OE',
     table_name           => 'CUST_CC',
     column_name          => 'CREDIT_CARD',
     policy               => 'redact_partial_cc');
   END;
   /
   

   You can use the % wildcard in this procedure to specify multiple items. For example, to disable protection for any columns that begin with CREDIT, you could enter the following:

   BEGIN
    DBMS_TSDP_PROTECT.DISABLE_PROTECTION_COLUMN(
     schema_name          => 'OE',
     table_name           => 'CUST_CC',
     column_name          => 'CREDIT%',
     policy               => 'redact_partial_cc');
   END;
   /
   

   To disable all transparent sensitive data protection policies for a table, you can omit the policy parameter. For example:

   BEGIN
    DBMS_TSDP_PROTECT.DISABLE_PROTECTION_COLUMN(
     schema_name          => 'OE',
     table_name           => 'CUST_CC',
     column_name          => '%');
   END;
   /

You can drop an entire TSDP policy or a condition-enable-options combination from the policy.

1. Query the POLICY_NAME column of the DBA_TSDP_POLICY_FEATURE data dictionary view to find the policy that you want to drop.

   SELECT POLICY_NAME FROM DBA_TSDP_POLICY_FEATURE;
   
   POLICY_NAME
   -----------------
   redact_partial_cc
   

   Remember that you must be granted the SELECT_CATALOG_ROLE role to query the transparent sensitive data protection data dictionary views.

2. Find the sensitive column that is associated with this policy.

   For example:

   SELECT COLUMN_NAME FROM DBA_TSDP_POLICY_PROTECTION WHERE TSDP_POLICY = 'redact_partial_cc';
   
   COLUMN_NAME
   -----------------
   CREDIT_CARD
   

3. Drop this sensitive column.

   For example:

   BEGIN
    DBMS_TSDP_MANAGE.DROP_SENSITIVE_COLUMN (
      schema_name        => 'OE',
      table_name         => 'CUST_CC',
      column_name        => 'CREDIT_CARD');
   END;
   /
   

4. Find the sensitive type that is associated with this policy.

   For example:

   SELECT SENSITIVE_TYPE FROM DBA_TSDP_POLICY_TYPE WHERE POLICY_NAME = 'redact_partial_cc'; 
   
   SENSITIVE_TYPE
   --------------------
   credit_card_num_type
   

5. Drop this sensitive type.

   For example:

   BEGIN
    DBMS_TSDP_MANAGE.DROP_SENSITIVE_TYPE (   sensitive_type     => 'credit_card_num_type');END;
   /
   

6. Run the DBMS_TSDP_PROTECT.DROP_POLICY procedure to drop the policy.

   For example, to completely drop the policy:

   BEGIN
    DBMS_TSDP_PROTECT.DROP_POLICY(
      policy_name     => 'redact_partial_cc');
   END;
   /
   

   To drop the default condition-enable options combination from the policy:

   DECLARE
      policy_conditions DBMS_TSDP_PROTECT.POLICY_CONDITIONS;
   BEGIN
      DBMS_TSDP_PROTECT.DROP_POLICY ('redact_partial_cc', policy_conditions);
   END;
   /
   

   To drop the default condition-enable options combination from the policy based on a specific condition:

   DECLARE
      policy_conditions DBMS_TSDP_PROTECT.POLICY_CONDITIONS;
   BEGIN
      policy_conditions (DBMS_TSDP_PROTECT.DATATYPE) := 'NUMBER';
      DBMS_TSDP_PROTECT.DROP_POLICY ('redact_partial_cc', policy_conditions);
   END;
   /

The predefined REDACT_AUDIT policy masks bind values, which can appear in trace files when an event is set.

• About the REDACT_AUDIT Policy
  The predefined REDACT_AUDIT transparent sensitive data protection policy masks bind values.
• Variables Associated with Sensitive Columns
  Bind variables affect the use of sensitive columns with conditions, SELECT items, and INSERT or UPDATE operations.
• How Bind Variables on Sensitive Columns Behave with Views
  A bind variable that appears in a query on a view is considered sensitive if the view column references a sensitive column.
• Disabling the REDACT_AUDIT Policy
  By default, the REDACT_AUDIT policy is enabled for all sensitive columns.
• Enabling the REDACT_AUDIT Policy
  You can enable the REDACT_AUDIT policy for a specific sensitive column or for all columns in the database.

Oracle Data Redaction features work with transparent sensitive data protection policies.

The Data Redaction function types, function parameters, and expressions can be used in the TSDP policy definition. For example, you can set the enable the TSDP policy to use FULL or PARTIAL data redaction. This chapter uses Data Redaction for examples of managing TSDP policies.

You can combine protections from TSDP and Oracle Virtual Private Database into one policy.

• About Using TSDP Policies with Oracle Virtual Private Database Policies
  To incorporate Oracle Virtual Private Database protection with transparent sensitive data protection policies, you must use the DBMS_TSDP_PROTECT and DBMS_RLS packages.
• DBMS_RLS.ADD_POLICY Parameters That Are Used for TSDP Policies
  Oracle Database provides a set of parameters for fine-tuning the behavior of TSDP policies.
• Tutorial: Creating a TSDP Policy That Uses Virtual Private Database Protection
  This tutorial demonstrates how to incorporate Oracle Virtual Private Database protection with a transparent sensitive data protection policy.

Oracle Database provides data dictionary views that list information about transparent sensitive data protection policies.