Wednesday, November 08, 2006

Oracle: Transparent Data Encryption

Oracle transparent data encryption (available from 10g Release 2) enables you to encrypt database columns and manage encryption keys. Transparent data encryption can be used to protect confidential data such as credit card and social security numbers. An application that processes sensitive data can use this feature to provide strong data encryption with little or no change to the application. Transparent data encryption is a key-based access control system. When a table contains encrypted columns, a single key is used regardless of the number of encrypted columns. The keys for all tables containing encrypted columns are encrypted with the database server master key and stored in a dictionary table in the database. No keys are stored in the clear. Follow these steps to implement encryption on the Database side.
  1. Set the Master Encryption Key by issuing the following command
  2. No database columns can be encrypted wihtout setting the master encryption key first. This command automatically creates an oracle wallet and sets the password for the wallet. The wallet is also opened as a result of this command. Note that there must be a directory $ORACLEBASE/admin/$ORACLESID otherwise you will ge an error
    ORA-28368: cannot auto-create wallet error
  3. Open the wallet: The wallet must be opened explicitly after the database instance starts. When you create the wallet you also open the wallet for operation. After you create the wallet and set the password, every time you open the database, you'll have to open the wallet using the same password as follows:
    alter system set encryption wallet open authenticated by password;
    You can close the wallet like this:
    alter system set encryption wallet close;
    The wallet must be open for Transparent Data Encryption to work. If the wallet is closed, you can access all nonencrypted columns, but not encrypted columns (you will get a "wallet not open" error).
  4. Create a table using CREATE TABLE as shown below
    ) ;
  5. Encrypt the columns: A column can be encrypted by altering the table with the following command
    The default algorithm for encryption is AES with 192-bit key. This can be changed to any other with the "using" clause added after encrypt, as shown below
    alter table accounts modify (ssn encrypt using 'AES128'); 
    Some other encryption algorithms that can be used are AES128, AES192, AES256, or 3DES168.
Do not, use transparent data encryption with these database features:
  • Index types other than B-tree
  • Range scan search through an index
  • Large object datatypes such as BLOB and CLOB
  • Original import/export utilities
  • Other database tools and utilities that directly access data files
For further information refer to Oracle Advanced security administrator's guide.


  1. Hi,
    If you want the wallet to be in a directory different than the default one, place the following lines in the sqlnet.ora file:


  2. Can you import a key from a third party key manamgement system into oracle wallet rather than have oracle generate its own keys?

  3. Hi,
    As per as my knowledge , once i open the wallet it is open to the whole world to view encrypted column ( till i close wallet ) !!
    Is there any way by which i can open the wallet for a particular session ?
    Where users in other session cannot view or insert data in the encrypted column though wallet is open in some other session?


Popular Posts