11 Using Transparent Sensitive Data Protection

About Creating 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 the DBMS_TSDP_PROTECT.ADD_POLICY procedure.

This section shows how to configure it for Oracle Data Redaction. (See "Tutorial: Creating a TSDP Policy That Uses Virtual Private Database Protection" for an example of how you can create a TSDP policy for Virtual Private Database.)

You can create the policy by defining an anonymous block that has the following components:

• If you are using Oracle Data Redaction for your policy, a specification of the type of Data Redaction that you want to use, such as partial Data Redaction

• If you are using Oracle Virtual Private Database for your policy, a specification of the VPD settings that you want to use

• Conditions to test when the policy is enabled. For example, the data type of the column which should be satisfied before the policy can be enabled.

• A named transparent sensitive data protection policy to tie these components together, by using the DBMS_TSDP_PROTECT.ADD_POLICY procedure

After you create the sensitive type, it resides in the SYS schema.

Creating the Transparent Sensitive Data Protection Policy

Example 11-3 shows how to create a transparent sensitive data protection policy that uses a partial number data type-based Data Redaction policy.

If you want to see an example of a similar policy for VPD, see "Step 4: Create and Enable a Transparent Sensitive Data Protection Policy". (You can copy and paste the following text by positioning the cursor at the start of DECLARE in the first line.)

• To create the policy, use the DBMS_TSDP_PROTECT.ADD_POLICY procedure, as shown in Example 11-3.

1
2
3
4
5
6
7
8
9
10
11
12
13
14

DECLARE
  redact_feature_options DBMS_TSDP_PROTECT.FEATURE_OPTIONS;
  policy_conditions 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') := '0,1,6';
  policy_conditions(DBMS_TSDP_PROTECT.DATATYPE) := 'NUMBER';
  policy_conditions(DBMS_TSDP_PROTECT.LENGTH) := '16';
 DBMS_TSDP_PROTECT.ADD_POLICY ('redact_partial_cc', DBMS_TSDP_PROTECT.REDACT,
  redact_feature_options, policy_conditions);
END;
/

In this example:

• Line 2: Creates the variable redact_feature_options, which uses the FEATURE_OPTIONS data type. See "Setting the Oracle Data Redaction or Virtual Private Database Feature Options" for more information.

• Line 3: Creates the variable policy_conditions, which uses the POLICY_CONDITIONS data type. See "Setting Conditions for the Transparent Sensitive Data Production Policy" for more information.

• Lines 5–8: Writes the Data Redaction policy settings to the redact_feature_option variable. This example applies the Data Redaction policy to the user APPUSER and defines the policy as a partial data redaction for number data types. See Oracle Database Advanced Security Guide for information about how the function_parameters parameter works for this case.

• Lines 9–10: Writes the TSDP policy conditions to the policy_conditions variable (that is, the data type and length) for the protected NUMBER data type column.

• Lines 11–12: Executes the DBMS_TSDP_PROTECT.ADD_POLICY procedure, which creates the redact_partial_cc TSDP policy. See "Specifying the DBMS_TSDP_PROTECT.ADD_POLICY Procedure" for more information.

Setting the Oracle Data Redaction or Virtual Private Database Feature Options

The TSDP feature options describe the Oracle Data Redaction or Virtual Private Database settings that you may want to use for the transparent sensitive data protection policy.

For Data Redaction, define the feature options by using the name redact_feature_options variable and for the type, you must use the type DBMS_TSDP_PROTECT.FEATURE_OPTIONS, which is an associative array of the data type VARCHAR2(TSDP_PARAM_MAX). Initialize these options with the parameter-value pairs that correspond with the DBMS_REDACT.ADD_POLICY parameters.

For example, to specify a TSDP policy that uses partial Data Redaction, Example 11-3 shows the following parameter-value pair:

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

For a partial Data Redaction policy that uses a number data type for the protected column, Example 11-3 specifies the following additional parameter-value pairs:

redact_feature_options ('expression')          := 'expression';
redact_feature_options ('function_parameters') := 'values';

See Oracle Database Advanced Security Guide for more information about Data Redaction policy creation parameters.

Similarly, for Virtual Private Database, you use the vpd_feature_options variable to define the VPD feature options. For example:

vpd_feature_options ('statement_types')   := 'SELECT, INSERT, UPDATE, DELETE';

See "DBMS_RLS.ADD_POLICY Parameters That Are Used for TSDP Policies" for more information about available VPD parameters.

Setting Conditions for the Transparent Sensitive Data Production Policy

Optionally, you can specify conditions for the transparent sensitive data protection policy.

However, if you choose to omit conditions, you still must include the following line in the DECLARE variables. (In this case, the default value for policy_conditions is an empty associative array.)

policy_conditions SYS.DBMS_TSDP_PROTECT.POLICY_CONDITIONS;

You define the conditions by using the name policy_conditions for the variable and for the type, you must use type DBMS_TSDP_PROTECT.POLICY_CONDITIONS, which is an associative array of the data type VARCHAR2(TSDP_PARAM_MAX). Ensure that no two conditions are satisfied by a single target sensitive column. The target column's properties should satisfy all the condition properties for the corresponding DBMS_TSDP_PROTECT.FEATURE_OPTIONS settings to be applied on the column.

Example 11-3 shows the policy conditions as follows:

policy_conditions(DBMS_TSDP_PROTECT.DATATYPE) := 'NUMBER';
policy_conditions(DBMS_TSDP_PROTECT.LENGTH)   := '16';

Optionally, you can specify one or more of the following keys for the POLICY_CONDITIONS settings:

• DBMS_TSDP_PROTECT.DATATYPE enables you to specify a data type.

• DBMS_TSDP_PROTECT.LENGTH enables you to specify a data type length for the DBMS_TSDP_PROTECT.DATATYPE key.

• DBMS_TSDP_PROTECT.PARENT_SCHEMA enables you to restrict the policy to a specific schema. If you omit this setting, then the policy applies to all schemas in the database.

• DBMS_TSDP_PROTECT.PARENT_TABLE enables you to restrict the policy to a table specified by the DBMS_TSDP_PROTECT.PARENT_SCHEMA key. If you omit this setting, then the policy applies to all tables within the specified schema.

Specifying the DBMS_TSDP_PROTECT.ADD_POLICY Procedure

The DBMS_TSDP_PROTECT.ADD_POLICY procedure names the transparent sensitive data protection policy and executes settings from the FEATURE_OPTIONS and POLICY_CONDITIONS settings.

In the policy, the redact_feature_options and the policy_conditions settings work together: When the policy is enabled (using any of the DBMS_TSDP_PROTECT.ENABLE_PROTECTION* procedures) on the target object, then the redact_feature_options settings apply only if the corresponding policy_condition settings are satisfied. You must enter the following parameters:

• policy_name creates a name for the TSDP policy. The name that you enter is stored in the database using the case sensitivity that you used when you created it. For example, if you had entered redact_partial_cc, then the database stores it as redact_partial_cc, not redact_partial_cc.

• security_feature refers to the security feature the TSDP policy will use. Enter DBMS_TSDP_PROTECT.REDACT to specify Oracle Data Redaction.

• policy_enable_options refers to the variable that you defined for the DBMS_TSDP_PROTECT.FEATURE_OPTIONS type.

• policy_apply_condition refers to the variable that you defined for the DBMS_TSDP_PROTECT.POLICY_CONDITIONS type.

Example 11-3 shows the policy set as follows:

DBMS_TSDP_PROTECT.ADD_POLICY('redact_partial_cc', DBMS_TSDP_PROTECT.REDACT, redact_feature_options, policy_conditions);

Enabling Protection for the Current Database in a Protected Source

You can enable transparent sensitive data protection for the current database in a protected source. If you must disable the protection, then you can run the DBMS_TSDP_PROTECT.DISABLE_PROTECTION_SOURCE procedure.

• Run the DBMS_TSDP_PROTECT.ENABLE_PROTECTION_SOURCE procedure to enable this type of protection.

Example 11-4 shows how to enable transparent sensitive data protection policies for the orders_db database.

BEGIN
 DBMS_TSDP_PROTECT.ENABLE_PROTECTION_SOURCE(
  discovery_source       => 'orders_db');
END;
/

Enabling Protection for a Specific Table Column

You can enable transparent sensitive data protection for a specific table column. Remember that you can enable only one policy per table. If you must disable the protection, then you can run the DBMS_TSDP_PROTECT.DISABLE_PROTECTION_COLUMN procedure.

• Run the DBMS_TSDP_PROTECT.ENABLE_PROTECTION_COLUMN procedure to enable this type of protection.

Example 11-5 shows how to enable the transparent sensitive data protection policy redact_partial_cc for a specific table column.

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

If an ORA-45622: warnings generated during policy enforcement error appears, then check the configuration of the policy. In this example, the redact_partial_cc policy is enabled on a column if this column is of the NUMBER data type and has a length of 16. Even though the OE.CUST_CC.CREDIT_CARD column is associated with the redact_partial_cc policy, the policy is not enabled if this column fails to satisfy the conditions (data type and length).

Enabling Protection for a Specific Column Type

You can enable transparent sensitive data protection for a specific column type, such as all columns that use the VARCHAR2 data type. If you must disable the protection, then you can run the DBMS_TSDP_PROTECT.DISABLE_PROTECTION_TYPE procedure.

• Run the DBMS_TSDP_PROTECT.ENABLE_PROTECTION_TYPE procedure to enable this type of protection.

Example 11-6 shows how to enable transparent sensitive data protection for all columns that use the credit_card_num_type sensitive type.

BEGIN
 DBMS_TSDP_PROTECT.ENABLE_PROTECTION_TYPE(
  sensitive_type           => 'credit_card_num_type');
END;
/

Bind Variables and Sensitive Columns in the Expressions of Conditions

In comparison conditions, a sensitive column and a bind variable appear in the expressions that are being compared.

The examples in this section illustrate how bind variables and sensitive columns work with condition expressions.

In the following comparison query, the bind value in VAR1 is masked because VAR1 and SALARY appear in the expression that is compared using the comparison condition >.

SELECT EMPLOYEE_ID FROM HR.EMPLOYEES WHERE SALARY > :VAR1;

In the next comparison query, the bind values in VAR1 and VAR2 are masked because VAR1, VAR2, and SALARY appear in expressions that use the comparison equality condition =.

SELECT EMPLOYEE_ID FROM HR.EMPLOYEES WHERE SALARY + :VAR1 = TO_NUMBER(:VAR2, '9G999D99');

For floating point conditions, the sensitive column and the bind variable appear in the expression that is evaluated. In the following example, the bind value in VAR1 is masked because VAR1 and SALARY appear in the expression for the IS NOT NAN condition.

SELECT COUNT( ) FROM HR.EMPLOYEES WHERE (SALARY * :VAR1) IS NOT NAN;

In pattern matching conditions, the sensitive column and the bind variable appear as arguments. In the following example, the bind value in VAR1 is masked because VAR1 and LAST_NAME are the arguments for the LIKE condition.

SELECT LAST_NAME FROM HR.EMPLOYEES WHERE LAST_NAME LIKE :VAR1;

For BETWEEN conditions, the sensitive column and the bind variable appear in the expressions that are arguments. In the following example, bind values in VAR1 and VAR2 are masked because VAR1, VAR2, and SALARY appear in expressions that are arguments to the BETWEEN condition.

SELECT EMPLOYEE_ID FROM HR.EMPLOYEES WHERE SALARY BETWEEN :VAR1 AND :VAR2;

In the next example, the sensitive column and the bind variable are the arguments of the IN condition. Here, the bind values in VAR1 and VAR2 are masked because VAR1, VAR2, and SALARY appear as arguments to the IN condition.

SELECT COUNT( ) FROM HR.EMPLOYEES WHERE SALARY IN ( :VAR1, :VAR2);

When a condition has a nested subquery as an argument, the bind variables and sensitive columns that appear in the nested subquery are not considered to be associated with the condition. For example, suppose HR.EMPLOYEES.SALARY is a sensitive column. In the following query, the SALARY column and the subquery are expressions for the greater-than condition >.

SELECT EMPLOYEE_ID FROM HR.EMPLOYEES WHERE SALARY > (SELECT SALARY FROM HR.EMPLOYEES WHERE MANAGER_ID = :VAR1);

However, variable VAR1 is associated with column MANAGER_ID as variable VAR1 and MANAGER_ID appears in expressions being compared using the condition =. Because MANAGER_ID is not a sensitive column, variable VAR1 is not considered sensitive. The variable VAR1 is not considered to be associated with the sensitive column SALARY.

In the case of the logical conditions, model conditions, multiset conditions, XML conditions, compound conditions, IS OF type conditions, and EXISTS conditions, there can be no cases where a bind variable and a sensitive column are associated with each other. This is due to the structure or the nature of these conditions.

A Bind Variable and a Sensitive Column Appearing in the Same SELECT Item

If a column in a SELECT item is sensitive, then all the binds in the SELECT item are considered sensitive.

For example, assume that HR.EMPLOYEES.SALARY and HR.EMPLOYEES.COMMISSION_PCT are sensitive columns. In the following query, the bind variable VAR1 is considered sensitive because it appears in the same SELECT item as the sensitive column SALARY, so its bind value is masked.

SELECT (SALARY * VAR1) AS BONUS AS FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = :VAR2;

In the next example, the bind variable VAR1 is considered sensitive because it appears in the same SELECT item as SALARY. VAR2 is considered sensitive because it appears in the same SELECT item as the sensitive column COMMISSION_PCT.

SELECT (SALARY * VAR1), (COMMISSION_PCT * :VAR2), (EMPNO + :VAR3) AS BONUS AS FROM PAYROLL.ACCOUNT;

Bind Variables in Expressions Assigned to Sensitive Columns in INSERT or UPDATE Operations

You can assign multiple bind variables to different columns in one INSERT or UPDATE statement.

Consider the following INSERT statement:

INSERT INTO PAYROLL.ACCOUNT (ACCOUNT_NUM, SALARY) VALUES (:VAR1 * :VAR2 , :VAR3); 

In this INSERT statement, the following takes place:

• The bind variables VAR1 and VAR2 appear in the expression (:VAR1 * :VAR2), which is assigned to the sensitive column ACCOUNT_NUM.

• The bind variable VAR3 is assigned to sensitive column SALARY.

Consider the following UPDATE statement:

UPDATE PAYROLL.ACCOUNT SET ACCOUNT_NUM = :VAR1, SALARY = :VAR2;

In this UPDATE statement, the following takes place:

• The bind variable VAR1 is assigned to sensitive column ACCOUNT_NUM.

• The bind variable VAR2 is assigned to sensitive column SALARY.

Step 1: Create the hr_appuser User Account

First, you must create a sample user account and then grant this user the appropriate privileges.

1. Log into the database instance as user SYS with the SYSDBA administrative privilege.

   sqlplus sys as sysdba
   Enter password: password
   

2. If you are using a multitenant environment, then connect to the appropriate pluggable database (PDB).

   For example:

   CONNECT SYS@hrpdb AS SYSDBA
   Enter password: password
   

   To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the show con_name command.

3. Create the following user accounts:

   GRANT CREATE SESSION TO hr_appuser IDENTIFIED BY password;
   GRANT CREATE SESSION TO tsdp_admin IDENTIFIED BY password;
   

   Replace password with a password that is secure. See "Minimum Requirements for Passwords" for more information.

4. Grant user tsdp_admin the following privileges:

   GRANT CREATE PROCEDURE TO tsdp_admin;
   GRANT EXECUTE ON DBMS_TSDP_MANAGE TO tsdp_admin;
   GRANT EXECUTE ON DBMS_TSDP_PROTECT TO tsdp_admin; 
   GRANT EXECUTE ON DBMS_RLS to tsdp_admin;
   

5. Connect as user SCOTT.

   CONNECT SCOTT -- Or, CONNECT SCOTT@hrpdb
   Enter password: password
   

6. Grant the hr_appuser the READ object privilege for the EMP table.

   GRANT READ ON EMP TO hr_appuser;
   

Step 2: Identify the Sensitive Columns

As the sample user tsdp_admin, you are ready to identify sensitive columns to protect.

1. Connect as user tsdp_admin.

   CONNECT tsdp_admin -- Or, CONNECT tsdb_admin@hrpdb
   Enter password: password
   

2. Create the salary_type sensitive type:

   BEGIN
    DBMS_TSDP_MANAGE.ADD_SENSITIVE_TYPE (
     sensitive_type  => 'salary_type',
     user_comment    => 'Type for SCOTT.EMP column');
   END;
   /
   

3. Associate the salary_type sensitive type with the SCOTT.EMP table.

   BEGIN
    DBMS_TSDP_MANAGE.ADD_SENSITIVE_COLUMN (
    schema_name        => 'SCOTT',
    table_name         => 'EMP',
    column_name        => 'SAL',
    sensitive_type     => 'salary_type',
    user_comment       => 'Sensitive column addition of SALARY_TYPE');
   END;
   /
   

Step 3: Create an Oracle Virtual Private Database Function

Next, you are ready to create the Oracle Virtual Private Database policy function that Transparent Sensitive Data Protection will associate with the VPD policy that will be automatically created when you enable the TSDP policy.

• To create the VPD policy function, use the CREATE OR REPLACE FUNCTION procedure, as follows:

  CREATE OR REPLACE FUNCTION vpd_function (
    v_schema IN VARCHAR2, 
    v_objname IN VARCHAR2) 
   RETURN VARCHAR2 AS 
  BEGIN
   RETURN 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''HR_APPUSER'''; 
  END vpd_function;
  /
  

Step 4: Create and Enable a Transparent Sensitive Data Protection Policy

After you have created the VPD policy function, you can associate it with a transparent sensitive data protection policy.

1. Create the Transparent Sensitive Data Protection policy.

   DECLARE 
    vpd_feature_options DBMS_TSDP_PROTECT.FEATURE_OPTIONS;
    policy_conditions DBMS_TSDP_PROTECT.POLICY_CONDITIONS;
   BEGIN
    vpd_feature_options ('policy_function') := 'vpd_function';   
    vpd_feature_options ('sec_relevant_cols_opt') := 'DBMS_RLS.ALL_ROWS';   
    dbms_tsdp_protect.add_policy('tsdp_vpd', DBMS_TSDP_PROTECT.VPD, vpd_feature_options, policy_conditions); 
   END; 
   /
   

   In this example, the vpd_feature_options parameter refers to the sec_relevant_cols_opt parameter from the DBMS_RLS.ADD_POLICY procedure. When the TSDP policy is enabled, the VPD policy that is automatically created will have its sec_relevant_cols parameter (of DBMS_RLS.ADD_POLICY) set to the name of the sensitive column on which TSDP enables the VPD policy. If you had not used the sec_relevant_cols_opt parameter, then TSDP would not have used the DBMS_RLS.ADD_POLICY sec_relevant_cols_opt parameter.

2. Associate the tsdp_vpd1 TSDP policy with the salary_type sensitive type.

   BEGIN
    DBMS_TSDP_PROTECT.ASSOCIATE_POLICY(
    policy_name        => 'tsdp_vpd',
    sensitive_type     => 'salary_type',
    associate          => TRUE);
   END;
   /
   

3. Enable protection to enforce the Virtual Private Database policy on all columns identified as SALARY_TYPE:

   BEGIN
    DBMS_TSDP_PROTECT.ENABLE_PROTECTION_TYPE(
     sensitive_type           => 'salary_type');
   END;
   /
   

Step 5: Test the Transparent Sensitive Data Protection Policy

Now, you are ready to test the transparent sensitive data protection policy.

1. Connect as user hr_appuser.

   CONNECT hr_appuser -- Or, CONNECT hr_appuser@hrpdb
   Enter password: password
   

2. Query the SCOTT.EMP table as follows:

   SELECT SAL, COMM, EMPNO FROM SCOTT.EMP;
   

   The following output appears:

       SAL   COMM   EMPNO
   --------- ------ ----------
       800          7369
      1600    300   7499
      1250    500   7521
      2975          7566
      1250   1400   7654
      2850          7698
      2450          7782
      3000          7788
      5000          7839
      1500     0    7844
      1100          7876
       950          7900
      3000          7902
      1300          7934
   14 rows selected. 
   

   The vpd_function function enables user hr_appuser to see the salaries in the SAL column of the EMP table.

3. Connect as user SCOTT and then perform the same query.

   CONNECT SCOTT -- Or, CONNECT SCOTT@hrpdb
   Enter password: password
   
   SELECT SAL, COMM, EMPNO FROM SCOTT.EMP;
   

   The following output appears:

       SAL   COMM   EMPNO
   --------- ------ ----------
                    7369
              300   7499
              500   7521
                    7566
             1400   7654
                    7698
                    7782
                    7788
                    7839
               0    7844
                    7876
                    7900
                    7902
                    7934
   14 rows selected. 
   

   Even though SCOTT owns the EMP table, the vpd_function function prevents him from seeing the salaries in the SAL column of this table

Step 6: Remove the Components of This Tutorial

If you no longer need the components of this tutorial, then you can remove them.

1. Connect as user tsdp_admin.

   CONNECT tsdp_admin -- Or, CONNECT tsdp_admin@hrpdb
   Enter password: password
   

2. Execute the following statements in the order shown.

   BEGIN
    DBMS_TSDP_MANAGE.DROP_SENSITIVE_COLUMN (
      schema_name        => 'SCOTT',
      table_name         => 'EMP',
      column_name        => 'SAL');
   END;
   /
   

   BEGIN
    DBMS_TSDP_MANAGE.DROP_SENSITIVE_TYPE(
    sensitive_type     => 'salary_type');
   END;
   /
   
   BEGIN
    DBMS_TSDP_PROTECT.DROP_POLICY(
      policy_name     => 'tsdp_vpd');
   END;
   /
   

3. Connect as user SYSTEM.

   CONNECT SYSTEM -- Or, CONNECT SYSTEM@hrpdb
   Enter password: password
   

4. Drop the tsdp_admin and hr_appuser accounts.

   DROP USER tsdp_admin CASCADE;
   DROP USER hr_appuser