Oracle Key Management Security Oracle Security Tips by Burleson Consulting

This is an excerpt from the bestselling book "Oracle Privacy Security Auditing", a complete Oracle security reference with working Oracle security scripts.
 

---

Key Management

The next thorny issue to be handled is how to manage the several keys that will be used to authenticate data. In the method described above, this issue is moot since the key is an integral part of the data itself. In other cases, where the data needs to be encrypted with a less dynamic scheme, or where the encryption key and the table data are disjointed, the issue of key management comes up.

One option is to have a single key, which is 24 bytes long and use it to encrypt everything. This is, of course, the simplest but not the safest. If the hacker learns the key, all of the data will be exposed. But with proper safeguarding, this option may be a viable one.

Other options for handling multiple keys are described here:

Database

The key can be stored in the database in a safe schema, similar to the application user management schema we saw earlier. The applications can ask for the key whenever it needs to en/decrypt anything. Although this seems simple, in practice this may create more problems. The biggest of all is how to manage the key updates. A simple task like changing keys will have to go through a process of re-encrypting the data and updating the entire table, a task that may be close to impossible in an active system.

Filesystem

The key can be stored in an operating system file, which can be retrieved at runtime and then supplied to the database during the decryption or encryption process. One advantage of this scheme is the security it offers from the DBA. Traditionally in any scheme involving only the database, the DBA can still query all tables and procedures, and therefore can learn the key to decrypt the data. By placing it in the OS, only a privileged set of users will be able to access it, not even the DBA.

However, this option also fraught with other problems – the most important of which is reliability. The key is not part of the database anymore and is not backed up, recovered or otherwise in sync with the stages of the database. If they key is lost, the data inside the database is lost forever.

Users

This is perhaps the best in terms of security. The user who is supposed to encrypt and decrypt has to supply the key at runtime. However, the biggest problem in this scheme is the chance that the user may lose the key and therefore render the database useless. Apart from that, the other serious problem is the fact that the key must be shared among a group of people who might have to operate on the data. This increases the security hole, the very thing we are supposed to prevent using encryption.

Based on the above analysis, it would seem that the dynamic encryption scheme is the best of the all solutions.

Securing the Code

The other weak link in the security chain is the code used to encrypt and decrypt. This is particularly true if the key is embedded inside the procedure or function. Even if the key is not present in the program, the code logic may give away several valuable features of the security scheme and should be protected. The feature Oracle provides to encrypt the code itself is known as WRAP. You would invoke the following to wrap a SQL file get_encrypted_data.sql

wrap iname=get_encrypted_data.sql

This will generate another file named get_encrypted_data.plb, which will contain encrypted contents of the file. An example is provided in Figure 6.3. This is part of the wrapped file. Note the syntax used for creating a function or procedure is intact, but everything else is replaced by seemingly random characters. This file can be directly run to create the function.

create or replace function get_encrypted_data wrapped
0
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
3
8
9200000
1
4
0
1d
2 :e:
1FUNCTION:
1GET_ENCRYPTED_DATA:
1P_INPUT:

Figure 6.3 Part of the Wrapped PL/SQL file.

One word of caution here –  wrap is an one-way encryption process for the files; there is no unwrap function. So never throw away your original file. The wrapping is done to make sure that someone peeking into the dba_source view will not be able to see the code in clear text.

A Word on MD5 Procedures

If you look into the source code for the dbms_obfuscation_toolkit package specification, you might notice some functions and procedures named MD5. Each procedure is also overloaded with both RAW and VARCHAR2 parameter datatypes, and therefore there are four declarations of MD5 available in the package.

These code segments are not documented, probably because they are not encryption mechanisms. The MD5 programs are used to create a hash or digested value from the input, not encrypt it, hence the name MD, short for Message Digest. This hashed value cannot be reversed to the original value, or un-hashed, or undigested. This property of one way hashing makes it an unsuitable candidate for encryption, and therefore is not covered in this book in detail. However, for the sake of completeness, the usage has been explained here.

MD5 routines can be used to create hash values for integrity checking purposes. For instance, while transmitting the data over a network, the hashed value of the data can also be sent, perhaps separately. Upon receiving the data and the hash, the target system may hash the data again and compare that with the hash it received to make sure the data is intact. This process is known loosely as checksumming. MD5 is a great way to provide checksumming functionality in your application.

As in the case of the DES3Encrypt and DES3Decrypt procedures, since it has two functions and two procedures defined with the same name and with the same number of inputs, a regular call to the procedure will return an error.

SQL> variable v varchar2(2000)
SQL> exec :v := dbms_obfuscation_toolkit.md5 ('ARUP' )

ERROR at line 1:
ORA-06550: line 1, column 13:
PLS-00307: too many declarations of 'MD5' match this call
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

The problem is the way MD5 is defined. There are two functions named MD5, with input parameters RAW in one and VARCHAR2 in the other. To correct the problem use:

SQL> exec :v := dbms_obfuscation_toolkit.md5 –
2> (input_string=> 'ARUP')

Note the use of named parameter as the input. This makes sure the correct parameter name is used.

 

This is an excerpt from the book "Oracle Privacy Security Auditing". You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle security and auditing scripts.