Column ecryption in Oracle 10g
Step 1
=======
Add the following lines in $ORACLE_HOME/network/admin/sqlnet.ora
# Create a wallet for column ecryption
ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)(METHOD_DATA=
(DIRECTORY=C:\oracle\oradata\SECURE)))
Step 2
========
create the wallet
ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "password";
Step 3
=========
Login as test1/test1
create table test1
(
id number,
password varchar2(10) encrypt
);
insert into test1 values (1,'pass1234');
Any schema (having the appropriate privileges )is able to select the data decrypted.
SQL> select * from test1;
select * from test1
*
ERROR at line 1:
ORA-28365: wallet is not open
Wallets must be reopened after an instance restart and can be closed to prevent access to encrypted columns.
ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "password";
ALTER SYSTEM SET WALLET CLOSE;
References : http://www.oraclebase.com/articles/10g/TransparentDataEncryption_10gR2.php
