Friday, April 18, 2014

TDE Part-1: Transparent Data Encryption

Transparent Data Encryption (TDE), part of Oracle Advanced Security,  encrypts critical data that are stored in data files in the form of tables, indexes, materialized views etc. Oracle provides several security mechanism for authentication and authorization to validate database users who access data. All these measures are there for secure access control but not any for database files such as datafiles, redo log and archive log files. If you have the datafiles, you can see the data though you have no access in the database. Therefore file level security is required to protect the data. On other side, you want minimal configurations or changes in middle and top layer of the application to achieve database file level security.

In 10g Release 2, Oracle first introduce TDE, a transparent data encryption-decryption mechanism to secure data stored in datafiles. Here transparent means database users or application need not to do any changes to achieve TDE. Database itself will encrypt data while writing into datafiles and decrypt then while reading. database users and applications not even bother for data encryption-decryption, Oracle database will do these transparently for them.    To prevent unauthorized decryption, transparent data encryption stores the encryption keys in a
security module (oracle wallet) external to the database. Data is transparently encrypt/decrypted by the database, user does not require any action on their part.

There are two types of TDE:

  1. TDE Column Encryption - data encryption-decryption for columns of a table. It encrypts/decrypts data at the SQL layer. This feature introduce in Oracle 10g R2.
  2. TDE tablespace Encryption - data encryption-decryption for all objects that stored into the encrypted tablespace. It encrypts/decrypts data during read/write operations This feature introduced in Oracle 11g R1.

Restrictions on Using TDE Column Encryption


Do not use TDE column encryption with the following database features:

  1. Index types other than B-tree
  2. Range scan search through an index
  3. External large objects (BFILE)
  4. Synchronous Change Data Capture
  5. Transportable Tablespaces
  6. Original import/export utilities does not support. You need to use Oracle Data Pump.
  7. TDE encrypted columns can't be use as foreign key constraints.

Restrictions on Using TDE Tablespace Encryption


Majority of the restrictions that apply to TDE column encryption, such as data type restrictions and index type restrictions are not applicable to TDE tablespace encryption. Though, the are some other restrictions
that apply to TDE tablespace encryption:

  1. External Large Objects (BFILEs) can not be encrypted using TDE tablespace encryption. This is because these files reside outside the database.
  2. To perform import and export operations, you need to use Oracle Data Pump.

Things You Need To Consider Before Implementing TDE

  1. 4-8% performance impact in end-user response time and an increase of 1-5% in CPU usage. Though it ensure below 10% performance impact.
  2. Existing tablespace can not be converted to encrypted tablespace. You need to use table reorganize/redefinition or datapump Export-Import for Migration.
  3. Increase disk consumption. Encrypting a single column would require between 32 and 48 bytes of additional storage for each row, on average. Though TDE tablespace encryption has no storage overheads.
  4. Require Goldengate 11.1.1.1 for replication
  5. Increase RMAN(backup/restore) & DataPump(export/import) duration.
  6. Need Separate backup task for Wallet that contains encryption decryption master key.
  7. Need Oracle Advanced Security Licence (11,500 USD/Core).
  8. Using a PKI key pair (PKI certificate) as a master encryption key may result in greater performance degradation when accessing encrypted columns in the database.

Sunday, July 7, 2013

OPTIMIZER_FEATURES_ENABLE: Downgrade Oracle optimizer functionality

After database up gradation or patched, you may find some under performing SQL statements. Due to one or more bug fixes Oracle optimizer my generate different execution plans for those SQL statements,  hence performance degraded . You need reasonable amount of time to tune them but unable to afford it in production environment! You have to do something quick ..........

In such situation, you can downgrade the optimizer functionality to a previous version or patch set; so that  optimizer just act like it's previous behavior. When you finish SQL optimization/tuning, you can switch to the upgraded or patched  optimizer version.

OPTIMIZER_FEATURES_ENABLE - An Initialization Parameter which can be altered at the system or session level to facilitate down gradation of oracle optimizer.

Current version (after up gradation or patched): 11.2.0.3

SQL> ALTER SYSTEM SET optimizer_features_enable='11.2.0.2' scope=both;
or
SQL> ALTER SESSION SET optimizer_features_enable='11.2.0.2';

Switch to current version
SQL> ALTER SYSTEM SET optimizer_features_enable='11.2.0.3' scope=both;