Borrar master keys antiguas de la TDE keystore
Updated
•3 min read
Después de un ciclo de pruebas de configuración y testing he dejado la keystore en situación no deseable cuando la desorganización te produce TOC.
Listamos previamente las master keys.
Master Key ID Tag PDB Name KEYSTORE_TYPE Origin Key Creation Time Key Act. Time
------------------------------------------------------- -------------------- --------------- ----------------- ---------- ------------------ ------------------
AcxxxxxxxxxxxxxxxxxxxxQAAAAAAAAAAAAAAAAAAAAAAAAAAAAA CDB$ROOT SOFTWARE KEYSTORE LOCAL 01/14/2025 18:34 01/14/2025 18:34
Adyyyyyyyyyyyyyyyyyyyy0AAAAAAAAAAAAAAAAAAAAAAAAAAAAA CDB$ROOT SOFTWARE KEYSTORE LOCAL 01/14/2025 19:16 01/14/2025 19:16
AbooooooooooooooooooooEAAAAAAAAAAAAAAAAAAAAAAAAAAAAA PDB_PRUEBA SOFTWARE KEYSTORE LOCAL 01/14/2025 19:16 01/14/2025 19:16
AcccccccccccccccccccccwAAAAAAAAAAAAAAAAAAAAAAAAAAAAA CDB$ROOT SOFTWARE KEYSTORE LOCAL 01/14/2025 19:20 01/14/2025 19:20
Aqqqqqqqqqqqqqqqqqqqqq0AAAAAAAAAAAAAAAAAAAAAAAAAAAAA PDB_PRUEBA SOFTWARE KEYSTORE LOCAL 01/14/2025 19:20 01/14/2025 19:20
AppppppppppppppppppppppAAAAAAAAAAAAAAAAAAAAAAAAAAAAA CDB$ROOT SOFTWARE KEYSTORE LOCAL 01/14/2025 19:49 01/14/2025 19:49
AmmmmmmmmmmmmmmmmmmmmmmAAAAAAAAAAAAAAAAAAAAAAAAAAAAA PDB_PRUEBA SOFTWARE KEYSTORE LOCAL 01/14/2025 19:49 01/14/2025 19:49
AssssssssssssssssssssssAAAAAAAAAAAAAAAAAAAAAAAAAAAAA CDB$ROOT SOFTWARE KEYSTORE LOCAL 01/14/2025 19:56 01/14/2025 19:56
AzzzzzzzzzzzzzzzzzzzzzzAAAAAAAAAAAAAAAAAAAAAAAAAAAAA PDB_PRUEBA SOFTWARE KEYSTORE LOCAL 01/14/2025 19:56 01/14/2025 19:56
AWjjjjjjjjjjjjjjjjjjjjjAAAAAAAAAAAAAAAAAAAAAAAAAAAAA CDB$ROOT SOFTWARE KEYSTORE LOCAL 01/14/2025 22:46 01/14/2025 22:46
ASfffffffffffffffffffffAAAAAAAAAAAAAAAAAAAAAAAAAAAAA PDB_PRUEBA SOFTWARE KEYSTORE LOCAL 01/14/2025 22:46 01/14/2025 22:46
Pongámonos manos a la obra para deshacer esta faena y tener el entorno limpio.
Primer paso: cuales estoy utilizando?
set linesize 170
set pagesize 50
column pdb_name heading "PDB Name" format a15
column masterkeyid_base64 heading "Master Key ID" format a25
column mkid heading "Key ID" format a35
break
select name pdb_name,
mkid,
utl_raw.cast_to_varchar2( utl_encode.base64_encode('01'||substr(mkeyid,1,4))) || utl_raw.cast_to_varchar2( utl_encode.base64_encode(substr(mkeyid,5,length(mkeyid)))) masterkeyid_base64
FROM (select con_id,
mkid,
RAWTOHEX(mkid) mkeyid from x$kcbdbk) a,v$containers b
where a.con_id=b.con_id;
PDB Name Key ID Master Key ID
--------------- -------------------------------- --------------------------------------------------
CDB$ROOT 6D0WWWWWWWWWWWWWWWWWWWWWWWWWWW38 AWjjjjjjjjjjjjjjjjjjjjj
PDB$SEED 00000000000000000000000000000000 AQAAAAAAAAAAAAAAAAAAAAA
PDB_PRUEBA 2CWWWWWWWWWWWWWWWWWWWWWWWWWWWWW9 ASfffffffffffffffffffff
Perfecto, no existe procedimiento para borrarlas. Para realizar la limpieza primero vamos a exportarlas.
SYS@cdbxxx> ADMINISTER KEY MANAGEMENT EXPORT KEYS WITH SECRET "xxxxxxxxxxx" TO '/home/oracle/export_old_wallet.exp' force keystore IDENTIFIED BY "xxxxxxxxxxxxxxx"
WITH IDENTIFIER IN
'AWjjjjjjjjjjjjjjjjjjjjjAAAAAAAAAAAAAAAAAAAAAAAAAAAAA',
'ASfffffffffffffffffffffAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
;
Genial. Ahora moveremos los ficheros relacionados con la wallet del path antiguo a uno nuevo del que haremos backup seguro verdad?
[oracle@rxxxxx-001 ~]$ mv /u01/app/oracle/wallets/cdbxxx/tde/*wallet* /home/oracle/old_wallet
Reiniciamos la base de datos, creamos una keystore nueva e importamos las claves:
SYS@cdbxxx> administer key management create keystore '/u01/app/oracle/wallets/cdbxxx/tde' identified by "xxxxxxxxxx";
SYS@cdbxxx> administer key management set keystore open identified by "xxxxxxxxxxxxx";
SYS@cdbxxx> ADMINISTER KEY MANAGEMENT IMPORT KEYS WITH SECRET "xxxxxxxxxxx" FROM '/home/oracle/export_old_wallet.exp' IDENTIFIED BY "xxxxxxxxxxxxxxx" WITH BACKUP;
Activamos master encryption key para CDB$ROOT:
SYS@cdbxxx> administer key management use key 'AWjjjjjjjjjjjjjjjjjjjjjAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' identified by "xxxxxxxxxxxxxxx" WITH BACKUP;
Conectamos a la PDB y repetimos el paso pero con su clave.
SYS@cdbxxx> administer key management set keystore open identified by "xxxxxxxxxxxxx";
SYS@cdbxxx> administer key management use key 'ASfffffffffffffffffffffAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' identified by "xxxxxxxxxxxxx" WITH BACKUP;
Reiniciamos BBDD y comprobamos.
set linesize 170
set pagesize 50
column pdb_name heading "PDB Name" format a15
column masterkeyid_base64 heading "Master Key ID" format a25
column mkid heading "Key ID" format a35
break
select name pdb_name,
mkid,
utl_raw.cast_to_varchar2( utl_encode.base64_encode('01'||substr(mkeyid,1,4))) || utl_raw.cast_to_varchar2( utl_encode.base64_encode(substr(mkeyid,5,length(mkeyid)))) masterkeyid_base64
FROM (select con_id,
mkid,
RAWTOHEX(mkid) mkeyid from x$kcbdbk) a,v$containers b
where a.con_id=b.con_id;
PDB Name Key ID Master Key ID
--------------- -------------------------------- --------------------------------------------------
CDB$ROOT 6D0WWWWWWWWWWWWWWWWWWWWWWWWWWW38 AWjjjjjjjjjjjjjjjjjjjjj
PDB$SEED 00000000000000000000000000000000 AQAAAAAAAAAAAAAAAAAAAAA
PDB_PRUEBA 2CWWWWWWWWWWWWWWWWWWWWWWWWWWWWW9 ASfffffffffffffffffffff
Si todo ha ido ok volvemos a habilitar el autologing en nuestro caso.
SYS@cdbxxx> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE IDENTIFIED BY "xxxxxxxxxxxxx";
INST_ID PDB Name Type WRL_PARAMETER Status WALLET_TYPE KEYSTORE Backed Up
---------- ---------- ---------- -------------------------------------------------- ------------------------------ -------------------- -------- ----------
1 CDB$ROOT FILE /u01/app/oracle/wallets/cdbxxx/tde/ OPEN AUTOLOGIN NONE NO
PDB$SEED FILE OPEN AUTOLOGIN UNITED NO
PDB_PRUEBA FILE OPEN AUTOLOGIN UNITED NO
2 CDB$ROOT FILE /u01/app/oracle/wallets/cdbxxx/tde/ OPEN AUTOLOGIN NONE NO
PDB$SEED FILE OPEN AUTOLOGIN UNITED NO
PDB_PRUEBA FILE OPEN AUTOLOGIN UNITED NO
