Protect from Prying Eyes: Encryption in Oracle 10g - Part 2
Part 1 | Part 2
In Part 1 of this article, we discussed some of the basics of data encryption, including what is encryption, encryption algorithms, what type of encryption scheme to use when, and more. Now, let’s take a look at the other side of encryption: decryption and its elements.
Decryption
The DECRYPT function in the Crypto package provides the reverse of the encryption. Let’s decrypt the value we encrypted in part 1 of this article. Since the encryption value is in RAW, we can write the encryption and decryption in one session and pass the encrypted value. For this, we have defined a SQL*Plus variable named enc_val, as shown in line 3 in the following code. Lines 4 through 21 show the piece of code used to perform encryption of an input value called “ConfidentialData” and the rest of lines show the decryption of the encrypted value in the variable enc_val.
1 REM
2 REM Define a variable to hold the encrypted value
3 variable enc_val varchar2(2000);
4 declare
5 l_key varchar2(2000) := '1234567890123456';
6 l_in_val varchar2(2000) := 'ConfidentialData';
7 l_mod number := dbms_crypto.ENCRYPT_AES128
8 + dbms_crypto.CHAIN_CBC
9 + dbms_crypto.PAD_PKCS5;
10 l_enc raw (2000);
11 begin
12 l_enc := dbms_crypto.encrypt
13 (
14 UTL_I18N.STRING_TO_RAW (l_in_val, 'AL32UTF8'),
15 l_mod,
16 UTL_I18N.STRING_TO_RAW (l_key, 'AL32UTF8')
17 );
18 dbms_output.put_line ('Encrypted='||l_enc);
19 :enc_val := rawtohex(l_enc);
20 end;
21 /
22 declare
23 l_key varchar2(2000) := '1234567890123456';
24 l_in_val raw (2000) := hextoraw(:enc_val);
25 l_mod number := dbms_crypto.ENCRYPT_AES128
26 + dbms_crypto.CHAIN_CBC
27 + dbms_crypto.PAD_PKCS5;
28 l_dec raw (2000);
29 begin
30 l_dec := dbms_crypto.decrypt
31 (
32 l_in_val,
33 l_mod,
34 UTL_I18N.STRING_TO_RAW (l_key, 'AL32UTF8')
35 );
36 dbms_output.put_line
37 ('Decrypted='||utl_i18n.raw_to_char(l_dec));
38* end;
Line description
The code example noted just previously also needs some explanation:
- Line 23 — The key is declared. Note the same key is used to encrypt that is used to decrypt as well.
- Line 24 — Since the variable enc_val is in hexadecimal, we have converted it to raw.
- Lines 35-27 — As in encryption, we have specified the algorithm and the padding and chaining schemes as a single parameter. Note that they are the same as in encryption. They have to be, in order for the decryption to work correctly.
- Line 34 — As in encryption, the key must be in hexadecimal as well, so we converted it.
The output of the previous code example is “ConfidentialData,” which is the same value we encrypted earlier.
These are the basics of encryption and decryption using dbms_crypto package.
Hashing and MAC
Crypto also provides mechanisms for hashing and MAC, implemented through functions HASH and MAC respectively. Hashing can be done through three different algorithms. The most popular is MD5, a 128-bit hash function. There is also an MD4 (a predecessor to MD5), which is also 128-bit, but is less secure. The other competing standard is Secure Hash Algorithm (SHA-1), which produces a 160-bit hash.
As in case of encryption, hashing algorithms are implemented in the package as constants. HASH_SH1, HASH_MD4, and HASH_MD5 correspond to the algorithms SHA-1, MD4, and MD5, respectively.
The following is a simple case of Hashing using the algorithm SHA-1:
SQL> l
1 declare
2 l_in_val varchar2(2000) := 'CriticalData';
3 l_hash raw(2000);
4 begin
5 l_hash := dbms_crypto.hash (
6 UTL_I18N.STRING_TO_RAW (l_in_val, 'AL32UTF8'),
7 dbms_crypto.hash_sh1
8 );
9 dbms_output.put_line('Hash='||l_hash);
10* end;
SQL> /
Hash=1F9DC42F9941623CF2455C5E6ABC4F1B63A73C64
PL/SQL procedure successfully completed.
Regardless of how many times this is executed, the hash value remains the same. You can change the algorithm by chaining the parameter in line 7 to the appropriate constant (i.e., dbms_crypto.hash_md5 for MD5).
Similarly, MAC is also implemented using MAC function in the package. Only MD5 and SHA-1 algorithms are supported in MAC. The constants that specify the algorithms are HMAC_SH1 and HMAC_MD5 representing SHA-1 and MD5, respectively.
Here is a simple implementation of the MAC function using SHA-1 algorithm
1 declare
2 l_in_val varchar2(2000) := 'CriticalData';
3 l_key varchar2(2000) := '1234567890123456';
4 l_mac raw(2000);
5 begin
6 l_mac := dbms_crypto.mac (
7 UTL_I18N.STRING_TO_RAW (l_in_val, 'AL32UTF8'),
8 dbms_crypto.hmac_sh1,
9 UTL_I18N.STRING_TO_RAW (l_key, 'AL32UTF8')
10 );
11 dbms_output.put_line('MAC='||l_mac);
12* end;
SQL> /
MAC=5154E27FBADD683D619561C8B39BDC0B5C5C1353
PL/SQL procedure successfully completed.
As in the hashing example, we supplied the input value in RAW. However, unlike hashing, we have provided a key (line 9), which is what makes hash different from MAC. The key is also provided in RAW, just like the input value. While generating a MAC value, the same key must be used to perform the comparison.
Key Management
So far, we have talked about how to encrypt or decrypt data. In real life, however, that is not the more difficult challenge; it is in managing keys. Since security of the encrypted values is as tight as the security of the keys themselves, it is very important to protect the keys from falling into the wrong hands. In the following section, we will discuss the several options available for managing keys.
One Key Strategy
The simplest approach is using only one key for encrypting data. In this case, just one key must be generated and used, which could be used by users performing encryption and decryption. However, if this key is lost or stolen, the entire database becomes exposed. Therefore, the advantages of simplicity in management and risks of key theft must be balanced for a proper security.
The most common application of this strategy is in the field of mass data distribution — such as when a central location publishes data to its associated data centers in a secure manner. For example, an organization’s financial department could disseminate financial data to subsidiaries in a recordable media. The data could be encrypted in some pre-determined manner using a key that is known to both parties. The same key can be used in every circumstance of this scenario, since the parties involved are small and chances of leaks are low. But for most other applications, this is not a viable idea.
Multi-key Strategy
In sharp contrast to the one-key strategy, another is to use a key for each row of the table. In this case, even if a key is stolen or lost, only the corresponding row is exposed — not the entire database. This strategy adds more security, but also increases complexity in managing and maintaining keys.
Key Storage
Whether you choose the many- or single-key decision, you must also deal with the factor of where to locate the key. One option is to store the key in the operating system, which works well for single-key strategies. The other option is to store the key in the database.
Before making that call, you should be considering the following:
Are you trying to store keys in a location in which no one but a legitimate user can access them? If so, database storage will work out well. However, a DBA will be able to access the tables and keys; are you trying to conceal them from the DBA? No matter how you look at it, it is impossible to conceal them from the DBA using the dbms_crypto package. Thus, you should be considering other, more sophisticated options such as using a “wallet” to dispense keys from an external entity. This approach requires a fairly elaborate setup involving LDAP, Oracle Advanced Security, and more, and it is perhaps overkill, anyway. The bottom line is that using this package, a rogue DBA will be able to decrypt sensitive information.
What other potential incidents are you trying to prevent? Server or disk theft? That’s rare, but possible. In this case, storing the keys in the database exposes the database to the risk of theft, so an external file system should be used to hold keys. (Note that a server theft will not expose the disks.)
However, if the thieves steal the server disks as well as the external disks, this risk can materialize. To address that risk, you may decide to have the user carry the keys separately on his or her person using a USB disk or personal hard disk. This adds security, but increases complexity many times over, as well as carrying the risk of losing the key.
A more practical and reasonably secure strategy is to use a combination of keys:
- A key for each row
- A master key for the entire table
While encrypting, the actual key used is not the key stored for the row; rather, it is the XOR of the two values. The master key can be stored on a different location from the other keys. The intruder must find both the keys to successfully decrypt values. The following example is a demonstration of the scheme, shown as a variation of the original encrypt-decrypt demonstration.
We have added a new variable called l_master_key in line 6, which accepts a value from the user (the substitution variable and master_key). In lines 14 through 17, we have XORed the key and the master key, which was used as the encryption key in line 22, instead of the l_key variable.
1 REM
2 REM Define a variable to hold the encrypted value
3 variable enc_val varchar2(2000);
4 declare
5 l_key varchar2(2000) := '1234567890123456';
6 l_master_key varchar2(2000) := '&master_key';
7 l_in_val varchar2(2000) := 'ConfidentialData';
8 l_mod number := dbms_crypto.ENCRYPT_AES128
9 + dbms_crypto.CHAIN_CBC
10 + dbms_crypto.PAD_PKCS5;
11 l_enc raw (2000);
12 l_enc_key raw (2000);
13 begin
14 l_enc_key := utl_raw.bit_xor (
15 UTL_I18N.STRING_TO_RAW (l_key, 'AL32UTF8'),
16 UTL_I18N.STRING_TO_RAW (l_master_key, 'AL32UTF8'
17 );
18 l_enc := dbms_crypto.encrypt
19 (
20 UTL_I18N.STRING_TO_RAW (l_in_val, 'AL32UTF8'),
21 l_mod,
22 l_enc_key
23 );
24 dbms_output.put_line ('Encrypted='||l_enc);
25 :enc_val := rawtohex(l_enc);
26 end;
27 /
28 declare
29 l_key varchar2(2000) := '1234567890123456';
30 l_master_key varchar2(2000) := '&master_key';
31 l_in_val raw (2000) := hextoraw(:enc_val);
32 l_mod number := dbms_crypto.ENCRYPT_AES128
33 + dbms_crypto.CHAIN_CBC
34 + dbms_crypto.PAD_PKCS5;
35 l_dec raw (2000);
36 l_enc_key raw (2000);
37 begin
38 l_enc_key := utl_raw.bit_xor (
39 UTL_I18N.STRING_TO_RAW (l_key, 'AL32UTF8'),
40 UTL_I18N.STRING_TO_RAW (l_master_key, 'AL32UTF8')
41 );
42 l_dec := dbms_crypto.decrypt
43 (
44 l_in_val,
45 l_mod,
46 l_enc_key
47 );
48 dbms_output.put_line ('Decrypted='||utl_i18n.raw_to_char(l_dec));
49* end;
When we execute this block, here is what the output looks like:
Enter value for master_key: MasterKey0123456
old 3: l_master_key varchar2(2000) := '&master_key';
new 3: l_master_key varchar2(2000) := 'MasterKey0123456';
Encrypted=C2CABD4FD4952BC3ABB23BD50849D0C937D3EE6659D58A32AC69EFFD4E83F79D
PL/SQL procedure successfully completed.
Enter value for master_key: MasterKey0123456
old 3: l_master_key varchar2(2000) := '&master_key';
new 3: l_master_key varchar2(2000) := 'MasterKey0123456';
Decrypted=ConfidentialData
PL/SQL procedure successfully completed.
This code example asked for the Master Key, which we supplied correctly, and the correct value popped up. But what if we supply a wrong Master Key?
Enter value for master_key: MasterKey0123456
old 3: l_master_key varchar2(2000) := '&master_key';
new 3: l_master_key varchar2(2000) :='MasterKey';
Encrypted=C2CABD4FD4952BC3ABB23BD50849D0C937D3EE6659D58A32AC69EFFD4E83F79D
PL/SQL procedure successfully completed.
Enter value for master_key: MasterKey0123455
old 3: l_master_key varchar2(2000) := '&master_key';
new 3: l_master_key varchar2(2000) := 'WrongMasterKey';
declare
*
ERROR at line 1:
ORA-28817: PL/SQL function returned an error.
ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 67
ORA-06512: at "SYS.DBMS_CRYPTO", line 41
ORA-06512: at line 15
Note the error in this: The use of a wrong master key did not expose the encrypted data. This enhanced security mechanism relies on two different keys, and both the keys must be present to successfully decrypt it.
Conclusion
Encryption is the art of disguising data to protect it from unauthorized intruders; authorized users can decrypt the data to look at the actual value. The Oracle-supplied package dbms_crypto provides all the implementations of the algorithms, and chaining and padding mechanisms commonly used in the cryptographic community. In this article, you learned how to use these methods to create an encryption system from scratch and how to work around the challenges to manage the keys securely.
It’s important to understand that encryption is not a substitute for common sense security such as a good authorization scheme or better security in the database. It’s designed to be the last resort defense to prevent an intruder from seeing sensitive data.
For more information on advanced encryption and sophisticated key management and user authentications using application contexts, you can refer to Oracle Privacy Security Auditing (ISBN 0972751394). You will also learn how to use the older dbms_obfuscation_toolkit to perform encryption and decryption.
--
Arup Nanda is the Lead DBA at Starwood Hotels & Resorts. He has been an Oracle DBA for more than 11 years, touching all aspects of database management — from modeling to performance tuning to disaster recovery. He is the co-author of the book Oracle Privacy Security Auditing (2003, Rampant Tech Press).
Arup Nanda
Last modified 2006-01-06 02:03 PM
