4.7. Building a Practical Encryption SystemIn 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:
So, how can you design a system that more efficiently matches against encrypted columns? The trick is to match against a hash value, rather than the encrypted value. Creating a hash value is significantly faster than encryption, and it consumes fewer CPU cycles. Because the hashing of an input value will always produce the same value, we could store the hash value of the sensitive data, create a hash value of the match data, and match it against the stored hash value. Here is a proposed system design. Assume that you have a table named CUSTOMERS where the credit card numbers are stored, which needs to be encrypted. Instead of storing the credit card number in the CUSTOMERS table, you would create two additional tables. Figure 4-10 shows the tables and their relationships.
Figure 4-10. Storing encrypted credit card informationThe cleartext entry of the credit card is not stored anywhere. You could write a BEFORE-row INSERT or UPDATE trigger that follows the pseudo-code shown below. 1 Calculate the hash value 2 Set the value of the column CC to the hash value calculated earlier 3 Search for this hash value in CC_MASTER table 4 IF found THEN 5 Do nothing 6 ELSE 7 Generate a key 8 Use this key to generate the encrypted value of the cleartext credit card number 9 Insert a record into the CC_KEYS table for this hash value and the key 10 Insert a record in the CC_MASTER table with the encrypted value and the key. 11 END IF This logic ensures that the cleartext credit card is not stored in the database. Applications will continue to insert the cleartext value, but the trigger will change it to a hash value. Here is the actual code for the trigger: 1 CREATE OR REPLACE TRIGGER tr_aiu_customers 2 BEFORE INSERT OR UPDATE 3 ON customers 4 FOR EACH ROW 5 DECLARE 6 l_hash VARCHAR2 (64); 7 l_enc RAW (2000); 8 l_key RAW (2000); 9 BEGIN 10 l_hash := get_hash_val (:NEW.cc); 11 12 BEGIN 13 SELECT cc_enc 14 INTO l_enc 15 FROM cc_master 16 WHERE cc_hash = l_hash; 17 EXCEPTION 18 WHEN NO_DATA_FOUND 19 THEN 20 BEGIN 21 l_key := get_key; 22 l_enc := get_enc_val (:NEW.cc, l_key); 23 24 INSERT INTO cc_master 25 (cc_hash, cc_enc 26 ) 27 VALUES (l_hash, l_enc 28 ); 29 30 INSERT INTO cc_keys 31 (cc_hash, cc_key 32 ) 33 VALUES (l_hash, l_key 34 ); 35 END; 36 WHEN OTHERS 37 THEN 38 RAISE; 39 END; 40 41 :NEW.cc := l_hash; 42 END; The following table summarizes the logic of this code.
Because this trigger changes the cleartext to a hash value, the application need not be changed. Programs that match credit card numbers will need to find a match against hash values, not against cleartext or encrypted values. Using this trigger and the functions described in this chapter, you can build an effective and efficient encryption infrastructure. |