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.
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.