Oracle 보안 TDE Author 고요한 Creation Date Last Updated Version V2.0 Copyright(C) 2004 Goodus Inc. All Rights Reserved Version 변경일자

Transcription

1 Oracle 보안 TDE Author 고요한 Creation Date Last Updated Version V2.0 Copyright(C) 2004 Goodus Inc. All Rights Reserved Version 변경일자 변경자 ( 작성자 ) 주요내용 박제헌대리 문서최초작성 박제헌대리 Chapter OWM 사용법추가 박제헌대리 메뉴편집 고요한대리 정관호대리 각환경에서의 Configuration 방법추가. TDE 제약사항추가. TDE 관련 detail 한설명추가. 본문서는개인의기술향상을목적으로만들어졌으며상업적으로이용하는것을일체금합니다.

2 Contents 1. 개요 Wallet File Wallet 위치조회와 Wallet 상태를조회 Encryption Key TDE 의사용되는암호화 Algorithms TDE 적용시주의점 Password 관리 Performance OverHead CPU Large table Encryption Storage 제약사항 Column Encryption 제약 (10g ~ 11g) [ID ] Tablespace Encryption 제약및변경사항. (11gR1~ ) [ID ] TDE Configuration Master Key Setting/resetting Silngle Instance Configuration Wallet File 위치지정 Master Key 생성 Master Key OPEN 및 Close 다중 Instance Configuration Wallet File 위치지정 Master Key 생성및 OPEN RAC 환경에서의 Configuration 일반적인 RAC 환경 RAC 환경에서의다중 Instance Configuration RAC TDE 구성시고려사항 Tool 을이용한 Wallet 설정 Orapki Tool Wallet 생성 Orapki 를이용한 Auto-login 설정 Orapki 를이용한 Password 변경 (11g 만가능 ) OWM OWM 을이용한 Auto-login 설정 Table 암호화 Table 생성시 Column 을암호화 Salt Option NOMAC Parameter External Table 생성시암호화

3 5.3. 기존 TABLE 의암호화 Column 을추가또는변경시 Column 추가시 Column 변경시 암호화 Column 을제거시 암호화 Column 의대한 Encryption Key 와알고리즘변경시 Encryption Key 변경시 암호화알고리즘변경시 Tablespace 암호화 Wallet Open 암호화된 Tablespace 생성 암호화 Tablespace Decryption Expdp/Impdp 사용법 Expdp/impdp Dump File 암호화

4 1. 개요 TDE(Transparent Database Encryption) 는오라클 10gR2 의 New feature 이며 Enterprise Edition 에서사용할수있다. 테이블의컬럼데이터를암호화하여디스크에저장하는기법인데디스크의도난이나백업데이터유출시데이터를보호할수있다. 디스크에저장되어있을때에는암호화되어있지만메모리에데이터가올라올때는마스터키를이용하여자동으로복호화가되어사용자에게보여지며디스크에저장될때에는다시암호화되어저장되기때문에테이블에적절한권한이있는사용자는 TDE 를적용하더라도암호화되지않은데이터를볼수있다. 10g 에서는컬럼단위의암호화를지원하며 11g 에는테이블스페이스단위의암호화를지원한다. 11g 에서는테이블스페이스단위로지원되기때문에테이블, 클러스터, 인덱스, Lobs, 테이블, 인덱스파티션도암호화를할수있다. TDE 는타솔루션과달리컬럼단위와테이블스페이스단위로암호화되기때문에 application 의변경이필요하지않는것이가장큰장점이라할수있다 Wallet File Wallet File 은 encryption 과 decryption 을수행하는마스터키를저장하고있으며, wallet File 은데이터베이스와 별도의공간에저장하여허가된사용자만접근토록한다. $ORACLE_HOME/network/admin/sqlnet.ora 파일에명시적으로 wallet File 의위치를지정할수있다. ENCRYPTION_WALLET_LOCATION= (SOURCE=(METHOD=FILE)(METHOD_DATA= (DIRECTORY= /oracle/product/admin/prod/wallet))) 위치를지정하지않았을때는기본적으로 $ORACLE_BASE/ADMIN/<SID>/WALLET 에생성되지만경로가존재하지않거 나 Platform 에따라파일위치를지정하지않았을때에는 ORA 에러가발생하기도한다.(Bug: ) 1.2. Wallet 위치조회와 Wallet 상태를조회 SQL> select wrl_parameter,status from v$encryption_wallet; WRL_PARAMETER STATUS /oracle11g/product/admin/orcl/wallet OPEN - 4 -

5 1.3. Encryption Key 1 Master Encryption Key Master Key 는 column 또는 tablespace 에사용되는보조키를암호화하는데사용되는암호화키이다. 2 Table Key (column 단위 -10g) 데이터베이스안의 Data Dicionary 에저장되며암호화된테이블에대하여 master key 와함께데이터를암복호화 하는데쓰인다. 3 Tablespace Key (tablespace 단위 -11g) 11g 부터는 Encryption Key 가각 tablespace header 안의저장이되어데이터암복호화에사용이된다 TDE 의사용되는암호화 Algorithms 암호화에대한국제표준을준수하는 Algotithms 을사용하고있다. Algorithm Key Size Parameter Name Triple DES (Data Encryption Standard) 168 bits 3DES168 AES (Advanced Encryption Standard) 128 bits (11gR2 default - tablespace) AES128 AES 192 bits (10g default - Column) AES192 AES 256 bits AES

6 2. TDE 적용시주의점 2.1. Password 관리 Wallet 이나 Wallet Password 를분실하게되면암호화된데이터는 Access 가불가하므로물리적인 Wallet File 의백업과 Wallet Password 관리가반드시필요하다 Performance OverHead CPU g : Tablespace 암호화는 Application 마다다를수있지만, 평균적으로 5~8% 사이에 Overhead 가발생한다.( 하지만실무에서의전체적인 Performance 를본다면 11g 에서의성능이 10g 보다월등한 Performance 를보여준다.) 10g : Column 암호화에서의 Table 은 Column 암호화가삽입되어있는경우에만해당이되며, 암호화, 복호화하는과정에서 5% 의 overhead 가발생한다. 또한암호화된 column 수나 access 를하는횟수에비례하기도한다 Large table Encryption Size 가큰 table 의관하여 Column 암호화를하는경우 redo log size 를증가시켜주어야하고, 인덱스가걸려있는상태에서의 Column 암호화는상당한시간이소요된다. 그렇기때문에 Index 를제거후에 No Salt 옵션과함께암호화를한후, Index 를새로만들어주어야한다.( 새로만들어진 Index 는암호화된값의의해서생성이됨 ) Storage 1 11g : Tablespace 암호화는 Storage OverHead 가없다. 2 10g : Column 암호화는일반데이터보다공간을좀더사용하게되는데, AES 경우최대 16byte,3DES 일경우 8Bytes 가추가적으로사용되며정합성체크를위한 20bytes 가추가적으로더필요하다.(nomac 파라미터이용시제외 ) salt 옵션으로암호화했을경우 16bytes 가더필요하여결론적으로최대발생할수있는스토리지오버해드는각암호화된 value 당 52bytes 가된다 제약사항. 동일한서버에여러데이터베이스가설치되어있는경우, 각각의 Encryption Key 를생성및사용해야한다. 서로공유하여사용할경우암호화된데이터손실을유발할수있기때문에 Oracle 에서는이같은방법은지원하지않는다 Column Encryption 제약 (10g ~ 11g) [ID ] 1 B-tree 가아닌 Index 유형 (Function Index, Domain Index, Join Index 등등 ) 생성불가. SQL> create bitmap index scott.i_tde on scott.tde(c); create bitmap index scott.i_tde on scott.tde(c) * ERROR at line 1: ORA-28337: the specified index may not be defined on an encrypted column 2 Index 를통한 Range Scan 불가능.( 동등비교연산자를통한 Scan 만가능 ) SQL> select * from scott.emp2 where empno > 7900 empno -> pk - 6 -

7 call count cpu elapsed disk query current rows Parse Execute Fetch total Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: SYS Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation TABLE ACCESS FULL EMP2 (cr=4 pr=0 pw=0 time=2720 us cost=3 size=24 card=2) Range Scan 은불가.. SQL> select * from scott.emp2 where empno = 7900; SQL ID: fkthsmpw58bb2 Plan Hash: select * from scott.emp2 where empno = 7900 call count cpu elapsed disk query current rows Parse Execute Fetch total Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation TABLE ACCESS BY INDEX ROWID EMP2 (cr=2 pr=0 pw=0 time=5472 us cost=1 size=121 card=1) INDEX UNIQUE SCAN PK_EMP2 (cr=1 pr=0 pw=0 time=5453 us cost=0 size=0 card=1)(object id ) 3 External Table(BFILE) oracle_datapump drver 로만가능. SQL> CREATE TABLE scott.tde_extract (c encrypt, c2) ORGANIZATION EXTERNAL (TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY d_dir LOCATION ('tde.extract') ) reject limit unlimited AS select * from scott.tde; Table created. SQL> select * from scott.tde_extract; C C SQL> select * from DBA_ENCRYPTED_COLUMNS; OWNER TABLE_NAME COLUMN_NAME ENCRYPTION_ALG SAL - 7 -

8 SCOTT TDE_EXTRACT C AES 192 bits key YES 4 TTS(Transportable Tablespace) 지원불가. $ expdp system/manager transport_tablespaces=users directory=d_dir dumpfile=filefull.dmp encryption_password="x" Export: Release Beta on Tuesday, 14 June, :02:08 Copyright (c) 2003, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release Beta With the Partitioning, Oracle Label Security, OLAP and Data Mining options ORA-39005: inconsistent arguments ORA-39032: function ENCRYPTION_PASSWORD is not supported in TRANSPORTABLE jobs 5 PK 를참조하는 FK 키 Column 지원불가 (ORA-28335) SQL> create table scott.tde_fk (c number references scott.tde(c)); create table scott.tde_fk (c number references scott.tde(c)) * ERROR at line 1: ORA-28335: referenced or referencing FK constraint column cannot be encrypted 6 암호화 Column 을 Partition Key, Cluster Key 로사용할수없음. SQL> create table SCOTT.TDE_PART (product_id number(5) ENCRYPT, time_id date) partition by range (product_id) (partition p1 values less than (500), partition p2 values less than (maxvalue)) ; partition by range (product_id) * ERROR at line 4: ORA-28346: an encrypted column cannot serve as a partitioning column 7 LOB(BLOB/CLOB) 지원불가 8 SYS 스키마 Object Column (ORA-28336) 지원불가 9 Export/Import Util (Expdp/Impdp 사용권장 )STREAMS 사용불가. 10 Advanced Queuing 사용불가. 11 Advanced Replication 사용불가. 12 Logical Standby Database 사용불가. 13 Logminer 에의한해석은가능하지만, 암호화 Column 은 Unsupported Type 라고표시됨. 14 비스칼라 Type ( 오브젝트 Type, 유저정의 Type, Varray, REF 등 ) 지원불가 15 암호화시 Salt 옵션을부여할경우 Index 생성불가능 - 8 -

9 Tablespace Encryption 제약및변경사항. (11gR1~ ) [ID ] 1 모든 table, cluster, index, LOB, table 및 index partition 등을지원가능. 2 SYSTEM, SYSAUX, UNDO, TEMP tablespaces 는암호화불가능. SQL> create undo tablespace TDE_UNDO ENCRYPTION USING '3DES168' default storage (ENCRYPT) datafile '/amer/rdbms/32bit/app/oracle/oradata/ama111u4/tdeundo01.dbf' size 2M; ERROR at line 1: ORA-30024: Invalid specification for CREATE UNDO TABLESPACE SQL> create temporary tablespace TDE_TEMP ENCRYPTION USING '3DES168' default storage (ENCRYPT) tempfile '/amer/rdbms/32bit/app/oracle/oradata/ama111u4/tdetemp01.dbf' size 2M; create temporary tablespace TDE_TEMP ENCRYPTION USING '3DES168' * ERROR at line 1: ORA-25139: invalid option for CREATE TEMPORARY TABLESPACE 3 Exp 불가능, Imp 는부분적으로가능.(Expdp/Impdp 권장 ) 암호화가설정이안되어있는 10gR2 또는이전버전에서 Export 이후, 암호화설정이되어있는 11g 로 Import 는가능하다. $ exp system/manager tables='scott.tde1' Export: Release Production on Fri Sep 21 17:10: Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release Production With the Partitioning, Oracle Label Security and Real Application Testing options Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion) About to export specified tables via Conventional Path... Current user changed to SCOTT EXP-00111: Table TDE1 resides in an Encrypted Tablespace TDE_TBS and will not be exported Export terminated successfully with warnings. $ expdp system/manager ENCRYPTION_MODE=password ENCRYPTION_PASSWORD=reencryptionpassword tablespaces=tde_tbs Export: Release Production on Friday, 21 September, :11:39 Copyright (c) 2003, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release Production With the Partitioning, Oracle Label Security and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_TABLESPACE_01": system/******** ENCRYPTION_MODE=password encryption_password=******** tablespaces=tde_tbs 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 "SCOTT"."TDE1" KB 1 rows Master table "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TABLESPACE_01 is: /u01/app/oracle/admin/v1110/dpdump/expdat.dmp Job "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully completed at 17:12:25 4 Bitmap Index 사용가능 SQL> create bitmap index scott.i_tde on scott.tde1(c); Index created - 9 -

10 5 Index 를통한 Range Scan 가능 SQL> create index scott.i_tde2 on scott.tde1(c); Index created. SQL> select * from scott.tde1 where c between 1 and 3; rows selected. Execution Plan Plan hash value: Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT (0) 00:00:01 * 1 INDEX RANGE SCAN I_TDE (0) 00:00: Predicate Information (identified by operation id): access("c">=1 AND "C"<=3) 6 LOB datatype 가능 SQL> create table scott.tde_lob (c clob) tablespace tde_tbs; 7 TTS 가능 -- Source 쪽에서 Wallet Key 를 Copy 해가져가야하기때문에 Target database 에서는 TDE 를사용하지않 고있어야만가능하다. (Cross-endianism 은불가능 ) 3. TDE Configuration TDE 구성에있어크게일반적인 Oracle 데이터이관기능 (datapump, exp/imp, CTAS, alter table move, 기타..) 을 이용하는오프라인방식과 Online Table Redefinition 기능을이용한온라인방식으로나눌수가있다. 1 Column 단위 : 암호화대상 column 이명확하고, 그수가많지않을때사용을한다. 2 Tablespace 단위 : 암호화대상 column 선정이쉽지않고, 그수가많을때사용한다 Master Key Setting/resetting 처음 TDE 를구성할시 Master Key 를생성해야하고, Database 를마이그레이션하거나, Clone DB, 업그레이드를할경우에는 Wallet 의사본으로하여금새롭게 Master Key 를 resetting( 권장 ) 을해야한다. 또한 Setting/resetting 의있어 wallet location 의 auto-login wallet 이존재한다면새로운 wallet 을생성하지않는다. SQL> ALTER SYSTEM SET ENCRYPTION KEY ["certificate_id"] IDENTIFIED BY "password" wallet setting/resetting SQL> alter system set wallet open identified by password ; wallet open SQL> alter system set wallet close identified by password ; wallet close SQL> alter system set wallet close; auto-login wallet 사용시 wallet close

11 3.2. Silngle Instance Configuration Wallet File 위치지정 Wallet 파일이지정될위치를 sqlnet.ora 에지정한다. Sqlnet.ora 파일에 wallet 의경로를지정후에는 v$encryption_wallet 뷰를통하여 sqlnet.ora 에지정된경로와 동일하게설정되어있는지확인한다. $> vi $ORACLE_HOME/network/admin/sqlnet.ora ENCRYPTION_WALLET_LOCATION= (SOURCE=(METHOD=FILE)(METHOD_DATA= (DIRECTORY=/oracle/wallet1/))) Wallet File 의 default 위치는 $ORACLE_BASE/admin/<global_db_name>/wallet 으로리눅스환경에서는명시적으 로위치를지정하지않을경우 ORA-28368: cannot auto-create wallet 이발생한다. SQL> select * from v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS file /oracle/wallet1/ CLOSED Master Key 생성 Master Key 는아래명령어를통하여생성이가능하며최초생성시에만사용해야하며아래의문장으로패스워드를 변경할경우에는기존의암호화되었던테이블을사용할수없게된다. Wallet file 이름은기본적으로 ewallet.p12 로생성이된다. SQL> alter system set encryption key identified by "oracle"; System altered. SQL> select * from v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS file /oracle/wallet1/ OPEN $> ls alrt /oracle/wallet1/ -rw oracle dba 1309 Mar 7 05:14 ewallet.p Master Key OPEN 및 Close 데이터베이스를재시작할때마다 master key 를아래의명령어로 LOAD 해야하며, 패스워드가틀린경우 ORA : Failed to open wallet 에러가발생한다. 또한 wallet 을 open 하지않고암호화된 Table 이나 Tablespace 를조회할때는 ORA-28365: wallet is not open 에러가발생한다. 혹 Master Key 를 Close 시에는 Auto-login 이설정되어있을경우패스워드부분은생략을하여도된다. SQL> alter system set wallet open identified by "oracle"; System altered. SQL> alter system set wallet close; wallet close ORA-28390: auto login wallet not open but encryption wallet may be open auto-login 이설정이안되어서발생. SQL> alter system set wallet close identified by "oracle"; System altered 다중 Instance Configuration 싱글 Instance 와는달리서버하나의다중 Instance 구성이라면 TDE 구성의있어경로설정방법은다르다. SQLNET.ORA 파일에경로설정에있어각인스턴스는 SID 로구분이되고, 환경변수를통하여경로를설정하여야만한다. [ID ]

12 Wallet File 위치지정 $> echo $ORACLE_SID orcl1 $> vi $ORACLE_HOME/network/admin/sqlnet.ora ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /oracle/wallet1/$oracle_sid))) $> mkdir p /oracle/wallet1/orcl Master Key 생성및 OPEN SQL> alter system set encryption key identified by "oracle"; System altered. SQL> select * from v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS file /oracle/wallet1/$oracle_sid OPEN $> ls alrt /oracle/wallet1/orcl1 -rw oracle dba 1309 Mar 7 05:14 ewallet.p12 SQL> alter system set wallet open identified by "oracle"; System altered RAC 환경에서의 Configuration RAC 환경에서의 wallet 관리는자동화되지않고, Open, Close, modify 같은작업은각 Local 별로수행을해주어야한다. 그렇기에 oracle 에서는 11g 부터 wallet file 을 shared storage 나 ACFS(ASM) 의저장하는것을권장을하고있다 일반적인 RAC 환경 RAC 에서는먼저 1 번노드에서 Wallet 과 Master Key 를생성후 2 번노드로 Copy 를하여 reopen 을한후사용을해 야하고, 2 번노드로 copy 를한후에는 wallet 을재오픈을해주어야한다. [ID ] (11g 에서도마찬가지로 oracle 유저의 ORACLE_HOME/network/admin/ 경로에 sqlnet.ora 파일을생성하면된다.) 1 번노드 $> vi $ORACLE_HOME/network/admin/sqlnet.ora ENCRYPTION_WALLET_LOCATION= (SOURCE=(METHOD=FILE)(METHOD_DATA= (DIRECTORY=/oracle/wallet1/))) SQL> alter system set encryption key identified by "oracle"; SQL> select * from v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS file /oracle/wallet1/ OPEN 1 번노드에서생긴 wallet file 을 2 번노드로 SQLNET.ORA 파일에기입할경로로복사한다

13 2 번노드 $> vi $ORACLE_HOME/network/admin/sqlnet.ora ENCRYPTION_WALLET_LOCATION= (SOURCE=(METHOD=FILE)(METHOD_DATA= (DIRECTORY=/oracle/wallet1/))) SQL> alter system set wallet close identified by oracle ; System altered. SQL> alter system set wallet open identified by "oracle"; System altered. SQL> select * from v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS file /oracle/wallet1/ OPEN RAC 환경에서의다중 Instance Configuration RAC 환경에서의동일한 ORACLE_HOME (Engine) 을사용하고하나이상의 database 를사용하는경우에는 Unqname 을지정하여 Dynamic 하게구성하는것을권장하고있다. $> mkdir p /oracle/wallets/orcl $> vi sqlnet.ora ENCRYPTION_WALLET_LOCATION = (SOURCE=(METHOD=FILE) (METHOD_DATA = (DIRECTORY=/oracle/wallets/$ORACLE_UNQNAME/))) $> srvctl setenv database d orcl T ORACLE_UNQNAME=orcl RAC TDE 구성시고려사항. 1 각인스턴스의 SQLNET.ORA 파일은 wallet 경로가기입되어있어야한다. 2 하나의인스턴스에서 Master Key 를생성및설정을해주어야하고, 그이후에다른노드로 copy 를해야한다. 3 다른노드로복사가완료되었다면모든인스턴스에서 wallet 을재오픈을해주어야한다. 4 Master Key 의대한변경및설정시에는 Wallet 을 Open 하거나 Close 를하면안된다. 5 Master Key 의대한변경및설정시에는모든 TDE 작업을수행되어선안된다. 6 Wallet Open 또는 Close 시에는 RAC 모든인스턴스에서 Open, Close 를해야한다. 4. Tool 을이용한 Wallet 설정 4.1. Orapki Tool Wallet 생성 Orapki 를생성시 Password 를미리기입할수있지만, 패스워드는미리기입안하는것을권장을하고있다. orapki wallet create wallet wallet 경로 [-pwd wallet password ] $> orapki wallet create -wallet /oracle/wallet/ Oracle PKI Tool : Version Production Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved

14 Enter password: oracle123 Enter password again: oracle123 $> ls /oracle/wallet/ ewallet.p Orapki 를이용한 Auto-login 설정. 데이터베이스를재시작할때마다 Master Key 를수동으로 open 을하는번거러움을해소하기위하여자동으로 open 을해주는설정을할수가있다. [ID ] 하지만, 10g 의경우 auto-login 을한후 Instance restart 를하여도상태는 CLOSE 가나온다. [ID ] 이는해당암호화 Table 에대해 DDL 이나 DML 을한후에상태를체크해보면 wallet 상태는다시 OPEN 으로변경이된다. 또한 wallet 상태를 Close 로변경이되더라도암호화 Table 을조회시자동 Open 이되어조회가가능해진다. 11g 에서는 DB restart 를하여도 OPEN 상태가유지가되고 wallet 을 Close 를하여도 VIEW 에서는 OPEN 상태로표시가된다. 개인적으로는 Auto-login 기능이편할수도있지만, 후에이러한기능으로 wallet 이 open, close 가잘안되는문제가발생할수도있으며오라클에서도이기능은권장하지않는다. orapki wallet create -wallet <wallet location> -auto_login -pwd "wallet password" $> orapki wallet create -wallet /oracle/wallet1/ -auto_login -pwd "oracle" SQL> create table test_col.test(col1 number, col2 varchar2(100) encrypt using 'AES256') SQL> insert into test_col.test Values (1, 22 ); SQL> shutdown immediate SQL> startup SQL> select * from v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS file /oracle/wallet1/ CLOSE SQL> select * from test_col.test; COL1 COL SQL> select * from v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS file /oracle/wallet1/ OPEN SQL> alter system set wallet close identified by "oracle"; ORA-28365: wallet is not open SQL> alter system set wallet close; System altered. SQL> select * from test_col.test; COL1 COL SQL> select * from v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS file /oracle/wallet1/ OPEN wallet 을 Close 를하여도해당암호화 Table 을조회하면 wallet 상태는자동 OPEN 이된다. Wallet_location 의 PKCS#12(ewallet.p12) 가존재한다면기존 PKCS#12 wallet 에대한 Password 를제공해야한

15 다. Wallet_location 의 PKCS#12(ewallet.p12) 이존재하지않다면새로 wallet 이생기면서새로운 Password 를지정해야한다. Auto-login 기능을제거시에는 OWM 을이용하여 auto-login 기능을해제해야한다 Orapki 를이용한 Password 변경 (11g 만가능 ) [ID ] orapki wallet change_pwd -wallet <path to the wallet> -oldpwd <oldpwd> -newpwd <newpwd> 패스워드변경후에는기존 Password 로 wallet 을 Close 한후새로운 Password 로 wallet 을 Open 을해야한다. $> orapki wallet change_pwd -wallet /oracle/wallet/ -oldpwd "oracle" -newpwd "oracle123" Oracle PKI Tool : Version Production Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved. SQL> select * from v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS file /oracle/wallet1/ OPEN SQL> alter system set wallet close identified by "oracle"; System altered. SQL> select * from v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS file /oracle/wallet1/ CLOSE SQL> alter system set encryption wallet open identified by "oracle"; alter system set encryption wallet open identified by "oracle" * ERROR at line 1: ORA-28353: failed to open wallet SQL> alter system set encryption wallet open identified by "oracle123"; System altered. SQL> select * from v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS file /oracle/wallet1/ OPEN

16 4.2. OWM OWM 은 Oracle Wallet Management 로 wallet 파일을보다쉽게관리하기위한 GUI Tool 이다. Master Key 생성, auto-login, 패스워드변경과같은 TDE 와관련된모든것들을이 Tool 하나로관리를할수가있다 OWM 을이용한 Auto-login 설정 1. OWM 실행. $> owm 2. Wallet Open 3. Wallet 경로설정 상단 wallet 메뉴클릭 Open 선택 4. Wallet 패스워드입력. Wallet 의경로를선택후 OK 클릭. 5. Wallet File 패스워드입력후 OK 클릭 6. Auto-login 설정 Wallet File 의경로를입력후에는좌측상단에메뉴가생김 상단 Wallet 메뉴선택 Auto-login 체크 Save 클릭

17 5. Table 암호화 5.1. Table 생성시 Column을암호화 Column 암호화시 AES192 는 default 값으로 using 3DES168 를작성을안할시기본적으로 AES192 가적용이된다. SQL> create table test (first_name varchar2(11), last_name varchar2(10), order_number number(13), credit_card_number varchar2(20) encrypt using 3DES168 no salt 'NOMAC') ; SQL> insert into test values ('Jon', 'Oldfield', 10001, ' '); SQL> insert into test values ('Chris', 'White', 10002, ' '); SQL> insert into test values ('Alan', 'Squire', 10003, ' '); Salt Option 암호화시의사용하는 Salt Option 은데이터의보안을강화하는방법으로임의의 String 으로암호화하는것이며암호화할때마다다른 String 패턴으로암호화가진행이된다. Salt Option 를사용하게되면추가적으로각데이터값마다 16byte 가필요하며, 인덱스를사용할예정이라면 Salt Option 을사용해서는안된다 NOMAC Parameter TDE 는기본적으로 SHA-1 알고리즘을사용하여무결성을체크하지만, NOMAC 파라미터를사용하게되면암호화및복호화시에하는무결성체크부분이빠지면서 TDE 의관한성능오버헤드를줄일수있다. 이파라미터를사용하게되면각암호화된값보다디스크공간이 20Byte 가더필요하게되고, 이미 SHA-1 알고리즘을사용하여 Table Column 암호화가있다면, 동일한 Table 의다른 Column 을 NOMAC Parameter 로암호화를할수없다. 무결성알고리즘변경. SQL> ALTER TABLE test REKEY USING '3DES168' 'SHA-1'; Table altered. SQL> ALTER TABLE test REKEY USING '3DES168' 'NOMAC'; Table altered External Table 생성시암호화 External Table 은오직 DataPump 로사용할때가능하고, External Table 을새로운위치로 Move 를하게되면임의로생성된 Encryption Key 를사용못하게된다. 그렇기에컬럼암호화시따로암호를지정하여 External Table 이 Move 를하더라도엑세스를가능하게해주도록해야한다. SQL> create directory d_dir as /oracle ; SQL> grant read, write on directory d_dir to hr; SQL> conn hr/hr SQL> create table test_ext (empno number(10), Empname varchar2(30), Salary number(10) ENCRYPT USING '3DES168' IDENTIFIED BY "oracle" ) ORGANIZATION EXTERNAL ( TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY "D_DIR"

18 LOCATION('test_ext.dat') ) 5.3. 기존 TABLE 의암호화 Column 을추가또는변경시. AES192 알고리즘과 Salt, MAC Option 은 default 값으로 Index 를사용할예정이라면 No salt Option 을부여해야 한다 Column 추가시 SQL> ALTER TABLE test ADD (sales VARCHAR2(11) ENCRYPT no salt 'NOMAC' ); Column 변경시 SQL> ALTER TABLE test MODIFY (sales ENCRYPT no salt 'NOMAC' ); 5.4. 암호화 Column 을제거시 SQL> ALTER TABLE test MODIFY (sales DECRYPT); 5.5. 암호화 Column 의대한 Encryption Key 와알고리즘변경시 Encryption Key 변경시 SQL> ALTER TABLE test REKEY; 암호화알고리즘변경시 SQL> ALTER TABLE employee REKEY USING '3DES168'; 6. Tablespace 암호화 11gR2 에서는 Column 암호화와 Tablespace 암호화모두동일한 Master Key 를사용하고, 만약 10g 에서 11g 로업그레이드한것이라면 Master Key 는재생성을해야만한다.(11gR1 에서 11gR2 로업그레이드도재생성이필요하고 ewallet.p12 파일도그대로가져가야한다.) 또한 Oracle 에서 Auto-login 기능은권장하지않으며, 기존 Tablespace 는암호화할수없기때문에새롭게암호화된 Tablespace 를생성하여 CTAS, Table Move, DataPump 등의방법으로이동시켜야한다

19 6.1. Wallet Open 암호화된 Tablespace 를만들기전에 Wallet 은 Open 이되어있어야만하고, Instance Crash 로인한 recover 가필요할시 redo 와 Undo 의암호화된 Data 의대한접근이필요하기때문에 Database 는 Open 이되기전의 Wallet 이 Open 되어있어야만한다. SQL> startup mount; SQL> alter system set encryption wallet open identified by oracle ; SQL> alter database open; 6.2. 암호화된 Tablespace 생성 Tablespace 암호화는기본알고리즘이 AES128 이다. SQL> CREATE TABLESPACE test DATAFILE '/oradata/test01.dbf' SIZE 100M ENCRYPTION USING '3DES168' DEFAULT STORAGE(ENCRYPT); SQL> Select tablespace_name,encrypted from dba_tablespaces where tablespace_name='test'; TABLESPACE_NAME ENC TEST YES SQL> Select * from v$encrypted_tablespaces; TS# ENCRYPT ENC DES168 YES 6.3. 암호화 Tablespace Decryption Tablespace 암호화를해제하기위해서는해당 Table 들을 alter table move, DataPump, dbms_redefinition 을이용 하여암호화되지않은 Tablespace 로옮기면된다. SQL> alter table cust_payment_info_2 move tablespace users; Table altered. SQL> select * from cust_payment_info_2; FIRST_NAME LAST_NAME ORDER_NUMBER CREDIT_CARD_NUMBER Jon Oldfield Chris White Alan Squire SQL> alter system set wallet close identified by "oracle"; System altered. SQL> select * from cust_payment_info_2; FIRST_NAME LAST_NAME ORDER_NUMBER CREDIT_CARD_NUMBER Jon Oldfield Chris White Alan Squire

20 7. Expdp/Impdp 사용법 TDE 로컬럼암호화를하였을경우 exp/imp 는사용할수없다. 단 11g 에서는 export 덤프파일을암호화된 Tablespace 로 Import 는사용할수있다 Expdp/impdp test 라는유저의 default tablespace 는 test 로지정이되어있고, test tablespace 는암호화된 tablespace 인환경이다. SQL> select ts#, ENCRYPTIONALG, ENCRYPTEDTS from v$encrypted_tablespaces; TS# ENCRYPT ENC DES168 YES SQL> conn test/oracle SQL> select count(*) from test; COUNT(*) 기존 exp/imp 는 metadata 들은정상적으로수행이되지만, 실제데이터가있는 Table 같은경우는 Error 가발 생하면서 export 가수행이되지않는다. $> exp test/oracle file=/oracle/exp.dmp log=exp.log owner=test Export: Release Production on Wed Apr 10 14:29: Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Tes Export done in KO16MSWIN949 character set and AL16UTF16 NCHAR character set. exporting pre-schema procedural objects and actions. exporting foreign function library names for user TEST. exporting PUBLIC type synonyms. exporting private type synonyms. exporting object type definitions for user TEST About to export TEST's objects.... exporting database links. exporting sequence numbers. exporting cluster definitions. about to export TEST's tables via Conventional Path... EXP-00111: Table TEST resides in an Encrypted Tablespace TEST and will not be exported. exporting synonyms. exporting views. exporting stored procedures. exporting operators. exporting referential integrity constraints. exporting triggers. exporting indextypes. exporting bitmap, functional and extensible indexes. exporting posttables actions. exporting materialized views. exporting snapshot logs

21 . exporting job queues. exporting refresh groups and children. exporting dimensions. exporting post-schema procedural objects and actions. exporting statistics Export terminated successfully with warnings. Expdp/Impdp 를사용하는경우. 동일한 wallet File(Master Key) 를사용하고있다면 Expdp/impdp 를수행함에있어아무러 Error 는발생하지않는다. 하지만 Master Key 가없거나잘못되어있을경우에는 ORA-28365: Wallet is not open 이발생한다. $> impdp system/oracle directory=en dumpfile=expdp.dmp logfile=impdp.log schemas=test remap_schema=test:hr Import: Release Production on Wed Apr 10 14:50: Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** directory=en dumpfile=expdp.dmp logfile=impdp.logschemas=test remap_schema=test:hr Procssing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA.. imported "HR"."TEST" KB rows Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 0 error(s) at 14:50: Dump File 암호화 encryption_password="password" 옵션을이용하면암호화된 table 을암호화형식의 dump 파일로받을수있다. $> expdp test/oracle schemas=test directory=en dumpfile=expdp.dmp logfile=expdp.log encryption_password="oracle" Export: Release Production on Wed Apr 10 15:48: Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Starting "TEST"."SYS_EXPORT_SCHEMA_01": test/******** schemas=test directory=en dumpfile=expdp.dmp logfile=expdp.log encryption_password=******** Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 256 KB Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS.. exported "TEST"."TEST" KB rows

22 Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is: /oracle/expdp.dmp Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:49:22 $> strings expdp.dmp more :001:000001: xPf fdfpq KAZMR BD)C >`z!!>u1o iwse* GNIH U=:+I qvi& XOOhF>" 4{1- {d{\ '&<P\.?O[ h0,p7on fn*oa GK.bn A%3C +nh6f 덤프파일에 strings 으로확인을해보면실제값들은임의의값들로암호화되어들어간것을알수가있다. 그외에도 Encryption, Encryption_Algorithm, Encryption_Mode 옵션을더추가하여좀더디테일하게암호화를할 수도있다