透明数据加密与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。

相关文章

SpringBoot分段处理List集合多线程批量插入数据方式

SpringBoot分段处理List集合多线程批量插入数据方式

《SpringBoot分段处理List集合多线程批量插入数据方式》文章介绍如何处理大数据量List批量插入数据库的优化方案：通过拆分List并分配独立线程处理,结合Spring线程池与异步方法提升效率... 目录项目场景解决方案1.实体类2.Mapper3.spring容器注入线程池bejsan对象4.创建
阅读更多...
PHP轻松处理千万行数据的方法详解

PHP轻松处理千万行数据的方法详解

《PHP轻松处理千万行数据的方法详解》说到处理大数据集,PHP通常不是第一个想到的语言,但如果你曾经需要处理数百万行数据而不让服务器崩溃或内存耗尽,你就会知道PHP用对了工具有多强大,下面小编就... 目录问题的本质php 中的数据流处理：为什么必不可少生成器：内存高效的迭代方式流量控制：避免系统过载一次性
阅读更多...
C#实现千万数据秒级导入的代码

C#实现千万数据秒级导入的代码

《C#实现千万数据秒级导入的代码》在实际开发中excel导入很常见,现代社会中很容易遇到大数据处理业务,所以本文我就给大家分享一下千万数据秒级导入怎么实现,文中有详细的代码示例供大家参考,需要的朋友可... 目录前言一、数据存储二、处理逻辑优化前代码处理逻辑优化后的代码总结前言在实际开发中excel导入很
阅读更多...
Spring 中的切面与事务结合使用完整示例

Spring 中的切面与事务结合使用完整示例

《Spring中的切面与事务结合使用完整示例》本文给大家介绍Spring中的切面与事务结合使用完整示例,本文通过实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考... 目录 一、前置知识：Spring AOP 与 事务的关系 事务本质上就是一个“切面”二、核心组件三、完
阅读更多...
MyBatis-plus处理存储json数据过程

MyBatis-plus处理存储json数据过程

《MyBatis-plus处理存储json数据过程》文章介绍MyBatis-Plus3.4.21处理对象与集合的差异：对象可用内置Handler配合autoResultMap,集合需自定义处理器继承F... 目录1、如果是对象2、如果需要转换的是List集合总结对象和集合分两种情况处理，目前我用的MP的版本
阅读更多...
GSON框架下将百度天气JSON数据转JavaBean

GSON框架下将百度天气JSON数据转JavaBean

《GSON框架下将百度天气JSON数据转JavaBean》这篇文章主要为大家详细介绍了如何在GSON框架下实现将百度天气JSON数据转JavaBean,文中的示例代码讲解详细,感兴趣的小伙伴可以了解下... 目录前言一、百度天气jsON1、请求参数2、返回参数3、属性映射二、GSON属性映射实战1、类对象映
阅读更多...
C# LiteDB处理时间序列数据的高性能解决方案

C# LiteDB处理时间序列数据的高性能解决方案

《C#LiteDB处理时间序列数据的高性能解决方案》LiteDB作为.NET生态下的轻量级嵌入式NoSQL数据库,一直是时间序列处理的优选方案,本文将为大家大家简单介绍一下LiteDB处理时间序列数... 目录为什么选择LiteDB处理时间序列数据第一章：LiteDB时间序列数据模型设计1.1 核心设计原则
阅读更多...
Java+AI驱动实现PDF文件数据提取与解析

Java+AI驱动实现PDF文件数据提取与解析

《Java+AI驱动实现PDF文件数据提取与解析》本文将和大家分享一套基于AI的体检报告智能评估方案,详细介绍从PDF上传、内容提取到AI分析、数据存储的全流程自动化实现方法,感兴趣的可以了解下... 目录一、核心流程：从上传到评估的完整链路二、第一步：解析 PDF，提取体检报告内容1. 引入依赖2. 封装
阅读更多...
MySQL中查询和展示LONGBLOB类型数据的技巧总结

MySQL中查询和展示LONGBLOB类型数据的技巧总结

《MySQL中查询和展示LONGBLOB类型数据的技巧总结》在MySQL中LONGBLOB是一种二进制大对象（BLOB）数据类型,用于存储大量的二进制数据,：本文主要介绍MySQL中查询和展示LO... 目录前言1. 查询 LONGBLOB 数据的大小2. 查询并展示 LONGBLOB 数据2.1 转换为十
阅读更多...
使用SpringBoot+InfluxDB实现高效数据存储与查询

使用SpringBoot+InfluxDB实现高效数据存储与查询

《使用SpringBoot+InfluxDB实现高效数据存储与查询》InfluxDB是一个开源的时间序列数据库,特别适合处理带有时间戳的监控数据、指标数据等,下面详细介绍如何在SpringBoot项目... 目录1、项目介绍2、 InfluxDB 介绍3、Spring Boot 配置 InfluxDB4、I
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2