TDE (Transparent Data Encryption)는 OS 데이터 파일에 저장된 데이터를 암호화하는 메커니즘을 제공합니다.
TDE를 사용하면 저장소 수준의 데이터를 암호화하여 데이터베이스 외부에서 데이터가 유출 되는 것을 방지 할 수 있습니다.
Transperant 데이터 암호화를 활성화하기 위해 oracle 12c에 새로운 명령이 도입되었습니다.
관리자 키 관리는 ALTER SYSTEM SET ENCRYPTION WALLET과 같은 이전 명령을 대체하며 Wallet은 12c 에서 keystore로 알려져 있습니다.
적용 방법 입니다.
1. Create a wallet/keystore location.
|
1 |
mkdir -p /oracle12c/haisins/WALLET |
2. update the wallet/keystore location in sqlnet.ora. It should look like.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
cat $ORACLE_HOME/network/admin/sqlnet.ora # sqlnet.ora Network Configuration File: /home/oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora # Generated by Oracle configuration tools. NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) ENCRYPTION_WALLET_LOCATION = (SOURCE =(METHOD = FILE)(METHOD_DATA = (DIRECTORY = /oracle12c/haisins/WALLET))) |
Create keystore:
|
1 2 3 4 5 6 7 |
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/oracle12c/haisins/WALLET/' IDENTIFIED BY walletpass#123; keystore altered. SQL> host ls /oracle12c/haisins/WALLET/ ewallet.p12 |
Now open the keystore:
|
1 2 3 |
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY walletpass#123; keystore altered. |
Now activate the key:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SQL> SET LINESIZE 100 SELECT con_id, key_id FROM v$encryption_keys; SQL> no rows selected SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY walletpass#123 WITH BACKUP; keystore altered. SQL> SET LINESIZE 100 SQL> SELECT con_id, key_id FROM v$encryption_keys; CON_ID KEY_ID ---------- ------------------------------------------------------------------------------ 0 AS6cSkI4u09zv9+RRWMrX2QAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SQL> SET LINESIZE 200 SQL> COLUMN wrl_parameter FORMAT A50 SQL> SELECT * FROM v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID -------------------- -------------------------------------------------- ------------------------------ -------------------- --------- --------- ---------- FILE /oracle12c/haisins/WALLET/ OPEN PASSWORD SINGLE NO 0 |
create a encrypted a tablespace
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SQL> CREATE TABLESPACE TEST_ENCRY datafile '/home/oracle/app/oracle/oradata/cdb1/testencry.dbf' size 2G ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT); 2 3 4 Tablespace created. SQL> create table emp_ency( empno Number(3), Name varchar(10) ) tablespace TEST_ENCRY; 2 3 4 Table created. SQL> select tablespace_name,encrypted from dba_tablespaces where tablespace_name='TEST_ENCRY'; TABLESPACE_NAME ENC ------------------------------ --- TEST_ENCRY YES |
Create a table with encrypted column:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SQL> CREATE TABLE employee ( first_name VARCHAR2(128), last_name VARCHAR2(128), empID NUMBER, salary NUMBER(6) ENCRYPT ); Table created. SQL> select owner,table_name,column_name,encryption_alg from dba_encrypted_columns where table_name='EMPLOYEE'; OWNER TABLE_NAME COLUMN_NAME ENCRYPTION_A ---------- ------------ ------------ ------------ RAJ EMPLOYEE SALARY AES 192 bits key |
ENABLE AUTOLOGIN :
|
1 2 3 4 5 6 7 |
SQL> SELECT * FROM v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID -------------------- -------------------------------------------------- ------------------------------ -------------------- --------- --------- ---------- FILE /oracle12c/haisins/WALLET/ OPEN PASSWORD SINGLE NO 0 |
여기서 wallet_type은 PASSWORD입니다. 즉, 데이터베이스를 다시 시작할 때마다 키 / 지갑을 명시 적으로 열어야합니다.
이를 방지하기 위해 자동 로그인을 활성화하여 다음에 db가 다시 시작될 때 자동으로 지갑을 엽니 다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/oracle12c/haisins/WALLET/' IDENTIFIED BY walletpass#123; keystore altered. SQL> SELECT * FROM v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID -------------------- -------------------------------------------------- ------------------------------ -------------------- --------- --------- ---------- FILE /oracle12c/haisins/WALLET/ OPEN PASSWORD SINGLE NO 0 SQL> SQL> startup force ORACLE instance started. Total System Global Area 838860800 bytes Fixed Size 2929936 bytes Variable Size 570428144 bytes Database Buffers 260046848 bytes Redo Buffers 5455872 bytes Database mounted. Database opened. SQL> SELECT * FROM v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID -------------------- -------------------------------------------------- ------------------------------ -------------------- --------- --------- ---------- FILE /oracle12c/haisins/WALLET/ OPEN AUTOLOGIN SINGLE NO 0 |
이제 wallet_type이 PASSWORD에서 AUTOLOGIN 으로 변경되었습니다.
For multi-tenant database:
멀티 테넌트 데이터베이스 (CDB)에서 키 스토어는 ROOT 컨테이너 (CDB $ ROOT)에 만들어야합니다.
이 단일 키 저장소는 CDB $ ROOT 컨테이너뿐만 아니라 모든 연관된 PDB가 공유합니다.
따라서 모든 pdbs에서 keystore를 열고 활성화하려면 CONTAINER = ALL 절을 사용해야합니다.
|
1 2 3 |
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY walletpass#123 CONTAINER=ALL; ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY walletpass#123 WITH BACKUP CONTAINER=ALL; |
참고 : 키 저장소 사용자를 만들려면 ADMINISTER KEY MANAGEMENT 또는 SYSKM 권한이 있어야합니다.
Related dictionary tables for TDE:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
----What tables contain TDE encrypted columns? sql> select table_name, column_name from dba_encrypted_columns; -------What tables are stored in TDE encrypted tablespaces? sql> select a.table_name, a.tablespace_name from dba_tables a, dba_tablespaces b where a.tablespace_name = b.tablespace_name and b.encrypted = 'YES'; -------What indexes are stored in TDE encrypted tablespaces? sql> select a.index_name, a.tablespace_name from dba_indexes a, dba_tablespaces b where a.tablespace_name = b.tablespace_name and b.encrypted = 'YES' and index_name not like 'SYS_IL%'; ------- getting key/wallet details: SQL> SELECT * FROM v$encryption_wallet; SQL> SELECT con_id, key_id FROM v$encryption_keys; |
rofzaaxmvntrcdjcprjeobfttysqab