Back to the Advanced Security Homepage

Transparent Data Encryption (TDE)

Frequently Asked Questions

Questions

  1. Will encrypted data be decrypted for all users who have been authorized to see it?
  2. What is the overhead associated with TDE?
  3. What are the encryption algorithms that can be used with TDE?
  4. Is it possible to use 3rd party encryptions algorithms in place of the ones provided by TDE?
  5. Can I use TDE column encryption on columns used in foreign key constraints?
  6. Can columns that are used for joins be encrypted?
  7. Can indexed columns be encrypted?
  8. What data types and data lengths does TDE column encryption support?
  9. Does the data stay encrypted on the network?
  10. Does the database memory (SGA) contain clear-text or encrypted data?
  11. How do I know which data to encrypt?
  12. Where is the data that needs to be encrypted?
  13. With Oracle Database 11gR1, shall I use TDE column encryption or TDE tablespace encryption?
  14. How is TDE different from the encryption toolkit Oracle already provides?
  15. How is TDE licensed?

Wallet Management

  1. What is a Wallet?
  2. How is the TDE wallet protected?
  3. Can I use Oracle Wallet Manager (OWM) to create the encryption wallet and master key for TDE?
  4. Can I change the wallet password?
  5. How do I create a (local) auto-open wallet?
  6. How do I prevent the Oracle TDE wallet from being backed up on the same tape as the RMAN database backups when using Oracle Secure Backup?
  7. Best practices for wallet backup

Cross-component Integration

  1. Which Oracle applications are certified with Transparent Data Encryption?
  2. Can I encrypt data stored in Exadata?
  3. What is Oracle Secure Backup (OSB)?
  4. How does Oracle RMAN treat encrypted data?
  5. Can I encrypt backups sent to disk using Oracle Secure Backup?
  6. Do transportable tablespaces work with TDE tablespace encryption?
  7. Does compression work with TDE?
  8. Does TDE work with Data Guard, Streams, and Oracle Golden Gate?
  9. Are there database features that TDE column encryption does not work with?
  10. Can I use SQL*Loader with direct path to load data into a table with encrypted columns?

Best Practices

  1. Why can I sometimes still see clear-text data after encrypting sensitive information with TDE column encryption?
  2. How do I change (rotate, re-key) the encryption keys?
  3. How do I quickly encrypt columns in very large tables with billions of rows?
  4. How do I migrate the content from clear text application tablespaces to encrypted tablespaces?
  5. Miscellaneous Questions

  6. Can TDE store its master encryption key in an external device using the PKSC11 interface?

 

Answers

  1. Will encrypted data be decrypted for all users who have been authorized to see it?

    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.


  2. What is the overhead associated with TDE?

    Table A: What is the overhead associated with TDE?
      TDE tablespace encryption
    (Oracle Database 11g)
    TDE column encryption
    (Oracle Database 10gR2,
    Oracle Database 11g)
    Storage No additional storage overhead. Storage overhead associated with TDE column encryption is between 1 and 52 bytes for each encrypted value:
    1. Mandatory: Padding to the next 16 byte (for AES; with 3DES168, to the next 8 bytes). When a value required 9 bytes of storage, encrypting this value requires an additional 7 bytes of storage.
    2. Optional: Additional 20 byte integrity check
    3. Optional: If 'SALT' is specified on the encrypted column, an additional 16 bytes per value is required

    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.

    Performance According to internal benchmarks and feedback from successful production implementations, the performance overhead is in the single digits. With Oracle Database 11g Release 2 Patchset 1 (11.2.0.2), the hardware crypto acceleration based on AES-NI available in most Intel® XEON® 5600 CPUs is automatically leveraged by TDE tablespace encryption, making TDE tablespace encryption a 'near-zero impact' encryption solution, specifically for datawarehouse environments. The performance overhead associated with encrypting or decrypting a common attribute such as a credit card number column with TDE column encryption is estimated to be around 5%. When an index is built on an encrypted column, the index will be built using the cipher text. If a TDE encrypted column is indexed and referenced in a SQL statement, Oracle will transparently encrypt the value used in the SQL statement with the table key and perform an index lookup using the cipher text; if all indexes are re-build the way they were originally designed before TDE column encryption was applied, customers reported a negligible performance impact. It is recommended that customers, who plan to use TDE column encryption, upgrade to Oracle Database 10gR2 10.2.0.4/5 (and apply patch 7639262) or Oracle Database 11gR1 11.1.0.7 (and apply patch 8421211), since both include changes that reduce the performance overhead. Both patches are integrated in Oracle Database 11g Release 2.

  3. What are the encryption algorithms that can be used with TDE?

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


  4. Is it possible to use 3rd party encryptions algorithms in place of the ones provided by TDE?

    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.


  5. Can I use TDE column encryption on columns used in foreign key constraints?

    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:

      SQL> select A.owner, A.table_name, A.column_name, A.constraint_name 
           from dba_cons_columns A, dba_constraints B 
           where A.table_name = B.table_name and B.constraint_type = 'R';

  6. Can columns that are used for joins be encrypted with TDE column encryption?

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


  7. Can indexed columns be 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.


  8. What data types and data lengths does TDE support?

    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:

    varchar2 (< 3933 characters)		nvarchar2 (< 1967 characters)
    char (< 1933 characters)		nchar (< 967 characters)
    number					raw
    binary_float				binary_double
    timestamp				date
    SecureFile (11gR1 and later)

  9. Does the data stay encrypted on the network?

    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.


  10. Does the database memory (SGA) contain clear-text or encrypted data?

    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.


  11. How do I know which data to encrypt?

    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.


  12. Where is the data that needs to be encrypted?

    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.


  13. With Oracle Database 11g, shall I use TDE column encryption or TDE tablespace encryption?

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

    1. You are looking for the most performant encryption solution. TDE tablespace encryption has better, more constant performance characteristics in most cases. Moreover, tablespace encryption in particular leverages hardware-based crypto acceleation where it is available, minimizing the performance impact even further to the 'near-zero' range. Support for hardware-based crypto accelaration is available in Oracle Database 11g Release 2 Patchset 1 (11.2.0.2) for Intel® XEON® 5600 CPUs with AES-NI.
    2. You cannot find all columns with sensitive content
    3. Data type and/or data length of sensitive column is not supported by TDE column encryption
    4. Sensitive column is used as foreign key
    5. Applications perform range scans over indexed, encrypted columns
    6. You need index types other than B-tree over encrypted columns

  14. How is TDE different from the encryption toolkit Oracle already provides?

    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.


  15. How is TDE licensed?

    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.


  16. What is the Oracle Wallet?

    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:

      $ sqlplus / as sysoper
      PUBLIC> startup mount;
      ORACLE instance started.
      Database mounted.
      PUBLIC> alter system set encryption wallet open identified by "wallet_password";
      System altered.
      PUBLIC> alter database open;
      Database altered.
    If the Wallet is not open, the database will return an error when TDE protected data is queried. The (local) auto-open wallet (filename 'cwallet.sso') opens automatically when a encrypted data is accessed; hence it can be used for unattended Data Guard (Oracle 10gR2: physical standby only; Oracle 11g: physical and logical standby) environments where encrypted data is shipped to secondary sites. Do never delete the encryption wallet after creating an auto-open wallet, since otherwise master encryption key re-key operations will fail.
    Oracle Database 11g Release 2 introduced the local auto-open wallet, which only opens automatically on the server it was created on.

  17. How is the TDE wallet protected?

    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.


  18. Can I use Oracle Wallet Manager (OWM) to create the encryption wallet and master key for TDE?

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

     SQL> alter system set encryption key identified by "wallet_password";

    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.


  19. Can I change the wallet password?

    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:

     $ orapki wallet change_pwd -wallet <wallet_location>

  20. How do I create a (local) auto-open wallet?

    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':

      $ orapki wallet create -wallet <wallet_location> -auto_login

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

      $ orapki wallet create -wallet <wallet_location> -auto_login_local

    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.


  21. How do I prevent the Oracle TDE wallet from being backed up on the same tape as the RMAN database backups when using Oracle Secure Backup?

    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:

      exclude name *.p12

  22. Best practices for wallet 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.


  23. Which packaged applications are certified with Transparent Data Encryption?

    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.

    Table B: Applications certified with Transparent Data Encryption
    TDE tablespace encryption
    (Oracle Database 11g)
    TDE column encryption
    (Oracle Database 10.2.0.4/5 or
    Oracle Database 11g)
    Oracle E-Business Suite (Datasheet) Click here for current updates
    Oracle PeopleSoft Enterprise 8.48+
    (Datasheet | Red Paper | Migration Guide)
    Oracle PeopleSoft Enterprise 8.46+ (Datasheet)
    Oracle Siebel CRM 8.0+ (Datasheet) Oracle Siebel CRM 7.7+
    Oracle JD Edwards EnterpriseOne (Datasheet) Oracle Financial Services (iFlex) FlexCube 10.0
      Oracle Retail Applications (Retek):
    • ReSA 12.0+ and 13.0 (10gR2)
    • ReSA 13.1 (11gR1)
      Infosys Finacle
    SAP 6.40_EX2+ (UNIX and Linux only) SAP 6.40 and later (SAP note 974876)
    Oracle Internet Directory 10.1.4.2 (White paper)


  24. Can I encrypt data stored in Exadata?

    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:

    1. Optimized Oracle hardware and software within the Exadata stack
    2. Distributed crypto processing across discrete storage and compute nodes
    3. Native features of Exadata such as Smart Scan and Hybrid Columnar Compression (EHCC)
    4. The availability of hardware-based crypto acceleration

    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.

    The speedup comparisons are based on encryption/decryption throughput measured with and without hardware acceleration enabled
    Exadata Model X2-2 X2-8
    Node Encrypt Decrypt Encrypt Decrypt
    Compute Enable hardware-acceleration in Intel Xeon X5670 with patch 10080579 Hardware acceleration in Intel Xeon X5670 enabled by default Hardware acceleration through Nehalem technology in Intel® X7560
    Storage n/a Hardware acceleration in Intel Xeon L5640 enabled by default n/a Hardware acceleration in Intel Xeon L5640 enabled by default

    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


  25. What is Oracle Secure Backup (OSB)?

    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.


  26. How does Oracle RMAN treat encrypted data?

    Table B: Oracle Transparent Data Encryption and Oracle RMAN
    Application data Backup with RMAN compression Backup with RMAN encryption Backup with RMAN compression and encryption
    Not encrypted Data compressed Data encrypted Data compressed first, then encrypted
    Encrypted with TDE column encryption Data compressed; encrypted columns are treated as if they were not encrypted Data encrypted; double encryption of encrypted columns Data compressed first, then encrypted; encrypted columns are treated as if they were not encrypted; double encryption of encrypted columns
    Encrypted with TDE tablespace encryption Encrypted tablespaces are decrypted, compressed, and re-encrypted Encrypted tablespaces are passed through to the backup unchanged Encrypted tablespaces are decrypted, compressed, and re-encrypted

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

       RMAN> connect target <ORACLE_SID>/<SYS pwd>
       RMAN> set encryption on;
       RMAN> backup [as compressed backupset] database;

    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.


  27. Can I encrypt backups sent to disk using Oracle Secure Backup?

    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.


  28. Do transportable tablespaces work with TDE tablespace encryption?

    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.


  29. Does compression work with TDE?

    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.


  30. Does TDE work with Data Guard, Streams, and Oracle Golden Gate?

    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.

    Oracle Golden Gate Version TDE column encryption TDE tablespace encryption
    Versions before 11.1.1.1 Partial support for all Oracle Database versions if table has primary key or unique index, and encrypted columns are
    • CHAR or VARCHAR2 data types, and
    • not primary key for the table
    Not supported
    11.1.1.1 Support for Golden Gate 11.1.1.1 built-in in Oracle Database 10.2.0.5 and 11.2.0.2; patch 9409423 needed in 11.1.0.7

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


  31. Are there database features that TDE column encryption does not work with?

    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:

    • Materialized View Logs (supported from Oracle 11g Release 2)
    • Synchronous and asynchronous change data capture for data warehousing (CDC)
    • Transportable Tablespaces
    • LOBs (SecureFiles are supported from 11gR1)
    • Streams (supported from 11gR1)

  32. Can I use SQL*Loader with direct path to load data into a table with encrypted columns?

    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

      sal number(7,2),

    to

      sal number(7,2) encrypt,

    and use the correct syntax for SQL*Loader:

      $ sqlldr USERID=scott/tiger CONTROL=ulcase6.ctl LOG=ulcase6.log DIRECT=TRUE

  33. Why can I sometimes still see clear-text data after encrypting sensitive information with TDE column encryption?

    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:

    1. Create a new tablespace in a new data file (CREATE TABLESPACE ... )
    2. Encrypt the clear text values in the original tablespace and data file (ALTER TABLE ... ENCRYPT )
    3. Repeat 2.) for all tables that contain encrypted columns
    4. Move all tables from the original tablespace into the new data file (ALTER TABLE ....MOVE... )
    5. Drop the original tablespace (DROP TABLESPACE ). Do not use the 'and datafiles' parameter; Oracle recommends to use stronger methods for OS level operations, see 6.)
    6. Use 'shred' or other OS commands for your platform to delete the old data file on the OS level.

    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.


  34. How do I change (rotate, re-key) the encryption keys?

    Table C: Encryption re-key capabilities (Oracle Wallet)
    Database Release TDE Column Encryption TDE Tablespace Encryption
    Master encryption key Individual table keys Master encryption key Individual tablespace keys
    10gR2 Yes Yes n/a n/a
    11gR1 Yes Yes No(*) No(*)
    11gR2 Yes Yes Yes No(*)

    (*): 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.


  35. How do I quickly encrypt columns in very large tables with billions of rows?

    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.


  36. How do I migrate the content from clear text application tablespaces to encrypted tablespaces?

    • Extract the DDL that was used to create the original application tablespaces using 'dbms_metadata.get_ddl' and save the output as a SQL script.
    • Add 'ENCRYPTION DEFAULT STORAGE(ENCRYPT)' to each 'create tablespace' statement (the SIZE parameter does not need to be changed, since TDE tablespace encryption does not increase storage requirements).
    • Export either the whole database, or the schema that owns the application tablespaces, with Data Pump (expdp).
    • Drop the application tablespaces 'with contents and datafiles'.
    • Run the SQL script to create encrypted application tablespace with otherwise unchanged characteristics.
    • Import the dumpfile with Data Pump (impdp).

  37. Can TDE store its master encryption key in an external device using the PKSC11 interface?

    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.





Hands-On


 Transparent Data Encryption
 Configuring native network encryption

Security Features


 Data Encryption
 Virtual Private Database
 Database Auditing
 Backup Encryption
 Export file encryption
 Proxy Authentication
 Enterprise User Security
 Secure Application Roles
 Fine Grained Auditing

Discussion Forums


 Security
 Audit Vault
 Database

Technical Information


 Transparent Data Encryption Best Practices
 Datasheet
 Overview Whitepaper
 Frequently Asked Questions
 Technical White Paper

Security Options


 Oracle Database Vault
 Oracle Advanced Security
 Oracle Label Security

Related Technologies


 Database Firewall
 Audit Vault
 Data Masking (pdf)
 Secure Backup
 Configuration Management
 Information Rights Management
 Identity Management

In-Memory Replay Banner