Key Management
 | ||
| ||
Encryption, as it is traditionally done, possesses a few operating characteristics that do not convey to databases. This is one reason database encryption is difficult—it is unlike most traditional uses of encryption.
Encryption generally has two participants: the sender of the data and the receiver of the data, which means that the challenge to key management (the creation, propagation, and destruction of encryption keys) is a two-person, point-to-point problem. (An implicit third person acts as the attacker, but this is immaterial for the discussion.)
For example, if the emperor wants to send his general a message, he has to securely create and deliver the key to the general. This is a person-to-person or point-to-point activity. If the emperor wants to send the same message to ten generals, he will have to create ten separate keys—one for each general. If ten generals want to send messages to each other, there will be a lot of keys being created and passed about.
A database generally allows many users to share and access data with many other users. The notion of personal or private keys is therefore not applicable (or at least not without some added work). For example, it is rare that a user will input data in the database and encrypt it so only they can get access. Instead, they usually want to encrypt the data so that only the authorized recipients can decrypt it. This implies that the data will be encrypted once for each recipient. Many times the recipients are not known to the user when the data is entered.
Another major difference in encryption can be seen by comparing database and network encryption. In network encryption, you have a point-to-point strategy. But the difference goes further. For each network session, a new set of keys is generated. When the session is closed, the keys are destroyed because they are no longer needed. The keys are needed only long enough to pass the data from one point to the other. Saving the keys for future access is not required.
When you encrypt data in the database, the data may have an indeterminate lifetime. This means that you not only have to create and pass the keys to authorized users, but you also need some way to preserve the keys for access into the future.
Don’t forget about those DBAs, who have the ability to peer into everything that occurs within the database. Access to the database log files may also provide valuable information to the DBAs. This significantly differs from standard encryption uses in which the administrators may only see and access at most half of the conversation, either the sender or receiver (unless both are within the domain of the administrator).
Ultimately, the success or failure of the encryption for providing added defense lies in the ability to protect the encryption key. Oracle’s policy on key management is simple: there are no restrictions in how you want to manage the keys. The decision is completely up to you.
Key Management Options
Oracle recommends several options for key management. I will start off with one that seems the most intuitive: storing the keys in the database.
Storing Keys in the Database
There are several effective ways to store keys in the database. A major benefit to doing this is that the keys are maintained with the data. This may be important from a backup and recovery perspective because the keys and the data need to be maintained to a consistent point in time.
The approach I prefer is not to simply store the keys in a table; this is not adequate. You have to protect this table with multiple techniques. Access should be limited and potentially protected with a secure application role. The rows should be protected by VPD and OLS. Auditing access to the key table will help to ensure that unauthorized yet privileged users are not querying the table. Encrypting the encryption keys with a master key is also an effective way to protect the table in the event someone does gain access to the table.
Here is an example of how to store the keys in an auxiliary table. You’ll need a table to hold the keys and the relationship of the key to the record which it was used. The table name (KM for key management) and column names are somewhat obscure. This is done intentionally to prevent data dictionary snooping from divulging the use of the table ( SELECT owner, table_name, column_name FROM dba_tab_cols WHERE column_name LIKE '%KEY%'). Obscuring this relationship is also helpful in protecting the data.
CREATE TABLE km (pk VARCHAR2(30), ek RAW(48));
For the sample data table, encrypt the salaries from the PEOPLE table by re-creating the PEOPLE table based on the EMP table.
sec_mgr@KNOX10g> CREATE TABLE people 2 (NAME VARCHAR2(30), 3 salary VARCHAR2(16)); Table created. sec_mgr@KNOX10g> INSERT INTO people 2 SELECT ename, sal 3 FROM scott.emp; 14 rows created.
Next, create a PL/SQL encryption handler for this table. Its job is to encrypt the data with a secret key. It creates the key, stores the key, and fetches the key automatically. A parameter has to be passed to unlock the encryption and decryption functions, which prevents someone with the EXECUTE ANY PROCEDURE privilege from decrypting the values.
CREATE OR REPLACE PACKAGE enc_people AS FUNCTION enc_salary ( p_salary IN VARCHAR2, p_name IN VARCHAR2, p_unlock_code IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2; FUNCTION dec_salary ( p_salary IN VARCHAR2, p_name IN VARCHAR2, p_unlock_code IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2; END; / CREATE OR REPLACE PACKAGE BODY enc_people AS -- master encryption key. This package has to be wrapped -- with the 10g wrap. Otherwise key will be visisble. g_master_key VARCHAR2 (32) := 'ThisIsTheSuperSecretMasterKey'; -- code provided to unlock the encrypt and decrypt operations g_unlock_code VARCHAR2 (10) := 'OpenSesame';
To create the key, develop a function that generates a pseudorandom string. Due to a bug in the initial release of the DBMS_CRYPTO, the random key generation has to rely on the old DBMS_OBFUSCATION_TOOLKIT. You’ll need to execute on DBMS_OBFUSCATION_TOOLKIT for this implementation.
FUNCTION create_key (p_seed IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 -- return a pseudo-random 32-byte string AS l_key VARCHAR2 (32); l_seed VARCHAR2 (80) := NVL (p_seed, DBMS_RANDOM.STRING ('p', 80)); BEGIN l_key := DBMS_OBFUSCATION_TOOLKIT.des3getkey (which => 1, seed_string => l_seed) || DBMS_OBFUSCATION_TOOLKIT.desgetkey (seed_string => l_seed); RETURN l_key; END; The encryption keys will be different for each record, and they will be stored in an encrypted form using a master key. This prevents someone from successfully decrypting the data even if they obtain the keys from our stored table (for example, someone with the SELECT ANY TABLE privilege might try this).
FUNCTION enc_salary ( p_salary IN VARCHAR2, p_name IN VARCHAR2, p_unlock_code IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 AS l_key RAW (32); l_stored_key RAW (48); BEGIN IF ( p_unlock_code IS NULL OR p_unlock_code != g_unlock_code) THEN RETURN NULL; END IF; data_crypto.set_algorithm (data_crypto.aes); -- create a key and size it for algorithm l_key := data_crypto.getkey (create_key); -- encrypt key with master l_stored_key := data_crypto.encryptraw (l_key, g_master_key, data_crypto.aes256); -- store relationship between key and primary key INSERT INTO km VALUES (p_name, l_stored_key); -- return resulting encrypted data RETURN data_crypto.encrypt (p_salary, l_key); END;
To decrypt the data, the user or application must provide the unlock code.
FUNCTION dec_salary ( p_salary IN VARCHAR2, p_name IN VARCHAR2, p_unlock_code IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 AS l_key RAW (48); BEGIN IF ( p_unlock_code IS NULL OR p_unlock_code != g_unlock_code) THEN RETURN NULL; END IF; SELECT data_crypto.decryptraw (ek, g_master_key, data_crypto.aes256) INTO l_key FROM km WHERE pk = p_name; RETURN data_crypto.decrypt (p_salary, l_ key, data_crypto.aes); END; END; /
To test this, issue a SQL update statement to encrypt all the salaries. The unlock code has to be provided to ensure an authorized user or application is calling the function.
sec_mgr@KNOX10g> UPDATE people 2 SET salary = 3 enc_people.enc_salary (salary, NAME, 'OpenSesame'); 14 rows updated. sec_mgr@KNOX10g> COMMIT ; Commit complete. sec_mgr@KNOX10g> COL salary format a16 sec_mgr@KNOX10g> SELECT NAME, salary 2 FROM people 3 WHERE ROWNUM <= 3; NAME SALARY -------- ------------ SMITH ¯3++9?+|íᡦ) ALLEN W¦8?'j¦¦±$?+¦Qû WARD &¦¦¿@ÿxNg¦d¶O
You can decrypt by executing the decrypt function in the package. Note the unlock code has to be provided here. Also, a user simply trying to decrypt the records using the key stored in the KM table is unsuccessful since these keys have also been encrypted. Notice the encryption keys also were encrypted with a different and stronger algorithm (AES256) than was the data (AES128).
sec_mgr@KNOX10g> -- data has to be decrypted this way. sec_mgr@KNOX10g> SELECT NAME, 2 enc_people.dec_salary (salary, NAME, 'OpenSesame') 3 salary 4 FROM people 5 WHERE ROWNUM <= 3; NAME SALARY -------- --------- SMITH 800 ALLEN 1600 WARD 1250 sec_mgr@KNOX10g> -- cannot decrypt without code. code is also case-sensitive sec_mgr@KNOX10g> SELECT NAME, 2 enc_people.dec_salary (salary, NAME, 'OPENSESAME') 3 salary 4 FROM people 5 WHERE ROWNUM <= 3; NAME SALARY ------- --------- SMITH ALLEN WARD sec_mgr@KNOX10g> -- p_unlock_code is default NULL'ed sec_mgr@KNOX10g> SELECT NAME, enc_people.dec_salary (salary, NAME) salary 2 FROM people 3 WHERE ROWNUM <= 3; NAME SALARY ------- ----------- SMITH ALLEN WARD sec_mgr@KNOX10g> -- note we cannot decrypt without this function sec_mgr@KNOX10g> DECLARE 2 l_key RAW (48); 3 BEGIN 4 SELECT ek 5 INTO l_key 6 FROM km 7 WHERE pk = 'SCOTT'; 8 9 FOR rec IN (SELECT * 10 FROM people 11 WHERE NAME = 'SCOTT') 12 LOOP 13 DBMS_OUTPUT.put_line ( 'Scott''s Salary: ' 14 || data_crypto.decrypt 15 (rec.salary, 16 l_key)); 17 END LOOP; 18 END; 19 / 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 "SEC_MGR.DATA_CRYPTO", line 319 ORA-06512: at "SEC_MGR.DATA_CRYPTO", line 355 ORA-06512: at line 13
This last line shows the error that is thrown when an incorrect key is supplied to the decryption algorithm. This example should be used to help motivate ideas for you to employ in storing the encryption keys in the database. This approach doesn’t guarantee that the keys are absolutely secured from the DBAs.
Application Managed Keys
The challenge of database stored keys is there is a substantial risk that the data can be discovered by a skilled and determined DBA. An alternate solution to this is to have the application manage the keys. With this, the key is only passed to the encryption and decryption programs. Anyone with full access to the database will be unable to recover the keys.
A similar approach to that used with the database stored keys can be taken. The application can store the keys in a file on the application server. This file should be encrypted with a master application key.
An important part to key management is ensuring the keys are always available to unlock the data. Therefore, a strategy should be developed to ensure the application managed keys are securely stored and backed up. If the keys are lost, the data is lost.
Computed Keys
You saw in many of the examples that an effective way to manage the keys is to not actually store the keys. The keys can be deterministically computed. That is, a function can return the key for the record based on a secure algorithm.
This approach is analogous to using a combination lock versus a keyed lock. The secret to a combination lock is in knowing the combination. If the algorithm is protected, then the keys are protected. Therefore, any procedures in the database should be wrapped to prevent privileged users from uncovering the secure algorithm.
This technique is attractive because it may involve the least amount of auxiliary work. For example, you could BIT_XOR the primary key with the schema name and instance name of the database server. This would encrypt the data and ensure that it is protected for this database instance and schema; thus, exporting to a different database or schema would not be allowed. Looking at the encryption procedure signature, it would not be obvious how the key is computed, only that the primary key was one of the components:
CREATE OR REPLACE FUNCTION enc_some_data ( p_data IN VARCHAR2 DEFAULT NULL, p_key IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 AS l_key RAW (64); l_other_val RAW (64); BEGIN l_other_val := UTL_RAW.cast_to_raw ( SYS_CONTEXT ('userenv', 'instance_name') || SYS_CONTEXT ('userenv', 'current_schema')); l_key := UTL_RAW.bit_xor (UTL_RAW.cast_to_raw (p_key), l_other_val); RETURN data_crypto.encrypt (p_data, UTL_RAW.cast_to_varchar2 (l_key)); END; / CREATE OR REPLACE FUNCTION dec_some_data ( p_data IN VARCHAR2 DEFAULT NULL, p_key IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 AS l_key RAW (64); l_other_val RAW (64); BEGIN l_other_val := UTL_RAW.cast_to_raw ( SYS_CONTEXT ('userenv', 'instance_name') || SYS_CONTEXT ('userenv', 'current_schema')); l_key := UTL_RAW.bit_xor (UTL_RAW.cast_to_raw (p_key), l_other_val); RETURN data_crypto.decrypt (p_data, UTL_RAW.cast_to_varchar2 (l_key)); END; / You could even use the application’s IP address as a component to the key. This would make the encrypted data only available to the application coming from the same IP address.
Protecting the Key Procedures
The secret to the database stored keys and computationally derived keys is in protecting the source. Wrapping the PL/SQL code is mandatory. If this isn’t done, there is a risk that the code will be analyzed, the algorithm will be figured out, and hidden keys will be discovered.
You can wrap the ENC_SALARY and DEC_SALARY functions by saving the code to a file called enc_some_data.sql. Then you execute the wrap program and load the resulting .PLB file:
C:\tmp>wrap iname=enc_salary.sql PL/SQL Wrapper: Release 10.1.0.2.0- Production on Thu Feb 26 17:42:26 2004 Copyright (c) 1993, 2004, Oracle. All rights reserved. Processing enc_some_data.sql to enc_some_data.plb
In SQL*Plus, you can load the PLB file. Querying the source shows that your algorithm is secure from prying eyes:
sec_mgr@KNOX10g> @enc_some_data.plb Function created. Function created. sec_mgr@KNOX10g> SELECT text 2 FROM user_source 3 WHERE NAME = 'ENC_SOME_DATA' 4 ORDER BY line; TEXT ----------------------------------------------- FUNCTION enc_some_data wrapped a000000 b2 abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd 8 287 185 /UgrsuAFkDp50zTPJiyOpAhbxm0wgzsJLUgVfC/NbmRTYkpGBOXlhMxbhnXTPQ33EGuRe+nM Gp/KHTfcz895hPMYFvVNsOvt/s/+uUgrJzcCs8oNyhcZFEN2rUs2cFLna1CmDEiVbgUShUNf nJ41nPLwZilcK6bqVFgXk/RjPUl67v9JPdAhetmaNwAikbfnBG4MDAzi/zcmzz7Spj8j3KxF oulBV29oqGtrFptC6q3TYIxxqEv1scQTYfjygL3kgWNPTJ07mOhj4ofE3jOYwJ21ByfXSIYq uvwT0J9MsDFSdjjcHbDr4d4BVkttPIYrwtmNctQfnSS5G4rsRjtvEdT5b+a4DVKfV9D9uUBV kNFG6eqvDnc6Xbn9viT32A==
To maintain security, you should encrypt all package bodies, procedures, and functions that perform your key management. Because views and trigger code cannot be wrapped, they should be modified to call the wrapped code procedures passing any parameters (for example, :NEW and :OLD) as needed.
The Best Key Management Strategy
To answer the question on how to manage keys, one has to first ask what you are using encryption to protect against. For example, if you are using encryption to hide data from the DBAs (which is probably not even possible), then storing the keys in the database is not the safest approach. If you are encrypting to reduce risks associated with physical server theft, storing the keys on the server’s file system is not a good idea.
The most secure technique uses a combination of methods. For example, an application could set a value in an application context. The encryption and decryption procedures could use this value in combination with a stored key (perhaps XORed as in the preceding code) to derive the key. Using sophisticated key management makes the solution more secure, but it can also be difficult to manage and maintain. There is no single best solution for key management. The pros and cons of the approach should be evaluated. Performance, maintainability, and recoverability are all important to consider along with the security.
| ||
| ||
EAN: 2147483647
Pages: 111