Tuesday, November 4, 2008

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?

3 comments:

Gary Myers said...

Hmmm.
If it is indexed, then there's a entry in an index structure that points to it.
And when you delete, it needs to delete that entry from the index.
But indexes are one way - there's no equivalent of the ROWID on the table entry that points to an index.

To find the index entry, it has to read the column, (apply the function for a function based index), then use that to access the index structure to find the index entry it needs to delete.

Drop the indexes, and the problem may go away.

oraclenerd said...

I was waiting for you to reply. I can't seem to forget your warnings about Virtual Columns.

I forgot about the indexes...very good point.

Thanks Gary!

Gary Myers said...

No problem.