Subject Re: Security
Author Adam
--- In, Mitchell Peek <mitchp@h...>
> >You may need to store things like credit card numbers or passwords in
> >the database. DBAs have full access to the database, and may need it,
> >but you don't want them to know this information. If you need to get
> >the information back out, then use encryption (blowfish etc) and store
> >the encrypted data only.
> >
> Any suggestions on the best means of implementing encryption of this
> sort? UDF? trigger? etc? How does one go about making this workable?

There are two things people think of when it comes to encryption.
encryption itself (Blowfish etc) and hashing (SHA-1, MD5 etc). The
basic difference is that encryption is reversible providing you know
the private key. Hashing is not reversible.

So the first question you need to consider is "Do I need to get the
original information back, or do I just need to validate that they
know information that generates an identical hash?"

Hashing is safer so use it if you don't need to reverse out the

Implementing it is going to require either having your client program
or data abstraction layer doing the encryption, or using a UDF call.
It would be an interesting exercise to see if one could implement it
using built in PSQL and the shipped udf libraries, but I am sure there
already exists UDF libraries with encryption / hashing function calls.
We wrote our own, primarily because we already had the working code in
Delphi so it was just a matter of compiling it as a DLL.

Stored procedures can be helpful in simplifying the comparison
operations and abstracting UDF calls from the select statement.