Oracle 10G Transparant Data Encryption How to

In my previous post, I already mentioned I misssed the Advanced Security option in my environment. The Database Creation Assistant (dbca) does not allow to add this afterwards, either. Maybe because it does not know what's installed and what isn't, because there is no entry in the (location changed!) oradim file.
Anyway, the beginning looks hopeful.

Step1: Create a Wallet Location.
Oracle does not know where to store the wallet. This can be accomplished by adding the following to sqlnet.ora on the server:
# Added for Transparent Data Encryption:
WALLET_LOCATION =
  (SOURCE =
   (METHOD = FILE)
    (METHOD_DATA =
     (DIRECTORY = /etc/wallets/oracle102)
  )
 )

Step2: Create a wallet.
Open a new session, use the following command to create an empty wallet:
SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY Welcome1;

System altered.
SQL> host ls /etc/wallets/oracle102
ewallet.p12
Seems to work - Oracle does not recommend this way of setting the wallet location, but the use of ENCRYPTION_WALLET_LOCATION; use this specifically for this purpose; the wallet_location is more generic, and could cause problems with Enterprise Security.
OK - as I still need a signed, auto-login certificate, I use the Oracle Certificate Autority Server as done before. Certificate #7 was issued.
As I use the good old password I use for Demo certificates, I then have to issue:
SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "Welcome1";
System altered.
Seems unneccesary; it's an auto login wallet

Step 3: Create a user.
Looks Promising, let's continue:

SQL> create user scott identified by tiger;
User created.

SQL> grant create session, create table, create view, create sequence to scott;
Grant succeeded.

Mind you: one of the features of 10g Release 2 is that the connect role has been stripped, basically forcing you to think about security! A change for the better, I'd say!

SQL> alter user scott quota unlimited on users;
User altered.
SQL> connect scott/tiger
Connected.
SQL> create table enc_demo (col1 number(10), col2 varchar2(30));
Table created.

SQL> insert into enc_demo values(1,'First test Data Encryption');
1 row created.

SQL> insert into enc_demo values(2,'Transparent Encryption used!');
1 row created.

SQL> commit;
Commit complete.

SQL> select * from enc_demo;
COL1 COL2
---------- ------------------------------
1 First test Data Encryption
2 Transparent Encryption used!

Step 4: Encrypt!
SQL> alter table enc_demo modify col2 encrypt;
Table altered.

SQL> select * from enc_demo;
COL1 COL2
---------- ------------------------------
1 First test Data Encryption
2 Transparent Encryption used!

Hmmmm.... That is not encrypted... Or would it be decrypted on-the-fly? Now, when and how do I get to see glibberish?!? OK, maybe I should read before jumping...
SQL> connect / as sysdba
Connected.
SQL> ALTER SYSTEM SET WALLET CLOSE;
System altered.

SQL> select col2 from scott.enc_demo;
Still shows the data... to be continued!
According to this article by Arup Nanda, the databases comes with a pre-configured wallet. As seen, this is not true; a default wallet will be created after the ALTER SYSTEM SET ENCRYPTION KEY command.
And only after you defined the location of the wallet in your sqlnet.ora file.

Also, he describes a way to create an encrypted dump; however following his example to the letter (cut-n-paste) results in ERROR at line 17:
ORA-00923: FROM keyword not found where expected.
Trimming the identified by part will result in aliasing the column, giving a column called 'encrypted'. And the data is still readable... to be continued!

OK, after some discussions on the internet, the lot became clear with the help of Tom Kyte. Basically, what I did to check wether the data was actually encrypted to disk, was a 'grep -a [datafile] Encryption'. This does show results, and I (wrongly) concluded, encryption was not done.
So, what's the case:

• First of all, it's called Transparent for a reason - once set up, you will not notice. Yes, the result of a select will be as usual - readable, that is.
• Secondly, you have to know the inner workings of Oracle; the original data is moved away, and has become unaccessable to Oracle - but it's there! Block dumping, instead of ousing grep or strings, should help - more on that!
• Thirdly - Transparent data encryption works, but beware of side effects:
1. data still shows up after alter table modify column encrypt;
2. you could do a flashback query, and go back to the time the data was still unencrypted.

OK - this concludes this entry, more in another.