软件开发的最后阶段，是需要尽量真实的数据来作为基础测试软件的一系列功能。尤其是企业信息化系统，ERP、BI、EPM等这些大型系统实施或开发的时候，对于基础数据的要求很严格，很多时候都是直接克隆生产环境的数据来进行软件系统的测试，但是随之而来的影响却是深远的，生产数据中，首先它是一个真实的数据，透过数据基本上掌握了整个企业的资料。其次，在这当中包含很多敏感数据，不光是敏感数据，而且还是真实的敏感数据。如果在测试环境中发生了信息泄露问题，那么对于企业数据安全将造成致命的后果。
ORACLRE在OEM 10g之后提供Data Masking pack组件，解决从生产环境的数据向测试环境（或者开发环境）导入时可能会产生的数据内容安全问题，data masking特点是：按照指定的规则进行数据转换，可以通过规则控制最终生成的数据格式；维护数据表间的引用关系；通过EM图形界面来定义Data Masking，容易理解和使用。流程如下图所示:
OEM10g版本是10.2.0.4，提供了新6个pack功能，Data Masking Pack就是其中之一。因此，要使用安装，升级至最新的版本。使用Grid Control＋OEM方便得使用管理Data Masking。
注：使用Data Masking的前提条件，安装Grid Control,升级OEM10g到10.2.0.4版本。
安装Grid Control软件。
下载介质：
从oracle官方网站下载gc_102011_linux.zip,补丁包GridControl_10.2.0.4_LINUX.zip。
具体安装步骤详见文档：Oracle Grid Control 10.2.0.4 for Linux 安装和配置指南。
使用SYSMAN用户登录 Oracle Enterprise Manger 10g（Grid Control）。
进入Grid Control主界面后，点击“Targets”
进入Targets管理界面，点击“Database”
选择要进行管理的数据库，如图，进入“emrep”（数据库名）进行配置。
进入要管理的数据库之后，点击“Administration”。
进入管理界面后，看见Data Masking pack组件选项，点击“Definition”进入Data Masking进行管理与配置。
注：对定义TT用户的Mask表的列选择“随机字符串”转化规则
注：指定随机字符串的长度
脚本的内容为：
|
set echo off set feedback off set serveroutput on set pagesize 0
spool /u02/app/oragrid/OracleHomes/db10g/dbs/masking14.log
-- Script Header Section -- ==============================================
-- functions and procedures
CREATE OR REPLACE PROCEDURE mgmt$mask_sendMsg (msg IN VARCHAR2) IS msg1 VARCHAR2(1020); len INTEGER := length(msg); i INTEGER := 1; BEGIN dbms_output.enable (1000000);
LOOP msg1 := SUBSTR (msg, i, 255); dbms_output.put_line (msg1); len := len - 255; i := i + 255; EXIT WHEN len <= 0; END LOOP; END mgmt$mask_sendMsg; /
CREATE OR REPLACE PROCEDURE mgmt$mask_errorExit (msg IN VARCHAR2) IS BEGIN mgmt$mask_sendMsg (msg); mgmt$mask_sendMsg (‘errorExit!‘); END mgmt$mask_errorExit; /
CREATE OR REPLACE PROCEDURE mgmt$mask_errorExitOraError (msg IN VARCHAR2, errMsg IN VARCHAR2) IS BEGIN mgmt$mask_sendMsg (msg); mgmt$mask_sendMsg (errMsg); mgmt$mask_sendMsg (‘errorExitOraError!‘); END mgmt$mask_errorExitOraError; /
CREATE OR REPLACE PROCEDURE mgmt$mask_checkDBAPrivs (user_name IN VARCHAR2) AUTHID CURRENT_USER IS granted_role REAL := 0; BEGIN EXECUTE IMMEDIATE ‘SELECT 1 FROM SYS.DBA_ROLE_PRIVS WHERE GRANTED_ROLE = ‘‘DBA‘‘ AND GRANTEE = (SELECT USER FROM DUAL)‘ INTO granted_role; EXCEPTION WHEN NO_DATA_FOUND THEN mgmt$mask_sendMsg ( ‘WARNING checking privileges... User Name: ‘ || user_name); mgmt$mask_sendMsg ( ‘User does not have DBA privs. ‘ ); mgmt$mask_sendMsg ( ‘The script will fail if it tries to perform operations for which the user lacks the appropriate privilege. ‘ ); END mgmt$mask_checkDBAPrivs; /
CREATE OR REPLACE PROCEDURE mgmt$mask_setUpJobTable (script_id IN INTEGER, job_table IN VARCHAR2, step_num OUT INTEGER) AUTHID CURRENT_USER IS ctsql_text VARCHAR2(200) := ‘CREATE TABLE ‘ || job_table || ‘(SCRIPT_ID NUMBER, LAST_STEP NUMBER, unique (SCRIPT_ID))‘; itsql_text VARCHAR2(200) := ‘INSERT INTO ‘ || job_table || ‘ (SCRIPT_ID, LAST_STEP) values (:1, :2)‘; stsql_text VARCHAR2(200) := ‘SELECT last_step FROM ‘ || job_table || ‘ WHERE script_id = :1‘;
TYPE CurTyp IS REF CURSOR; -- define weak REF CURSOR type stsql_cur CurTyp; -- declare cursor variable
BEGIN step_num := 0; BEGIN EXECUTE IMMEDIATE ctsql_text; EXCEPTION WHEN OTHERS THEN NULL; END;
BEGIN OPEN stsql_cur FOR -- open cursor variable stsql_text USING script_id; FETCH stsql_cur INTO step_num; IF stsql_cur%FOUND THEN NULL; ELSE EXECUTE IMMEDIATE itsql_text USING script_id, step_num; COMMIT; step_num := 1; END IF; CLOSE stsql_cur; EXCEPTION WHEN OTHERS THEN mgmt$mask_errorExit (‘ERROR selecting or inserting from table: ‘ || job_table); return; END;
return;
EXCEPTION WHEN OTHERS THEN mgmt$mask_errorExit (‘ERROR accessing table: ‘ || job_table); return; END mgmt$mask_setUpJobTable; /
CREATE OR REPLACE PROCEDURE mgmt$mask_deleteJobTableEntry(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN INTEGER, highest_step IN INTEGER) AUTHID CURRENT_USER IS delete_text VARCHAR2(200) := ‘DELETE FROM ‘ || job_table || ‘ WHERE SCRIPT_ID = :1‘; BEGIN
IF step_num <= highest_step THEN return; END IF;
BEGIN EXECUTE IMMEDIATE delete_text USING script_id; IF SQL%NOTFOUND THEN mgmt$mask_errorExit (‘ERROR deleting entry from table: ‘ || job_table); return; END IF; EXCEPTION WHEN OTHERS THEN mgmt$mask_errorExit (‘ERROR deleting entry from table: ‘ || job_table); return; END;
COMMIT; END mgmt$mask_deleteJobTableEntry; /
CREATE OR REPLACE PROCEDURE mgmt$mask_setStep (script_id IN INTEGER, job_table IN VARCHAR2, step_num IN INTEGER) AUTHID CURRENT_USER IS update_text VARCHAR2(200) := ‘UPDATE ‘ || job_table || ‘ SET last_step = :1 WHERE script_id = :2‘; BEGIN -- update job table EXECUTE IMMEDIATE update_text USING step_num, script_id; IF SQL%NOTFOUND THEN mgmt$mask_sendMsg (‘NOTFOUND EXCEPTION of sql_text: ‘ || update_text); mgmt$mask_errorExit (‘ERROR accessing table: ‘ || job_table); return; END IF; COMMIT; EXCEPTION WHEN OTHERS THEN mgmt$mask_errorExit (‘ERROR accessing table: ‘ || job_table); return; END mgmt$mask_setStep; /
CREATE OR REPLACE FUNCTION mgmt$mask_randomencode (i_input VARCHAR2, pad_length NUMBER) RETURN VARCHAR2 AUTHID CURRENT_USER IS TYPE charmap IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER; l_input NUMBER; l_mod NUMBER; l_retCode VARCHAR2(100); l_map charmap; l_base number := 25; BEGIN if (i_input is null) then return lpad(‘a‘,pad_length,‘a‘); end if;
l_map(0) := ‘a‘; l_map(1) := ‘b‘; l_map(2) := ‘c‘; l_map(3) := ‘d‘; l_map(4) := ‘e‘; l_map(5) := ‘f‘; l_map(6) := ‘g‘; l_map(7) := ‘h‘; l_map(8) := ‘i‘; l_map(9) := ‘j‘; l_map(10) := ‘k‘; l_map(11) := ‘l‘; l_map(12) := ‘m‘; l_map(13) := ‘n‘; l_map(14) := ‘o‘; l_map(15) := ‘p‘; l_map(16) := ‘q‘; l_map(17) := ‘r‘; l_map(18) := ‘s‘; l_map(19) := ‘t‘; l_map(20) := ‘u‘; l_map(21) := ‘v‘; l_map(22) := ‘w‘; l_map(23) := ‘x‘; l_map(24) := ‘y‘; l_map(25) := ‘z‘;
l_input := i_input; l_retCode := ‘‘;
LOOP -- skip ‘a‘ for padding l_mod := l_input mod l_base + 1; l_retCode := l_map(l_mod) || l_retCode ; IF (l_input >= l_base) THEN l_input := round(l_input / l_base); ELSE l_input := 0; END IF; -- dbms_output.put_line(‘left ‘ || l_input || ‘ mod ‘ || l_mod ); EXIT WHEN l_input = 0; END LOOP; return lpad(l_retCode, pad_length, ‘a‘); END ; /
create or replace type mgmt$mask_array_list as table of varchar2(128) / CREATE OR REPLACE PROCEDURE mgmt$step_1_14(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER) AUTHID CURRENT_USER IS sqlerr_msg VARCHAR2(100); BEGIN IF step_num <> 1 THEN return; END IF;
mgmt$mask_setStep (14, ‘MGMT$MASK_CHECKPOINT‘, step_num); step_num := step_num + 1; BEGIN mgmt$mask_sendMsg (‘declare adj number:=0; begin select length(count(distinct NAME)) into adj from "TT"."MASK"; adj := greatest(adj - 1, 0); execute immediate ‘‘create table MGMT_DM_TT_15 (orig_val, new_val) NOLOGGING as select s.orig_val, CAST(lpad(lower(dbms_random.string(‘‘‘‘l‘‘‘‘, trunc(dbms_random.value(1, 7 - least(7, greatest(‘‘||adj||‘‘ - 0, 0)))))), 7 - least(7, greatest(‘‘||adj||‘‘ - 0, 0)),‘‘‘‘a‘‘‘‘) || mgmt$mask_randomencode(nvl(substr(s.new_num, 1, 1 + least(7, greatest(‘‘||adj||‘‘ - 0, 0))), ‘‘‘‘0‘‘‘‘),1 + least(7, greatest(‘‘||adj||‘‘ - 0, 0))) AS VARCHAR2(10)) new_val from (select rownum rn , orig_val , lpad(rownum - 1, ‘‘||adj||‘‘ + 1, 0) new_num from (select distinct NAME orig_val from "TT"."MASK" order by dbms_random.value)) s where 1=1 ‘‘; DBMS_STATS.GATHER_TABLE_STATS(NULL, ‘‘"MGMT_DM_TT_15"‘‘, estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>DBMS_STATS.DEFAULT_DEGREE); end;
‘); EXECUTE IMMEDIATE ‘declare adj number:=0; begin select length(count(distinct NAME)) into adj from "TT"."MASK"; adj := greatest(adj - 1, 0); execute immediate ‘‘create table MGMT_DM_TT_15 (orig_val, new_val) NOLOGGING as select s.orig_val, CAST(lpad(lower(dbms_random.string(‘‘‘‘l‘‘‘‘, trunc(dbms_random.value(1, 7 - least(7, greatest(‘‘||adj||‘‘ - 0, 0)))))), 7 - least(7, greatest(‘‘||adj||‘‘ - 0, 0)),‘‘‘‘a‘‘‘‘) || mgmt$mask_randomencode(nvl(substr(s.new_num, 1, 1 + least(7, greatest(‘‘||adj||‘‘ - 0, 0))), ‘‘‘‘0‘‘‘‘),1 + least(7, greatest(‘‘||adj||‘‘ - 0, 0))) AS VARCHAR2(10)) new_val from (select rownum rn , orig_val , lpad(rownum - 1, ‘‘||adj||‘‘ + 1, 0) new_num from (select distinct NAME orig_val from "TT"."MASK" order by dbms_random.value)) s where 1=1 ‘‘; DBMS_STATS.GATHER_TABLE_STATS(NULL, ‘‘"MGMT_DM_TT_15"‘‘, estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>DBMS_STATS.DEFAULT_DEGREE); end;
‘; EXCEPTION WHEN OTHERS THEN sqlerr_msg := SUBSTR(SQLERRM, 1, 100); mgmt$mask_errorExitOraError(‘ERROR executing steps ‘, sqlerr_msg); step_num := -1; return; END; END mgmt$step_1_14; /
CREATE OR REPLACE PROCEDURE mgmt$step_2_14(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER) AUTHID CURRENT_USER IS sqlerr_msg VARCHAR2(100); BEGIN IF step_num <> 2 THEN return; END IF;
mgmt$mask_setStep (14, ‘MGMT$MASK_CHECKPOINT‘, step_num); step_num := step_num + 1; BEGIN mgmt$mask_sendMsg (‘ALTER TABLE "TT"."MASK" RENAME TO "MASK$DMASK"‘); EXECUTE IMMEDIATE ‘ALTER TABLE "TT"."MASK" RENAME TO "MASK$DMASK"‘; EXCEPTION WHEN OTHERS THEN sqlerr_msg := SUBSTR(SQLERRM, 1, 100); mgmt$mask_errorExitOraError(‘ERROR executing steps ‘, sqlerr_msg); step_num := -1; return; END; END mgmt$step_2_14; /
CREATE OR REPLACE PROCEDURE mgmt$step_3_14(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER) AUTHID CURRENT_USER IS sqlerr_msg VARCHAR2(100); BEGIN IF step_num <> 3 THEN return; END IF;
mgmt$mask_setStep (14, ‘MGMT$MASK_CHECKPOINT‘, step_num); step_num := step_num + 1; BEGIN mgmt$mask_sendMsg (‘CREATE TABLE "TT"."MASK" TABLESPACE "USERS" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) NOLOGGING AS SELECT s."ID", c0m15.NEW_VAL "NAME" FROM "TT"."MASK$DMASK" s , MGMT_DM_TT_15 c0m15 WHERE s."NAME" = c0m15.ORIG_VAL(+) ‘); EXECUTE IMMEDIATE ‘CREATE TABLE "TT"."MASK" TABLESPACE "USERS" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) NOLOGGING AS SELECT s."ID", c0m15.NEW_VAL "NAME" FROM "TT"."MASK$DMASK" s , MGMT_DM_TT_15 c0m15 WHERE s."NAME" = c0m15.ORIG_VAL(+) ‘; EXCEPTION WHEN OTHERS THEN sqlerr_msg := SUBSTR(SQLERRM, 1, 100); mgmt$mask_errorExitOraError(‘ERROR executing steps ‘, sqlerr_msg); step_num := -1; return; END; END mgmt$step_3_14; /
CREATE OR REPLACE PROCEDURE mgmt$step_4_14(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER) AUTHID CURRENT_USER IS sqlerr_msg VARCHAR2(100); BEGIN IF step_num <> 4 THEN return; END IF;
mgmt$mask_setStep (14, ‘MGMT$MASK_CHECKPOINT‘, step_num); step_num := step_num + 1; BEGIN mgmt$mask_sendMsg (‘ALTER TABLE "TT"."MASK" LOGGING ‘); EXECUTE IMMEDIATE ‘ALTER TABLE "TT"."MASK" LOGGING ‘; EXCEPTION WHEN OTHERS THEN sqlerr_msg := SUBSTR(SQLERRM, 1, 100); mgmt$mask_errorExitOraError(‘ERROR executing steps ‘, sqlerr_msg); step_num := -1; return; END; END mgmt$step_4_14; /
CREATE OR REPLACE PROCEDURE mgmt$step_5_14(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER) AUTHID CURRENT_USER IS sqlerr_msg VARCHAR2(100); BEGIN IF step_num <> 5 THEN return; END IF;
mgmt$mask_setStep (14, ‘MGMT$MASK_CHECKPOINT‘, step_num); step_num := step_num + 1; BEGIN mgmt$mask_sendMsg (‘DROP TABLE "TT"."MASK$DMASK" PURGE‘); EXECUTE IMMEDIATE ‘DROP TABLE "TT"."MASK$DMASK" PURGE‘; EXCEPTION WHEN OTHERS THEN sqlerr_msg := SUBSTR(SQLERRM, 1, 100); mgmt$mask_errorExitOraError(‘ERROR executing steps ‘, sqlerr_msg); step_num := -1; return; END; END mgmt$step_5_14; /
CREATE OR REPLACE PROCEDURE mgmt$step_6_14(script_id IN INTEGER, job_table IN VARCHAR2, step_num IN OUT INTEGER) AUTHID CURRENT_USER IS sqlerr_msg VARCHAR2(100); BEGIN IF step_num <> 6 THEN return; END IF;
mgmt$mask_setStep (14, ‘MGMT$MASK_CHECKPOINT‘, step_num); step_num := step_num + 1; BEGIN mgmt$mask_sendMsg (‘BEGIN DBMS_STATS.GATHER_TABLE_STATS(‘‘"TT"‘‘, ‘‘"MASK"‘‘, estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, cascade=>TRUE); END;‘); EXECUTE IMMEDIATE ‘BEGIN DBMS_STATS.GATHER_TABLE_STATS(‘‘"TT"‘‘, ‘‘"MASK"‘‘, estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, cascade=>TRUE); END;‘; EXCEPTION WHEN OTHERS THEN sqlerr_msg := SUBSTR(SQLERRM, 1, 100); mgmt$mask_errorExitOraError(‘ERROR executing steps ‘, sqlerr_msg); step_num := -1; return; END; END mgmt$step_6_14; /
CREATE OR REPLACE PROCEDURE mgmt$mask_cleanup_14 (script_id IN INTEGER, job_table IN VARCHAR2, step_num IN INTEGER, highest_step IN INTEGER) AUTHID CURRENT_USER IS BEGIN IF step_num <= highest_step THEN return; END IF;
mgmt$mask_sendMsg (‘Starting cleanup of recovery tables‘);
mgmt$mask_deleteJobTableEntry(script_id, job_table, step_num, highest_step);
mgmt$mask_sendMsg (‘Completed cleanup of recovery tables‘); END mgmt$mask_cleanup_14; /
CREATE OR REPLACE PROCEDURE mgmt$mask_commentheader_14 IS BEGIN mgmt$mask_sendMsg (‘-- Target database: emrep‘); mgmt$mask_sendMsg (‘-- Script generated at: 24-MAR-2012 23:52‘); END mgmt$mask_commentheader_14; /
-- Script Execution Controller -- ==============================================
variable step_num number; exec mgmt$mask_commentheader_14; exec mgmt$mask_sendMsg (‘Starting Data Masking‘); exec mgmt$mask_sendMsg (‘Executing as user: ‘ || ‘system‘); exec mgmt$mask_checkDBAPrivs (‘system‘); exec mgmt$mask_setupJobTable (14, ‘MGMT$MASK_CHECKPOINT‘, :step_num);
exec mgmt$step_1_14(14, ‘MGMT$MASK_CHECKPOINT‘, :step_num); exec mgmt$step_2_14(14, ‘MGMT$MASK_CHECKPOINT‘, :step_num); exec mgmt$step_3_14(14, ‘MGMT$MASK_CHECKPOINT‘, :step_num); exec mgmt$step_4_14(14, ‘MGMT$MASK_CHECKPOINT‘, :step_num); exec mgmt$step_5_14(14, ‘MGMT$MASK_CHECKPOINT‘, :step_num); exec mgmt$step_6_14(14, ‘MGMT$MASK_CHECKPOINT‘, :step_num);
exec mgmt$mask_sendMsg (‘Completed Data Masking. Starting cleanup phase.‘);
exec mgmt$mask_cleanup_14 (14, ‘MGMT$MASK_CHECKPOINT‘, :step_num, 6);
exec mgmt$mask_sendMsg (‘Starting cleanup of generated procedures‘);
DROP PROCEDURE mgmt$step_1_14; DROP PROCEDURE mgmt$step_2_14; DROP PROCEDURE mgmt$step_3_14; DROP PROCEDURE mgmt$step_4_14; DROP PROCEDURE mgmt$step_5_14; DROP PROCEDURE mgmt$step_6_14;
DROP PROCEDURE mgmt$mask_cleanup_14; DROP PROCEDURE mgmt$mask_commentheader_14;
DROP TABLE MGMT_DM_TT_15;
exec mgmt$mask_sendMsg (‘Completed cleanup of generated procedures‘);
exec mgmt$mask_sendMsg (‘Script execution complete‘);
spool off set pagesize 24 set serveroutput off set feedback on set echo on |
排程有两种运行方式：立即执行和指定时间执行。
查看数据库中TT用户的mask表的原数据。
执行Job之后：
查看执行之后mask表的数据：
前后结果进行对比之后发现，mask表里面的name列数据发生变化，Data Masking就是通过生成脚本的方式将原有敏感数据，进行处理保证机密资料不被外泄,同时也满足开发者能获得和生产库相近的资料。
原文：https://www.cnblogs.com/buffercache/p/10836553.html