Oracle DBMS_TSDP_PROTECT
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
Purpose Provides an interface to configure transparent sensitive data protection (TSDP) policies in conjunction with the DBMS_TSDP_MANAGE package.
AUTHID CURRENT_USER
Constants
Name Data Type Value
DATATYPE INTEGER 1
LENGTH INTEGER 2
SCHEMA_NAME INTEGER 3
TABLE_NAME INTEGER 4
TSDP_PARAM_MAX INTEGER 4000
REDACT INTEGER 1
AUDIT_NG INTEGER 2
VPD INTEGER 3
TDE INTEGER 4
Dependencies
DBA_TSDP_IMPORT_ERRORS DBA_TSDP_POLICY_TYPE TSDP_POLICY$
DBA_TSDP_POLICY_CONDITION DBMS_TSDP_LIB TSDP_PROTECTION$
DBA_TSDP_POLICY_FEATURE PLITBLM TSDP_SUBPOL$
DBA_TSDP_POLICY_PARAMETER TSDP$DATAPUMP TSDP_SENSITIVE_DATA$
DBA_TSDP_POLICY_PROTECTION TSDP_FEATURE_POLICY$  
Data Types TYPE feature_options IS TABLE OF VARCHAR2(4000) INDEX BY VARCHAR2(30);

TYPE policy_conditions IS TABLE OF VARCHAR2(4000) INDEX BY PLS_INTEGER;
Documented Yes
Exceptions
Error Code Reason
ORA-45614 invalid Transparent Sensitive Data Protection (TSDP) policy <policy_name>
First Available 12.1.0
Policy Conditions tsdp$default_condition POLICY_CONDITIONS;
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/dbmstsdpe.sql
{ORACLE_HOME}/rdbms/admin/exectsdp.sql
Subprograms
 
ADD_POLICY
Create a Protection Policy 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_CONDITIONS DEFAULT tsdp$default_condition);


Security Features
DBMS_TSDP_PROTECT.AUDIT
DBMS_TSDP_PROTECT.REDACT
DBMS_TSDP_PROTECT.TDE_CE
DBMS_TSDP_PROTECT.VPD
Policy Apply Conditions
DBMS_TSDP_PROPERTY.DATATYPE
DBMS_TSDP_PROPERTY.LENGTH
DBMS_TSDP_PROPERTY.PARENT_SCHEMA
DBMS_TSDP_PROPERTY.PARENT_TABLE
conn uwclass/uwclass@pdbdev

CREATE TABLE uwclass.employees (
pid   NUMBER,
pname VARCHAR2(20),
ssn   VARCHAR2(11));

INSERT INTO uwclass.employees VALUES (1, 'Ellison', '111-11-1234');
INSERT INTO uwclass.employees VALUES (2, 'Catz', '111-22-1234');
INSERT INTO uwclass.employees VALUES (3, 'Hurd', '111-33-1234');
INSERT INTO uwclass.employees VALUES (4, 'Kurian', '111-44-1234');
INSERT INTO uwclass.employees VALUES (5, 'Reese', '111-55-1234');
COMMIT;

SELECT *
FROM uwclass.employees;

col column_name format a12
col policy_name format a15
col parameter format a20
col schema_name format a12
col security_feature_policy format a24
col sensitive_type format a15
col table_name format a12
col tsdp_policy format a13

SELECT *
FROM dba_tsdp_policy_feature;

SELECT *
FROM dba_tsdp_policy_parameter;

SELECT *
FROM dba_tsdp_policy_type;

SELECT *
FROM dba_tsdp_policy_protection;

DECLARE
 redact_feature_opts dbms_tsdp_protect.feature_options;
 policy_conditions   dbms_tsdp_protect.policy_conditions;
BEGIN
  redact_feature_opts('expression') := 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') =''SYS''';
  redact_feature_opts('function_type') := 'DBMS_REDACT.PARTIAL';
  redact_feature_opts('function_parameters') := 'STR, VVVVVVVVV,VVVVVVVVV, *, 1, 6';

  policy_conditions(dbms_tsdp_protect.datatype) := 'VARCHAR2';

  dbms_tsdp_protect.add_policy('PARTIAL_MASK',
                                dbms_tsdp_protect.redact,
                                redact_feature_opts,
                                policy_conditions);
END;
/

SELECT *
FROM dba_tsdp_policy_feature;

SELECT *
FROM dba_tsdp_policy_condition;

SELECT *
FROM dba_tsdp_policy_parameter;
 
ALTER_POLICY
Alter an existing TSDP Policy dbms_tsdp_protect.alter_policy(
policy_name            IN VARCHAR2,
policy_enable_options  IN FEATURE_OPTIONS,
policy_apply_condition IN POLICY_CONDITIONS DEFAULT tsdp$default_condition);
SELECT *
FROM dba_tsdp_policy_parameter;

DECLARE
 redact_feature_opts dbms_tsdp_protect.feature_options;
 pol_conditions      dbms_tsdp_protect.policy_conditions;
BEGIN
  redact_feature_opts('expression') := 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''SYS''';
  redact_feature_opts('function_type') := 'dbms_redact.partial';
  redact_feature_opts('function_parameters') := 'STR, VVVVVVVVV,VVVVVVVVV, *, 1, 6';
  pol_conditions(dbms_tsdp_protect.datatype) := 'VARCHAR2';

  dbms_tsdp_protect.alter_policy('PARTIAL_MASK', redact_feature_opts, pol_conditions);
END;
/

SELECT *
FROM dba_tsdp_policy_parameter;
 
ASSOCIATE_POLICY
Associate/dis-associate a TSDP Policy with a Sensitive Column Type dbms_tsdp_protect.associate_policy(
policy_name    IN VARCHAR2,
sensitive_type IN VARCHAR2,
associate      IN BOOLEAN DEFAULT TRUE);
-- associate PARTIAL_MASK with SSN_TYPE

SELECT *
FROM dba_tsdp_policy_type;

exec dbms_tsdp_protect.associate_policy('PARTIAL_MASK', 'SSN_TYPE');

SELECT *
FROM dba_tsdp_policy_type;
 
DISABLE_PROTECTION_COLUMN
Used to disable protection for columns dbms_tsdp_protect.disable_protection_column(
schema_name IN VARCHAR2 DEFAULT '%',
table_name  IN VARCHAR2 DEFAULT '%',
column_name IN VARCHAR2 DEFAULT '%',
policy      IN VARCHAR2 DEFAULT NULL);
See ENABLE_PROTECTION_COLUMN Demo Below
 
DISABLE_PROTECTION_SOURCE
Used to disable protection based on the source of truth for the sensitive columns dbms_tsdp_protect.disable_protection_source(discovery_source IN VARCHAR2);
exec dbms_tsdp_protect.disable_protection_source('UW_DRIMPORT_DEMO');
 
DISABLE_PROTECTION_TYPE
Used to disable protection for a Sensitive Column Type dbms_tsdp_protect.disable_protection_type(sensitive_type IN VARCHAR2);
exec dbms_tsdp_protect.disable_protection_type('SSN_TYPE');
 
DROP_POLICY
drop a TSDP Policy or one of its Condition-Enable_Options combinations
Overload 1
dbms_tsdp_protect.drop_policy(
policy_name            IN VARCHAR2,
policy_apply_condition IN POLICY_CONDITIONS);
exec dbms_tsdp_protect.drop_policy('PARTIAL_MASK', dbms_tsdp_protect.policy_conditions);
Overload 2 dbms_tsdp_protect.drop_policy(policy_name IN VARCHAR2);
exec dbms_tsdp_protect.drop_policy('PARTIAL_MASK');
 
ENABLE_PROTECTION_COLUMN
Used to enable protection for columns dbms_tsdp_protect.enable_protection_column(
schema_name IN VARCHAR2 DEFAULT '%',
table_name  IN VARCHAR2 DEFAULT '%',
column_name IN VARCHAR2 DEFAULT '%',
policy      IN VARCHAR2 DEFAULT NULL);
exec dbms_tsdp_protect.enable_protection_column('UWCLASS', 'EMPLOYEES', 'SSN', 'PARTIAL_MASK');

exec dbms_tsdp_protect.disable_protection_column('UWCLASS', 'EMPLOYEES', 'SSN', 'PARTIAL_MASK');
 
ENABLE_PROTECTION_SOURCE
Used to enable protection based on the source of truth for the sensitive columns dbms_tsdp_protect.enable_protection_source(discovery_source IN VARCHAR2);
exec dbms_tsdp_protect.enable_protection_source('UW_DRIMPORT_DEMO');
 
ENABLE_PROTECTION_TYPE
Used to enable protection for a Sensitive Column Type dbms_tsdp_protect.enable_protection_type(sensitive_type IN VARCHAR2);
exec dbms_tsdp_protect.enable_protection_type('SSN_TYPE');

Related Topics
Built-in Functions
Built-in Packages
Data Masking
DBMS_REDACT
DBMS_TSDP_MANAGE
PSTDY_DATAPUMP_SUPPORT
Security
TSDP$DATAPUMP
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved