Hi Jim

Jim Starkey wrote:

> Mark O'Donohue wrote:
>
>
>>Specifying that certain fields are stored encrypted and/or compressed
>>could be useful.
>>
>>
>
> This is highly problematic. First, most symmetric cyphers including DES
> and AES, are block cyphers that will wreak havoc with the ODS. Rounding
> all fields to 8 bytes (DES) or 16 bytes (AES) is going to be a mess.
> Second, encrypting a field will require that the index also be
> encrypted. Third, how can triggers, validation expressions, and
> constraints be evaluated without access to the field.
>

Yes there are problems that would need to be resolved.

But usually there are a few sensitive fields, and as an alternative to
encrypting the whole database, it has some merit. So I figured you were
the guy to bounce the idea off to see if it's feasible :-).

It would have to be non-indexed field.

I was thinking of something along the lines of how varchars are
currently compressed, with run length encoding.

Perhaps restricted to varchar and blob fields (or blob only).

I don't see the rounding being too big an issue since the encrypt would
come out with either a binary blob or possibly b64 varchar string. For
blobs it's going to work fairly similar to a blob filter.

But in storing the data in an encrypted form, who has the key?.

If it's a server based thing, with the server knowing the decrypt key,
then the server can decrypt it on any access to the field (so triggers
should work).

Backups are secureish, but it doesn't add a real lot of value.

Alternatively perhaps it's just best to provide a few functions to the user.

For server wide storing sensitive data.

varchar/blob encrypt(alg, varchar/blob)
varchar/blob decrypt(alg, varchar/blob)

or on a per user basis:

encrypt(alg, varchar/blob, key)
decrypt(alg, varchar/blob, key)

update customer
set medical_report = encrypt("3DES", clear_data_field, "password")
where ...;

And leave it to the application to work out the issues.

Cheers

Mark