This article describes the best practices for using Oracle Advanced Security Transparent Data Encryption (TDE).
- Oracle Advanced Security TDE provides the ability to encrypt sensitive application data on storage media completely transparent to the application itself. TDE addresses encryption requirements associated with privacy and security mandates such as PCI, HIPPA, and so on.
- TDE transparently encrypts data at rest in Oracle Databases. It stops unauthorized attempts from the operating system to access database data stored in files, without impacting how applications access the data using SQL. TDE can encrypt entire application tablespaces or specific sensitive columns. TDE is fully integrated with the Oracle database. Encrypted data remains encrypted in the database, whether it is in tablespace storage files, temporary tablespaces, undo tablespaces, or other files that Oracle Database relies on such as redo logs. Also, TDE can encrypt entire database backups (RMAN) and Data Pump exports.
Benefits of Using TDE
- As a security administrator, you can be sure that sensitive data is encrypted and therefore safe in the event that the storage media or data file is stolen.
- Using TDE helps you address security-related regulatory compliance issues.
- You do not need to create auxiliary tables, triggers, or views to decrypt data for the authorized user or application. Data from tables is transparently decrypted for the database user and application. An application that processes sensitive data can use TDE to provide strong data encryption with little or no change to the application.
- Data is transparently decrypted for database users and applications that access this data. Database users and applications do not need to be aware that the data they are accessing is stored in encrypted form.
- You can encrypt data with zero downtime on production systems by using online table redefinition or you can encrypt it offline during maintenance periods.
- You do not need to modify your applications to handle the encrypted data. The database manages the data encryption and decryption
- Oracle Database automates TDE master encryption key and Keystore management operations. The user or application does not need to manage TDE master encryption keys.
Who Can Configure TDE
- A database administrator or a database security admin can configure TDE. You must be granted the
ADMINISTER KEY MANAGEMENTsystem privilege to configure Transparent Data Encryption (TDE).
- If you must open the Keystore at the mount stage, then you must be granted the
SYSKMadministrative privilege, which includes the
ADMINISTER KEY MANAGEMENTsystem privilege and other necessary privileges.
- When you grant the
SYSKMadministrative privilege to a user, ensure that you create a password file for it so that the user can connect to the database as
SYSKMusing a password. This enables the user to perform actions such as querying the
- To configure TDE column or tablespace encryption, you do not need the
ADMINISTER KEY MANAGEMENTYou must have the following additional privileges to create TDE policies on tables and tablespaces:
Securely Managing TDE Master Key
Oracle TDE uses a 2-tier key architecture where the Master Encryption Key stored outside the database and the Data Encryption Key stored within the database is responsible for encryption and decryption of data that is stored inside a database. It is always a good practice to store the Master Key in a Key Management Solution outside your database server to protect it from unintended deletion or server failures.
Fortanix Data Security Manager (DSM) provides FIFS 140-2 Level 3 compliant hardware security module appliances to store and manage the TDE master keys and provides secure access to database end-points for TDE online access. By default, the Oracle TDE master key stored in Fortanix DSM does not expire unless you explicitly define the expiry.
Type Consideration for TDE Implementation
You can encrypt sensitive data at the column level or the tablespace level. At the column level, you can encrypt data using selected table columns. TDE tablespace encryption enables you to encrypt all the data that is stored in a tablespace. Both TDE column encryption and TDE tablespace encryption use a two-tiered key-based architecture. Unauthorized users, such as intruders who are attempting security attacks, cannot read the data from storage and back up media unless they have the TDE Master Encryption Key to decrypt it.
It is often unclear for customers which type of TDE they should use that can provide better performance and suitable for their needs. This section will clarify that ambiguity and will provide a better understanding of the best practices while choosing TDE.
When to Choose TDE Tablespace Encryption?
- It is useful if your table contains sensitive data in multiple columns.
- If you want to protect the entire table and not just individual columns.
- TDE tablespace encryption takes advantage of bulk encryption and caching to provide enhanced performance. It is useful when you are using an OLTP database that does bulk writing/updates.
- When you do not want to modify the table structure.
When to Choose TDE Column Encryption
- When you have fewer columns to encrypt
- Columns holding the sensitive information are pre-known.
What to Expect After TDE Implementation
- It is very important to take into consideration that the encrypt/decrypt operations are overhead to the existing plans. Encryption and decryption are typically CPU intensive operations and would always require additional CPU resources.
- The most common problem is caused by the fact that column encryption is unable to use the range scan on indexes, because when the index columns are encrypted (all or some of them) the index keys are not sorted in the same order as in the non-encrypted form. Consequently, with TDE column encryption, an index range scan becomes a full table scan.
- If using tablespace encryption, queries requesting a large number of encrypted blocks (especially full table scan) would suffer from performance degradations. It is the expected overhead when reading a full table from the disk into the buffer cache.
- RMAN Backup size may increase after the implementation of TDE. You can use Oracle advanced compression feature to reduce the size of the backup, optionally if you are using any third-party backup solution then you can use their deduplication and other mechanisms to adjust the compression ratio.
- There will be an additional storage overhead with TDE implementation.
- In TDE Column encryption: between 1-52 bytes for each encrypted value caused by the following factor.
- 20 bytes overhead for integrity check
- 16 bytes overhead if ‘SALT’ is used to encrypt a column
- Padding (AES uses 16 bytes, 3DES uses 8 bytes)
- Tablespace encryption causes almost no storage overhead.
- In TDE Column encryption: between 1-52 bytes for each encrypted value caused by the following factor.
What to do to Alleviate the TDE Performance
- if the columns used in the predicates and joins of the statements are encrypted with SALT. Try encrypting these columns with NO SALT, as it generally should provide better performance.
- For tablespace encryption and full table scan operations:
- If a table is very large and queried mostly with full table scan operations, consider not storing it in an encrypted tablespace but use column encryption and encrypt just the sensitive columns.
- If a table is not very large, queried mostly with full table scan operations, and must reside in an encrypted tablespace, consider the possibility of keeping it as much as possible in the buffer cache by enabling the “keep buffer pool” and setting the table to use it. This way, only part of the blocks in the table would be read from disk and decrypted, significantly reducing the decryption overhead.
- Consider the possibility of building aggregate materialized views on such large tables residing in encrypted tablespaces. This way, the final data would reach in a faster way to the end-user.
- The degree of parallelism, the number, and power of the CPU is also an important factor that can reduce the time taken for a full table scan. Consider increasing the degree of parallelism for these problematic tables.
- If the queries are not using aggregate functions and yet returning a small number of records, then indexes would likely help. The statements should be tuned, and indexes added as needed on the tables.
- The NOMAC parameter enables you to skip the integrity check and also saves 20 bytes of disk space.
TDE Best Practices
The overhead of the TDE, be it column or tablespace encryption cannot be assessed without testing, it depends on the statement to statement. Please test thoroughly before implementing it.
Those are certain best practices that can be followed for a successful TDE implementation.
- Periodic key rotation: Perform TDE master key rotation every 6 months or 1 year depending on your internal security compliance.
- Full DB/table backup before TDE implementation: Take a full backup of the database or the table before encryption on tablespace or table column.
- Safeguard master key: It is important to understand that if you lose the TDE master key, you will not be able to access the encrypted data. Hence it is extremely important to safeguard the TDE master key from any unintended deletion or server failure. As per security compliance, it is always advisable to store the TDE master key outside the database server such as in an HSM.
- Avoid full table scan: If your sensitive data is stored in an encrypted table column or in an encrypted tablespace, avoid full table scan as full tablespace scan will impact the performance by doing a lot of disks I/O and CPU cycles to decrypt the data.
- Cache the small tables in buffer cache: It is always a better way to enhance the performance by keeping the small tables in the buffer cache for the encrypted tables.
- Leverage hardware acceleration: Hardware acceleration can help to reduce encryption/decryption overhead, all modern Intel CPU's support AES-NI.
- Use auto-login wallet: It is a good practice to use the Oracle DB auto-login wallet as it eliminates the manual effort of opening the Keystore from file or HSM during the database restart or other database/server maintenance activities.
- TDE restrictions: TDE tablespace encryption encrypts/decrypts data during read/write operations, as opposed to TDE column encryption, which encrypts/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, are not applicable to TDE tablespace encryption.
The following list includes the restrictions that apply to TDE tablespace encryption:
- External Large Objects (BFILEs) cannot be encrypted using TDE tablespace encryption. This is because these files reside outside the database.
- You should use Oracle data dump for import and export, not the standard
TDE Best Practice for Fortanix Data Security Manager Integration
As the database is a business-critical infrastructure in an enterprise, maintaining the high availability of a key management system that holds the database master key is absolutely important.
Fortanix DSM is designed to provide High availability to all critical infrastructure that needs access to the Fortanix DSM system for creating and accessing any security objects such as Oracle TDE master keys.
Here are some important suggestions that an Oracle DBA or security admin should consider when planning for Fortanix DSM integration with Oracle TDE.
- Appropriately configure the PKCS#11 library location. Oracle suggests keeping the PKCS#11 library in its designated location.
- You should create the
/etc/fortanixdirectory. Make sure you should not change the file name
pkcs11.confto any other name.
- If you want to change the path of the
pkcs11.conffile, you should export the patch for the following environment variable:
- Make sure you keep the following entries in the conf file:
prevent_duplicate_opaque_objects = true(It will prevent duplicate object creation if it is already available in sdkms)
retry_timeout_millis = 60000(This will make sure PKCS will retry connecting Fortanix DSM before the session expires)
- If you are expecting a network latency in the communication channel between Fortanix DSM and Database Server, then you should consider increasing the fault tolerance of the database by setting the below as sysdba. (from 12.1 onwards)
ALTER SYSTEM SET "_heartbeat_period_multiplier"= xx SCOPE=SPFILE;
ALTER SYSTEM SET "_heartbeat_config"=AUTOCONNECT SCOPE=SPFILE;
xxis used in the following way to determine the heartbeat interval:
3(initial seconds) =
xxvalue will be
39x3 + 3(initial seconds) =
- If you are using 11gR2 (18.104.22.168 and 22.214.171.124) you should consider setting the following event which will improve resilience to minor network outage when using TDE with an HSM.
ALTER SYSTEM SET EVENT='28420 trace name context forever, level 10:28421 trace name context forever, level 3' COMMENT='HSM heartbeat timeout and reconnect attempt' SCOPE=SPFILE;Event 28420 is used to determine how many HSM heartbeats can fail before the wallet is closed. The HSM heartbeat fires every 3 seconds which means that a very short network outage can lead to wallet closure. For example, if event 28420 is set to level 5 then the RDBMS will allow 5 heartbeats to fail before closing the wallet, which would allow a 15 second loss of contact with the HSM before closing the wallet. This event should NOT be used to disable the heartbeat functionality. We strongly advise that this value is set no higher than 20 (i.e. one-minute loss of HSM connectivity before wallet closure).
- Event 28420 is used to determine how many HSM heartbeats can fail before the wallet is closed. The HSM heartbeat fires every 3 seconds which means that a very short network outage can lead to wallet closure. For example, if event 28420 is set to level 5 then the RDBMS will allow 5 heartbeats to fail before closing the wallet, which would allow a 15-second loss of contact with the HSM before closing the wallet. This event should NOT be used to disable the heartbeat functionality. We strongly advise that this value is set no higher than 20 (i.e. one-minute loss of HSM connectivity before wallet closure).
- Event 28421 will cause the HSM heartbeat to attempt to reconnect with the HSM once the wallet has been closed, and if successful, reopen the wallet. For example, if event 28421 is set to level 3 then every 3rd heartbeat will attempt to re-establish connection with the HSM. Event 28421 is only useful if HSM has been configured using the auto-open feature. This means that a local
cwallet.ssomaintains the HSM credentials in the secret store entry