Oracle EBS R12 and TDE Tablespace Encryption

Keeping data secure is one of the primary job of DBA. Oracle’s Transparent Data Encryption Tablespace Encryption option of Oracle Database 11g Enterprise Edition provides an excellent method to encrypt data at rest. This option works perfectly with Oracle EBS R12.

The TDE Tablespace Encryption option can be used to encrypt the tablespaces that store the content of your E-Business Suite application so you can encrypt the database files and any backups of these files. Good thing about TDE is you do not have to change any of your existing code as TDE is transparent to application.

Note that Oracle introduced TDE first at column level in Oracle 10g Release 2. In Oracle 11g Oracle introduced the encryption at tablespace level. TDE is Oracle’s advance security option and it supports multiple encryption algorithms like DES/AES with varied key sizes (128/192/256 bits).

Let us understand the basics first for TDE:

FEW KEY POINTS TO REMEMBER FOR TDE:

• Data at rest is encrypted which means even if somebody stole your data files the data can not be read.
• Oracle Transparent Data Encryption (TDE) enables you to encrypt individual columns that hold sensitive application data, or entire application tablespaces.
• TDE transparently encrypts data when it is written to disk and decrypts it when it is read back to the authorized user and/or application.
• Secure Management of keys. A secure key store is made to safeguard keys.
• Although data is encrypted by it is transparent to application means no hindrance to application.
• No code/application change required. Application can be EBS, People soft etc.
• TDE does not protect against access to data once the requesting user or application has authenticated to the Oracle database.
• Also support Exadata. Exadata Smart Scans parallelize cryptographic processing across multiple storage cells, resulting in faster queries on encrypted data. TDE integration with Exadata Hybrid Columnar Compression (EHCC) compresses data first, improving cryptographic performance by greatly reducing the total amount of data to encrypt and decrypt.
• You should never ever lose the keystore/wallet and also never forget the password of the key store/wallet
• Before making any changes to keystore/wallet, you must take backups of it for safeguard.
• Directory where you keep the key store/wallet should be restricted to limited authorized users.
• It is recommended to not share same key store with multiple databases.
• Oracle recommends placing the Oracle Wallet outside of the $ORACLE_BASE directory tree to avoid accidentally storing the wallet with the encrypted data on a backup tape.
• The master key needs to be present and open on any Physical Standby database site too as the data is getting transferred in encrypted form to the standby site.
• Note that system, sysaux, temp, undo tablespaces can not be encrypted although if the temporary data that gets stored in temp/undo tablespaces was marked as encrypted then it will stay encrypted there too.
• You can also manage TDE master keys using Oracle Enterprise Manager 12c or 13c
• Both seeded as well as custom tables can be encrypted.
• Always remember that TDE provides data security at OS/data file level. So if the data is read from files and goes on the network, it is clear-text data.For encrypting data-in-motion you need to use separate solution.

TWO TYPES OF TDE : COLUMN AND TABLESPACE ENCRYPTION

1. TDE Column Encryption

• Available from version 10g onwards
• Each table with encrypted columns has its own encryption key (table key), that is used for all encrypted columns in that table. These table keys are stored in the data dictionary, and are encrypted with the master encryption key, which is stored outside of the Oracle database in the Oracle Wallet file.
• Can have some limitations e.g. foreign-key columns, non-normal B-tree indexes, plus the application can no longer perform range scans over encrypted data can not be used with TDE at column level
• Performance impact depends on percentage of encrypted columns; how oftenthe encrypted values are selected or updated, the size of encrypted data, and other variables.

2. TDE Tablespace Encryption

• Available from version 11g onwards
• TDE Tablespace Encryption enables you to encrypt entire application tablespaces. All objects created in the encrypted tablespaces are automatically encrypted. Tablespace encryption has the following benefits over TDE Column Encryption

•  No increase in storage requirements
• True transparency, with no change in execution plans 
•  No need to identify individual columns for encryption
• Support of all data types and index types.

• There can be a performance impact of 4 to 8% in end-user response time, and an increase of 1 to 5% in CPU usage as per Oracle.

CONCEPT OF WALLET (ALSO KNOWN AS KEY STORE IN 12C) 

Wallet/Key store is a container that store TDE Master encryption key. Key store can be of below types:

1. Password based software key store: In such key stores you specify a password during wallet creation that will be required every time you need to open keystone  to get the master key for encryption/decryption of data.
2. Auto-login software key store: You don’t need to specify a password for it while creating wallet and Oracle internally will specify a system generated password for opening this kind of key store. This avoids hassles of manually supplying the password every time you start a database.
3. Local Auto-login software key store: Similar to auto-login kind but limitation that you must use it locally on same machine and you can not transfer these across machines. RAC deployment typically use this kind of key store
4. HSM Based Key Store: HSM stands for Hardware Security Module. Such wallets are provided by third party vendors and we store master key on these wallets.

CREATION OF SOFTWARE KEY STORE

High level steps for creating software keystore:

• Specify the method/location of key store in sqlnet.ora
• Create the software keystore using ADMINISTER KEY MANAGEMENT CREATE.. command in 12c
• Open the Software key store using ADMINISTER KEY MANAGEMENT OPEN.. command in 12c
• Set the master encryption key ADMINISTER KEY MANAGEMENT SET KEY ..command in 12c
• If required make your wallet autologin using ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE..command in 12c. This step will create file named cwallet.sso in the wallet location which means that now onwards no password is required to open wallet

Note that for 10g/11g systems you will use ALTER SYSTEM/orapki command for the creation of wallet/key store.

METHODS FOR MIGRATING EXISTING CLEAR DATA INTO ENCRYPTED TDE TABLSPACES

• DBMS_REDEFINITION: For no downtime data migration to encrypted tablespaces you can use Oracle Online Table Redefinition (DBMS_REDEFINITION) which copies the data in background with no downtime. This approach works for both 11g and 12c databases.
• DATA GUARD: You can use Data Guard and Oracle Data Pump to encrypt existing clear data with near zero downtime. This procedure encrypts on standby first (using DataPump Export/Import), switches over, and then encrypts on the new standby. Database downtime is limited to the time it takes to perform Data Guard switch over.
• DATA PUMP (OFFLINE) METHOD: In offline method, you can use Data Pump to migrate in bulk. You also can use SQL commands such as ALTER TABLE MOVE, ALTER INDEX REBUILD (to move an index), and CREATE TABLE AS SELECT to migrate individual objects.

Note that TDE column encryption is online operation and you can encrypt an existing clear column in the background using a single SQL command such as ALTER TABLE MODIFY.

WHAT IS NEW IN 12C FOR TDE:

• TDE supports Oracle Multi-tenant architecture
• TDE Keystore can be stored on ASM Disk group.
• New administrative privilege has been provided – SYSKM (KM stands for KEY MANAGEMENT)
• New commands : ADMINISTER KEY MANAGEMENT .. (unlike  11g where you used to use alter system)
• New view: V$ENCRYPTION_KEYS
• 12c refers “wallet” as “key store”

IMPORTANT DATA DICTIONARY VIEWS RELATED TO TDE:

DBA_ENCRYPTED_COLUMNS
DBA_TABLESPACES
V$ENCRYPTED_TABLESPACES
V$ENCRYPTION_WALLET
V$ENCRUPTION_KEYS (new view in DB 12c)

DATABASE PERFORMANCE WITH TDE

• Performance overhead from TDE typically not very high.
• According to Oracle, TDE tablespace encryption leverages a modern  cryptographic method which encrypts and decrypts the data very fast.
• Oracle Database also caches decrypted tablespace data to make repeated queries faster
• If your SQL queries/tables are properly indexed then you will see minimal impact. For applications that run full table scans (FTS), the performance impact may be higher. You should plan for more memory/cache if you have more FTS.
• You can also move all non-sensitive tables to an non-encrypted tablespace as this will increase performance.
• If you are bothered about security of only few columns then use TDE column encryption instead of TDE tablespace encryption

ORACLE EBS R12 AND TDE ENCRYPTION

There are various methods by which you can implement TDE with Oracle EBS R12. The methods vary depending on the database versions on which EBS system is running and also on your encryption goals.

1. FAST OFFLINE CONVERSION METHOD

Oracle E-Business Suite database requires downtime, as the tablespace to be encrypted needs to be temporarily offline. This process is the recommended practice for converting to TDE with minimal downtime and least complexity.

Supported versions of Oracle E-Business Suite: 12.1.3, 12.2.2 and higher
Supported versions of Oracle E-Business Suite Database: Version 11.2.0.4 to 12.1.0.2

You will need to apply Patch 23315889 to all primary and standby databases

High level steps:

1. Shut down application
2. Create a specific wallet by specifying the wallet location in the sqlnet_ifile.ora
3. Set the master encryption key from SQL*Plus
4. Re-Start the database normally, ensuring that the wallet is open
5. Bring tablespaces other than system, sysaux, temp and undo offline
6. Encrypt all your datafiles, except system, sysaux, temp and undo using “alter database datafile encrypt” command.
7. Take the offline tablespaces online
8. Bounce Database

2. USING EXPDP/IMPDP AND CREATING A SECONDARY DATABASE

In this method we create another blank database which has exact same number and names of tablespace as your EBS databases with only difference that the tablespaces in blank databases are encrypted. Then we export import whole database usin EXPDP/IMPDP method.

This method is applicable to R12/R12.2 applications running with 11g/12c databases.
This method is also applicable to 11i applications running with 11g database.

For exact steps followed in this method follow the Note IDs given in the reference section below.

3. USING “ALTER TABLE MOVE” IF YOU WANT TO ENCRYPT ONLY SELECTED TABLES

In this method you can define new tablspaces as Encrypted tablespaces and only move those tables to the new encrypted tabelspaces for which you need encryption.

High level steps:

1. Shut down application
2. Create a specific wallet by specifying the wallet location in the sqlnet_ifile.ora
3. Set the master encryption key from SQL*Plus
4. Create the Auto Login Wallet
5. Bounce the database and verify that wallet has opened autoamtically
6. Create Encrypted tablespaces using “create tablespace .. compress for oltp storage ( encrypt ) encryption”
7. Encrypt tables using “alter table <table_name> move tablespace <encrypted_tablespace_name>”
8. Encrypt indexes using “alter index <index_name> rebuild tablespace <encrypted_tablespace_name>”
9. Bounce Application and Databases

4. ENCRYPTING ONLY SELECTED COLUMNS

In this method you can the TDE column encryption functionality can be used to encrypt selected columns of database files. Please note that encrypting columns only has certain limitations and also it can impact performance and patching of application/database. Columns to be encrypted must be chosen with care, and thoroughly tested prior to implementing on a production system.

Regarding application patching  it is important to understand that once TDE column encryption is implemented on a column, any subsequent patches that change the data model may fail to apply correctly. Specifically, Oracle E-Business Suite patches that apply changes to columns encrypted by TDE may fail to apply in the following situations:

• An index added to an encrypted column
• A column increasing in length beyond the threshold for TDE

Also note that TDE column encryption is not supported with Oracle LogMiner based technologies such as Streams, or DataGuard in logical standby mode

High level steps:

1. Create a specific wallet by specifying the wallet location in the sqlnet_ifile.ora
2. Set the master encryption key from SQL*Plus
3. Create the Auto Login Wallet
4. Bounce the database and verify that wallet has opened automatically
5. Prepare a list of columns you would like to encrypt, noting their owning table and schema
6. Download and apply Patch 7337863 (if you are not on 12.0.6) For each column you wish to encrypt, run the script supplied by the patch (located in $FND_TOP/sql) using the following command:

   sqlplus apps/apps@db aftdeval.sql HR PER_ALL_PEOPLE_F NATIONAL_IDENTIFIER E

7. Test system performance before putting the new configuration into production use. Depending on the number of columns encrypted, performance may be impacted to a greater or lesser extent.

For reading more on the limitations on what columns can be encrypted refer to the Note Id mentioned below in reference section.

REFERENCES FOR TDE IMPLEMENTATION IN EBS

Doc ID 2322920.1: Using Fast Offline Conversion to Enable Transparent Data Encryption (TDE) for Oracle E-Business Suite

Doc ID 2263927.1: Does Rapid Clone Support a Database Converted to TDE?

Doc ID 2063486.1: FAQ: Oracle E-Business Suite Security

Doc ID 828223.1: Using TDE Tablespace Encryption with Oracle E-Business Suite Release 11i

Doc ID 2148746.1: Enable Transparent Data Encryption (TDE) Using Fast Offline Conversion in 11.2.0.4 and 12.1.0.2

R12

****

Doc ID 732764.1 : Using Transparent Data Encryption (TDE) Column Encryption with Oracle E-Business Suite Release 12

Doc ID 828229.1 : Using TDE Tablespace Encryption with Oracle E-Business Suite Release 12 (Database 11gR2)

Doc ID 1584458.1: Using TDE Tablespace Encryption with Oracle E-Business Suite Release 12 (Database 12c)

R12.2

*****

Doc ID 1585696.1: Using TDE Column Encryption with Oracle E-Business Suite Release 12.2

Doc ID 1585296.1: Using TDE Tablespace Encryption with Oracle E-Business Suite Release 12.2 (Database 11gR2)

Doc ID 1926686.1: Using TDE Tablespace Encryption with Oracle E-Business Suite Release 12.2 (Database 12c)

• Author
• Recent Posts

Brijesh Gogia

I’m an experienced Cloud/Oracle Applications/DBA Architect with more than 15 years of full-time DBA/Architect experience. I have gained wide knowledge on Oracle and Non-Oracle software stack running on-prem and on Cloud and have worked on several big projects for multi-national companies. I enjoy working with leading-edge technology and have a passion for Cloud architecture, automation, database performance, and stability. Thankfully my work allows me time for researching new technologies (and to write about them).

Latest posts by Brijesh Gogia (see all)

• Oracle Multitenant DB 4 : Parameters/SGA/PGA management in CDB-PDB - July 18, 2020
• Details of running concurrent requests in Oracle EBS - May 26, 2020
• Oracle Multitenant DB 3 : Data Dictionary Architecture in CDB-PDB - March 20, 2020