Re: searching for encrypted fields in data columns

<sffan_at_hotmail.com> wrote in message news:4042F2DF.6080800_at_hotmail.com...
| I am new to database programming and was curious how others solve the
| problem of storing encrypted in data in db table columns and then
| subsequently searching for these records.
| The particular problem that I am facing is in dealing with (privacy)
| critical information like credit-card #s and SSNs or business critical
| information like sales opportunity size or revenue in the database. The
| requirement is that this data be stored encrypted (and not in the
| clear). Just limiting access to tables with this data isn't sufficient.
| Does any database provide native facilities to store specific columns as
| encrypted data ? The other option I have is to use something like RC4 to
| encrypt the data before storing them in the database.
|
| However, the subsequent problem is how do I search/sort on these columns
| ? Its not a big deal if I have a few hundred records; I could
| potentially retrieve all the records, decrypt the specific fields and
| then do in process searches/sorts. But what happens when I have (say) a
| million records - I really don't want to suck in all that data and work
| on it but instead use the native db search/sort capabilities.
|
| Any suggestions and past experiences would be greatly appreciated.
|
| much thanks,
| ~s
|

for oracle (reply not posted to
alt.php.sql,alt.comp.databases,comp.databases.ms-sqlserver,comp.lang.java.da tabases)

regarding encryption, look into the DBMS_OBFUSCATION_TOOLKIT

the way i've used this in the past is to encapsulate the encrypt and decrypt function in a package, and use a package variable to store the key, then set the key programmatically (either via user input, or via as secure a UI as possible). if the package is not initialized with the key, its encrypt and decrypt functions raise an error

encapsulating the calls in a package helps, because all data must be padded to fix width (8-byte multiples). also, once the key is set, the encrypt and decrypt functions can be called with a very simple syntax -- we would call the encrypt function for inserts via procs or instead-of triggers, and use the decrypt function within views

sorting is not a major issue, just sort on the expression that uses the decrypt function (which, again, can be incorporated into the view) -- however any sorts that could have used an index will not be able to

searching is an issue for performance -- one seeming solution would be to create a function based index, but then you have the decrypted values stored in the database and accessible via an index dump (i forget how this is done, but i remember it as an issue)

;-{ mcs Received on Mon Mar 01 2004 - 09:25:31 CST