如果表定義了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)