Thursday, June 7, 2012

Oracle 11g: Encrypting Tablespaces


In Oracle Database 11g, we can encrypt an entire tablespace by simply using a pair of special clauses 'encryption' during tablespace creation.Once the tablespace is encrypted, the entire tables and associated indexes in the tablespace are encrypted along with the data stored in the redo logs.

But before doing tablespace encryption, we'll need to create an Oracle wallet 

Creating the Oracle Wallet

An Oracle Wallet is a container to store authentication and signing credentials.

Ways to create Oracle Wallet:

Wallet can be created from OS, SQLPLUS or GUI interface. However, before creating the Wallet, we  must first create a directory named wallet under $ORACLE_BASE/admin/$ORACLE_SID to avoid ORA-28368: cannot auto-create wallet.

1. OS:

$ mkstore -wrl $ORACLE_BASE/admin/$ORACLE_SID/wallet -create
Enter password:
Enter password again:

2. SQLPLUS:

SQL> alter system set encryption key identified by "password";
System altered.

The alter system statement  works in the following way:

  • If Oracle Wallet exists, it opens that wallet and creates (or re-creates) the master encryption key
  • If Oracle Wallet does not exist, it creates a new wallet, opens the wallet, and creates a new master encryption key.
3. GUI :


Not covered in this article.

The tablespace encryption feature uses the wallet to protect the master key used in the encryption.Oracle stores the encryption keys outside the database, in a file called an Oracle Wallet. By default, this file is named ewallet.p12 under both Windows and UNIX/Linux-based systems. The location where Oracle stores this file is operating ystem–specific. However, you can specify a different location by using the parameter encryption_wallet_location in the sqlnet.ora file as below

ENCRYPTION_WALLET_LOCATION =
 (SOURCE=
    (METHOD=file)
    (METHOD_DATA=
       (DIRECTORY=/apps/oracle/general/wallet)     )  )

Creating an Encrypted Tablespace:

Once Oracle Wallet is created, encrypted tablespace is failrly simple 

SQL> create tablespace encrypt_ts
   datafile 'u01\app\oracle\oradata\encrp_ts\ encrypt_01.dbf' size 100m
   encryption 
   default storage (encrypt);


Tablespace created.

The storage parameter encrypt ensures that the tablespace is encrypted.


The new column ENCRYPTED in the DBA_TABLESPACES displays encryption status of a tablespace:

SQL> select tablespace_name, encrypted
  2 from dba_tablespaces;


TABLESPACE_NAME           ENC
----------------------------          ----
SYSTEM                           NO
SYSAUX                           NO
UNDOTBS1                       NO
TEMP                               NO
USERS                              NO
ENCRYPT_TS                  YES


6 rows selected.


Oracle encrypts the data in the tablespace upon writing it and decrypts it upon reading the data.There is no additional memory requirement however,  there is an encryption overhead on I/O.

Restrictions on Tablespace Encryption


• You cannot encrypt an existing tablespace.
• exp and imp utilities are not supported with objects in the encrypted tablespaces.
• You cannot re-create the tablespace encryption key.
• The NO SALT option is not supported.
• Temporary and undo tablespaces cannot be encrypted.
• COMPATIBLE parameter must be set to 11.1 or higher.
• BFILES and external tables are not encrypted.
• Logically, encrypted tablespace is less efficient than normal un-encrypted tablespace







No comments:

Post a Comment