Oracle DBの暗号化（TDE）に関するCheatメモ


# TDEの実装基本Step

## Keystoreの作成

TDE Keystore用のディレクトリ作成

```bash
export WALLET_DIR=/etc/ORACLE/WALLETS/cdb1
sudo mkdir -vp ${WALLET_DIR}/tde
sudo mkdir -vp ${WALLET_DIR}/tde_seps
sudo mkdir -vp ${WALLET_DIR}/okv
sudo chown oracle:oinstall -R /etc/ORACLE/
```

パラメータの設定

```sql
$ sqlplus -s / as sysdba
SQL> alter system set wallet_root = '${WALLET_DIR}' scope= spfile;
SQL> shutdown immediate;
SQL> startup;
SQL> alter system set "_tablespace_encryption_default_algorithm" = 'AES256' scope = both;
SQL> alter system set tde_configuration = "keystore_configuration=FILE" scope=BOTH;
```

Walletの作成

```sql
-- for CDB
administer key management create keystore identified by ${DBUSR_PWD};
-- for ALL PDBs
administer key management set keystore open identified by ${DBUSR_PWD} container=all;
```

## Master Keyの作成

CDB用のMaster Keyの作成

```sql
ADMINISTER KEY MANAGEMENT SET KEY USING TAG 'CDB1: Initial Master Key' IDENTIFIED BY ${DBUSR_PWD} WITH BACKUP container=current;
```

PDB1用のMaster Keyの作成

```sql
alter session set container=${pdbname};
ADMINISTER KEY MANAGEMENT SET KEY USING TAG '${pdbname}: Initial Master Key' IDENTIFIED BY ${DBUSR_PWD} WITH BACKUP container=current;
```

## Auto-login Walletの作成

```sql
administer key management create auto_login keystore from keystore '${WALLET_DIR}/tde' identified by ${DBUSR_PWD};
```

## 既存表領域の暗号化

```sql
ALTER TABLESPACE EMPDATA_PROD ENCRYPTION ONLINE USING 'AES256' ENCRYPT;
```

## すべての表領域の暗号化

```sql
alter system set encrypt_new_tablespaces = 'ALWAYS' scope=both;
alter system set "_tablespace_encryption_default_algorithm" = 'AES256' scope = both;
```

# 確認

## Wallet & TDE Configurationの確認

```sql
SQL> show parameter wallet_root;
SQL> show parameter tde_configuration;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
wallet_root                          string      /etc/ORACLE/WALLETS/cdb1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
tde_configuration                    string      keystore_configuration=FILE
```

## Keystore/コンテナごとの暗号化状態の確認

```sql
set lines 130
set pages 9999
col wrl_type        format a12
col wrl_parameter   format a36
col activation_time format a36
col key_use         format a14
col tag             format a36
col name            format a10
col wallet_type     format a12
select a.con_id, b.name, a.wrl_type, a.wrl_parameter, a.status, a.wallet_type from v$encryption_wallet a, v$containers b where a.con_id=b.con_id order by a.con_id;
```

keystore作成前

```
    CON_ID NAME       WRL_TYPE     WRL_PARAMETER                        STATUS                         WALLET_TYPE
---------- ---------- ------------ ------------------------------------ ------------------------------ ------------
         1 CDB$ROOT   FILE         /etc/ORACLE/WALLETS/cdb1/tde/        NOT_AVAILABLE                  UNKNOWN
         2 PDB$SEED   FILE                                              NOT_AVAILABLE                  UNKNOWN
         3 PDB1       FILE                                              NOT_AVAILABLE                  UNKNOWN
         4 PDB2       FILE                                              NOT_AVAILABLE                  UNKNOWN
```

keystore作成後

```
    CON_ID NAME       WRL_TYPE     WRL_PARAMETER                        STATUS                         WALLET_TYPE
---------- ---------- ------------ ------------------------------------ ------------------------------ ------------
         1 CDB$ROOT   FILE         /etc/ORACLE/WALLETS/cdb1/tde/        OPEN_NO_MASTER_KEY             PASSWORD
         2 PDB$SEED   FILE                                              OPEN_NO_MASTER_KEY             PASSWORD
         3 PDB1       FILE                                              OPEN_NO_MASTER_KEY             PASSWORD
         4 PDB2       FILE                                              OPEN_NO_MASTER_KEY             PASSWORD
```

CDBへのMaster Key作成後

```
    CON_ID NAME       WRL_TYPE     WRL_PARAMETER                            STATUS
---------- ---------- ------------ ---------------------------------------- ------------------------------
         1 CDB$ROOT   FILE         /etc/ORACLE/WALLETS/cdb1/tde/            OPEN
         2 PDB$SEED   FILE                                                  OPEN
         3 PDB1       FILE                                                  OPEN_NO_MASTER_KEY
         4 PDB2       FILE                                                  OPEN_NO_MASTER_KEY
```

PDBへのMaster Key作成後

```
    CON_ID NAME       WRL_TYPE     WRL_PARAMETER                            STATUS
---------- ---------- ------------ ---------------------------------------- ------------------------------
         3 PDB1       FILE                                                  OPEN
```

DB上にあるKeyの確認

```sql
select con_id, activation_time, key_use, tag from v$encryption_keys order by con_id;

    CON_ID ACTIVATION_TIME                      KEY_USE        TAG
---------- ------------------------------------ -------------- ------------------------------------
         1 01-JUN-21 02.13.50.963496 PM +00:00  TDE IN PDB     CDB1: Initial Master Key
         3 01-JUN-21 02.17.11.905041 PM +00:00  TDE IN PDB     pdb1: Initial Master Key
```

## TableSpaceごとの暗号化状態の確認

TableSpaceが暗号化されているかどうかを確認

```sql
set lines 110
set pages 9999
col algorithm       format a10
col encrypted       format a10
col file_name       format a45
col pdb_name        format a20
col online_status   format a15
col tablespace_name format a30

select tablespace_name, encrypted from dba_tablespaces where tablespace_name = 'EMPDATA_PROD';

TABLESPACE_NAME                ENCRYPTED
------------------------------ ----------
EMPDATA_PROD                   YES
```

## 使われている暗号鍵の確認

```sql
set lines 140
set pages 9999
col wrl_type format a12
col wrl_parameter format a40
col activation_time format a36
col key_id format a36
col tag format a52
col pdb_name format a10
select b.name pdb_name, a.key_id, a.activation_time, a.tag from v$encryption_keys a, v$containers b where a.con_id=b.con_id order by a.con_id, a.activation_time;
```


## 関連するパラメータの確認

```sql
set lines 110
set pages 999
col name  format a40
col value format a40

select name, value 
  from v$parameter
 where name in ('encrypt_new_tablespaces'
               ,'tde_configuration'
               ,'external_keystore_credential_location'
               ,'wallet_root'
               ,'one_step_plugin_for_pdb_with_tde');

NAME                                     VALUE
---------------------------------------- ----------------------------------------
encrypt_new_tablespaces                  CLOUD_ONLY ※ すべての表領域を暗号化対象とすると、ここがAlwaysとなる
one_step_plugin_for_pdb_with_tde         FALSE
external_keystore_credential_location
wallet_root                              /etc/ORACLE/WALLETS/cdb1
tde_configuration                        keystore_configuration=FILE
```


# 参考情報

## MOS
- Primary Note For Transparent Data Encryption ( TDE ) (Doc ID 1228046.1)
- HOW TO clone PDB with TDE implemented (Doc ID 2350151.1)
- Quick TDE Setup and FAQ (Doc ID 1251597.1)
- How to configure TDE in pluggable database in 12c for standalone and RAC environment (Doc ID 2107821.1)
- How to migrate a non pluggable database that uses TDE to pluggable database ? (Doc ID 1678525.1)
- TDE 12c : Frequently Asked Questions (Doc ID 2253348.1)

## YouTube

- [Transparent Data Encryption - Advanced Use Cases Part 1](https://www.youtube.com/watch?v=avNWykLpic4&t=6s)
- [Transparent Data Encryption Advanced Use Cases - Part 2](https://www.youtube.com/watch?v=aUfwG5MIMNU)

## FAQ
- https://www.oracle.com/database/technologies/faq-tde.html