Data Masking安装和使用指南

Data Masking安装和使用指南

1. Data masking 简介

软件开发的最后阶段，是需要尽量真实的数据来作为基础测试软件的一系列功能。尤其是企业信息化系统，ERP、BI、EPM等这些大型系统实施或开发的时候，对于基础数据的要求很严格，很多时候都是直接克隆生产环境的数据来进行软件系统的测试，但是随之而来的影响却是深远的，生产数据中，首先它是一个真实的数据，透过数据基本上掌握了整个企业的资料。其次，在这当中包含很多敏感数据，不光是敏感数据，而且还是真实的敏感数据。如果在测试环境中发生了信息泄露问题，那么对于企业数据安全将造成致命的后果。

ORACLRE在OEM 10g之后提供Data Masking pack组件，解决从生产环境的数据向测试环境（或者开发环境）导入时可能会产生的数据内容安全问题，data masking特点是：按照指定的规则进行数据转换，可以通过规则控制最终生成的数据格式；维护数据表间的引用关系；通过EM图形界面来定义Data Masking，容易理解和使用。流程如下图所示:

 

2. 安装Data Masking Pack组件

         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 安装和配置指南。

3.    通过Grid Control使用Data Masking

1.     进入管理界面

使用SYSMAN用户登录 Oracle Enterprise Manger 10g（Grid Control）。

 

进入Grid Control主界面后，点击“Targets”

 

进入Targets管理界面，点击“Database”

 

选择要进行管理的数据库，如图，进入“emrep”（数据库名）进行配置。

 

进入要管理的数据库之后，点击“Administration”。

 

进入管理界面后，看见Data Masking pack组件选项，点击“Definition”进入Data Masking进行管理与配置。

 

2.     定义/配置Data Masking

1.      添加一个定义

1. 1.      选择需要mask的数据库，点击“Mask”，进入配置界面

 

 

1. 2.      选择要mask的表，以及对应的列，并对格式进行定义。

 

1. 3.      选择列转换规则（Array List，Fixed Number，Fixed string等转化规则，根据实际需求选择）

 

注：对定义TT用户的Mask表的列选择“随机字符串”转化规则

 

 

注：指定随机字符串的长度

 

2.      生成列转化规则脚本

 

脚本的内容为：

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

3.      生成排程计划

排程有两种运行方式：立即执行和指定时间执行。

 

3.     数据格式转化

查看数据库中TT用户的mask表的原数据。

 

执行Job之后：

 

查看执行之后mask表的数据：

 

前后结果进行对比之后发现，mask表里面的name列数据发生变化，Data Masking就是通过生成脚本的方式将原有敏感数据，进行处理保证机密资料不被外泄,同时也满足开发者能获得和生产库相近的资料。