深圳微商城网站设计多少钱,个人网站域名选择,宿迁房产网上备案查询,网站个人简介怎么做如果表定义了Redaction Policy#xff0c;导出时数据会脱敏吗#xff1f;本文解答这个问题。
按照Oracle文档Advanced Security Guide第13章#xff0c;13.6.5的Tutorial#xff0c;假设表HR.jobs定义了Redaction Policy。
假设HR用户被授予了访问目录对象的权限#xf…如果表定义了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/Welcome1orclpdb1 tablesjobs directorydata_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 tablesjobs directorydata_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/Welcome1orclpdb1 tablesjobs directorydata_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 tablesjobs directorydata_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不是一个物理脱敏的方案物理脱敏还需要靠DMSData 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 ExpressionData Redaction and Data Pump in #Oracle 12cData 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)
