Help: A difficult request

[joe bayer]

We are using Oracle 9206 in windows.

My manager wants me to encrypt credit card number in our database, that is
not a problem, so I created two functions, encrpt_card_no and
deencrypt_card_no, and encrypted data.

The problem is, our vendor software will issue command
select card_no ..... from ....
Ask them to change the source code is very expensive.

Could we write a "select" trigger, using query-rewrite or something, which
is doing this

if (program='vendor program')
change
"select card_no"
to
"select deencrypt_card_no(card_no,key)"
end if;

so that we can avoid a huge bill from the vendor.

Is this feasible in oracle?

Thanks very much for your help.

[sybr...@yahoo.com]

consider renaming the original table and implementing a view, using the
original table name, and you have exactly what you want. No
query_rewrite necessary (and I don't think query_rewrite applies, as
this was one of the reasons to implement views).
If the vendor acts like most vendors do, he won't support your firm
anymore after you have implemented this.
I'm not sure why people can't think of the need to store creditcard
numbers encrypted beforehand, and buy a sw package in which this is not
possible.

--
Sybrand Bakker
Senior Oracle DBA