Transparent Data Encryption (TDE) – Oracle

Yes, TDE is designed to provide customers the ability to transparently apply encryption within the database without impacting existing applications. Returning data in encrypted format would break most existing applications. TDE provides the benefit of encryption without the overhead associated with traditional database encryption solutions that typically require expensive and lengthy changes to applications, incl. database triggers and views. Oracle Database Vault can be used to protect application data from the DBA and other powerful users as well as implementing robust controls on access to the database and application.

These numbers are important for storage planning, but DBAs or developers don't have to manually expand the columns for TDE column encryption; the expansion is done transparently by TDE when a column is marked 'encrypted'.

Users can reduce the amount of additional storage by choosing the 'no salt' option (16 byte saved), and/or the 'nomac' option (available from 10.2.0.4, 11.1.0.7 and Oracle Database 11g Release 2), which eliminates the additional CPU cycles and disk space needed for calculating and storing the 20 byte hash value for each encrypted field.

TDE supports AES256, AES192 (default for TDE column encryption), AES128 (default for TDE tablespace encryption), and 3DES168.

No, it is not possible to plug-in other encryption algorithms. Oracle provides encryption algorithms that are broadly accepted, and will add new standard algorithms as they become available.

TDE doesn't support encrypting columns with foreign key constraints. This is due to the fact that individual tables have their own unique encryption key. The following query lists all occurrences of RI (Referential Integrity) constraints in your database:

Yes. Joining tables is transparent to users and applications, even if the columns for the join condition are encrypted.

TDE tablespace encryption supports all indexes transparently.

For TDE column encryption, the index needs to be a normal B-tree index, used for equality searches. In case of a composite, function-based index, the encrypted column cannot be the one that was used for the function. When encrypting a column with an existing index, it is recommended to first extract the index definition with dbms_metadata.get_ddl, then drop the index, encrypt the column with the 'no salt' option, and re-build the index.

For TDE tablespace encryption, there are no limitations in terms of supported data types; the following data types can be encrypted using TDE column encryption:

Data encrypted with TDE is decrypted when it is read back from database file. Thus if this data goes on the network, it is clear-text data. However, the data can be encrypted using Oracle's network encryption solution (Example), which is included along with TDE in the Oracle Advanced Security option. Oracle's network encryption solution encrypts all data traveling to and from a database over SQL*Net.

With TDE column encryption, encrypted data remains encrypted inside the SGA, but with TDE tablespace encryption, data is already decrypted in the SGA, which provides 100% transparency.

If you have to comply to the PCI-DSS standard, then credit card numbers (a.k.a. Primary Account Number, or PAN) need to be stored encrypted.

The need to comply to the almost ubiquitous Breach Notification Laws (for example CA SB 1386, CA AB 1950, and similar laws in 43+ more US states), adds first name, last name, driver license number and other PII to your list. In early 2008, CA AB 1298 added medical and health insurance information to PII data.

Additionally, your industry specific privacy and security standards may require encryption of certain assets, plus your own core business assets (such as research results in the pharmaceutical industry, results of oil field exploration, financial contracts, or the personal details of informants in law enforcement) may be worth encrypting to safeguard this information on the storage medium. In the health care industry, the privacy of patient data, health records and X-ray images is of the highest importance. Most X-ray images are stored following the DICOM standard, which intentionally includes PII information into the image meta data, making image and patient data readily available to an intruder if not properly protected through encryption. With Oracle Database 11g, DICOM images can be stored in 'SecureFile' columns where they can be encrypted either with TDE column encryption, or tables with 'SecureFile' columns (or classic LOB columns) can be stored in an encrypted tablespace.

This is the most difficult task ahead of a security team or team of DBAs when using TDE column encryption:

If you run applications that were developed in-house, chances are you can locate tables with sensitive information by talking to your developers.

It is more difficult when you run packaged software applications. Since privacy and security requirements are different for each of the deployments of these applications, vendors themselves cannot readily determine what to encrypt. If PCI compliance is the goal, and the column names of the application tables are named similar to 'CREDIT_CARD' or 'ACCOUNT_NUMBER', they are easy to find using Oracle's rich metadata repository.

More complex is the search for sensitive data when column names are not descriptive about their content; the only method of finding sensitive content is the search for patterns: Social Security Numbers always look like 'aaa-bb-cccc', but Credit Card Numbers are less consistent: They have 13 or 16 digits, and are not always grouped by 4 digits.

If you need to encrypt columns that have characteristics which are not supported by TDE column encryption (in terms of indexes, data types, or foreign keys), or if it is not possible to locate columns that store sensitive data in application tables, TDE tablespace encryption is your best choice.

Use TDE tablespace encryption if any of the following is true:

Oracle introduced an encryption package ('dbms_obfuscation_toolkit') with Oracle8i. In Oracle 10g Release 1, the new 'dbms_crypto' package was introduced. These APIs can be used to manually encrypt data within the database. However, the application must manage the encryption keys and perform required encryption and decryption operations by calling the API.

As opposed to dbms_obfuscation_toolkit and dbms_crypto, both TDE column encryption (from 10gR2) and TDE tablespace encryption (from 11gR1) don't require changes to the application, are transparent to the end users, and provide automated, built-in key management.

TDE is part of the Oracle Advanced Security Option, which also includes Network Encryption and Strong Authentication. It is available for the Oracle Enterprise Edition.

A wallet is an encrypted container that is used to store authentication and signing credentials, including passwords, the TDE master key, PKI private keys, certificates, and trusted certificates needed by SSL. With TDE, wallets are used on the server to protect the TDE master key. With the exception of Diffie-Hellman, Oracle requires entities that communicate over SSL to have a wallet containing an X.509 version 3 certificate, private key, and list of trusted certificates.

Oracle provides two different types of wallets: encryption wallet and (local) auto-open wallet. The encryption wallet (filename 'ewallet.p12') is the one recommended for TDE. It needs to be opened manually after database startup and prior to TDE encrypted data being accessed. Because data is encrypted in REDO logs, UNDO and TEMP tablespaces, the TDE master encryption key needs to be available to the database before it is opened:

On Unix, access to the wallet should be limited to the 'oracle:oinstall' user:group, using proper directory (700) and file permissions (600). Even though the 'root' user has access to the wallet file, if she does not know the wallet password, she has no access to the master encryption key. For all platforms, the password (that encrypts the wallet) should contain a minimum of 8 alphanumeric characters. Wallet passwords can be changed using Oracle Wallet Manager, or the 'orapki' utility. It is highly recommended to make a backup of the Oracle Wallet before changing the wallet password. Changing the wallet password does not change the TDE master key (they are independent). Starting with Oracle Database 11g Release 2 (11.2.0.2) on Linux, it is recommended to store the Oracle Wallet in ACFS, a cluster file system on top of ASM (applies to single instance, RAC one node, multi-node RAC, but not Exadata X2), as it's new Security features provide excellent wallet protection and separation of duties. A detailed step-by-step guide on how to create an access control policy in ACFS incl. separation of duties is available in the frequently updated TDE best practices document.

If you create a wallet with Oracle Wallet Manager, it does not contain the master key required by TDE. Only the SQL command:

creates a wallet (if it doesn't already exist in the location specified in the local sqlnet.ora file) and adds the TDE master key to it.

In Oracle 11gR1, TDE and other security features have been migrated to Enterprise Manager Database Control, thus enabling the wallet and the master key to be generated using the Web-based GUI of Enterprise Manager.

New in Oracle 11g Release 2 is the unified master encryption key, which is used for both TDE column and TDE tablespace encryption; this key can be created, stored and re-keyed (rotated) in the Oracle Wallet.

Yes, the wallet password can be changed with Oracle Wallet Manager (OWM). Create a backup before attempting to change the wallet password. Changing the wallet password does not change the encryption master key they are independent. In Oracle 11gR1 11.1.0.7, orapki has been enhanced to allow wallet password changes from the command line:

A password-protected, encrypted wallet for the TDE master key might not be the right solution when database availability needs to be maintained without human intervention ('lights-out' operation); a (local) auto-open wallet does not require a wallet password after a database came up, so encrypted data is available to authorized users and applications.

A (local) auto-open wallet ('cwallet.sso') needs to be created from an existing encryption wallet ('ewallet.p12'), so that the master key can be transferred to the new auto-open wallet.

You can either open the encryption wallet in Oracle Wallet Manager (OWM), check the 'Auto Login' check box, then select 'Save' to write the auto-open wallet to disk, or, using the command-line tool 'orapki':

The syntax to create a local auto-open wallet is:

In both cases (Oracle Wallet Manager and 'orapki') the user will be prompted for the wallet password. Keep the encryption wallet; it is required for master key re-key operations, and potentially contains a list of retired master keys.

RMAN only adds database files, redo-logs etc. to the backup file, and thus there is no risk of the encryption wallet or the auto-open wallet becoming part of a database backup. Oracle Secure Backup (OSB) uses datasets to define which operating system files to add to a backup. OSB automatically excludes auto-open wallets ('cwallet.sso'). Encryption wallets ('ewallet.p12') are NOT automatically excluded; you need to use the exclude dataset statement to specify what files to skip during a backup:

Backup the Oracle wallet right after creating it, and each time it's content changes, for example due to a master key re-key operation, and each time you change the wallet password. Always store the wallet (encrypted or (local) auto-open) away from your database backups.

Oracle invests in compatibility testing for a range of software solutions including applications that are part of the integrated Oracle hardware-software stack and other third-party applications. The table below summarizes these application certifications. For further details, refer to the linked pages and files.

Transparent Data Encryption is a great way to protect sensitive data in large-scale Exadata scenarios. With Exadata, substantial crypto performance gains are possible. Unique factors in Exadata that maximize the crypto performance include:

For example, the hardware-based crypto acceleration in Exadata alone can improve performance by up to 10x (relative to without hardware acceleration).

Below is a table that summarizes the performance characteristics of Exadata X2 systems across compute and storage. The table highlights where hardware-based crypto accleration may be enabled.

Note: In Oracle Exadata V2 and X2, the table keys (for TDE column encryption) or tablespace keys (for TDE tablespace encryption) are sent to the storage cells, so that content can be first decrypted and then, Smart Scan is applied. Content is encrypted on the compute nodes. Decryption usually takes place in the compute nodes, but when queries are pushed to the storage nodes, decryption takes place there to enable Smart Scan

Oracle Secure Backup provides an optimized, highly efficient tape backup solution for the Oracle Database. OSB can store data on tape in encrypted form, providing protection against theft of backup tapes.

Example for 'transparent' encryption [and compression] when the local TDE master encryption key is available:

A license of the Advanced Security Option is neccessary to encrypt RMAN backups to disk, regardless if the TDE master encryption key or a passphrase is used to encrypt the file.

No, however, Oracle RMAN can be used in conjunction with Oracle Advanced Security to encrypt database backups sent to disk. This requires a license of the Oracle Advanced Security Option.

Yes, but it requires that the wallet containing the master key is copied to the secondary database. If the tablespace is moved and the master key is not available, the secondary database will return an error when the data in the tablespace is accessed.

Customers using TDE tablespace encryption get the full benefit of compression (standard and Advanced Compression, as well as Exadata Hybrid Columnar Compression (EHCC)) because compression is applied before the data blocks are encrypted. Customers using TDE column encryption will get the full benefit of compression only on table columns that are not encrypted. Individual table columns that are encrypted using TDE column encryption will have a much lower level of compression because the encryption takes place in the SQL layer before the advanced compression process.

When TDE is used with Data Guard physical standby (10gR2 and later), encrypted data remains encrypted in the log files during shipping to the secondary database(s), so ASO Network Encryption is optional to encrypt data in transit that has not be encrypted on disk; Metalink note 749947.1 explains how to setup ASO native network encryption, while Metalink note 1143443.1 explains how to setup SSL based encryption. The master key needs to be present and open on any Physical Standby database site, whether just applying redo, open read only, open in Active Data Guard (read only and applying redo) and for role transition (switchover or failover).

When TDE is used with Data Guard logical standby (11gR1), the master key needs to be present and open at the secondary site for SQL Apply to decrypt the data that it reads from the log files. The same master encryption key can also be used to optionally encrypt the incoming data while it is written to the Logical Standby database. Encrypted data remains encrypted in log files and during transit when the log files are shipped to the secondary database; Oracle Network Encryption is optional. Metalink note 749947.1 explains how to setup ASO native network encryption, while Metalink note 1143443.1 explains how to setup SSL based encryption.

When TDE is used with Streams in 11gR1, data is transmitted between active databases in clear text to allow data transformation (character sets, database versions, platforms, etc.). When the receiving side cannot be reached and data needs to be stored temporarily, encrypted columns are stored encrypted on disk. Streams in database versions prior to 11gR1 treat encrypted columns as 'unsupported data types' and skip these tables.

The traffic can be encrypted either with blowfish or SSH port forwarding

TDE tablespace encryption encrypts all content stored in that tablespace and does not conflict with any other database feature. TDE column encryption encrypts and decrypts data transparently when data passes through the SQL layer. Some features of Oracle bypass the SQL layer, and hence cannot benefit from TDE column encryption:

Yes, you can. When the target table contains encrypted columns, the data will be encrypted upon loading the data. Here is a simple example on how to use SQL*Loader with direct path. Simply modify one column in ulcase6.sql from

to

and use the correct syntax for SQL*Loader:

This is no different from finding the data still on the disk even after a table is dropped, or a file is deleted. During the lifetime of a table, data may become fragmented, re-arranged, sorted, copied and moved within the tablespace; this leaves 'ghost copies' of your data within the database file. When encrypting an existing column, only the most recent 'valid' copy is encrypted, leaving behind older clear-text versions in ghost copies. If the data file holding the tablespace is directly accessed bypassing the access controls of the database (for example with an hex editor), old clear text values might be visible for some time, until those blocks are overwritten by the database. To minimize this risk, please follow these recommendations:

The 6th step is recommended to lower the probability of being able to find ghost copies of the database file, generated by either the operating system, or storage firmware.

(*): Content can be moved from one encrypted tablespace to a new encrypted tablespace, where it is encrypted with a new tablespace key.

TDE uses a two tier key mechanism. When TDE column encryption is applied to an existing application table column, a new table key is created and stored in the Oracle data dictionary. When TDE tablespace encryption is used, the individual tablespace keys are stored in the header of the underlying OS file(s). The table and tablespace keys are encrypted using the TDE master encryption key. The master encryption key is generated when TDE is initialized and stored outside the database in the Oracle Wallet. Both the master key and table keys can be independently changed (rotated, re-keyed) based on company security policies. Tablespace keys cannot be re-keyed (rotated); work around is to move the data into a new encrypted tablespace. Oracle recommends backing up the wallet before and after each master key change.

Changing the wallet password does not re-key the TDE master encryption key.

Encrypting columns in an existing table is an 'update' operation and allows Read access, but no DML operations, on that table. With billions of rows, this window of limited availability can last several hours. But with Online Table Redefinition, a mature High-Availability feature of the Oracle Database, the table is locked in exclusive mode only during a very small window that is independent of the size of the table and complexity of the redefinition, and that is completely transparent to users and applications, without any data loss.

Starting in Oracle Database 11g Release 2, customers of Oracle Advanced Security Transparent Data Encryption (TDE) optionally may store the TDE master encryption key in an external device using the PKCS11 interface. In this setup, the master key is stored directly in the third-party device rather than in the included Oracle Wallet (note: the Oracle Wallet is a PKCS12 file-based keystore which is used by most TDE customers).

When using PKCS11, the third-party vendor provides the storage device, PKCS11 software client library, secure communication from the device to the PKCS11 client (running on the database server), authentication, auditing, and other related functionality. The vendor also is responsible for testing and ensuring high-availability of the TDE master encryption key in diverse database server environments and configurations. Customers should contact the device vendor to receive assistance for any related issues.

Read more from the original source:
Transparent Data Encryption (TDE) - Oracle