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

相关文章

MyBatis-Plus通用中等、大量数据分批查询和处理方法

MyBatis-Plus通用中等、大量数据分批查询和处理方法

《MyBatis-Plus通用中等、大量数据分批查询和处理方法》文章介绍MyBatis-Plus分页查询处理,通过函数式接口与Lambda表达式实现通用逻辑,方法抽象但功能强大,建议扩展分批处理及流式... 目录函数式接口获取分页数据接口数据处理接口通用逻辑工具类使用方法简单查询自定义查询方法总结函数式接口
阅读更多...
Spring Boot 结合 WxJava 实现文章上传微信公众号草稿箱与群发

Spring Boot 结合 WxJava 实现文章上传微信公众号草稿箱与群发

《SpringBoot结合WxJava实现文章上传微信公众号草稿箱与群发》本文将详细介绍如何使用SpringBoot框架结合WxJava开发工具包,实现文章上传到微信公众号草稿箱以及群发功能,... 目录一、项目环境准备1.1 开发环境1.2 微信公众号准备二、Spring Boot 项目搭建2.1 创建
阅读更多...
nginx -t、nginx -s stop 和 nginx -s reload 命令的详细解析(结合应用场景)

nginx -t、nginx -s stop 和 nginx -s reload 命令的详细解析(结合应用场景)

《nginx-t、nginx-sstop和nginx-sreload命令的详细解析(结合应用场景)》本文解析Nginx的-t、-sstop、-sreload命令,分别用于配置语法检... 以下是关于 nginx -t、nginx -s stop 和 nginx -s reload 命令的详细解析，结合实际应
阅读更多...
SpringBoot结合Docker进行容器化处理指南

SpringBoot结合Docker进行容器化处理指南

《SpringBoot结合Docker进行容器化处理指南》在当今快速发展的软件工程领域,SpringBoot和Docker已经成为现代Java开发者的必备工具,本文将深入讲解如何将一个SpringBo... 目录前言一、为什么选择 Spring Bootjavascript + docker1. 快速部署与
阅读更多...
SQL中如何添加数据(常见方法及示例)

SQL中如何添加数据(常见方法及示例)

《SQL中如何添加数据(常见方法及示例)》SQL全称为StructuredQueryLanguage,是一种用于管理关系数据库的标准编程语言,下面给大家介绍SQL中如何添加数据,感兴趣的朋友一起看看吧... 目录在mysql中，有多种方法可以添加数据。以下是一些常见的方法及其示例。1. 使用INSERT I
阅读更多...
Python使用vllm处理多模态数据的预处理技巧

Python使用vllm处理多模态数据的预处理技巧

《Python使用vllm处理多模态数据的预处理技巧》本文深入探讨了在Python环境下使用vLLM处理多模态数据的预处理技巧,我们将从基础概念出发,详细讲解文本、图像、音频等多模态数据的预处理方法,... 目录1. 背景介绍1.1 目的和范围1.2 预期读者1.3 文档结构概述1.4 术语表1.4.1 核
阅读更多...
SpringBoot中SM2公钥加密、私钥解密的实现示例详解

SpringBoot中SM2公钥加密、私钥解密的实现示例详解

《SpringBoot中SM2公钥加密、私钥解密的实现示例详解》本文介绍了如何在SpringBoot项目中实现SM2公钥加密和私钥解密的功能,通过使用Hutool库和BouncyCastle依赖,简化... 目录一、前言1、加密信息(示例)2、加密结果(示例)二、实现代码1、yml文件配置2、创建SM2工具
阅读更多...
MySQL 删除数据详解(最新整理)

MySQL 删除数据详解(最新整理)

《MySQL删除数据详解(最新整理)》：本文主要介绍MySQL删除数据的相关知识,本文通过实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录一、前言二、mysql 中的三种删除方式1.DELETE语句✅ 基本语法： 示例：2.TRUNCATE语句✅ 基本语
阅读更多...
Python中re模块结合正则表达式的实际应用案例

Python中re模块结合正则表达式的实际应用案例

《Python中re模块结合正则表达式的实际应用案例》Python中的re模块是用于处理正则表达式的强大工具,正则表达式是一种用来匹配字符串的模式,它可以在文本中搜索和匹配特定的字符串模式,这篇文章主... 目录前言re模块常用函数一、查看文本中是否包含 A 或 B 字符串二、替换多个关键词为统一格式三、提
阅读更多...
MyBatisPlus如何优化千万级数据的CRUD

MyBatisPlus如何优化千万级数据的CRUD

《MyBatisPlus如何优化千万级数据的CRUD》最近负责的一个项目,数据库表量级破千万,每次执行CRUD都像走钢丝,稍有不慎就引起数据库报警,本文就结合这个项目的实战经验,聊聊MyBatisPl... 目录背景一、MyBATis Plus 简介二、千万级数据的挑战三、优化 CRUD 的关键策略1. 查
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2