Oracle DBMS_TSDP_PROTECT
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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
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.disable_protection_type('PARTIAL_MASK', dbms_tsdp_protect.policy_conditions);
Overload 2 dbms_tsdp_protect.drop_policy(policy_name IN VARCHAR2);
exec dbms_tsdp_protect.disable_protection_type('FIN_TYPE');
 
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
Data Masking
DBMS_REDACT
DBMS_TSDP_MANAGE
Packages
PSTDY_DATAPUMP_SUPPORT
Security
TSDP$DATAPUMP

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-2014 Daniel A. Morgan All Rights Reserved