Oracle MGMT_DM Data Masking Demos

Oracle MGMT_DM Version 11.2.0.3

General Information

Purpose

Utility used by the OEM Grid/Cloud Control Data Masking pack to create, update, and drop rules.

AUTHID

DEFINER

Constants

Name	Data Type	Value
Rule Types
rule_type_arraylist	VARCHAR2(2)	AL'
rule_type_tablecolumn	VARCHAR2(2)	'CC'
rule_type_randomdate	VARCHAR2(2)	'DT'
rule_type_randomdigit	VARCHAR2(2)	'RD'
rule_type_randomnumber	VARCHAR2(2)	'RN'
rule_type_randomstring	VARCHAR2(2)	'RS'
rule_type_fixednumber	VARCHAR2(2)	'FN'
rule_type_fixedstring	VARCHAR2(2)	'FS'
rule_type_userdefined	VARCHAR2(2)	'UF'
rule_type_postudf	VARCHAR2(2)	'UT'
rule_type_shuffle	VARCHAR2(2)	'SH'
rule_type_substr	VARCHAR2(2)	'SS'
rule_type_droprow	VARCHAR2(2)	'DR'
rule_type_preserve	VARCHAR2(2)	'PR'
Rule Output Types
output_type_source	INTEGER	0
output_type_string	INTEGER	1
output_type_number	INTEGER	2
output_type_date	INTEGER	3
Masking Metadata Object Type
masking_definition_t	INTEGER	0
masking_format_t	INTEGER	1

Data Types

TYPE weak_cur_typ IS REF CURSOR;

Dependencies

MGMT_DM_ALITEMS	MGMT_DM_RULETEMPLATES	MGMT_TARGET
MGMT_DM_ARRAY_LIST	MGMT_DM_SCOPESPECS	MGMT_TARGETS
MGMT_DM_COLUMN_RULES	MGMT_DM_SS_COLUMNS	MGMT_TARGET_GUID_ARRAY
MGMT_DM_INFCONS_COLUMNS	MGMT_JOBS	MGMT_USER
MGMT_DM_JOB_EXECUTIONS	MGMT_JOB_GUID_ARRAY	PLITBLM
MGMT_DM_RULEENTRY

Security Model

Owned by SYSMAN with no privileges granted

Source

{$ORACLE_HOME}/sysman/admin/emdrep/sql/db/latest/dm_pkgdef.sql

Subprograms

ADD_INFCONS_COLUMN	CREATE_MASKING_DEFINITION	REMOVE_MASKING_DEFINITION
ADD_MASKING_JOB	CREATE_MASKING_RULE	REMOVE_MASKING_RULE
ADD_RULE_ENTRY	GET_MASKING_RULE_ARRAYLIST	REMOVE_RULE_ENTRIES
ADD_SS_COLUMN	HAS_REMOVE_PRIV	UPDATE_MASKING_RULE

ADD_INFCONS_COLUMN

Undocumented

add_infcons_column(
p_ss_guid       IN RAW,
p_table_schema IN VARCHAR2,
p_table_name    IN VARCHAR2,
p_column_name   IN VARCHAR2,
p_parent_schema IN VARCHAR2,
p_parent_table IN VARCHAR2,
p_parent_column IN VARCHAR2);

TBD

SELECT *
FROM sysman.mgmt_dm_infcons_columns;

ADD_MASKING_JOB

Creates a masking job using DBMS_SCHEDULER

add_masking_job(p_ss_guid IN RAW, p_execution_id IN RAW);

TBD

SELECT *
FROM sysman.mgmt_dm_job_executions;

ADD_RULE_ENTRY

Undocumented

add_rule_entry(
p_rule_guid    IN RAW,
p_entry_order IN NUMBER,
p_rule_type    IN VARCHAR2,
p_rule_option IN VARCHAR2,
p_rule_low     IN NUMBER,
p_rule_high    IN NUMBER,
p_start_date   IN DATE,
p_end_date     IN DATE,
p_fixed_string IN VARCHAR2,
p_fixed_number IN NUMBER,
p_table_schema IN VARCHAR2,
p_table_name   IN VARCHAR2,
p_column_name IN VARCHAR2,
p_udf_name     IN VARCHAR2,
p_arr_list     IN mgmt_dm_array_list DEFAULT NULL);

TBD

SELECT *
FROM sysman.mgmt_dm_ruleentry;

SELECT *
FROM sysman.mgmt_dm_alitems;

ADD_SS_COLUMN

Undocumented

add_ss_column(
p_ss_guid IN RAW,
p_table_schema IN VARCHAR2,
p_table_name IN VARCHAR2,
p_column_name IN VARCHAR2,
p_column_group IN VARCHAR2);

TBD

SELECT *
FROM sysman.mgmt_dm_ss_columns;

CREATE_MASKING_DEFINITION

Create a masking definition and returns a guid

create_masking_definition(
p_owner IN VARCHAR2,
p_name IN VARCHAR2,
p_source_name IN VARCHAR2,
p_source_type IN VARCHAR2,
p_description IN VARCHAR2 DEFAULT NULL)
RETURN RAW;

conn / as sysdba

SELECT target_name, target_type
FROM sysman.mgmt_targets;
-- the results are used in the next statement

DECLARE
r RAW(16);
BEGIN
r := sysman.mgmt_dm.create_masking_definition('UWCLASS', 'DM1', 'orabase', 'oracle_database', 'CMD_DEMO1');
dbms_output.put_line(r);
END;
/
-- returned 642CAE19738947CDA9F0149C578FEC09 as can be seen in the query below too

SELECT *
FROM sysman.mgmt_dm_scopespecs;

exec sysman.mgmt_dm.remove_masking_definition('642CAE19738947CDA9F0149C578FEC09');

SELECT *
FROM sysman.mgmt_dm_scopespecs;

CREATE_MASKING_RULE

Create a masking rule, depending on is_library, either an internal rule or library template and returns a rule guid

create_masking_rule(
p_owner          IN VARCHAR2,
p_name           IN VARCHAR2,
p_description    IN VARCHAR2 DEFAULT NULL,
p_data_type      IN NUMBER DEFAULT 0,
p_is_library     IN NUMBER DEFAULT 0,
p_rule_order     IN NUMBER DEFAULT 1,
p_rule_condition IN VARCHAR2 DEFAULT NULL,
p_ss_guid        IN RAW DEFAULT NULL,
p_masked_schema IN VARCHAR2 DEFAULT NULL,
p_masked_table   IN VARCHAR2 DEFAULT NULL,
p_masked_column IN VARCHAR2 DEFAULT NULL)
RETURN RAW;

conn / as sysdba

DECLARE
r RAW(16);
BEGIN
r := sysman.mgmt_dm.create_masking_rule('UWCLASS', 'DM2', 'DEMO2', 0, 0, 1);
dbms_output.put_line(r);
END;
/

Returns: 6B1C0D9204C746DD9C68D0E55E0F1372

desc sysman.mgmt_dm_ruletemplates

col rule_owner format a10
col rule_name format a9
col description format a12
col rule_condition format a20

SELECT *
FROM sysman.mgmt_dm_ruletemplates
WHERE rule_guid = '6B1C0D9204C746DD9C68D0E55E0F1372';

SELECT *
FROM sysman.mgmt_dm_column_rules
WHERE ss_guid = '<need the value from input param p_ss_guid>';

SELECT sysman.mgmt_dm.has_remove_priv('6B1C0D9204C746DD9C68D0E55E0F1372', 0)
FROM dual;

exec sysman.mgmt_dm.remove_masking_rule('6B1C0D9204C746DD9C68D0E55E0F1372');

SELECT *
FROM sysman.mgmt_dm_ruletemplates
WHERE rule_guid = '6B1C0D9204C746DD9C68D0E55E0F1372';

GET_MASKING_RULE_ARRAYLIST

Given a masking rule guid return an array of associated array list items

get_masking_rule_arraylist(
p_rule_guid IN RAW,
p_entry_order IN NUMBER,
p_array_list OUT mgmt_dm_array_list);

col arraylist_item format a30

SELECT *
FROM sysman.mgmt_dm_alitems;

-- returned 0569D9EED8BA4551ACF06648E8532872

DECLARE
retArray sysman.mgmt_dm_array_list;
BEGIN
sysman.mgmt_dm.get_masking_rule_arraylist('0569D9EED8BA4551ACF06648E8532872', 1, retArray);

FOR i IN 1.. retArray.COUNT LOOP
dbms_output.put_line(retArray(i));
END LOOP;
END;
/

HAS_REMOVE_PRIV

Returns 1 if the user has appropriate privileges to remove a masking definition or rule

has_remove_priv(p_guid IN RAW, p_type IN NUMBER) RETURN NUMBER;

See CREATE_MASKING_RULE Demo Above

REMOVE_MASKING_DEFINITION

Removes a masking definition

remove_masking_definition(p_ss_guid IN RAW);

See CREATE_MASKING_DEFINITION Demo Above

REMOVE_MASKING_RULE

Removes a masking rule from the data dictionary

remove_masking_rule(p_rule_guid IN RAW);

See CREATE_MASKING_RULE Demo Above

REMOVE_RULE_ENTRIES

Removes entries from a data masking rule

remove_rule_entries(p_rule_guid IN RAW);

TBD

SELECT *
FROM sysman.mgmt_dm_ruleentry;

UPDATE_MASKING_RULE

Updates a data masking rule

update_masking_rule(
p_rule_guid   IN RAW,
p_name        IN VARCHAR2,
p_description IN VARCHAR2 DEFAULT NULL,
p_data_type   IN NUMBER DEFAULT 0,
p_is_library IN NUMBER DEFAULT 0);

TBD

SELECT *
FROM sysman.mgmt_dm_ruletemplates;