Oracle 12c Cloud Control中的数据掩码(Data Masking)功能实验日志

最新推荐文章于 2024-09-04 07:36:08 发布

cuanmi4326

最新推荐文章于 2024-09-04 07:36:08 发布

阅读量452

点赞数

Data Masking with Oracle Enterprise Manager 12c Cloud Control

实验环境：

Cloud Control Version: 12.1.0.4.0

Database Version:11.2.0.4 + RAC

1.创建测试数据表,我将分别对表中的几列(日期,数字,字符类型)进行掩码实验

create table tt.employees

(employee_id int primary key,
employee_name varchar2(20) not null,
email varchar2(50),
phone_number varchar2(20),
hire_date date,
salary int,
department_id int);

alter session set nls_date_format='yyyy-mm-dd';

insert into tt.employees values (1,'Mike','mike@atos.net','13889876543','1969-03-15',21000,10);

insert into tt.employees values (2,'Jack','jack@atos.net','13938135432','1973-04-26',19000,10);

insert into tt.employees values (3,'Helen','helen@atos.net','18640113245','1933-01-06',13000,10);

commit;

2.安装DM_FMTLIB掩码格式包(系统自带的格式包，如果不用可以不装)

Locate the following scripts in your Enterprise Manager installation:

$ORACLE_HOME/sysman/admin/emdrep/sql/db/latest/masking/dm_fmtlib_pkgdef.sql
$ORACLE_HOME/sysman/admin/emdrep/sql/db/latest/masking/dm_fmtlib_pkgbody.sql

Copy these scripts to a directory in your target database installation and execute them using SQL*Plus, connected as a user that can create packages in the DBSNMP schema.

3. View query result.

TT@BWC2(dm02db02)> select owner,object_type,object_name from dba_objects where object_name='DM_FMTLIB';

OWNER OBJECT_TYPE OBJECT_NAME
------------------------------ ------------------- ------------------------------------------------------------------------------------------------------------DBSNMP PACKAGE DM_FMTLIB
DBSNMP PACKAGE BODY DM_FMTLIB

2 rows selected.

3.登录12c Cloud Control,创建应用数据模型,在菜单项中选择Enterprise-->Quality Management--->Application Data Models---->Create

上面的步骤就创建完了应用数据模型，接下来进入数据掩码定义阶段

在这里需要定义每列的掩码格式：

定义好之后的状态，具体的列定义格式在本文的后面可以看到。

定义好列格式之后就可以生成脚本了

这里有两个选项In-Place Masking和At-Source Masking，显示如果你已经是在测试环境操作则选择in-place选项直接掩码表中的数据，但如果是在生产环境下可以选择at-source选项，后续可以通过数据泵工具导入掩码数据。这里我选择的是at-source，后面会有in-place的演示。

生成完脚本之后，点击Schedule Job按钮如下：

可以点击查看job运行情况

这时带有掩码的数据表已经导出了。

4.在测试环境下导入经过掩码的数据，这里我就直接将带有掩码的表重新命名为test_employees

[oracle@dm02db02 bwc]$ impdp tt/tt directory=DUMP_BWC dumpfile=EXPDAT01_TT.DMP logfile=IMPDATA01_TT.LOG remap_table=EMPLOYEES:test_employees tables=employees

Import: Release 11.2.0.4.0 - Production on Thu Jun 23 09:57:52 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "TT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TT"."SYS_IMPORT_TABLE_01": tt/******** directory=DUMP_BWC dumpfile=EXPDAT01_TT.DMP logfile=IMPDATA01_TT.LOG remap_table=EMPLOYEES:test_employees tables=employees
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TT"."TEST_EMPLOYEES" 7.617 KB 3 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "TT"."SYS_IMPORT_TABLE_01" successfully completed at Thu Jun 23 09:58:06 2016 elapsed 0 00:00:02

5.验证原始表和掩码表中的数据

SYS@BWC1(dm02db01)> select * from tt.test_employees;

EMPLOYEE_ID EMPLOYEE_NAME EMAIL PHONE_NUMBER HIRE_DATE SALARY DEPARTMENT_ID
----------- -------------------- -------------------------------------------------- -------------------- ------------------- ---------- -------------
1 Abbott Abner@atos.net 9406212140 1999-12-23 17:17:30 3870 10
2 Jack Adolph@atos.net 5642840231 1998-07-07 21:52:41 19901 10
3 Tonny Abraham@atos.net 8625934032 1987-01-12 16:47:22 11392 10

SYS@BWC1(dm02db01)> select * from tt.employees;

EMPLOYEE_ID EMPLOYEE_NAME EMAIL PHONE_NUMBER HIRE_DATE SALARY DEPARTMENT_ID
----------- -------------------- -------------------------------------------------- -------------------- ------------------- ---------- -------------
1 Mike mike@atos.net 13889876543 1969-03-15 00:00:00 21000 10
2 Jack jack@atos.net 13938135432 1973-04-26 00:00:00 19000 10
3 Helen helen@atos.net 18640113245 1933-01-06 00:00:00 13000 10

6.如果已经是测试环境的话，可以直接操作表中数据，我将表中的行记录数扩展至10W行后，再测试下执行速度

SYS@BWC1(dm02db01)> begin
2 for i in 5..100000 loop
3 insert into tt.employees values (i,'qqqq'||i,'xxx@ss.com','18640128331','1982-04-26',10000,15);
4 end loop;
5 commit;
6 end;
7 /

PL/SQL procedure successfully completed.

SYS@BWC1(dm02db01)> select count(1) from tt.employees;

COUNT(1)
----------
100000

在Data Masking Definitions页面点击Generate Script,选择In-Place Masking

job执行失败了，日志显示Not connected.

7.虽然job失败了，但是生成掩码的sql脚本却成生了，我们也可以手动执行生成的sql脚本。

[oracle@dm02db02 dbs]$ ls

-rw-r----- 1 oracle oinstall 160553 Jun 23 13:40 masking119.sql
-rw-r----- 1 oracle oinstall 10157 Jun 23 13:40 masking113.log
[oracle@dm02db02 dbs]$

SYS@BWC2(dm02db02)> @masking119.sql

TT@BWC2(dm02db02)> select * from employees where rownum<20;

EMPLOYEE_ID EMPLOYEE_NAME EMAIL PHONE_NUMBER HIRE_DATE SALARY DEPARTMENT_ID
----------- -------------------- -------------------------------------------------- -------------------- --------- ---------- -------------
3 Philip Jack@atos.net 4142485680 08-JUL-74 12810 10
1 Addison Adair@atos.net 4020557081 06-DEC-86 8401 10
2 Luke Abraham@atos.net 5010168142 25-MAR-82 7232 10
431 Adolph Tonny@atos.net 9048471411 22-APR-80 3991 15
1422 Philip Tonny@atos.net 9048471411 22-APR-80 3991 15
19395 Addison Tonny@atos.net 9048471411 22-APR-80 3991 15
20090 Mike Tonny@atos.net 9048471411 22-APR-80 3991 15
20089 Adonis Tonny@atos.net 9048471411 22-APR-80 3991 15
20088 Abner Tonny@atos.net 9048471411 22-APR-80 3991 15
20087 Andy Tonny@atos.net 9048471411 22-APR-80 3991 15
20086 Abbott Tonny@atos.net 9048471411 22-APR-80 3991 15
20085 Andy Tonny@atos.net 9048471411 22-APR-80 3991 15
20084 Mike Tonny@atos.net 9048471411 22-APR-80 3991 15
20083 Abbott Tonny@atos.net 9048471411 22-APR-80 3991 15
20082 Abner Tonny@atos.net 9048471411 22-APR-80 3991 15
20081 Abner Tonny@atos.net 9048471411 22-APR-80 3991 15
20080 Adam Tonny@atos.net 9048471411 22-APR-80 3991 15
20079 Mike Tonny@atos.net 9048471411 22-APR-80 3991 15
20078 Adam Tonny@atos.net 9048471411 22-APR-80 3991 15

19 rows selected.

提示：对于一个10W行的表在运行sql脚本时，还是比较快的，这里还必须注意的是，Data Masking可能是根据列值进行hash算法算出的掩码值，如果列的值是相同的，在跑过sql脚本以后，这些列的值既然被掩盖了，但列的值仍然是相同的。

--每列中的格式定义如下：

email字段掩码格式由2部分组成，第一部分是固定名字的数组，第二部分是固定字符串。