Systems Engineering and RDBMS

  • Click Here for Decipher's Homepage

  • Categories

  • Questions?

    Please send your wish list of things that you would like us to write about or if you have suggestions to help improve this blog site. You can send all questions/suggestions to: Blog Support
  • Archives

  • Blog Stats


Archive for February 5th, 2008

Encrypted Tablespaces in Oracle 11g

Posted by decipherinfosys on February 5, 2008

Starting with Oracle 11g, we can now encrypt an entire tablespace instead of encrypting a single column. With the increased security concerns pertaining to data, this new feature can definitely help a lot. Data from encrypted tablespace remains encrypted when data is written to the operating system files or to a backup device.

Encryption is supported for indexes, Lobs and partitions as well. Tablespace encryption is performed at the block level. Most important thing is that encrypting a tablespace is very transparent and does not require any changes from the application side.

Oracle’s transparent data encryption feature which supports the industry standard encryption algorithms is used to encrypt tablespaces. This feature supports AES (Advanced encryption standard) and 3DES (Triple data encryption standard).  When specific algorithm is not specified, default is AES128 (128-bit key encryptions). Let us take a look at the steps to create an encrypted tablespace.

Step 1: In order to encrypt a tablespace, the initialization parameter ‘COMAPTIBLE’ should be set to 11.1.0 or higher. When 11g is installed, compatible parameter is set to 11.1.0. We can check parameter using following.

SQL> show parameter compatible

NAME                                 TYPE        VALUE
———————————— ———– ————
compatible                           string

Step 2: Since Oracle uses transparent data encryption feature we have to create an oracle wallet to store the master encryption of the database and it should be opened. Let us create wallet along with the key.


We need to create a wallet directory under $ORACLE_BASE/admin/<sid> directory before issuing the above command.  So, if the instance name is orcl then wallet directory should be created under $ORACLE_BASE/admin/orcl.  This is not created by default. If wallet directory does not exist, Oracle gives an error: “ORA-28368: cannot auto-create wallet” error. Above statement also opens the wallet. Once created, a wallet is always open unless we explicitly close it or the database is shut down. Wallet is available for all sessions.

Step 3: Once above two steps are performed, now is the time to create encrypted tablespace.

DATAFILE ‘C:/app/oradata/orcl/secdec01.dbf’ SIZE 100M

In the above command, if we omit the ‘ENCRYPTION USING’ clause, default algorithm for encryption will be ‘AES128’.

Once tablespace is created, we can create objects just like we create it in regular tablespace. Using new view, we can identify encrypted tablespaces in the database.

SELECT, es.encryptedts, es.encryptionalg
FROM v$tablespace ts
INNER JOIN v$encrypted_tablespaces es
ON es.ts# = ts.ts#

Result is,

NAME                           ENC ENCRYPT
—————————— — ——-
SECDEC                         YES AES256

Couple of restrictions we need to keep in mind are:

•    Existing tablespaces cannot be encrypted using ‘ALTER TABLESPACE’ command.
•    During the recovery process of the database which contains the encrypted tablespace, oracle wallet must be opened when the database is in a mounted state. This helps the recovery process to decrypt the data blocks for easy recovery.

Posted in Oracle | 1 Comment »