Concepts and Overview
Based on Database Advanced Security Guide – Oracle 12c Documentation
Oracle Transparent Data Encryption (TDE) enables the organizations to encrypt sensitive application data on storage media completely transparent to the application. TDE addresses encryption requirements associated with public and private privacy and security regulations such as PCI DSS. TDE column encryption was introduced in Oracle Database 10g Release 2, enabling encryption of table columns containing sensitive information. The TDE tablespace encryption and the support for hardware security modules (HSM) were introduced in Oracle Database 11gR1.
TDE is protecting the data at rest. It is encrypting the data in the datafiles so that in case they are obtained by other parties it will not be possible to access the clear text data. TDE cannot be used to obfuscate the data for the users who have privileges to access the tables. In the databases where TDE is configured any user who has access on an encrypted table will be able to see the data in clear text because Oracle will transparently decrypt the data for any user having the necessary privileges.
TDE is using a two tier encryption key architecture consisting of:
a master encryption key – this is the encryption key used to encrypt secondary keys used for column encryption and tablespace encryption
one or more table and/or tablespace keys – these are the keys that are used to encrypt one or more specific columns or the keys used to encrypt tablespaces. There is only one table key regardless of the number of encrypted columns in a table and it will be stored in the data dictionary. The tablespace key is stored in the header of each datafile of the encrypted tablespace.
The table and tablespace keys are encrypted using the master key. The master key is stored in an external security module (ESM) that can be one of the following:
an Oracle Wallet – a secure container outside of the database. It is encrypted with a password.
a Hardware Security Module (HSM) – a device used to secure keys and perform cryptographic operations. Oracle interfaces to the device using a PKCS#11 library supplied by the HSM vendor.
Currently it is possible to migrate the TDE master keys from the Oracle wallet to a HSM but it is not supported to migrate the master keys from the HSM back to the wallets. See Note 1282980.1 for details.
Oracle TDE is available by default in Oracle RDBMS Enteprise Edition (not in SE or SE2 Edition), but you have to purchase an Oracle Advanced Security license to use it.
Transparent Data Encryption (TDE) column encryption protects confidential data, such as credit card and Social Security numbers, that is stored in table columns. TDE column encryption uses the two-tiered key-based architecture to transparently encrypt and decrypt sensitive table columns. The TDE master encryption key is stored in an external security module, which can be an Oracle software keystore or hardware keystore. This TDE master encryption key encrypts and decrypts the TDE table key, which in turn encrypts and decrypts data in the table column.
Figure 2-1 an overview of the TDE column encryption process.
Figure 2-1 TDE Column Encryption Overview
Description of « Figure 2-1 TDE Column Encryption Overview »
As shown in Figure 2-1, the TDE master encryption key is stored in an external security module that is outside of the database and accessible only to a user who was granted the appropriate privileges. For this external security module, Oracle Database uses an Oracle software keystore (wallet, in previous releases) or hardware security module (HSM) keystore. Storing the TDE master encryption key in this way prevents its unauthorized use.
Using an external security module separates ordinary program functions from encryption operations, making it possible to assign separate, distinct duties to database administrators and security administrators. Security is enhanced because the keystore password can be unknown to the database administrator, requiring the security administrator to provide the password.
When a table contains encrypted columns, TDE uses a single TDE table key regardless of the number of encrypted columns. Each TDE table key is individually encrypted with the TDE master encryption key. All of the TDE table keys are located together in the colklc column of the ENC$ data dictionary table. No keys are stored in plaintext.
Transparent Data Encryption (TDE) column encryption encrypts and decrypts data at the SQL layer. Oracle Database utilities and features that bypass the SQL layer cannot use the services provided by TDE column encryption.
Do not use TDE column encryption with the following database features:
- Index types other than B-tree
- Range scan search through an index
- Synchronouschange data capture
In addition, you cannot use TDE column encryption to encrypt columns used in foreign key constraints.
Transparent Data Encryption (TDE) tablespace encryption enables you to encrypt an entire tablespace. All of the objects that are created in the encrypted tablespace are automatically encrypted. TDE tablespace encryption is useful if your tables contain sensitive data in multiple columns, or if you want to protect the entire table and not just individual columns. You do not need to perform a granular analysis of each table column to determine the columns that need encryption.
In addition, TDE tablespace encryption takes advantage of bulk encryption and caching to provide enhanced performance. The actual performance impact on applications can vary.
TDE tablespace encryption encrypts all of the data stored in an encrypted tablespace including its redo data. TDE tablespace encryption does not encrypt data that is stored outside of the tablespace. For example, BFILE data is not encrypted because it is stored outside the database. If you create a table with a BFILE column in an encrypted tablespace, then this particular column will not be encrypted.
All of the data in an encrypted tablespace is stored in encrypted format on the disk. Data is transparently decrypted for an authorized user having the necessary privileges to view or modify the data. A database user or application does not need to know if the data in a particular table is encrypted on the disk. In the event that the data files on a disk or backup media is stolen, the data is not compromised.
TDE tablespace encryption uses the two-tiered, key-based architecture to transparently encrypt (and decrypt) tablespaces. The TDE master encryption key is stored in an external security module (software or hardware keystore). This TDE master encryption key is used to encrypt the TDE tablespace encryption key, which in turn is used to encrypt and decrypt data in the tablespace.
Figure 2-2 shows an overview of the TDE tablespace encryption process.
Figure 2-2 TDE Tablespace Encryption
Description of « Figure 2-2 TDE Tablespace Encryption »
The encrypted data is protected during operations such as JOIN and SORT. This means that the data is safe when it is moved to temporary tablespaces. Data in undo and redo logs is also protected.
TDE tablespace encryption also allows index range scans on data in encrypted tablespaces. This is not possible with TDE column encryption.
Oracle Database implements the following features to TDE tablespace encryption:
- It uses a unified TDE master encryption key for both TDE column encryption and TDE tablespace encryption.
- You can reset the unified TDE master encryption key. This provides enhanced security and helps meet security and compliance requirements.
You should be aware of restrictions on using Transparent Data Encryption when you encrypt a tablespace.
Note the following restrictions:
- TransparentData Encryption (TDE) tablespace encryption encrypts or decrypts data during read and write operations, as opposed to TDE column encryption, which encrypts and decrypts data at the SQL layer. This means that most restrictions that apply to TDE column encryption, such as data type restrictions and index type restrictions, do not apply to TDE tablespace encryption.
- To perform import and export operations, use Oracle Data Pump.
By default, Transparent Data Encryption (TDE) Column encryption uses the Advanced Encryption Standard with a 192-bit length cipher key (AES192). In addition, salt is added by default to plaintext before encryption unless specified otherwise. You cannot add salt to indexed columns that you want to encrypt. For indexed columns, choose the NO SALT parameter for the SQL ENCRYPT clause.
For Transparent Data Encryption (TDE) Tablespace encryption, the default is to use the Advanced Encryption Standard with a 128-bit length cipher key (AES128). In addition, salt is always added to plaintext before encryption.
You can change encryption algorithms and encryption keys on existing encrypted columns by setting a different algorithm with the SQL ENCRYPT clause.
Table 2-1 lists the supported encryption algorithms.
Table 2-1 Supported Encryption Algorithms for Transparent Data Encryption
|Triple Encryption Standard (DES)||168 bits|
|Advanced Encryption Standard (AES)||128 bits|
|AES||· Default for column level encryption is 192 bits
· Default for tablespace encryption is 128 bits
TDE column encryption is limited by the fact we couldn’t encrypt columns used in foreign key constraints, nor we could use IOT index.
Due to some complexity for data model used, and difficulty to pre validate any change on alter / create of table, Dba Team recommend for these 2 reasons to use the TDE Tablespace Encryption Method.
To host the Master Key, we have 2 possibilities :
- Creation of a Software Keystore, that’s a file located on your storage, whose location would be indicate in sqlnet.ora file. Oracle provides the procedure to create the Master Key and to automatically open it.
- Creation of a Hardware Keystore, we have to configure in sqlnet.ora we use an HSM, and to add HSM API in the right directory. And Follow your HSM vendor’s instructions to set up the hardware security module.
The Keystore management solution should be decided with security team and TO PCI responsible.
Key used by Oracle to crypt/decrypt the data are generated directly by command.
Note : To set the TDE master encryption key in a software keystore, the DB user must have the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
1 Ensure you parameter the Software Keystore Location in the sqlnet.ora File
ENCRYPTION_WALLET_LOCATION= (SOURCE= (METHOD=FILE) (METHOD_DATA= (DIRECTORY=+ASM_file_path_of_the_diskgroup) ) )
2 Log in to the database instance as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
In a multitenant environment, log in to the root. For example:
sqlplus c##sec_admin as syskm Enter password: password Connected.
If SQL*Plus is already open and you had modified the sqlnet.ora file during this time, then reconnect to SQL*Plus. The database session must be changed before the sqlnet.orachanges can take effect.
3 Run the ADMINISTER KEY MANAGEMENT SQL statement to create the keystore.
The syntax is as follows:
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE ‘keystore_location‘ IDENTIFIED BY software_keystore_password;
In this specification:
- keystore_locationis the path to the keystore directory location of the password-based keystore for which you want to create the auto-login keystore (for example,/etc/ORACLE/WALLETS/orcl). Enclose the keystore_location setting in single quotation marks (‘ ‘). To find this location, you can query the WRL_PARAMETER column of theV$ENCRYPTION_WALLET (If the keystore was not created in the default location, then the STATUS column of the V$ENCRYPTION_WALLET view is NOT_AVAILABLE.)
- software_keystore_passwordis the password of the keystore that you, the security administrator, creates.
For example, to create the keystore in the /etc/ORACLE/WALLETS/orcl directory:
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/etc/ORACLE/WALLETS/orcl' IDENTIFIED BY password; keystore altered.
After you run this statement, the ewallet.p12 file, which is the keystore, appears in the keystore location.
Creating an auto-login keystore
ADMINISTER KEY MANAGEMENT CREATE [LOCAL] AUTO_LOGIN KEYSTORE FROM KEYSTORE '/etc/ORACLE/WALLETS/orcl' IDENTIFIED BY password; SHUTDOWN IMMEDIATE; STARTUP
After you run this statement, the
cwallet.sso file appears in the keystore location. The
ewallet.p12 file is the password-based wallet.
Note : Key Holder set the Master Key password, following Key management Process from security Team ( Key holders only are authorized to know the master Key).
Set the COMPATIBLE Initialization Parameter for Tablespace Encryption
Check the COMPATIBLE parameter for the database is set to 18.104.22.168 or later.
Set the Tablespace TDE Master Encryption Key
For password software keystores, ensure that you complete the procedure described in « Step 3: Open the Software Keystore » to open the key.
Auto-login or local auto-login software keys are opened automatically after you create them. Password-based software keystores must be open before you can set the TDE master encryption key. If the auto-login software keystore is open, then you must close it and open the password-based software keystore. If both the password-based keystore and auto-login keystores are present in the configured location and the password-based keystore is open, then the TDE master encryption key is automatically written to the auto-login keystore as well.
Log in to the database instance as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
In a multitenant environment, log in to the root or to the PDB. For example, to log in to a PDB:
sqlplus sec_admin@hrpdb as syskm Enter password: password Connected.
To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the show con_name command.
Ensure that the database is open in READ WRITE mode.
You can set the TDE master encryption key if OPEN_MODE is set to READ WRITE. To find the status, for a non-multitenant environment, query the OPEN_MODE column of the V$DATABASEdynamic view. If you are using a multitenant environment, then query the V$PDBS view. (If you cannot access these views, then connect as SYSDBA and try the query again. In order to connect as SYSKM for this type of query, you must create a password file for it. See Oracle Database Administrator’s Guide for more information.)
- Connect using the SYSKM administrative privilege and then run the ADMINISTER KEY MANAGEMENT SQL statement to set the software management keystore.
ADMINISTER KEY MANAGEMENT SET KEY [USING TAG ‘tag‘] IDENTIFIED BY password [WITH BACKUP [USING ‘backup_identifier‘]] [CONTAINER = ALL | CURRENT];
In this specification:
- tagis the associated attributes and information that you define. Enclose this setting in single quotation marks (‘ ‘).
- passwordis the mandatory keystore password that you created when you created the keystore in « Step 2: Create the Software Keystore ».
- WITH BACKUPcreates a backup of the keystore. You must use this option for password-based keystores. Optionally, you can use the USING clause to add a brief description of the backup. Enclose this description in single quotation marks (‘ ‘). This identifier is appended to the named keystore file (for example, ewallet_time_stampp12, with emp_key_backup being the backup identifier). Follow the file naming conventions that your operating system uses.
- CONTAINERis for use in a multitenant environment. Enter ALL to set the key in all of the PDBs in this CDB, or CURRENT for the current PDB.
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY password WITH BACKUP USING 'emp_key_backup'; keystore altered.
Create the Encrypted Tablespace
- Log in to the database instance as a user who has been granted the CREATE TABLESPACE system privilege.
In a multitenant environment, log in to the PDB. For example:
sqlplus sec_admin@DB as syskm Enter password: password Connected.
- Run the CREATE TABLESPACE statement, using its encryption clauses.
CREATE TABLESPACE D_CRYPT DATAFILE '+DATA' SIZE 10G ENCRYPTION USING 'AES256' DEFAULT STORAGE (ENCRYPT);
Alter table TABLE_WITH_PAN move online tablespace D_CRYPT
In 11g, equivalent commands to create master key and keystore were :
-- master key creation sqlplus / as sysdba db11p15.SYS > alter system set encryption key identified by "PASSWORD"; System altered. db11p15.SYS > ! ls /appli/oracle/admindb/admin/$ORACLE_SID/wallet ewallet.p12 quit # wallet creation (auto login) [oracle]$ orapki wallet create -wallet orapki wallet create -wallet /etc/ORACLE/WALLETS/orcl -auto_login Oracle PKI Tool : version : 22.214.171.124.0 - Production Copyright (c) 2004, 2013, Oracle et/ou ses filiales. Tous droits réservés Entrez le mot de passe du portefeuille : [oracle]$
Documentation link Database Advanced Security Guide / TDE :