Oracle 12cR2 DBMS_TSDP

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	DISABLE_PROTECTION_SOURCE	ENABLE_PROTECTION_COLUMN
ALTER_POLICY	DISABLE_PROTECTION_TYPE	ENABLE_PROTECTION_SOURCE
ASSOCIATE_POLICY	DROP_POLICY	ENABLE_PROTECTION_TYPE
DISABLE_PROTECTION_COLUMN

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');