Hello fellow Oracle DBAs. In this Article I will give you a few established „Best Practices“ that have emerged in my work when using TDE (Transparent Data Encryption) Wallets. Also, I will give you a few commands for handling Wallets and Keys and a Guide for a little bit of Troubleshooting.
CAVE: On Premises TDE is a feature that needs to be licensed. You need to have "Advanced Security" licensed in order to use TDE. The license is included in Autonmous Databases and Database Cloud Services within the OCI (Oracle Cloud Infrastructure)
Versions that are covered by this Article: 18c, 19c
Best Practices
Naturally this list is not complete and probably never will be and some of these practices may not be for you. Nonetheless, let me know, if you have something that could be missing.
Use TDE Wallets only for TDE Masterkeys. No Credentials and no Certificates
Storing Credentials or Certificates in an TDE Wallet can lead to the database not being able to open the Wallet at startup and thus not being able to access TDE encrypted Data in the Database.
This is one possible reason for the error: „ORA-28365 Wallet not open“
Manage TDE Wallets only with SQL*Plus
When managing TDE Wallets and Keys use only the SQL*Plus „ADMINISTER KEY MANAGEMENT“ commands.
These command require a backup parameter whenever there is a change done to the wallet which then backs up the file.
Store Wallet in Filesystem
Store the Wallet in a Filesystem that is part of the Filesystembackup, thus we regularly have a backup. On RAC Systems this should be a shared ACFS Filesystem.
Don’t use ASM to store your TDE Wallets, or have a really good concept for backups whenver you make a change to the Wallet.
Store the Wallet Password securely in a Password Manager
Don’t lose the Password. Losing Access to the Wallet and Keys within can lead to not being able to access the Data
Use „wallet_root“ Parameter in Database
Don’t use the old sqlnet.ora Parameters to define the path to your Oracle TDE Wallet this is deprecated in future Database Versions.
Use the Database Parameter „wallet_root“, which overrides any old sqlnet.ora parameters.
How-Tos
Step by Step Instructions to Configure a Wallet in a Non-CDB or CDB Database
Create Wallet Directory in Non-Rac Environment and secure directory
[oracle@dbhost01:ORCL]/home/oracle:
[oracle@dbhost01:ORCL]/home/oracle: mkdir /u01/tde_wallet
[oracle@dbhost01:ORCL]/home/oracle: cd /u01/tde_wallet
[oracle@dbhost01:ORCL]/u01/tde_wallet: ls -la .
total 0
drwxrwxr-x 2 oracle dba 6 Jan 11 14:14 .
drwxrwxr-x 13 oracle dba 140 Jan 11 14:14 ..
[oracle@dbhost01:ORCL]/u01/tde_wallet: chmod 750 .
[oracle@dbhost01:ORCL]/u01/tde_wallet: ls -la .
total 0
drwxr-x--- 2 oracle dba 6 Jan 11 14:14 .
drwxrwxr-x 13 oracle dba 140 Jan 11 14:14 ..
[oracle@dbhost01:ORCL]/u01/tde_wallet:Create shared ACFS Wallet Directory in RAC Environment and secure directory
Create ACFS Volume
[oracle@dbhost01:+ASM1]/u01/tde_wallet: asmcmd
ASMCMD> volcreate -G DATA -s 10G vol_wallets
ASMCMD> volinfo -G DATA vol_wallets
Diskgroup Name: DATA
Volume Name: VOL_WALLETS
Volume Device: /dev/asm/vol_wallets-377
State: ENABLED
Size (MB): 10240
Resize Unit (MB): 64
Redundancy: HIGH
Stripe Columns: 8
Stripe Width (K): 1024
Usage:
Mountpath:
ASMCMD> exit
[oracle@dbhost01:+ASM1]/u01/tde_wallet:Create Filesystem on ACFS Volume
[oracle@dbhost01:+ASM1]/u01/tde_wallet: /sbin/mkfs -t acfs /dev/asm/vol_wallets-377
mkfs.acfs: version = 19.0.0.0.0
mkfs.acfs: on-disk version = 46.0
mkfs.acfs: volume = /dev/asm/vol_wallets-377
mkfs.acfs: volume size = 10737418240 ( 10.00 GB )
mkfs.acfs: Format complete.Register Filesystem and wait until it is mounted on both nodes
[root@dbhost01:+ASM1]/root:
[root@dbhost01:+ASM1]/root: /sbin/acfsutil registry -a /dev/asm/vol_wallets-377 /u01/tde_wallets
acfsutil registry: mount point /u01/tde_wallets successfully added to Oracle Registry
[root@dbhost01:+ASM1]/root: df -h /u01/tde_wallets
Filesystem Size Used Avail Use% Mounted on
/dev/asm/vol_wallets-377 10G 570M 9.5G 6% /u01/tde_wallets
[root@dbhost01:+ASM1]/root: chown oracle:dba /u01/tde_wallets
[root@dbhost01:+ASM1]/root: ls -la /u01/tde_wallets
total 100
drwxr-xr-x 4 oracle dba 32768 Jan 11 14:35 .
drwxr-xr-x 11 root dba 4096 Jan 11 14:35 ..
drwx------ 2 root root 65536 Jan 11 14:35 lost+found
[root@dbhost01:+ASM1]/root:Set Database Parameters for Wallet and restart Database
Set wallet_root Parameter
[oracle@dbhost01:ORCL]/u01/tde_wallet: sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 11 15:04:43 2023
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
SQL> alter system set wallet_root='/u01/tde_wallets/ORCL' scope=spfile;
System altered.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
[oracle@dbhost01:ORCL]/u01/tde_wallet:Restart Database and create Wallet directory
[oracle@dbhost01:ORCL]/u01/tde_wallet: srvctl stop database -db ORCL
[oracle@dbhost01:ORCL]/u01/tde_wallet: mkdir -p /u01/tde_wallets/ORCL/tde
[oracle@dbhost01:ORCL]/u01/tde_wallet: srvctl start database -db ORCLSet tde_configuration parameter
[oracle@dbhost01:ORCL]/u01/tde_wallet: sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 11 15:22:46 2023
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
SQL> alter system set tde_configuration='KEYSTORE_CONFIGURATION=FILE' scope=spfile;
System altered.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0Create Wallet and TDE Masterkey
- Create Wallet
- Open Wallet
- Create and Set Masterkey
- Create Autologin
- Restart Database to test Autologin Wallet
[oracle@dbhost01:ORCL]/u01/tde_wallets/ORCL/tde: sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 12 08:24:53 2023
Version 19.17.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/tde_wallets/ORCL/tde' IDENTIFIED BY "SuperSecretPassword";
keystore altered.
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "SuperSecretPassword";
keystore altered.
SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "SuperSecretPassword" with backup;
keystore altered.
SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u01/tde_wallets/ORCL/tde' IDENTIFIED BY "SuperSecretPassword";
keystore altered.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
[oracle@dbhost01:ORCL]/u01/tde_wallets/ORCL/tde: srvctl stop database -db ORCL
[oracle@dbhost01:ORCL]/u01/tde_wallets/ORCL/tde: srvctl start database -db ORCL
[oracle@dbhost01:ORCL]/u01/tde_wallets/ORCL/tde: sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 12 08:28:03 2023
Version 19.17.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
SQL> select * from gv$encryption_wallet;
INST_ID WRL_TYPE
---------- --------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC
------------------------------ -------------------- --------- -------- ---------
CON_ID
----------
2 FILE
/u01/tde_wallets/ORCL/tde/
OPEN AUTOLOGIN SINGLE NONE NO
1
1 FILE
/u01/tde_wallets/ORCL/tde/
OPEN AUTOLOGIN SINGLE NONE NO
1
2 rows selected.
SQL> select inst_id, key_id, creation_time, CREATOR_DBNAME from gv$encryption_keys;
INST_ID
----------
KEY_ID
------------------------------------------------------------------------------
CREATION_TIME
---------------------------------------------------------------------------
CREATOR_DBNAME
--------------------------------------------------------------------------------
1
AUC/+0hT/E9pv4p+f5I7KQcAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
12-JAN-23 09.25.39.988752 AM +02:00
ORCL
2
AUC/+0hT/E9pv4p+f5I7KQcAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
12-JAN-23 09.25.39.988752 AM +02:00
ORCL
SQL>Open Wallet in Pluggable Database and Create/Set Masterkey
For this to work, you have to have the CDB Steps completed. Thus, a functioning Wallet already has to exist
Change into Pluggable Database and show Wallet Status
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCL_P01 READ WRITE NO
SQL> alter session set container=ORCL_P01;
Session altered.
SQL> SELECT wrl_parameter, status, wallet_type FROM v$encryption_wallet;
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS WALLET_TYPE
------------------------------ --------------------
OPEN_NO_MASTER_KEY AUTOLOGINIf Wallet is Closed, open in CDB.
Create Masterkey in Pluggable Database and Check Key
[oracle@dbhost01:ORCL]/DBA/users/oracle: sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 18 14:24:44 2023
Version 19.17.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
SQL> alter session set container=ORCL_P01;
Session altered.
SQL> ADMINISTER KEY MANAGEMENT SET KEY USING TAG 'ORCL_P01' FORCE KEYSTORE IDENTIFIED BY "SuperSecretPassword" WITH BACKUP;
keystore altered.
SQL> SELECT wrl_parameter, status, wallet_type FROM v$encryption_wallet;
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS WALLET_TYPE
------------------------------ --------------------
OPEN AUTOLOGIN
SQL> set line window
SQL> set pagesize 50
SQL> select key_id, con_id, CREATOR_PDBNAME from gv$encryption_keys order by con_id,key_id;
KEY_ID CON_ID
------------------------------------------------------------------------------ ----------
CREATOR_PDBNAME
--------------------------------------------------------------------------------------------------------------------------------
AefCYCk3qk+Hv+MPk9F/dJgAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 3
ORCL_P01
AefCYCk3qk+Hv+MPk9F/dJgAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 3
ORCL_P01
SQL>Various Commands for Wallet Handling and Troubleshooting
Possible Troubleshooting for „Wallet not open“ Error
Various Steps can now be taken to check for the most common reasons that lead this error. I give you here a small checklist and the necessary commands to check your wallet.
- Check Wallet Parameters and Keys
- Check if Wallet and Autologin Wallet are existing and have correct Permissions
- Try to show contents of Wallet as OS User oracle
Check Wallet Parameters and Keys
Here’s an example output of a correct open autologin wallet with various keys
[oracle@dbhost01:ORCL]/DBA/users/oracle: sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 18 16:16:22 2023
Version 19.17.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
SQL> set line 400
SQL> set pagesize 50
SQL> col STATUS for a20
SQL> col WRL_PARAMETER for a100
SQL> col STATUS for a20
SQL> col WALLET_TYPE for a10
SQL> select INST_ID,WRL_PARAMETER,STATUS, WALLET_TYPE, con_id from gv$encryption_wallet;
INST_ID WRL_PARAMETER STATUS WALLET_TYP CON_ID
---------- ---------------------------------------------------------------------------------------------------- -------------------- ---------- ----------
1 /u01/tde_wallets/ORCL/tde/ OPEN AUTOLOGIN 1
1 OPEN AUTOLOGIN 2
1 OPEN AUTOLOGIN 3
2 /u01/tde_wallets/ORCL/tde/ OPEN AUTOLOGIN 1
2 OPEN AUTOLOGIN 2
2 OPEN AUTOLOGIN 3
6 rows selected.
SQL> select key_id, con_id, CREATOR_PDBNAME from gv$encryption_keys order by con_id,key_id;
KEY_ID CON_ID CREATOR_PDBNAME
------------------------------------------------------------------------------ ---------- --------------------------------------------------------------------------------------------------------------------------------
AUC/+0hT/E9pv4p+f5I7KQcAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 1 CDB$ROOT
AUC/+0hT/E9pv4p+f5I7KQcAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 1 CDB$ROOT
AefCYCk3qk+Hv+MPk9F/dJgAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 3 ORCL_P01
AefCYCk3qk+Hv+MPk9F/dJgAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 3 ORCL_P01
SQL>Check if Wallet and Autologin Wallet are existing and have correct Permissions
Files ewallet.p12 for Wallet and also cwallet.sso for autologin Wallet should be present in the Wallet directory. Owner oracle, permissions rw for Oracle and rwx on the directory
[oracle@dbhost01:ORCL]/DBA/users/oracle: ls -la /u01/tde_wallets/ORCL/tde/
total 136
drwxrwxr-x 2 oracle dba 20480 Jan 18 14:25 .
drwxrwxr-x 3 oracle dba 20480 Jan 11 15:20 ..
-rw------- 1 oracle dba 5704 Jan 18 14:25 cwallet.sso
-rw------- 1 oracle dba 2555 Jan 12 08:25 ewallet_2023011207253993.p12
-rw------- 1 oracle dba 3995 Jan 18 14:25 ewallet_2023011813250035.p12
-rw------- 1 oracle dba 5659 Jan 18 14:25 ewallet.p12The files ewallet_BunchOfNumbers.p12 are Backups created by „ADMINISTER KEYSTORE“ Commands. Better keep them also and don’t delete them.
Try to show contents of Wallet as OS User oracle
You can show the Wallet contents with the following command as OS User „oracle“.
If a valid Autologin Wallet is missing, you will be prompted for the Wallet password.
[oracle@dbhost01:ORCL]/DBA/users/oracle: cd /u01/tde_wallets/ORCL/tde/
[oracle@dbhost01:ORCL]/u01/tde_wallets/ORCL/tde: orapki wallet display -wallet .
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2022, Oracle and/or its affiliates. All rights reserved.
Requested Certificates:
Subject: CN=oracle
User Certificates:
Oracle Secret Store entries:
ORACLE.SECURITY.DB.ENCRYPTION.AefCYCk3qk+Hv+MPk9F/dJgAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.AUC/+0hT/E9pv4p+f5I7KQcAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY.E04A6D293EC456C4E0539618A10A9767
ORACLE.SECURITY.ID.ENCRYPTION.
ORACLE.SECURITY.KB.ENCRYPTION.
ORACLE.SECURITY.KM.ENCRYPTION.AefCYCk3qk+Hv+MPk9F/dJgAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.KM.ENCRYPTION.AUC/+0hT/E9pv4p+f5I7KQcAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.KT.ENCRYPTION.AefCYCk3qk+Hv+MPk9F/dJgAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
Trusted Certificates:
[oracle@dbhost01:ORCL]/u01/tde_wallets/ORCL/tde:Be aware: The Wallet must only contain TDE Keys in the „Oracle Secret Store entries“.
If there are other types of contents in the Wallet, this could also lead to the Error „Wallet not open“, because then the Database will not be able to open the TDE Wallet.