Transparent Data Encryption In Oracle : In this article we will discuss about configuring and enabling Transparent Data Encryption (TDE) in Oracle 19c. To configure Auto Login Wallet in Oracle 19c there are few parameters which needs to be set in spfile.
Lets see how to configure TDE.
I need to create a subdirectory for my wallet for my orcl database:
Create a wallet directory. Typically, wallet directory is located in $ORACLE_BASE/admin/$ORACLE_SID/wallet. Ideally wallet directory should be empty.
[oracle@orakldba ~]$
[oracle@orakldba ~]$ . oraenv
ORACLE_SID = [orcl] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@orakldba ~]$
[oracle@orakldba ~]$ echo $ORACLE_BASE
/u01/app/oracle
[oracle@orakldba ~]$
[oracle@orakldba ~]$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/wallet
[oracle@orakldba ~]$
Specify the ENCRYPTION WALLET LOCATION
In earlier releases, This is specified in the sqlnet.ora file like this :
[oracle@orakldba ~]$ cd $ORACLE_HOME/network/admin
[oracle@orakldba admin]$
[oracle@orakldba admin]$ cat sqlnet.ora
ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=$ORACLE_BASE/admin/$ORACLE_SID/wallet)))
[oracle@orakldba admin]$
However, in Oracle 19c, Oracle recommends that,
You must configure Keystore location and type by setting WALLET_ROOT and TDE_CONFIGURATION parameters in pfile or spfile. After completed this configuration you need to restart the database.
SQL>
SQL> show parameter wallet
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
ssl_wallet string
wallet_root string
SQL>
SQL> alter system set wallet_root='$ORACLE_BASE/admin/$ORACLE_SID/wallet' scope=spfile;
System altered.
SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 733999368 bytes
Fixed Size 8900872 bytes
Variable Size 226492416 bytes
Database Buffers 494927872 bytes
Redo Buffers 3678208 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter wallet
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
ssl_wallet string
wallet_root string /u01/app/oracle/admin/orcl/wal
let
SQL>
SQL> show parameter tde
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
one_step_plugin_for_pdb_with_tde boolean FALSE
tde_configuration string
SQL>
SQL> alter system set tde_configuration='KEYSTORE_CONFIGURATION=FILE' scope=spfile;
System altered.
SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 733999368 bytes
Fixed Size 8900872 bytes
Variable Size 226492416 bytes
Database Buffers 494927872 bytes
Redo Buffers 3678208 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter tde
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
one_step_plugin_for_pdb_with_tde boolean FALSE
tde_configuration string KEYSTORE_CONFIGURATION=FILE
SQL>
Create software Keystore and check the status of the wallet :
SQL>
SQL> administer key management create keystore identified by Welcome123;
keystore altered.
SQL>
SQL> set lines 400 pages 400
SQL> col WRL_PARAMETER for a60
SQL> select WRL_TYPE, WRL_PARAMETER, STATUS, CON_ID from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS CON_ID
-------------------- ------------------------------------------------------------ ------------------------------ ----------
FILE /u01/app/oracle/admin/orcl/wallet/tde/ CLOSED 0
SQL>
SQL> !ls -ltrh /u01/app/oracle/admin/orcl/wallet/tde/
total 4.0K
-rw-------. 1 oracle oinstall 2.5K Jun 6 00:16 ewallet.p12
SQL>
Open and close a software key :
SQL> administer key management set keystore open identified by "Welcome123";
keystore altered.
SQL> select WRL_TYPE, WRL_PARAMETER, STATUS, CON_ID from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS CON_ID
-------------------- ------------------------------------------------------------ ------------------------------ ----------
FILE /u01/app/oracle/admin/orcl/wallet/tde/ OPEN_NO_MASTER_KEY 0
SQL>
SQL> administer key management set keystore close identified by "Welcome123";
keystore altered.
SQL>
SQL> select WRL_TYPE, WRL_PARAMETER, STATUS, CON_ID from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS CON_ID
-------------------- ------------------------------------------------------------ ------------------------------ ----------
FILE /u01/app/oracle/admin/orcl/wallet/tde/ CLOSED 0
SQL>
Set the Keystore TDE Encryption Master Key :
SQL> select WRL_TYPE, WRL_PARAMETER, STATUS, CON_ID from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS CON_ID
-------------------- ------------------------------------------------------------ ------------------------------ ----------
FILE /u01/app/oracle/admin/orcl/wallet/tde/ OPEN_NO_MASTER_KEY 0
SQL>
SQL> administer key management set key identified by Welcome123 with backup using 'Master_key_bkp_orcl';
administer key management set key identified by Welcome123 with backup using 'Master_key_bkp_orcl'
*
ERROR at line 1:
ORA-28417: password-based keystore is not open
SQL> administer key management set keystore open identified by "Welcome123";
keystore altered.
SQL> administer key management set key identified by Welcome123 with backup using 'Master_key_bkp_orcl';
keystore altered.
SQL> select WRL_TYPE, WRL_PARAMETER, STATUS, CON_ID from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS CON_ID
-------------------- ------------------------------------------------------------ ------------------------------ ----------
FILE /u01/app/oracle/admin/orcl/wallet/tde/ OPEN 0
SQL>
Configure Auto Login Keystore and check the status :
After configuration you can see autologin wallet is open and enabled, so there would be no overhead of opening or closing the wallet. Once you will restart the database, wallet will be automatically opened.
SQL> administer key management create auto_login keystore from keystore identified by Welcome123;
keystore altered.
SQL> select WRL_TYPE, WRL_PARAMETER, STATUS, CON_ID from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS CON_ID
-------------------- ------------------------------------------------------------ ------------------------------ ----------
FILE /u01/app/oracle/admin/orcl/wallet/tde/ OPEN 0
SQL>
Create an encrypted tablespace and check :
SQL>
SQL> create tablespace ts_encrypted datafile '/u01/app/oracle/oradata/ORCL/ts_encrypted01.dbf' size 100M encryption default storage(encrypt);
Tablespace created.
SQL>
SQL> select a.FILE_ID, a.TABLESPACE_NAME, a.FILE_NAME, b.ENCRYPTED from dba_data_files a, dba_tablespaces b where a.TABLESPACE_NAME=b.TABLESPACE_NAME;
FILE_ID TABLESPACE_NAME FILE_NAME ENCRYPTED
---------- ------------------------------ ------------------------------------------------------------ ----------
7 USERS /u01/app/oracle/oradata/ORCL/users01.dbf NO
4 UNDOTBS1 /u01/app/oracle/oradata/ORCL/undotbs01.dbf NO
1 SYSTEM /u01/app/oracle/oradata/ORCL/system01.dbf NO
3 SYSAUX /u01/app/oracle/oradata/ORCL/sysaux01.dbf NO
5 TS_ENCRYPTED /u01/app/oracle/oradata/ORCL/ts_encrypted01.dbf YES
SQL>
Lets we will discuss about how we can test data is encrypted or not?
Here we are going to create a table (orakldba_not_encrypted) on the USERS (unencrypted) tablespace and another table (orakldba_encrypted) on the TS_ENCRYPTED (encrypted) tablespace. Only you can create any object on the encrypted tablespace when the wallet is open. In my case Oracle Wallet is already open and I can create on the encrypted tablespace the orakldba_encrypted table and insert some rows in it.
SQL>
SQL> create table orakldba_not_encrypted (text varchar2(100)) tablespace USERS;
Table created.
SQL> insert into orakldba_not_encrypted values ('I am an Oracle DBA');
1 row created.
SQL>
SQL> create table orakldba_encrypted (text varchar2(100)) tablespace TS_ENCRYPTED;
Table created.
SQL> insert into orakldba_encrypted values ('The Ganges is the most sacred river to Hindus');
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> alter system checkpoint;
System altered.
SQL>
Now you can see below i’m able to grep and see the text on the USERS tablespace which is not encrypted, but can not see the text on the TS_ENCRYPTED tablespace which is encrypted.
[oracle@orakldba ~]$ strings /u01/app/oracle/oradata/ORCL/users01.dbf | grep "Oracle DBA"
I am an Oracle DBA
[oracle@orakldba ~]$
[oracle@orakldba ~]$ strings /u01/app/oracle/oradata/ORCL/ts_encrypted01.dbf | grep "The Ganges is"
[oracle@orakldba ~]$
Let’s see what happens when the Oracle Wallet is open or closed.
SQL>
SQL> select WRL_TYPE, WRL_PARAMETER, STATUS, CON_ID, WALLET_TYPE from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS CON_ID WALLET_TYPE
-------------------- -------------------------------------------------- ------------------------------ ---------- --------------------
FILE /u01/app/oracle/admin/orcl/wallet/tde/ OPEN 0 PASSWORD
SQL>
SQL> select * from orakldba_encrypted;
TEXT
----------------------------------------------------------------------------------------------------
The Ganges is the most sacred river to Hindus
SQL>
SQL> administer key management set keystore close identified by "Welcome123";
keystore altered.
SQL>
SQL> select * from orakldba_encrypted;
select * from orakldba_encrypted
*
ERROR at line 1:
ORA-28365: wallet is not open
SQL>
SQL> select WRL_TYPE, WRL_PARAMETER, STATUS, CON_ID, WALLET_TYPE from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS CON_ID WALLET_TYPE
-------------------- -------------------------------------------------- ------------------------------ ---------- --------------------
FILE /u01/app/oracle/admin/orcl/wallet/tde/ CLOSED 0 UNKNOWN
SQL>
SQL> administer key management set keystore open identified by "Welcome123";
keystore altered.
SQL> select * from orakldba_encrypted;
TEXT
----------------------------------------------------------------------------------------------------
The Ganges is the most sacred river to Hindus
SQL>
I hope my written content will helped you. Your suggestions/feedback are most welcome from orakldba.com.
Keep learning… Have a great day!!!
Please comment here for your any query related to above content. You can write to mail me through : orakldba@gmail.com.
How does a java program connect to the database encrypted using TDE ? Do you need to provide the encryption key or wallet location to your java code to be able to connect to the DB and perform Read/Write operations ?