DATAPUMP 암호화

DATAPUMP 사용시 ENCRYPTION 옵션 사용을 통하여 암호화를 진행 할 수 있다.

ENCRYPTION 의 옵션은 ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY and NONE 이 있으며,

ENCRYPTION_ALGORITHM 은 AES128(기본 값), AES192 and AES256 이 있다.

 

아래는 test_20170119 테이블을 ENCRYPTION 옵션으로 export 하는 예제이다.

expdp \" / as sysdba\" dumpfile=MST_1st.dmp logfile=MST_1st.log directory=EXP_HOON tables=\(hoon.test_20170119\) ENCRYPTION=DATA_ONLY ENCRYPTION_MODE=PASSWORD ENCRYPTION_PASSWORD=tyvld Export: Release 11.2.0.2.0 - Production on Thu Jan 19 11:32:16 2017 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" dumpfile=MST_1st.dmp logfile=MST_1st.log directory=EXP_HOON tables=(hoon.test_20170119) ENCRYPTION=DATA_ONLY ENCRYPTION_MODE=PASSWORD ENCRYPTION_PASSWORD=******** Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "HOON"."TEST_20170119"                      44.07 KB     660 rows Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_TABLE_01 is:   /home/oracle/DBA/hoon/tmp/MST_1st.dmp Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 11:32:25

 

이제 반대로 import 해보자

impdp \" / as sysdba\" dumpfile=MST_1st.dmp directory=EXP_HOON Import: Release 11.2.0.2.0 - Production on Thu Jan 19 11:33:10 2017 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORA-39002: invalid operation ORA-39174: Encryption password must be supplied.

패스워드를 지정하지 않아 import 진행이 되지 않는다.

 

패스워드를 지정하고 다시 import 해보자.

impdp \" / as sysdba\" dumpfile=MST_1st.dmp directory=EXP_HOON ENCRYPTION_PASSWORD=tyvld Import: Release 11.2.0.2.0 - Production on Thu Jan 19 11:33:27 2017 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" dumpfile=MST_1st.dmp directory=EXP_HOON ENCRYPTION_PASSWORD=******** Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "HOON"."TEST_20170119"                      44.07 KB     660 rows Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 11:33:30

정상적으로 테이블이 import 되었다.

 

ENCRYPTION 옵션을 잘 황용하면 데이터 백업에 대한 보안을 강화 할 수 있다.