Oracle PL/SQL for DBAs

Building a Practical Encryption System

In this section, I’ll wrap up the chapter by describing a practical, real-world system that illustrates the encryption and hashing concepts we’ve been discussing throughout this chapter.

Sometimes your encrypted data will need to be matched with incoming data. For instance, many Customer Relationship Management (CRM) applications use different attributes of customers, such as credit card numbers, passport numbers, etc., to identify unique customers. Medical applications may need to go through the patient’s diagnosis history to project a pattern and suggest treatment options. Insurance applications may need to search patient diagnoses to assess the validity of the claims, and so on. Because these data items are stored in an encrypted manner, the matching applications cannot simply match against the stored data.

There are two options for handling such situations:

Encrypt the data to be matched, and match it against the stored encrypted values

This option is possible only if the encryption key is known. If your approach is to have one key per database (or table or schema), then you know the exact key that must have been used to encrypt the values. On the other hand, if your approach is to use one key per row, then you will have an idea of what key must have been used to encrypt the value in that particular row. Hence, you can’t use this approach.

The other issue with using this option is indexing. If you have an index on this encrypted column, ...