oracle TDE 구성

1. 구조

TDE Column Encryption

TDE Tablespace Encryption

2. Single 구성시

1) Wallet 생성

ENCRYPTION_WALLET_LOCATION =
(SOURCE =(METHOD = FILE)(METHOD_DATA =
(DIRECTORY = $ORACLE_HOME/network/admin/wallet)))

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '$ORACLE_HOME/network/admin/wallet/' identified by "oracle00";

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "oracle00" CONTAINER=ALL;

select * from v$encryption_wallet;

ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "oracle00" CONTAINER=ALL;

2) Key 생성

# cdb, pdb 각각에서 key 생성
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "oracle00" WITH BACKUP CONTAINER=ALL;

SET LINESIZE 100
SELECT con_id, key_id FROM v$encryption_keys;

3) tablespace 테스트
CREATE TABLESPACE encrypted_ts
DATAFILE '/oracle/app/oradata/cdb1/pdb1/encrypted01.dbf' size 1m
ENCRYPTION USING 'AES256'
DEFAULT STORAGE(ENCRYPT)

ALTER USER test QUOTA UNLIMITED ON encrypted_ts;

CREATE TABLE tde_ts_test (
id NUMBER(10),
data VARCHAR2(50)
) TABLESPACE encrypted_ts;

INSERT INTO tde_ts_test VALUES (1, 'This is also a secret!');
COMMIT;

4) DB restart 및 재오픈

4.1 PDB restart일 경우
alter session set container=pdb1; or conn sys@pdb1 as sysdba

shutdown immediate;
startup;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "oracle00";

4.2 CDB restart일 경우
shutdown immediate;
startup;

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "oracle00" CONTAINER=ALL;

5) unplug/plug

CONN sys@pdb1 AS SYSDBA

ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "mySecret" TO '/tmp/export.p12' IDENTIFIED BY myPassword;

ALTER PLUGGABLE DATABASE pdb1 CLOSE;
ALTER PLUGGABLE DATABASE pdb1 UNPLUG INTO '/tmp/pdb1.xml';

CREATE PLUGGABLE DATABASE pdb2 USING '/tmp/pdb1.xml';
CREATE PLUGGABLE DATABASE pdb2 USING '/tmp/pdb1.xml' FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb1/','/u01/app/oracle/oradata/cdb2/pdb2/');

CONN / AS SYSDBA
HOST mkdir -p /u01/app/oracle/admin/cdb2/encryption_keystore/
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/admin/cdb2/encryption_keystore/' IDENTIFIED BY myPassword;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY myPassword;

ALTER SESSION SET CONTAINER=pdb2;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "myPassword";
ADMINISTER KEY MANAGEMENT IMPORT ENCRYPTION KEYS WITH SECRET "mySecret" FROM '/tmp/export.p12' IDENTIFIED BY "myPassword" WITH BACKUP;

SHUTDOWN;
STARTUP;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "myPassword";

6) auto login
ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE FROM KEYSTORE '$ORACLE_HOME/network/admin/wallet/' IDENTIFIED BY "oracle00";

shutdown immediate;
startup;

 select * from v$encrypted_tablespaces; 

3. RAC 구성시

2 node 설치를 가정할 경우 1번 node에서 wallet(keystore) 생성후 key 생성하고 해당 key를 다른 노드로 copy 하여 배포

(Autologin은 각 노드별로 생성해야 함)

1) Wallet 생성

ENCRYPTION_WALLET_LOCATION =
(SOURCE =(METHOD = FILE)(METHOD_DATA =
(DIRECTORY = $ORACLE_HOME/network/admin/wallet)))

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '$ORACLE_HOME/network/admin/wallet/' identified by "oracle00";

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "oracle00" CONTAINER=ALL;

select * from v$encryption_wallet;

ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "oracle00" CONTAINER=ALL;

2) Key 생성

# cdb, pdb 각각에서 key 생성
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "oracle00" WITH BACKUP CONTAINER=ALL;

SET LINESIZE 100
SELECT con_id, key_id FROM v$encryption_keys;

3) Key 배포

2) 번에서 생성한 key를 Rac 각 노드에 배포

4) Autologin 설정

ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE FROM KEYSTORE '$ORACLE_HOME/network/admin/wallet/' IDENTIFIED BY "oracle00";

shutdown immediate;
startup;

 select * from v$encrypted_tablespaces; 

* ASM에 keystore 와 key 생성

1) sqlnet.ora 설정

ENCRYPTION_WALLET_LOCATION =
(SOURCE =(METHOD = FILE)(METHOD_DATA =
(DIRECTORY = +DATA/WALLET/  )))

2) asmcmd 디렉토리 생성

cd /DATA

mkdir WALLET

3) keystore 생성

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '+DATA/WALLET/ ' identified by "oracle00";

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "oracle00" ;

select * from v$encryption_wallet;

4) auto login 설정

ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE FROM KEYSTORE '+DATA/WALLET/' IDENTIFIED BY "oracle00";

select * from v$encryption_wallet;

5) master key 생성

ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "oracle00" WITH BACKUP ;

SELECT con_id, key_id FROM v$encryption_keys;