202 DBMS_TSDP_PROTECT
The DBMS_TSDP_PROTECT
package provides an interface to configure transparent sensitive data protection (TSDP) policies in conjunction with the DBMS_TSDP_MANAGE package.
DBMS_TSDP_PROTECT
is available with the Enterprise Edition only.
This chapter contains the following topics:
Related Topics
See Also:
202.1 DBMS_TSDP_PROTECT Overview
Use the DBMS_TSDP_PROTECT
package to create transparent sensitive data protection policies, configure protection by associating the policies with sensitive types, and to enable and disable the configured protection.
Sensitive types can be added using the DBMS_TSDP_MANAGE package.
202.2 DBMS_TSDP_PROTECT Security Model
All procedures are executed with invoker's rights. Typically, a security administrator should have the EXECUTE
privilege for this package.
202.3 DBMS_TSDP_PROTECT Constants
DBMS_TSDP_PROTECT
defines the TSDP_PARAM_MAX
constant for use when specifying parameter values.
This constant is described in the following table.
Table 202-1 DBMS_TDSP_PROTECT Constants - Compression Types
Constant | Type | Value | Description |
---|---|---|---|
|
|
4000 |
Maximum length of the parameter value that can be specified in |
202.4 DBMS_TSDP_PROTECT Data Structures
The DBMS_TSDP_PROTECT
package defines two TABLE
types.
202.4.1 FEATURE_OPTIONS Table Type
The following type is an associative array of VARCHAR2(TSDP_PARAM_MAX)
that is indexed by VARCHAR2(M_IDEN)
.
Syntax
TYPE FEATURE_OPTIONS IS TABLE OF VARCHAR2(TSDP_PARAM_MAX) INDEX BY VARCHAR2(M_IDEN);
202.5 Summary of DBMS_TSDP_PROTECT Subprograms
This table lists the DBMS_TSDP_PROTECT
subprograms and briefly describes them.
Table 202-2 DBMS_TSDP_PROTECT Package Subprograms
Subprogram | Description |
---|---|
Creates a TSDP policy |
|
Alters a TDSP policy |
|
Associates or disassociates a TSDP policy with a sensitive column type |
|
Disables protection for columns |
|
Disables protection based on the source of truth for the sensitive columns |
|
Disables protection for a sensitive column type |
|
Removes a TDSP policy |
|
Enables protection for columns |
|
Enables protection based on the source of truth for the sensitive columns |
|
Enables protection for a sensitive column type |
202.5.1 ADD_POLICY Procedure
This procedure creates a TDSP policy.
Syntax
DBMS_TSDP_PROTECT.ADD_POLICY ( policy_name IN VARCHAR2, security_feature IN PLS_INTEGER, policy_enable_options IN FEATURE_OPTIONS, policy_apply_condition IN POLICY_CONDITION DEFAULT TSDP$default_condition);
Parameters
Table 202-3 ADD_POLICY Procedure Parameters
Parameter | Description |
---|---|
|
Name of the policy being created. The maximum length for this identifier is |
|
Oracle security feature with which the policy is associated. Allowed values:
|
|
Initialized with the parameter-value pairs corresponding to the |
|
Initialized with the property-value pairs that must be satisfied in order to apply the corresponding Example:
|
Usage Notes
To create the TDSP policy, you must include the procedure in an anonymous block that defines the type of security feature that will use the policy and conditions to test when the policy is enabled. For more information, see Oracle Database Security Guide.
Examples
Create a policy PARTIAL_MASK_POLICY
:
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') := 'STR, VVVVVVVVV,VVVVVVVVV, *, 1, 6'; policy_conditions(DBMS_TSDP_PROTECT.DATATYPE) := 'VARCHAR2'; DBMS_TSDP_PROTECT.ADD_POLICY ('PARTIAL_MASK_POLICY', DBMS_TSDP_PROTECT.REDACT, redact_feature_options, policy_conditions); END;
202.5.2 ALTER_POLICY Procedure
This procedure alters an existing TDSP policy
Syntax
DBMS_TSDP_PROTECT.ALTER_POLICY ( policy_name IN VARCHAR2, policy_enable_options IN FEATURE_OPTIONS, policy_apply_condition IN POLICY_CONDITION default TSDP$default_condition);
Parameters
Table 202-4 ALTER_POLICY Procedure Parameters
Parameter | Description |
---|---|
|
Name of the policy to alter |
|
Initialized with the parameter-value pairs corresponding to the security feature |
|
Initialized with the property-value pairs that must be satisfied in order to apply the corresponding Example:
|
Usage Notes
-
If the
policy_apply_condition
matches an existing condition for the policy, then the corresponding enable options are updated withpolicy_enable_options
. -
If the
policy_apply_condition
does not match any existing condition for the policy, the combination ofpolicy_enable_options
andpolicy_apply_condition
is added to the policy.
Examples
Add a new combination of policy_apply_condition
and policy_enable_options
to an existing policy PARTIAL_MASK_POLICY
:
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') := 'STR, VVVVVVVVV,VVVVVVVVV, *, 1, 6'; policy_conditions (DBMS_TSDP_PROTECT.DATATYPE) := 'VARCHAR2'; DBMS_TSDP_PROTECT.ALTER_POLICY ('PARTIAL_MASK_POLICY', redact_feature_options, policy_conditions); END;
202.5.3 ASSOCIATE_POLICY Procedure
This procedure associates or disassociates a TSDP policy with a sensitive column type.
Syntax
DBMS_TSDP_PROTECT.ASSOCIATE_POLICY ( policy_name IN VARCHAR2, sensitive_type IN VARCHAR2, associate IN BOOLEAN DEFAULT TRUE);
Parameters
Table 202-5 ASSOCIATE_POLICY Procedure Parameters
Parameter | Description |
---|---|
|
Name of the TDSP policy |
|
Name of the sensitive column type: |
|
Associate or Disassociate. TRUE implies Associate |
Usage Notes
Both the policy and the sensitive column type should exist in the database.
Examples
Associate PARTIAL_MASK_POLICY
with SSN_TYPE
:
DBMS_TSDP_PROTECT.ASSOCIATE_POLICY ('PARTIAL_MASK_POLICY', 'SSN_TYPE');
202.5.4 DISABLE_PROTECTION_COLUMN Procedure
This procedure disables protection for columns.
Syntax
DBMS_TSDP_PROTECT.DISABLE_PROTECTION_COLUMN ( schema_name IN VARCHAR2 DEFAULT '%', table_name IN VARCHAR2 DEFAULT '%', column_name IN VARCHAR2 DEFAULT '%', policy_name IN VARCHAR2 DEFAULT NULL);
Parameters
Table 202-6 DISABLE_PROTECTION_COLUMN Procedure Parameters
Parameter | Description |
---|---|
|
Name of the schema containing the column |
|
Table containing the column |
|
Column name |
|
Optional policy name. If given, only this policy is disabled. |
Examples
Disable TSDP policies associated with the corresponding sensitive column types for columns that reside in schema with name like %PAYROLL%
, table name like EMP%
, and column name like SAL%
:
EXEC DBMS_TSDP_PROTECT.DISABLE_PROTECTION_COLUMN ('%PAYROLL%', 'EMP%', 'SAL%');
202.5.5 DISABLE_PROTECTION_SOURCE Procedure
This procedure disables protection based on the source of truth for the sensitive columns.
Syntax
DBMS_TSDP_PROTECT.DISABLE_PROTECTION_SOURCE ( discovery_sourcename IN VARCHAR2);
Parameters
Table 202-7 DISABLE_PROTECTION_SOURCE Procedure Parameters
Parameter | Description |
---|---|
|
Name of the discovery source. This could be the Application Data Model (ADM) name or the database user. |
Examples
Disable protection for all columns corresponding to ADM_Demo
:
DBMS_TSDP_PROTECT.DISABLE_PROTECTION_SOURCE ('ADM_Demo');
202.5.6 DISABLE_PROTECTION_TYPE Procedure
This procedure disables protection for a sensitive column type.
Syntax
DBMS_TSDP_PROTECT.DISABLE_PROTECTION_TYPE ( sensitive_type IN VARCHAR2);
Parameters
Table 202-8 DISABLE_PROTECTION_TYPE Procedure Parameters
Parameter | Description |
---|---|
|
Name of the sensitive column type |
Examples
Disable protection for all columns identified by SSN_TYPE
:
DBMS_TSDP_PROTECT.DISABLE_PROTECTION_TYPE ('SSN_TYPE');
202.5.7 DROP_POLICY Procedure
This procedure removes a TDSP policy or one of its condition-enable_options combinations.
Syntax
DBMS_TSDP_PROTECT.DROP_POLICY ( policy_name IN VARCHAR2, policy_apply_condition IN POLICY_CONDITIONS); DBMS_TSDP_PROTECT.DROP_POLICY ( policy_name IN VARCHAR2);
Parameters
Table 202-9 DROP_POLICY Procedure Parameters
Parameter | Description |
---|---|
|
Name of the policy to drop |
|
To be initialized with the relevant condition |
Usage Notes
-
The combination of
policy_conditions
andpolicy_enable_options
can be dropped from a TSDP policy by giving thepolicy_apply_condition
parameter. The default condition-default options combination can also be dropped (if it exists for the policy) by passing an empty associative array of typeDBMS_TSDP_PROTECT.POLICY_CONDITION
. -
If the condition-enable_options combination that is being dropped is the last condition-enable_options combination for the policy, the policy itself is dropped.
-
A policy can be completely dropped by using the overloaded of the procedure that takes only
policy_name
. -
A policy or one of its conditions can be dropped only if the policy is not associated with any sensitive column type. This also means that a policy that is being dropped is not enabled on any column (object).
Examples
Dropping the condition-enable_options combination based on a specific condition:
DECLARE policy_conditions DBMS_TSDP_PROTECT.POLICY_CONDITIONS; BEGIN policy_conditions (DBMS_TSDP_PROTECT.DATATYPE) := 'VARCHAR2'; DBMS_TSDP_PROTECT.DROP_POLICY ('PARTIAL_MASK_POLICY', policy_conditions); END;
The default condition-enable_options combination can be dropped by passing an empty associative array of type DBMS_TSDP_PROTECT.POLICY_CONDITIONS
for the policy_apply_condition
parameter:
DECLARE policy_conditions DBMS_TSDP_PROTECT.POLICY_CONDITIONS; BEGIN DBMS_TSDP_PROTECT.DROP_POLICY ('redact_partial_cc', policy_conditions); END;
Dropping a TSDP policy:
BEGIN DBMS_TSDP_PROTECT.DROP_POLICY( policy_name => 'PARTIAL_MASK_POLICY'); END;
202.5.8 ENABLE_PROTECTION_COLUMN Procedure
This procedure enables protection for columns.
Syntax
DBMS_TSDP_PROTECT.ENABLE_PROTECTION_COLUMN ( schema_name IN VARCHAR2 DEFAULT '%', table_name IN VARCHAR2 DEFAULT '%', column_name IN VARCHAR2 DEFAULT '%', policy_name IN VARCHAR2 DEFAULT NULL);
Parameters
Table 202-10 ENABLE_PROTECTION_COLUMN Procedure Parameters
Parameter | Description |
---|---|
|
Name of the schema containing the column |
|
Table containing the column |
|
Column name |
|
Optional policy name. If given, only this policy is enabled. |
Usage Notes
-
Only a TSDP Policy that is associated with the sensitive column type of the sensitive column can be enabled using this Procedure.
-
LIKE
condition is used forschema_name
,table_name
andcolumn_name
.AND
semantics is followed.
Examples
Enable TSDP policies associated with the corresponding sensitive column types for columns that reside in schema with name like %PAYROLL%
, table name like EMP%
, and column name like SAL%
:
DBMS_TSDP_PROTECT.ENABLE_PROTECTION_COLUMN ('%PAYROLL%', 'EMP%', 'SAL%');
202.5.9 ENABLE_PROTECTION_SOURCE Procedure
This procedure enables protection based on the source of truth for the sensitive columns.
Syntax
DBMS_TSDP_PROTECT.ENABLE_PROTECTION_SOURCE ( discovery_sourcename IN VARCHAR2);
Parameters
Table 202-11 ENABLE_PROTECTION_SOURCE Procedure Parameters
Parameter | Description |
---|---|
|
Name of the discovery source. This could be the Application Data Model (ADM) name or the database user. |
Examples
Enable protection for all columns corresponding to ADM_Demo
:
DBMS_TSDP_PROTECT.ENABLE_PROTECTION_SOURCE ('ADM_Demo');
202.5.10 ENABLE_PROTECTION_TYPE Procedure
This procedure enables protection for a sensitive column type.
Syntax
DBMS_TSDP_PROTECT.ENABLE_PROTECTION_TYPE ( sensitive_type IN VARCHAR2);
Parameters
Table 202-12 ENABLE_PROTECTION_TYPE Procedure Parameters
Parameter | Description |
---|---|
|
Name of the sensitive column type |
Examples
Enable protection for all columns identified by SSN_TYPE
:
DBMS_TSDP_PROTECT.ENABLE_PROTECTION_TYPE ('SSN_TYPE');