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 126.96.36.199.0
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.
SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY “DeciPher”;
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.
CREATE TABLESPACE secdec
DATAFILE ‘C:/app/oradata/orcl/secdec01.dbf’ SIZE 100M
ENCRYPTION USING ‘AES256’
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 ts.name, es.encryptedts, es.encryptionalg
FROM v$tablespace ts
INNER JOIN v$encrypted_tablespaces es
ON es.ts# = ts.ts#
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.
One Response to “Encrypted Tablespaces in Oracle 11g”
Sorry, the comment form is closed at this time.