What is the recommended way to encrypt in Oracle?

Question

I need some help from Oracle/Security experts.

I'm going to make functions for encryption/decryption in our Oracle DB. I intend to use dbms_crypto with AES256. I understand that I should store the key file in the O/S and read it using utl_file.

Is this a good idea? Are there any problems with this approach? E.g. Can utl_file have problems if the key file is read concurrently by 10 callers of the function? Is anything else recommended instead?

I'm sure that this is a very common thing. Does anyone know where I can find a good sample that does this?

Since this is security-related, I would prefer to follow some standard that others are following.

Answer 1

If you have Oracle Advanced Security in your Oracle Database Enterprise Edition, you already have transparent data encryption (TDE) of data stored in the database.Take a look:

http://download.oracle.com/docs/cd/B19306_01/network.102/b14268/asotrans.htm

You can check out also this link:

http://www.oracle-base.com/articles/10g/TransparentDataEncryption_10gR2.php

Summarizing the last page:

• Setup: Creating a database file and user.

  CONN sys/password AS SYSDBA

  CREATE TABLESPACE tde_test DATAFILE '/u01/oradata/DB10G/tde_test.dbf' SIZE 128K AUTOEXTEND ON NEXT 64K;

  CREATE USER test IDENTIFIED BY test DEFAULT TABLESPACE tde_test; ALTER USER test QUOTA UNLIMITED ON tde_test; GRANT CONNECT TO test; GRANT CREATE TABLE TO test;

• Encrypted Data: How to create a encrypted Column.You must create a wallet to hold the encryption key.Add the following entry into the sqlnet.ora file on the server and make sure the specified directory has been created.

  ENCRYPTION_WALLET_LOCATION= (SOURCE=(METHOD=FILE)(METHOD_DATA= (DIRECTORY=/u01/app/oracle/admin/DB10G/encryption_wallet/)))

You must create and opne the wallet:

CONN sys/password AS SYSDBA
ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "myPassword";

Then you can create your tables with the desired columns encrypted or not:

CREATE TABLE tde_test (
  id    NUMBER(10),
  data  VARCHAR2(50) ENCRYPT
)
TABLESPACE tde_test;

I hope this help you.