透明数据加密与Data Pump的结合

2023-11-23 13:12
文章标签 数据 加密 结合 data 透明 pump

本文主要是介绍透明数据加密与Data Pump的结合，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

我有2套数据库环境：

  • 源数据库为19c，users表空间已加密
  • 目标数据库为11g，表空间已加密

我需要迁移源数据库users表空间上的employees表到目标数据库上的加密表空间。

源数据库上表的导出。为简化实验，我只导出数据，而不包含索引，约束等：

$ expdp system@orclpdb1 tables=hr.employees content=data_onlyExport: Release 19.0.0.0.0 - Production on Tue Nov 21 11:17:01 2023
Version 19.20.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Password:Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/********@orclpdb1 tables=hr.employees content=data_only
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . exported "HR"."EMPLOYEES"                            17.08 KB     107 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:/u01/app/oracle/admin/ORCL/dpdump/079124B6FE41560CE06500001703C3BC/expdat.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Tue Nov 21 11:17:07 2023 elapsed 0 00:00:03

输出信息中，可以看到ORA-39173，表示加密表在导出时被解密了。

如果我们关闭key store，导出会报错。这也从侧面说明了数据泵导出时需要解密数据。

$ expdp system@orclpdb1 tables=hr.employees content=data_onlyExport: Release 19.0.0.0.0 - Production on Tue Nov 21 11:38:43 2023
Version 19.20.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Password:Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/********@orclpdb1 tables=hr.employees content=data_only
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "HR"."EMPLOYEES" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-28365: wallet is not openORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:/u01/app/oracle/admin/ORCL/dpdump/079124B6FE41560CE06500001703C3BC/expdat.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at Tue Nov 21 11:38:52 2023 elapsed 0 00:00:07

注意输出中的ORA-28365报错。

如果想对数据泵导出的内容加密，那么就必须利用数据泵的透明数据加密功能。

从expdp的帮助中可以查询到加密相关的选项：

$ expdp help=y
...
ENCRYPTION
Encrypt part or all of a dump file.
Valid keyword values are: ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY and NONE.ENCRYPTION_ALGORITHM
Specify how encryption should be done.
Valid keyword values are: [AES128], AES192 and AES256.ENCRYPTION_MODE
Method of generating encryption key.
Valid keyword values are: DUAL, PASSWORD and [TRANSPARENT].ENCRYPTION_PASSWORD
Password key for creating encrypted data within a dump file.ENCRYPTION_PWD_PROMPT
Specifies whether to prompt for the encryption password [NO].
Terminal echo will be suppressed while standard input is read.
...

使用口令加密，这是最简单的方式：

$ expdp system@orclpdb1 tables=hr.employees content=data_only encryption_password=Welcome1Export: Release 19.0.0.0.0 - Production on Tue Nov 21 11:48:25 2023
Version 19.20.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Password:Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/********@orclpdb1 tables=hr.employees content=data_only encryption_password=********
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . exported "HR"."EMPLOYEES"                            17.09 KB     107 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:/u01/app/oracle/admin/ORCL/dpdump/079124B6FE41560CE06500001703C3BC/expdat.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Tue Nov 21 11:48:30 2023 elapsed 0 00:00:03

如果觉得在命令行中指定口令不安全，也可以交互式的指定加密口令，下面的命令与上面的命令是等效的：

$ expdp system@orclpdb1 tables=hr.employees content=data_only encryption_pwd_prompt=yesExport: Release 19.0.0.0.0 - Production on Tue Nov 21 11:52:00 2023
Version 19.20.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Password:Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionEncryption Password: <在这里输入加密口令，但屏幕上不会显示>
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/********@orclpdb1 tables=hr.employees content=data_only encryption_pwd_prompt=yes
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . exported "HR"."EMPLOYEES"                            17.09 KB     107 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:/u01/app/oracle/admin/ORCL/dpdump/079124B6FE41560CE06500001703C3BC/expdat.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Tue Nov 21 11:52:10 2023 elapsed 0 00:00:08

通过加密，我们已经无法从文件中发现隐私数据：

$ strings noenc.dmp |grep -i nancy
Nancy   Greenberg$ strings pwdenc.dmp |grep -i nancy

准确的说，Oracle是利用提供的口令生成加密秘钥，然后对数据泵文件进行加密的。

如果不想指定口令，也可以利用从master key生成的秘钥来加密数据：

$ expdp system@orclpdb1 tables=hr.employees content=data_only encryption=all encryption_mode=transparentExport: Release 19.0.0.0.0 - Production on Tue Nov 21 12:07:13 2023
Version 19.20.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Password:Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/********@orclpdb1 tables=hr.employees content=data_only encryption=all encryption_mode=transparent
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . exported "HR"."EMPLOYEES"                            17.09 KB     107 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:/u01/app/oracle/admin/ORCL/dpdump/079124B6FE41560CE06500001703C3BC/expdat.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Tue Nov 21 12:07:19 2023 elapsed 0 00:00:02

比较令人迷惑的是ENCRYPTION_MODE的DUAL选项。

$ expdp system@orclpdb1 tables=hr.employees content=data_only encryption=all encryption_mode=dualExport: Release 19.0.0.0.0 - Production on Tue Nov 21 13:26:38 2023
Version 19.20.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Password:Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39002: invalid operation
ORA-39174: Encryption password must be supplied.

DUAL选项必须和加密口令联合用：

$ expdp system@orclpdb1 tables=hr.employees content=data_only encryption=all encryption_mode=dual encryption_password=abcExport: Release 19.0.0.0.0 - Production on Tue Nov 21 13:28:01 2023
Version 19.20.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Password:Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/********@orclpdb1 tables=hr.employees content=data_only encryption=all encryption_mode=dual encryption_password=********
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . exported "HR"."EMPLOYEES"                            17.09 KB     107 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:/u01/app/oracle/admin/ORCL/dpdump/079124B6FE41560CE06500001703C3BC/expdat.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Tue Nov 21 13:28:06 2023 elapsed 0 00:00:02

数据泵文件的导入

impdp和加密相关的选项就简单多了：

$ impdp help=y
...
ENCRYPTION_PASSWORD
Password key for accessing encrypted data within a dump file.
Not valid for network import jobs.ENCRYPTION_PWD_PROMPT
Specifies whether to prompt for the encryption password [NO].
Terminal echo is suppressed while standard input is read.
...

那么现在就有一个问题了，如果导出的数据泵文件是用master key生成的秘钥加密的，那么这个秘钥会内嵌在导出文件中吗？

接下来我们做导入测试，导入的目标数据库尚未启用表空间加密。之前导出的文件如下：

数据泵文件名说明
dualenc.dmpDUAL模式导出，利用master key加密，并提供口令
mekenc.dmp透明模式导出，利用master key加密
noenc.dmp默认导出，无加密
pwdenc.dmpPASSWORD模式导出，口令加密

把这些文件放到默认的dump目录下：

cp * /opt/oracle/admin/ORCLCDB/dpdump/073FB7B7E0274E22E0630101007F82AF/

由于只导出了数据，因此每次导入前都执行以下命令，创建目标表：

drop table emp purge;
create table emp as select * from employees where 1=2;

noenc的导入没有悬念，成功了：

$ impdp system/Welcome1@orclpdb1 remap_table=employees:emp dumpfile=noenc.dmpImport: Release 19.0.0.0.0 - Production on Wed Nov 22 09:05:36 2023
Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@orclpdb1 remap_table=employees:emp dumpfile=noenc.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."EMP"                                  17.08 KB     107 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Wed Nov 22 09:05:42 2023 elapsed 0 00:00:05

pwdenc的导入。第一次失败了，因为文件是加密的，而命令行中又未指定口令，因此impdp试图从wallet中寻找秘钥，但未遂。

$ impdp system/Welcome1@orclpdb1 remap_table=employees:emp dumpfile=pwdenc.dmpImport: Release 19.0.0.0.0 - Production on Wed Nov 22 09:07:30 2023
Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39002: invalid operation
ORA-39189: unable to decrypt dump file set
ORA-28365: wallet is not open

提供口令就成功了：

$ impdp system/Welcome1@orclpdb1 remap_table=employees:emp dumpfile=pwdenc.dmp encryption_password=Welcome1Import: Release 19.0.0.0.0 - Production on Wed Nov 22 09:18:28 2023
Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@orclpdb1 remap_table=employees:emp dumpfile=pwdenc.dmp encryption_password=********
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."EMP"                                  17.09 KB     107 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Wed Nov 22 09:18:31 2023 elapsed 0 00:00:02

当然，此处提供的口令必须与之前的相符，否则也会报错：

$ impdp system/Welcome1@orclpdb1 remap_table=employees:emp dumpfile=pwdenc.dmp encryption_password=abcImport: Release 19.0.0.0.0 - Production on Wed Nov 22 09:18:11 2023
Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39002: invalid operation
ORA-39189: unable to decrypt dump file set
ORA-28365: wallet is not open

mekenc的导入，开始有点迷惑。由于目标数据库尚未配置wallet，现在肯定会失败。

$ impdp system/Welcome1@orclpdb1 remap_table=employees:emp dumpfile=mekenc.dmpImport: Release 19.0.0.0.0 - Production on Wed Nov 22 09:20:38 2023
Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39002: invalid operation
ORA-39189: unable to decrypt dump file set
ORA-28365: wallet is not open

dualenc的导入。如果提供口令，肯定会成功：

$ impdp system/Welcome1@orclpdb1 remap_table=employees:emp dumpfile=dualenc.dmp encryption_password=abcImport: Release 19.0.0.0.0 - Production on Wed Nov 22 09:22:01 2023
Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@orclpdb1 remap_table=employees:emp dumpfile=dualenc.dmp encryption_password=********
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."EMP"                                  17.09 KB     107 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Wed Nov 22 09:22:04 2023 elapsed 0 00:00:02

如果不提供口令，肯定会失败：

$ impdp system/Welcome1@orclpdb1 remap_table=employees:emp dumpfile=dualenc.dmpImport: Release 19.0.0.0.0 - Production on Wed Nov 22 09:23:43 2023
Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39002: invalid operation
ORA-39189: unable to decrypt dump file set
ORA-28365: wallet is not open

接下来，为目标数据库配置wallet（但不配置表空间加密），将源数据库中的master key导入。之前失败的几个场景就应该都可以成功了。

导出和导入master key的过程略。

果然成功了：

$ impdp system/Welcome1@orclpdb1 remap_table=employees:emp dumpfile=mekenc.dmpImport: Release 19.0.0.0.0 - Production on Wed Nov 22 13:24:29 2023
Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@orclpdb1 remap_table=employees:emp dumpfile=mekenc.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."EMP"                                  17.09 KB     107 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Wed Nov 22 13:24:32 2023 elapsed 0 00:00:02$ impdp system/Welcome1@orclpdb1 remap_table=employees:emp dumpfile=dualenc.dmpImport: Release 19.0.0.0.0 - Production on Wed Nov 22 13:26:09 2023
Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@orclpdb1 remap_table=employees:emp dumpfile=dualenc.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."EMP"                                  17.09 KB     107 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Wed Nov 22 13:26:12 2023 elapsed 0 00:00:02

参考

  • Oracle Data Pump Encrypted Dump File Support 11g 版本
  • 8.1 How Transparent Data Encryption Works with Export and Import Operations

这篇关于透明数据加密与Data Pump的结合的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/418195。

相关文章

SQL Server修改数据库名及物理数据文件名操作步骤

SQL Server修改数据库名及物理数据文件名操作步骤

《SQLServer修改数据库名及物理数据文件名操作步骤》在SQLServer中重命名数据库是一个常见的操作,但需要确保用户具有足够的权限来执行此操作,：本文主要介绍SQLServer修改数据... 目录一、背景介绍二、操作步骤2.1 设置为单用户模式（断开连接）2.2 修改数据库名称2.3 查找逻辑文件名
阅读更多...
canal实现mysql数据同步的详细过程

canal实现mysql数据同步的详细过程

《canal实现mysql数据同步的详细过程》：本文主要介绍canal实现mysql数据同步的详细过程,本文通过实例图文相结合给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的... 目录1、canal下载2、mysql同步用户创建和授权3、canal admin安装和启动4、canal
阅读更多...
使用SpringBoot整合Sharding Sphere实现数据脱敏的示例

使用SpringBoot整合Sharding Sphere实现数据脱敏的示例

《使用SpringBoot整合ShardingSphere实现数据脱敏的示例》ApacheShardingSphere数据脱敏模块,通过SQL拦截与改写实现敏感信息加密存储,解决手动处理繁琐及系统改... 目录痛点一：痛点二：脱敏配置Quick Start——Spring 显示配置：1.引入依赖2.创建脱敏
阅读更多...
详解如何使用Python构建从数据到文档的自动化工作流

详解如何使用Python构建从数据到文档的自动化工作流

《详解如何使用Python构建从数据到文档的自动化工作流》这篇文章将通过真实工作场景拆解,为大家展示如何用Python构建自动化工作流,让工具代替人力完成这些数字苦力活,感兴趣的小伙伴可以跟随小编一起... 目录一、Excel处理：从数据搬运工到智能分析师二、PDF处理：文档工厂的智能生产线三、邮件自动化：
阅读更多...
Python数据分析与可视化的全面指南(从数据清洗到图表呈现)

Python数据分析与可视化的全面指南(从数据清洗到图表呈现)

《Python数据分析与可视化的全面指南(从数据清洗到图表呈现)》Python是数据分析与可视化领域中最受欢迎的编程语言之一,凭借其丰富的库和工具,Python能够帮助我们快速处理、分析数据并生成高质... 目录一、数据采集与初步探索二、数据清洗的七种武器1. 缺失值处理策略2. 异常值检测与修正3. 数据
阅读更多...
pandas实现数据concat拼接的示例代码

pandas实现数据concat拼接的示例代码

《pandas实现数据concat拼接的示例代码》pandas.concat用于合并DataFrame或Series,本文主要介绍了pandas实现数据concat拼接的示例代码,具有一定的参考价值,... 目录语法示例：使用pandas.concat合并数据默认的concat：参数axis=0，join=
阅读更多...
C#代码实现解析WTGPS和BD数据

C#代码实现解析WTGPS和BD数据

《C#代码实现解析WTGPS和BD数据》在现代的导航与定位应用中,准确解析GPS和北斗（BD）等卫星定位数据至关重要,本文将使用C#语言实现解析WTGPS和BD数据,需要的可以了解下... 目录一、代码结构概览1. 核心解析方法2. 位置信息解析3. 经纬度转换方法4. 日期和时间戳解析5. 辅助方法二、L
阅读更多...
使用Python和Matplotlib实现可视化字体轮廓(从路径数据到矢量图形)

使用Python和Matplotlib实现可视化字体轮廓(从路径数据到矢量图形)

《使用Python和Matplotlib实现可视化字体轮廓(从路径数据到矢量图形)》字体设计和矢量图形处理是编程中一个有趣且实用的领域,通过Python的matplotlib库,我们可以轻松将字体轮廓... 目录背景知识字体轮廓的表示实现步骤1. 安装依赖库2. 准备数据3. 解析路径指令4. 绘制图形关键
阅读更多...
解决mysql插入数据锁等待超时报错:Lock wait timeout exceeded;try restarting transaction

解决mysql插入数据锁等待超时报错:Lock wait timeout exceeded;try restarting transaction

《解决mysql插入数据锁等待超时报错:Lockwaittimeoutexceeded;tryrestartingtransaction》：本文主要介绍解决mysql插入数据锁等待超时报... 目录报错信息解决办法1、数据库中执行如下sql2、再到 INNODB_TRX 事务表中查看总结报错信息Lock
阅读更多...
在Spring Boot中实现HTTPS加密通信及常见问题排查

在Spring Boot中实现HTTPS加密通信及常见问题排查

《在SpringBoot中实现HTTPS加密通信及常见问题排查》HTTPS是HTTP的安全版本,通过SSL/TLS协议为通讯提供加密、身份验证和数据完整性保护,下面通过本文给大家介绍在SpringB... 目录一、HTTPS核心原理1.加密流程概述2.加密技术组合二、证书体系详解1、证书类型对比2. 证书获
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2