We’ll examine TDE technology in the Oracle 19c version which is one of the most important security solutions of Oracle. What is TDE, how to integrate and manage it? Those questions will be answered in practice with Auto-Login Wallet configuration. Wish you a happy reading…
What is TDE?
In an Oracle database, we can learn about the data by reading datafiles with basic operating system commands. This led to development of TDE in order to prevent security weakness in Oracle’s disk layer. TDE ensures the data is stored encrypted on the datafiles; therefore prevents users without the key from accessing the data. Every data is stored encrypted and every data is read by decrypting.
Thanks to TDE, sensitive data can be encrypted in tablespace or column-level. When the data encrypted by the authorised users is tried to be accessed, it is decrypted transparently and the data is viewed. What I mean is that a user or an application will not need an extra transaction to see the encrypted data. So, all encrypt & decrypt transactions are running automated in the database layer.
Wallets are needed to use TDE. TDE passwords are stored in those wallets. If you back up the database, you also should back up the wallet because you cannot access the data without the wallet when you restore back up. Following the activation of TDE, the wallet password should be stored safely and not be lost. TDE technology is easy to apply but it should be well organised administratively. It may not be possible to save the data after administrative mistakes.
Benefits of TDE
The sensitive data cannot be accessed if the media and disk involving datafiles is stolen.
The application or users do not need an extra configuration to see the sensitive data.
Users do not need to manage TDE master encryption key, the database automate it itself.
Zero Downtime column encryption can be done with Table Redefinition.
The wallet is encrypted only in the security DBA and other DBAs do not need it to log in.
Compatible Parameter
In order to use TDE, compatible version of the database should not be older than 11.2.0.0. Starting from this version:
- It is possible to switch from software keystore to hardware keystore.
- Master Encryption Key, reset and rotate transactions are possible.
- 4 types of encryption algorithm can be used in accordance with the level of encryption.
3DES168
AES128
AES192
AES256
- 3 types of integration algorithm can be used which protect against attackers.
MD5
SHA-1
SHA-2
NOMAC (Using integration algorithm)
- SALT or NO SALT options are present. The SALT transaction helps improving safety by adding random 16 bytes before encryption of the data.
! While NOMAC and NO SALT options harm the degree of safety, they will improve performance and disk use. Here, every user should determine performance/safety curve in accordance with his/her data, transaction ability and frequency.
TDE Tablespace Encryption
It helps encrypting all of the data in a tablespace. It encryptes and decryptes all reading and writing transactions. If you have more than one sensitive columns in your tables, it is better you choose this option. Also, tablespace encryption will work with better performance in bulk insert transactions. You can encrypt your existing tablespaces unintrusive (online). You can make Import & Export transactions with Data Pump.
! Data stored out of tablespace such as BFILE cannot be encrypted in this option. But they can be encrypted manually with the DBMS_CRYPTO package.
! The data is protected in the temp tablespace during Join and Sort transactions, also redo and undos of the encrypted data are protected with password.
TDE Tablespace Encryption Default Encryption Options
AES128
SALT (Imperative)
SHA-1
! Due to safety concerns, TDE Tablespace cannot be used with the NO SALT option.
TDE Tablespace Encryption Application
We’ll do the tablespace encryption application non-pluggable and on the Oracle Linux 7.8 operating system in the version of Oracle 19c. First, create a wallet index. Set the index in spfile and close the instance consistently.
In the 12c version, WALLET_ROOT index is remarked in the sqlnet.ora file but in 19c and after, it is stored in the database.
$ mkdir -p /u01/app/oracle/admin/orcl/wallet/tde
$ sqlplus / as sysdba
SQL> alter system set WALLET_ROOT="/u01/app/oracle/admin/orcl/wallet/" scope=spfile;
SQL> shutdown immediate
Start the database and set the TDE configuration parameter.
SQL> startup
SQL> alter system set TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE";
Create keystore with the identified password.
SQL> administer key management create keystore identified by Password;
Configure keystore as auto-login. With auto-login option, users connecting to the database will not enter password in every connection. If we use the other option Local Auto-Login, keystore can only be used in the local server where it is created. We cannot log into another server. We’ll use auto-login in this application which is more practical.
SQL> administer key management create auto_login keystore from keystore '/u01/app/oracle/admin/orcl/wallet/tde' identified by Password;
Set TDE Master Encryption Key.
SQL> select * from v$encryption_wallet;
SQL> administer key management set key using tag 'orcl_Tablespace_TDE' force keystore identified by Password with backup using 'TDE_backup';
SQL> select * from v$encryption_wallet;
Created key is seen in v$encryption_keys view.
SQL> selecy key_id, tag, keystore_type, creation_time from v$encryption_keys;
First, create a standard tablespace where we will store sensitive data. Then, create a normal table and add a line of data in it.
SQL> create tablespace sensitive_data datafile '/u01/app/oracle/oradata/ORCL/normal_tbs.dbf' size 50M;
SQL> select * from v$tablespace;
SQL> create table test (id number, txt varchar2(20)) tablespace sensitive_data;
SQL> insert into test (1,'deneme');
SQL> commit;
The data in a tablespace not protected with TDE can be viewed easily with the "strings" command, this is the security weakness I'm talking about.
SQL> !strings /u01/app/oracle/oradata/ORCL/normal_tbs.dbf | grep denemeUpon inserting the data, although it is committed the “strings” command was null. If you are working in a test environment like me, probably you will see the same. :)
Since it is not checkpoint yet, the data in the redolog is not in the datafile. After the checkpoint is triggered, we will see the data clearly when the datafile is read with the “strings” command.
SQL> alter system checkpoint;
SQL> !strings /u01/app/oracle/oradata/ORCL/normal_tbs.dbf | grep denemeEncrypt the tablespace involving sensitive data with the AES192 encryption algorithm online (unintrusive). Also, you can change name of the datafile with the file_name_convert parameter.
SQL> alter tablespace sensitive_data encryption online using 'AES192' encrypt file_name_convert=('/u01/app/oracle/oradata/ORCL/normal_tbs.dbf','/u01/app/oracle/oradata/ORCL/tde_tbs.dbf');
SQL> !strings /u01/app/oracle/oradata/ORCL/tde_tbs.dbf | grep deneme
As you can see in the example, no data in this tablespace can be read in the disk layer, so it is safe. This is the end of the TDE Tablespace Encryption.
TDE Column Encryption
Encrypting one or more columns in a table is called Column Encryption. If you have specific sensitive columns in your data set, you may choose this option. Column Encryption has important limitations and is more complex compared to Tablespace Encryption administratively.
TDE Column Encryption Default Encryption Options
AES192
SALT
SHA-1
Conditions Not to Use TDE Column Encryption
If an index type is used other than B-Tree
If Index Range Scan is used
Transportable Tablespaces
TDE Column Encryption Limitations
An indexed column can only be encrypted with NO SALT.
Index Range Scan cannot be used.
Foreign Key space cannot be encrypted.
If more than one column to be encrypted in a table, integration and encryption algorithms should be at the same level. However SALT option is dependent from other columns.
Lock the table to encrypt a column in an existing table. You can encrypt nonintrusive with table redefinition (Zero Downtime).
Only the supported data types can be used without exceeding type sizes:
binary_double
binary_float
char
date
interval day to second
intercal year to month
nchar
number
nvachar2
raw
timestamp
varchar2
TDE Column Encryption Application
Create a test user and create a test table in a unencrypted tablespace involving an encrypted column. Encrypt the column with AES256. Since we did not remark integration algorithm and salt option, default values SHA-1 and SALT will be used.
SQL> create user test identified by test;
SQL> create table test.deneme2 (
ad varchar2(128),
soyad varchar2(128),
numara number(6) ENCRYPT USING 'AES256' )
tablespace users;
Try adding a new encrypted column to this table. You will encounter a mistake if you try to add a column encrypted with AES192 to that table (ORA-28340). As I said above, if there are more than one columns in a table, encryption and integration algorithms should be at the same level.
SQL> alter table test.deneme2 add (text varchar2(100) encrypt using 'AES256' NO SALT);
Let's try to encrypt and decrypt an existing column in this table by modifying. We cannot encrypt any column in this table with NOMAC since the other encrypted column is created with the default SHA-1 algorithm (ORA-28379).
SQL> alter table test.deneme2 modify (soyad ENCRYPT);
SQL> alter table test.deneme2 modify (soyad DECRYPT);
We can learn about encrypted columns from DBA_ENCRYPTED_COLUMNS view.
Try to create an index on an encrypted column. As you can see in the example, we cannot create an index in a column encrypted with SALT (ORA-28338).
SQL> select * from dba_encrypted_columns;
SQL> create index ind on test.deneme2 (text);
We can alter the encryption algorithm in a table with rekey command.
SQL> alter table test.deneme2 rekey using 'AES192';
SQL> select * from dba_encrypted_columns;
Necessary authorisation is granted to the test user to insert. Test data is inserted and checkpoint is seen. Upon triggering the checkpoint, dirty buffers are written on datafiles.
Note: Upon inserting the record, even if you do not comit it, it is written on the datafile after triggering of checkpoint. From that point on, you cannot see the data in the table if you run the rollback command but it is seen in the datafile till it is crushed. Even if the data in the encrypted column is deleted, it can be found in the datafile. It will not cause a security weakness since it is encrypted.
SQL> grant connect, resource to test;
SQL> alter user test quota unlimited on users;
SQL> insert into test.deneme2 values ('alperen','ulku','123456','sifreli');
SQL> alter system checkpoint;
Encrypted columns cannot be viewed when the data is tried to be read on datafile. This is how column-level encryption works.
SQL> !strings /u01/app/oracle/oradata/ORCL/users01.dbf | grep alperen
SQL> !strings /u01/app/oracle/oradata/ORCL/users01.dbf | grep ulku
SQL> !strings /u01/app/oracle/oradata/ORCL/users01.dbf | grep 123456
SQL> !strings /u01/app/oracle/oradata/ORCL/users01.dbf | grep sifreli
Here ends my writing on Oracle TDE. I've tried to tell you about Tablespace and column-level uses of TDE with details. I've shown especially possible mistakes and limitations in column-level encryption in practice. Hope you liked it.
Hope to see you in new posts,
Take care.
Comments