Oracle 12cR2 DBMS_TSDP

Oracle DBMS_TSDP_PROTECT Version 12.2.0.1

General Information

Library Note

Morgan's Library Page Header

Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.

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