5

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.

Zesty
  • 2,922
  • 9
  • 38
  • 69

1 Answers1

4

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.

Aitor
  • 3,309
  • 2
  • 27
  • 32
  • +1 Thank you. That was useful information, however, what I need is a pair of encryption/decryption functions. I must be able to encrypt/decrypt on the fly. The data does not necessarily reside in a table. – Zesty Oct 11 '11 at 09:59
  • 1
    You're welcome ;)... mmm, and how about DBMS_OBFUSCATION_TOOLKIT? It contain functions to encrypt/decrypt data using either the Data Encryption Standard (DES) or the Triple DES algorithms. – Aitor Oct 11 '11 at 10:06
  • From what I understand, DBMS_CRYPTO is recommended. DBMS_OBFUSCATION_TOOLKIT is for older DBs. Moreover, I need to protect the key by storing it in the O/S. – Zesty Oct 11 '11 at 12:23
  • 1
    @Zesty,yes, you're right. IN this url http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/apdvncrp.htm you can find some example using DBMS_CRYPTO and your different options to storing the Keys (in your OS, in the database, etc..) – Aitor Oct 11 '11 at 14:40