Oracle TDE暗号化ウォレットの作成と暗号化アルゴリズムの変更
目次
構成
- Single DB
- マルチテナント構成
- キーストア配置先：ファイルシステム
- キーストア：統合モード
- キーストア配置先の指定：sqlnet.ora の ENCRYPTION_WALLET_LOCATION
- 自動ログインウォレット：あり
設定確認
- PDB確認
SQL> select con_id, name from v$pdbs; CON_ID NAME ---------- -------------------- 2 PDB$SEED 3 PDB1
- 初期化パラメータ確認
SQL> set line 200 pages 100 tab off SQL> col name for a20 SQL> col value for a40 SQL> select name, value, con_id from v$system_parameter where name in ('tde_configuration','wallet_root'); NAME VALUE CON_ID -------------------- ---------------------------------------- ---------- wallet_root 0 tde_configuration 0 tde_configuration 2 tde_configuration 3
- sqlnet.ora
cat $ORACLE_HOME/network/admin/sqlnet.ora cat: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora: No such file or directory
- wallet 状態確認(キーストア作成前)
SQL> set line 200 pages 100 tab off SQL> col wrl_parameter for a60 SQL> select * from v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID -------------------- ------------------------------------------------------------ ------------------------------ -------------------- --------- -------- --------- ---------- FILE /u01/app/oracle/admin/cdb1/wallet NOT_AVAILABLE UNKNOWN SINGLE NONE UNDEFINED 1 FILE NOT_AVAILABLE UNKNOWN SINGLE UNITED UNDEFINED 2 FILE NOT_AVAILABLE UNKNOWN SINGLE UNITED UNDEFINED 3
キーストア作成
キーストア配置先作成
- ディレクトリ作成
$ mkdir -p /u01/app/oracle/admin/cdb1/wallet $ ls -dl /u01/app/oracle/admin/cdb1/wallet drwxr-xr-x. 2 oracle oinstall 6 Nov 7 08:45 /u01/app/oracle/admin/cdb1/wallet
- sqlnet.ora作成
cat << EOF > $ORACLE_HOME/network/admin/sqlnet.ora
ENCRYPTION_WALLET_LOCATION =
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=/u01/app/oracle/admin/cdb1/wallet)))
EOF
キーストア作成
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/admin/cdb1/wallet' IDENTIFIED BY "Welcome1234!Welcome1234!"; keystore altered.
自動ログインウォレット作成
SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u01/app/oracle/admin/cdb1/wallet'IDENTIFIED BY "Welcome1234!Welcome1234!"; keystore altered.
- hash確認(キーストア作成後)
$ ls -l /u01/app/oracle/admin/cdb1/wallet/* -rw-------. 1 oracle oinstall 2600 Nov 7 08:54 /u01/app/oracle/admin/cdb1/wallet/cwallet.sso -rw-------. 1 oracle oinstall 2555 Nov 7 08:52 /u01/app/oracle/admin/cdb1/wallet/ewallet.p12 $ md5sum /u01/app/oracle/admin/cdb1/wallet/* acf747b5d50a0b183f4defc5c9f7cd7c /u01/app/oracle/admin/cdb1/wallet/cwallet.sso 9ba05e5df3cbc3f058a50cdc05b79dfb /u01/app/oracle/admin/cdb1/wallet/ewallet.p12
- wallet 状態確認(キーストア作成後)
SQL> set line 200 pages 100 tab off SQL> col wrl_parameter for a60 SQL> select * from v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID -------------------- ------------------------------------------------------------ ------------------------------ -------------------- --------- -------- --------- ---------- FILE /u01/app/oracle/admin/cdb1/wallet/ OPEN_NO_MASTER_KEY AUTOLOGIN SINGLE NONE UNDEFINED 1 FILE OPEN_NO_MASTER_KEY AUTOLOGIN SINGLE UNITED UNDEFINED 2 FILE OPEN_NO_MASTER_KEY AUTOLOGIN SINGLE UNITED UNDEFINED 3
マスター鍵作成(CDB)
SQL> select * from V$ENCRYPTION_KEYS; no rows selected SQL> ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY USING TAG 'tag0' FORCE KEYSTORE IDENTIFIED BY "Welcome1234!Welcome1234!" WITH BACKUP USING 'backup0'; keystore altered.
マスター鍵作成(PDB)
SQL> alter session set container = pdb1; SQL> show con_name CON_NAME ------------------------------ PDB1 SQL> ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY USING TAG 'tag1' FORCE KEYSTORE IDENTIFIED BY "Welcome1234!Welcome1234!" WITH BACKUP USING 'backup1';
- hash確認(マスター鍵作成後)
$ ls -l /u01/app/oracle/admin/cdb1/wallet/* -rw-------. 1 oracle oinstall 5864 Nov 7 09:14 /u01/app/oracle/admin/cdb1/wallet/cwallet.sso -rw-------. 1 oracle oinstall 2555 Nov 7 09:06 /u01/app/oracle/admin/cdb1/wallet/ewallet_2022110709060456_backup0.p12 -rw-------. 1 oracle oinstall 4171 Nov 7 09:14 /u01/app/oracle/admin/cdb1/wallet/ewallet_2022110709142272_backup1.p12 -rw-------. 1 oracle oinstall 5819 Nov 7 09:14 /u01/app/oracle/admin/cdb1/wallet/ewallet.p12 $ md5sum /u01/app/oracle/admin/cdb1/wallet/* a1840d67634bb173d30f7521081d6d42 /u01/app/oracle/admin/cdb1/wallet/cwallet.sso 9ba05e5df3cbc3f058a50cdc05b79dfb /u01/app/oracle/admin/cdb1/wallet/ewallet_2022110709060456_backup0.p12 74be09571afa669e0ba083f4b4c952a2 /u01/app/oracle/admin/cdb1/wallet/ewallet_2022110709142272_backup1.p12 2d1a2ee92aeedf74bd418791eb506d49 /u01/app/oracle/admin/cdb1/wallet/ewallet.p12
- マスター鍵確認(CDB, PDB)
SQL> col key_id for a60 SQL> col tag for a10 SQL> col CREATOR_PDBNAME for a10 SQL> select key_id, creation_time, activation_time, tag, CREATOR_PDBNAME from V$ENCRYPTION_KEYS; KEY_ID CREATION_TIME ACTIVATION_TIME TAG CREATOR_PD ------------------------------------------------------------ --------------------------------------------------------------------------- --------------------------------------------------------------------------- ---------- ---------- AU1LHvHFk0+Iv8/79pk6GdQAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 07-NOV-22 09.06.04.730520 AM +00:00 07-NOV-22 09.06.04.730523 AM +00:00 tag0 CDB$ROOT AewNE+jX8U+evxCHpOnIzj8AAAAAAAAAAAAAAAAAAAAAAAAAAAAA 07-NOV-22 09.14.22.919688 AM +00:00 07-NOV-22 09.14.22.919690 AM +00:00 tag1 PDB1
暗号化表領域作成
SQL> alter session set container = pdb1; SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u02/oradata/CDB1/pdb1/system01.dbf /u02/oradata/CDB1/pdb1/sysaux01.dbf /u02/oradata/CDB1/pdb1/users01.dbf /u02/oradata/CDB1/pdb1/undo_fix_10m.dbf SQL> CREATE TABLESPACE ts_aes128 DATAFILE '/u02/oradata/CDB1/pdb1/ts_aes128.dbf' SIZE 10M ENCRYPTION USING 'AES128' DEFAULT STORAGE(ENCRYPT); Tablespace created. SQL> select ts#, name from v$datafile; TS# NAME ---------- ---------------------------------------- 0 /u02/oradata/CDB1/pdb1/system01.dbf 1 /u02/oradata/CDB1/pdb1/sysaux01.dbf 5 /u02/oradata/CDB1/pdb1/users01.dbf 7 /u02/oradata/CDB1/pdb1/undo_fix_10m.dbf 8 /u02/oradata/CDB1/pdb1/ts_aes128.dbf
- 暗号化アルゴリズム確認
SQL> set line 300 pages 100 tab off SQL> col name for a40 SQL> select e.ts#, d.name, e.encryptionalg, e.encryptedts, e.encryptedkey, e.masterkeyid, e.key_version, e.status, e.con_id from V$ENCRYPTED_TABLESPACES e, v$datafile d where e.ts#=d.ts# ; TS# NAME ENCRYPT ENC ENCRYPTEDKEY MASTERKEYID KEY_VERSION STATUS CON_ID ---------- ---------------------------------------- ------- --- ---------------------------------------------------------------- -------------------------------- ----------- ---------- ---------- 8 /u02/oradata/CDB1/pdb1/ts_aes128.dbf AES128 YES 6425018322B6C79A1DAA97FD459DE36500000000000000000000000000000000 EC0D13E8D7F14F9EBF1087A4E9C8CE3F 0 NORMAL 3
- データ作成
SQL> grant dba to user01 identified by "Welcome123!Welcome123!"; SQL> create table user01.t1 ( c1 number(10), c2 varchar2(100)) tablespace ts_aes128; SQL> insert into user01.t1 values (1, 'TEST'); SQL> commit;
暗号化アルゴリズム変更
SQL> alter session set container = pdb1; SQL> alter tablespace ts_aes128 encryption online using 'AES256' rekey; Tablespace altered.
- 暗号化アルゴリズム確認
SQL> set line 300 pages 100 tab off SQL> col name for a40 SQL> select e.ts#, d.name, e.encryptionalg, e.encryptedts, e.encryptedkey, e.masterkeyid, e.key_version, e.status, e.con_id from V$ENCRYPTED_TABLESPACES e, v$datafile d where e.ts#=d.ts#; TS# NAME ENCRYPT ENC ENCRYPTEDKEY MASTERKEYID KEY_VERSION STATUS CON_ID ---------- ---------------------------------------- ------- --- ---------------------------------------------------------------- -------------------------------- ----------- ---------- ---------- 8 /u02/oradata/CDB1/pdb1/ts_aes128.dbf AES256 YES 2B1E5943759E3D48A23A56A029DFE958D317BFBBB34D8FB5D7304466E5F0739E EC0D13E8D7F14F9EBF1087A4E9C8CE3F 1 NORMAL 3
- マスター鍵確認(PDB)
SQL> col key_id for a60 SQL> col tag for a10 SQL> col CREATOR_PDBNAME for a10 SQL> select key_id, creation_time, activation_time, tag, CREATOR_PDBNAME from V$ENCRYPTION_KEYS; KEY_ID CREATION_TIME ACTIVATION_TIME TAG CREATOR_PD ------------------------------------------------------------ --------------------------------------------------------------------------- --------------------------------------------------------------------------- ---------- ---------- AewNE+jX8U+evxCHpOnIzj8AAAAAAAAAAAAAAAAAAAAAAAAAAAAA 07-NOV-22 09.14.22.919688 AM +00:00 07-NOV-22 09.14.22.919690 AM +00:00 tag1 PDB1
- hash確認(暗号化アルゴリズム変更後)
[oracle@db01 ~]$ ls -l /u01/app/oracle/admin/cdb1/wallet/* -rw-------. 1 oracle oinstall 5864 Nov 7 09:14 /u01/app/oracle/admin/cdb1/wallet/cwallet.sso -rw-------. 1 oracle oinstall 2555 Nov 7 09:06 /u01/app/oracle/admin/cdb1/wallet/ewallet_2022110709060456_backup0.p12 -rw-------. 1 oracle oinstall 4171 Nov 7 09:14 /u01/app/oracle/admin/cdb1/wallet/ewallet_2022110709142272_backup1.p12 -rw-------. 1 oracle oinstall 5819 Nov 7 09:14 /u01/app/oracle/admin/cdb1/wallet/ewallet.p12 [oracle@db01 ~]$ md5sum /u01/app/oracle/admin/cdb1/wallet/* a1840d67634bb173d30f7521081d6d42 /u01/app/oracle/admin/cdb1/wallet/cwallet.sso 9ba05e5df3cbc3f058a50cdc05b79dfb /u01/app/oracle/admin/cdb1/wallet/ewallet_2022110709060456_backup0.p12 74be09571afa669e0ba083f4b4c952a2 /u01/app/oracle/admin/cdb1/wallet/ewallet_2022110709142272_backup1.p12 2d1a2ee92aeedf74bd418791eb506d49 /u01/app/oracle/admin/cdb1/wallet/ewallet.p12
→ハッシュ値が 2d1a2ee92aeedf74bd418791eb506d49 から変わらない
参考
以上
