Oracle Data Redaction与Data Pump

如果表定义了Redaction Policy，导出时数据会脱敏吗？本文解答这个问题。

按照Oracle文档Advanced Security Guide第13章，13.6.5的Tutorial，假设表HR.jobs定义了Redaction Policy。

假设HR用户被授予了访问目录对象的权限：

grant read, write on directory data_pump_dir to hr;

此时导出出错：

$ expdp hr/Welcome1@orclpdb1 tables=jobs directory=data_pump_dirExport: Release 19.0.0.0.0 - Production on Fri Aug 11 16:04: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
Starting "HR"."SYS_EXPORT_TABLE_01":  hr/********@orclpdb1 tables=jobs directory=data_pump_dir
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-31693: Table data object "HR"."JOBS" failed to load/unload and is being skipped due to error:
ORA-28081: Insufficient privileges - the command references a redacted object.Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:/opt/oracle/admin/ORCLCDB/dpdump/028F128E6BA24783E0630101007F820F/expdat.dmp
Job "HR"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at Fri Aug 11 16:04:25 2023 elapsed 0 00:00:24

其中的关键错误信息为：

ORA-28081: Insufficient privileges - the command references a redacted object.

如果你用sys用户导出，就不会有错，因为SYS用户又豁免Data Redaction的权限。

我们如果赋予HR用户相应的权限，

grant DATAPUMP_EXP_FULL_DATABASE to hr;

然后导出成功：

$ expdp hr/Welcome1@orclpdb1 tables=jobs directory=data_pump_dirExport: Release 19.0.0.0.0 - Production on Fri Aug 11 16:22:25 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
Starting "HR"."SYS_EXPORT_TABLE_01":  hr/********@orclpdb1 tables=jobs directory=data_pump_dir
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/RADM_POLICY
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "HR"."JOBS"                                 7.109 KB      19 rows
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:/opt/oracle/admin/ORCLCDB/dpdump/028F128E6BA24783E0630101007F820F/expdat.dmp
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at Fri Aug 11 16:22:50 2023 elapsed 0 00:00:23

总之，Data Redaction不是一个物理脱敏的方案，物理脱敏还需要靠DMS（Data Masking and Subsetting）。

那么，结论就是：

• 对于启用了Redaction 策略的表，数据泵导出时数据不会脱敏
• 但是会把策略也一并导出，然后在目标库可以用impdp导入。

实验做完了。收回hr的权限：

revoke read, write on directory data_pump_dir from hr;
revoke DATAPUMP_EXP_FULL_DATABASE from hr;

参考

• 13.6.5 Tutorial: Creating and Sharing a Named Data Redaction Policy Expression
• Data Redaction and Data Pump in #Oracle 12c
• Data Redaction Error While Doing An Expdp: ORA-28081: Insufficient privileges - the command references a redacted object. (Doc ID 1631729.1)
• Some examples of implementing Data Redaction using DBMS_REDACT (Doc ID 1588270.1)