TDE란 Transparent Data Encryption 로 응용 프로그램의 수정 없이 DB 내부에서 칼럼, 테이블스페이스 레벨의 암호화를 하는 것을 말한다.
1. TDE 암호화 종류
1. Columns Level Encryption (10g,11g)
테이블의 특정 칼럼만 암호화, 블록의 특정 부분만 암호화.
2. Tablespace Level Encryption (11g)
테이블스페이스 전체가 암호화, 테이블 사이즈의 증가가 없음.
- System, Sysaux, Undo, Temp 의 경우 테이블스페이스 레벨 암호화 방식을 사용할 수 없다.
- Expdp/Impdp 는 TDE 를 지원 (10g R2, 11g)
- Bitmap index, Index range scan 지원
- Lob 지원(11g), Logminer, logical standby db, streams, HSM(Hardware Security Module)
2. Column Level Test
|
1. 암호화 키가 저장될 O/S상의 디렉터리 생성 [oracle11@singledb product]$ cd $ORACLE_BASE [oracle11@singledb oracle]$ pwd [oracle11@singledb oracle]$ mkdir tde_key [oracle11@singledb oracle]$ cd tde_key [oracle11@singledb tde_key]$ pwd /ora11g/app/oracle/tde_key
2. sqlnet.ora 파일에 Master Key가 저장될 위치 지정 [oracle11@singledb admin]$ cat sqlnet.ora # sqlnet.ora Network Configuration File: /ora11g/app/oracle/product/11.2.0.4/db_1/network/admin/sqlnet.ora # Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
ADR_BASE = /ora11g/app/oracle
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/ora11g/app/oracle/tde_key)))
3. 암호화 키 생성 및 확인 [oracle11@singledb admin]$ dba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Jun 19 15:41:41 2017
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter system set encryption key identified by oracle ;
System altered.
Elapsed: 00:00:00.06 SQL> !ls -al $ORACLE_BASE/tde_key/* -rw-r--r-- 1 oracle11 oinstall 1573 Jun 19 15:43 /ora11g/app/oracle/tde_key/ewallet.p12
SQL> col wrl_parameter format a50 SQL> SELECT * FROM V$ENCRYPTION_WALLET ;
WRL_TYPE WRL_PARAMETER STATUS -------------------- ----------------------------- ------------------ file /ora11g/app/oracle/tde_key OPEN
4. 칼럼 암호화 진행 SQL> connect scott/tiger Connected. SQL> show user USER is "SCOTT" SQL> select * from tab ;
TNAME TABTYPE CLUSTERID ---------------------- ------- ---------- BONUS TABLE DEPT TABLE EMP TABLE SALGRADE TABLE
Elapsed: 00:00:00.02 SQL> desc dept Name Null? Type -------------------- -------- ---------------- DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13)
SQL> create table dept_enc 2 (deptno number(2) not null , 3 dname varchar2(14) , 4 loc varchar2(13) encrypt ) ;
Table created.
Elapsed: 00:00:00.09 SQL> desc dept_enc ; Name Null? Type ------------------- -------- -------------- DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13) ENCRYPT
SQL> insert into dept_enc select * from dept ;
4 rows created.
Elapsed: 00:00:00.03 SQL> commit ;
Commit complete.
Elapsed: 00:00:00.00 SQL> select * from dept_enc ;
DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
Elapsed: 00:00:00.00 SQL> select * from dept ;
DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
Elapsed: 00:00:00.00
SQL> SELECT * FROM user_encrypted_columns ;
TABLE_NAME COLUMN_NAME ENCRYPTION_ALG SAL INTEGRITY_AL ------------------ ---------------- ------------------------------ --- ------------ DEPT_ENC LOC AES 192 bits key YES SHA-1
Elapsed: 00:00:00.02
5.암호화 종료 SQL> connect / as sysdba Connected. SQL> alter system set encryption wallet close identified by oracle ;
System altered.
Elapsed: 00:00:00.00
SQL> connect scott/tiger Connected. SQL> select * from dept_enc ; select * from dept_enc * ERROR at line 1: ORA-28365: wallet is not open
Elapsed: 00:00:00.00 SQL> select * from dept ;
DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
Elapsed: 00:00:00.01
|
3. 테이블 스페이스 암호화
위에서와 같이 sqlnet.ora파일 수정 후, 마스터 키 생성
|
1. 암호화 활성 화 SQL> connect / as sysdba Connected. SQL> alter system set encryption key identified by oracle ;
2. 암호화 테이블 스페이스 생성 SQL> create tablespace enc_tbs 2 datafile '/ora11g/app/oracle/oradata/ora11g/enc_tbs01.dbf' size 100M 3 encryption using 'AES256' 4 default storage(encrypt) ;
SQL> select tablespace_name , encrypted from dba_tablespaces ;
TABLESPACE_NAME ENC ------------------------------ --- SYSTEM NO SYSAUX NO UNDOTBS1 NO TEMP NO USERS NO EXAMPLE NO ENC_TBS YES
SQL> select * from v$encrypted_tablespaces ;
TS# ENCRYPT ENC ---------- ------- --- 7 AES256 YES
3. 테이블 생성 SQL> connect scott/tiger Connected. SQL> create table dept_tbs_enc 2 (deptno number(2) not null , 3 dname varchar2(14) , 4 loc varchar2(13)) 5 tablespace enc_tbs ; SQL> desc dept_tbs_enc ; Name Null? Type ----------------------- -------- -------------------- DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13)
SQL> insert into dept_tbs_enc select * from dept ; SQL> commit ;
SQL> select * from dept_tbs_enc ;
DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BSTON
SQL> select * from dept_enc ;
DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
4. 암호화 비활성 화 SQL> connect / as sysdba Connected. SQL> alter system set encryption wallet close identified by oracle ;
SQL> connect scott/tiger Connected. SQL> SQL> SQL> SQL> select * from dept ;
DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
SQL> select * from dept_tbs_enc ; select * from dept_tbs_enc * ERROR at line 1: ORA-28365: wallet is not open
select * from dept_enc * ERROR at line 1: ORA-28365: wallet is not open
|