A few years ago I wrote a quite comprehensive encryption system using database procedures. It never made it into production, but it was thoroughly tested and worked well. It was possible to encrypt any field on any record and it allowed different encryption keys for each database. It was all parameter driven, so there was just a single generic database procedure and not a custom one written for each record. There was overhead, but it was minimal.
The database procedure was called BEFORE STORE and BEFORE MODIFY to encrypt the data and AFTER GET to DECRYPT it. If the encrypted field was a CALC key or the key of a sorted set or index, then it was also necessary to do a BEFORE GET call to encrypt the CALC/sort key on a FIND/OBTAIN.
For CALC it was fairly simple. The database procedure would check if it was a FIND CALC/DUPLICATE before GET call and simply encrypt the CALC key in the record if required.
It wasn't quite so simple for sorted sets and there were some restrictions. The database procedure would check if it was an FIND USING SORTKEY verb and then check if the set was one with an encrypted field in it. If it was, it would then locate the passed sortkey and encrypt the field there. Obviously the encrypted key must be passed in full, it won't work with a partial encrypted key field. Also, as the data is going to be sorted by the encrypted value, it may not be possible to retrieve data via the set in the "correct" sorted order. Basically it works well if the sorted set is used to obtain specific record occurrences, but there may be issues if used with partial keys or to obtain data in a sorted order. So some analysis is required.
There may have been an issue with RETURN ... KEY into verb as well, but maybe I'm wrong there. I looked in my notes and I didn't see anything about it, so maybe I am just recalling incorrectly.
Let me know if you have any questions about what I did and I'll do my best to answer them.
Cheers,
Steve Franzon