Creating Auto-Login Keystores for TDE

If we need to create a TDE column or a tablespace, first requirement is to create a keystore to save the encryption key (secret). 

Provide the location (ENCRYPTION_WALLET_LOCATION) where the wallet or keystore will be stored in sqlnet.ora. This can also be provided as database parameter WALLET_ROOT if you don't want to use the sqlnet.ora method.

[oracle@linux75-2 ~]$ cat /u01/orcl/122/network/admin/sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/orcl/122/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES)

ENCRYPTION_WALLET_LOCATION =
  (SOURCE =(METHOD = FILE)(METHOD_DATA =
    (DIRECTORY = /u01/orcl/122/network/wallet/)))
SQLNET.EXPIRE_TIME=2
[oracle@linux75-2 ~]$ 

Create a keystore 

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/orcl/122/network/wallet/' IDENTIFIED BY oracle;

keystore altered.

SQL> 

Now, keystore ewallet.p12 file will be created in the location mentioned. NEVER delete, lose or tamper this file. 

We can open and close the keystore as needed. The issue is that every time we bounce the database, we have to explicitly open the keystore. 

SQL> select * from v$encryption_wallet;

WRL_TYPE             WRL_PARAMETER                                      STATUS                         WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
-------------------- -------------------------------------------------- ------------------------------ -------------------- --------- --------- ----------
FILE                 /u01/orcl/122/network/wallet/                      CLOSED                         UNKNOWN              SINGLE    NO                 0

SQL> administer key management set keystore open identified by oracle;

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE             WRL_PARAMETER                                      STATUS                         WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
-------------------- -------------------------------------------------- ------------------------------ -------------------- --------- --------- ----------
FILE                 /u01/orcl/122/network/wallet/                      OPEN                           PASSWORD             SINGLE    NO                 0

SQL> exit

When I tried to upgrade the database to 19c from the current 12.2 version using Autoupgrade utility, the analyze phase failed due to non availability of Auto-login keystore even though the keystore is open when the analyze phase is initiated. 

So how do we create an auto login keystore? 

Auto-login keystore is nothing but we don't have to manually open the keystore after a restart of the database. The creation is simple by the below command. 

SQL> ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u01/orcl/122/network/wallet/' IDENTIFIED BY oracle;

keystore altered.

SQL> !
[oracle@linux75-2 ~]$ ls -lrt /u01/orcl/122/network/wallet/
total 12
-rw-------. 1 oracle oinstall 2408 Jun 12 23:41 ewallet_2021061218110265.p12
-rw-------. 1 oracle oinstall 3848 Jun 12 23:41 ewallet.p12
-rw-------. 1 oracle oinstall 3901 Sep 26 00:21 cwallet.sso
[oracle@linux75-2 ~]$ exit
exit

The LOCAL keyword specifies the keystore can only be opened from the local machine. 

You can see a new file by name cwallet.sso is created which is the auto login keystore file. NEVER delete, lose or tamper this file as this will be an irreversible damage to the database. 

Once the auto login keystore is created, the password keystore will no longer work and the autologin keystore will automatically precedence and login and open the keystore from the next restart. 

SQL> select * from v$encryption_wallet;

WRL_TYPE   WRL_PARAMETER                                      STATUS                         WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
---------- -------------------------------------------------- ------------------------------ -------------------- --------- --------- ----------
FILE       /u01/orcl/122/network/wallet/                      OPEN                           PASSWORD             SINGLE    NO                 0

SQL> administer key management set keystore close;
administer key management set keystore close
*
ERROR at line 1:
ORA-28389: cannot close auto login wallet


SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  8628936 bytes
Variable Size             595592504 bytes
Database Buffers          461373440 bytes
Redo Buffers                8146944 bytes
Database mounted.
Database opened.
SQL> set lines 200
SQL> col wrl_parameter for a50
SQL> select * from v$encryption_wallet;

WRL_TYPE   WRL_PARAMETER                                      STATUS                         WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
---------- -------------------------------------------------- ------------------------------ -------------------- --------- --------- ----------
FILE       /u01/orcl/122/network/wallet/                      OPEN                           LOCAL_AUTOLOGIN      SINGLE    NO                 0

SQL> exit

You can see that the WALLET_TYPE is changed from PASSWORD to LOCAL_AUTOLOGIN and is also the STATUS is OPEN upon the restart of the database. 

Now, my analyze phase of the Autoupgrade also completed successfully without any errors. 

Pic courtesy: https://tenor.com/view/secret-boss-baby-on-the-phone-gif-7991222

Happy Keystoring...!!!