My Virtual [Column] Failure

Well, I wouldn't really say a failure, but it's certainly been a trial.

We're trying to create a new process that will detect fraud. Much of the data is sensitive in nature ('natch, thanks Jake).

Step 1: Create a encrypted tablespace

CREATE TABLESPACE encrypted_data
  DATAFILE 'c:\oracle\oradata\eleven\encrypted_data_001.dbf'
  ENCRYPTION USING '3DES168'
  DEFAULT STORAGE (ENCRYPT);

Note, I did not do that as our production DBA won't let me near the physical machine (thankfully).

OK, an encrypted tablespace. What else can we do to protect the data? Encrypt it using DBMS_CRYPTO!

Step 2: Create the encryption/decryption routine. For added security, put it in the SYS schema. I decided on that because even if you have EXECUTE ANY PROCEDURE, you can't execute procedures owned by SYS (without being SYS of course). I'll skip the example as it's linked above.

This has to be fast, really fast. How can I index?

Well, I could index the RAW column, encrypt the incoming text/number and then compare...but we need the ability to do partial matches.

Virtual Columns to the rescue! It's not that exciting really, but a good opportunity to put them to use.

So here's the definition of the table:

CREATE TABLE t
(
  id NUMBER(10)
    CONSTRAINT pk_id PRIMARY KEY,
  name_e RAW(256),
  name AS ( CAST( sys.e_pkg.decrypt( name_e ) AS VARCHAR2(20) ) ) VIRTUAL,
  dob_e RAW(256),
  dob AS ( CAST( sys.e_pkg.decrypt( name_e ) AS VARCHAR2(11) ) ) VIRTUAL
);

Instead of doing the decryption in a view I can do it in the table and the best part of all is that you can index those virtual columns.

CREATE INDEX name_idx ON t( name );

CREATE INDEX ssn_idx ON t( ssn );

I loaded some records into the table and began testing. Wall clock results weren't too bad, less than a second for searches (~1 million records) on SSN.

This is where the problems began.

The actual table has some 60 columns. Since I'm lazy, I created a procedure with an input parameter of T%ROWTYPE. You can use the CAST columns (VARCHAR2) to hold the incoming values, encrypt them and put them in their proper column.

I initially had difficulties using the %ROWTYPE so I went back to named parameters (p_name, p_ssn, etc). What I didn't realize is that the length of the values were no longer constrained. Running some tests I managed to successfully insert some data. Much of it longer than the CAST would allow. When performing a SELECT on the table, Oracle gave me the wonderful

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

.After a bit of searching, I found the offending records and tried to DELETE them:

CJUSTICE@SANDBOX>DELETE FROM t WHERE id = 10;
DELETE FROM t WHERE decisionid = 10
     *

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.E_PKG", line 42

What? I'm just trying to DELETE a row...not SELECT from it.

So I leave this to the internals experts out there. Why does Oracle attempt to decrypt the column on a DELETE?