Oracle 12c DBMS_TSDP_PROTECT Demos

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_PARAMETER	DBMS_TSDP_LIB
DBA_TSDP_POLICY_CONDITION	DBA_TSDP_POLICY_PROTECTION	PLITBLM
DBA_TSDP_POLICY_FEATURE	DBA_TSDP_POLICY_TYPE	TSDP$DATAPUMP

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	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 AS
SELECT * FROM hr.employees
WHERE rownum < 6;

ALTER TABLE uwclass.employees
ADD (ssn VARCHAR2(11));

UPDATE uwclass.employees SET ssn = '111-11-1234'
WHERE ssn IS NULL AND rownum = 1;

UPDATE uwclass.employees SET ssn = '111-11-2345'
WHERE ssn IS NULL AND rownum = 1;

UPDATE uwclass.employees SET ssn = '111-11-3456'
WHERE ssn IS NULL AND rownum = 1;

UPDATE uwclass.employees SET ssn = '111-11-4567'
WHERE ssn IS NULL AND rownum = 1;

UPDATE uwclass.employees SET ssn = '111-11-5678'
WHERE ssn IS NULL AND rownum = 1;
COMMIT;

SELECT last_name, ssn
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);

-- there are some issues here ... recheck with the final release as to whether they have been addressed

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