Oracle Transparent Data Encryption (TDE)

• Need for secure information
• Automatic encryption of sensitive information : ex) 급여, 카드번호, 비밀번호
  • Embedded in the Oracle database
  • No need to change application logic
  • Encrypts data and index values
• Using an encryption key
  • Master key for the entire database
  • Stored in Oracle Wallet

  ※ master key : 개인키 + 대칭형키 로 암호화 한것 (oracle 비밀번호 바꾸면 대칭형 키가 바뀜)

     

TDE : 투명하다 - dml 작업 시 다른 작업은 없지만

암호화했다 - datafile은 보호 (데이터 추출 X) 한다.

   

▣ Implementing Transparent Data Encryption

▶ Wallet 생성

   

▶ [oracle@edrsr4p1 ~]$ vi $ORACLE_HOME/network/admin/sqlnet.ora

   

▶ Master Key Setting

SQL> alter system set encryption key identified by "welcome1";

   

▶ Create Table ( 암호화된 컬럼이 포함되며 sys 객체는 불가능하다 )

▶ 데이터 삽입

▶ Wallet 닫힌 상태에서 확인

   

▣ Backup the wallet

[oracle@edrsr4p1 ~]$ cat $ORACLE_HOME/network/admin/sqlnet.ora

# sqlnet.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

# Generated by Oracle configuration tools.

   

#NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

   

ENCRYPTION_WALLET_LOCATION=

(SOURCE=

(METHOD=FILE)

(METHOD_DATA=

(DIRECTORY="/u01/app/oracle/product/10.2.0/db_1/")

)

)

   

[oracle@edrsr4p1 ~]$ ls /u01/app/oracle/product/10.2.0/db_1/ewallet.p12

/u01/app/oracle/product/10.2.0/db_1/ewallet.p12

[oracle@edrsr4p1 ~]$ cp /u01/app/oracle/product/10.2.0/db_1/ewallet.p12 ./

[oracle@edrsr4p1 ~]$

[oracle@edrsr4p1 ~]$ rman target /

   

Recovery Manager: Release 10.2.0.1.0 - Production on Sun Aug 23 15:56:10 2009

   

Copyright (c) 1982, 2005, Oracle. All rights reserved.

   

connected to target database: ORCL (DBID=1173970302)

   

RMAN> backup datafile 4;

   

Starting backup at 23-AUG-09

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=136 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00004 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_3trzflmk_.dbf

channel ORA_DISK_1: starting piece 1 at 23-AUG-09

channel ORA_DISK_1: finished piece 1 at 23-AUG-09

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_08_23/o1_mf_nnndf_TAG20090823T155611_591t0d36_.bkp tag=TAG20090823T155611 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 23-AUG-09

   

RMAN> CONFIGURE ENCRYPTION FOR DATABASE on;

   

new RMAN configuration parameters:

CONFIGURE ENCRYPTION FOR DATABASE ON;

new RMAN configuration parameters are successfully stored

   

RMAN> backup datafile 4; => Wallet 이 깨졌기 때문에 에러 발생

   

Starting backup at 23-AUG-09

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00004 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_3trzflmk_.dbf

channel ORA_DISK_1: starting piece 1 at 23-AUG-09

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 08/23/2009 15:56:28

ORA-19914: unable to encrypt backup

ORA-28365: wallet is not open

   

   

S SYS> alter system set encryption key identified by "welcome1"; => Wallet 복구

   

System altered.

   

S SYS> !

[oracle@edrsr4p1 ~]$ rman target /

   

Recovery Manager: Release 10.2.0.1.0 - Production on Sun Aug 23 15:56:59 2009

   

Copyright (c) 1982, 2005, Oracle. All rights reserved.

   

connected to target database: ORCL (DBID=1173970302)

   

RMAN> CONFIGURE ENCRYPTION FOR DATABASE on;

   

using target database control file instead of recovery catalog

old RMAN configuration parameters:

CONFIGURE ENCRYPTION FOR DATABASE ON;

new RMAN configuration parameters:

CONFIGURE ENCRYPTION FOR DATABASE ON;

new RMAN configuration parameters are successfully stored

   

RMAN> backup datafile 4; => wallet 복구했기 때문에 가능

   

Starting backup at 23-AUG-09

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=135 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00004 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_3trzflmk_.dbf

channel ORA_DISK_1: starting piece 1 at 23-AUG-09

channel ORA_DISK_1: finished piece 1 at 23-AUG-09

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_08_23/o1_mf_nnndf_TAG20090823T155702_591t1yvl_.bkp tag=TAG20090823T155702 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 23-AUG-09

   

▣ Existing Tables and TDE

• Add encrypted columns
  • ALTER TABLE emp ADD (ssn VARCHAR2(11) ENCYPT); 컬럼 추가 시 암호화
• Encrupt unencrypted columns
  • ALTER TABLE emp MODIFY (first_name ENCRYPT); 기존 컬럼 암호화
• Disable column encryption
  • ALTER TABLE emp MODIFY (first_name DECRYPT); 암호화된 컬럼 암호해제
• Add or remove salt
  • ALTER TABLE emp MODIFY (first_name ENCRYPT [NO] SALT); 성능향상 위해 암호화
• Change keys and the encryption algorithm
  • ALTER TABLE emp REKEY USING '3DES168';