Monday, 28 January 2008

Oracle Programing and security

Encryption is a fascinating issue within database design and for this post we are looking principally at Oracle.
Oracle provides some inbuilt encryption and hashing packages as standard modules. There are also a number of both free solutions and commercial alternatives that provide this capability. The familiar issues of compliance, performance and protecting keys also needs to be addressed.

One of the biggest issues has come as a result of the PCI-DSS (the standards that are designed to protect payment card information). In particular to the database issue, we need to look at section 3.4 of the PCI-DSS (Version 1.1). It states that the followign task must be completed:

Render PAN, at minimum, unreadable anywhere it is stored (including data on portable digital
media, backup media, in logs, and data received from or stored by wireless networks) by using any of the following approaches:

  • Strong one-way hash functions (hashed indexes)
  • Truncation
  • Index tokens and pads (pads must be securely stored)
  • Strong cryptography with associated key management processes and procedures.
The MINIMUM account information that must be rendered unreadable is the PAN.
If for some reason, a company is unable to encrypt cardholder data, refer to Appendix B: “Compensating Controls for Encryption of Stored Data.”

3.4.1 If disk encryption is used (rather than file- or column-level database encryption), logical access must be managed independently of native operating system access control Payment Card Industry (PCI) Data Security Standard 6 mechanisms (for example, by not using local system or Active Directory accounts). Decryption keys must not be tied to user accounts.

The MD5 routines in DBMS_OBFUSCATION_TOOLKIT are available from version 8iR3, and the DBMS_METADATA package is available from version 9iR1 of Oracle.

The DBMS_OBFUSCATION_TOOLKIT package provies an Oracle developer with the ability to encrypt data in the database. At present it only implements the DES and 3DES encryption algorithms. The package doesn’t allow double encryption and doesn’t support CBC modes. DBMS_CRYPTO is a newer package that is designed as a replacement for the DBMS_OBFUSCATION_TOOLKIT package available in Oracle 8i and 9i, it addresses many of the shortcomings in the previous package. It is is easier to use and contains more cryptographic algorithms:
  • Cryptographic algorithms - DES, 3DES, AES, RC4, 3DES_2KEY
  • Padding forms - PKCS5, zeroes
  • Block cipher chaining modes - CBC, CFB, ECB, OFB
  • Cryptographic hash algorithms - MD5, SHA-1, MD4
  • Keyed hash (MAC) algorithms - HMAC_MD5, HMAC_SH1
  • Cryptographic pseudo-random number generator - RAW, NUMBER, BINARY_INTEGER
  • Database types - RAW, CLOB, BLOB
The hashing checksum routines provide functions to allow checksums to be created for both raw and text data. These functions are used to both obscure data and to ensure the integrity of the data. An example would be for the developer to write a checksum value for each object in a schema. It is also important to add error checking and input validation.

It is essential to monitor and log which users have access to the packages DBMS_METADATA, DBMS_OBFUSCATION_TOOLKIT, and DBMS_CRYPTO. The who_can_access.sql script may be used to provide this level of auditing or a a simple control. The access of any users who do not need to run these packages needs to be REVOKEd to ensure that you only grant privileges to those that need them. MD5 checksums can be used to make it more difficult for an attacker to cover their tracks.

The DBMS_OBFUSCATION_TOOLKIT has a limitation with the MD5 routines. This package only sums the first 32K of data. This is also a limitation inherent to PL/SQL. An attacker could use this flaw to alter any code after the 32K boundary. By splitting PL/SQL code into blocks of 32K that are processed separately for the purpose of creating a checksum, this flaw is avoided. It does increase the processor utilisation however.

A simple example of using the encryption packages is:

l_visa_card_num VARCHAR2(19) := '0123 4567 8901 2345';
l_ccn_raw RAW(128) := UTL_RAW.cast_to_raw(l_visa_card_num);
l_key RAW(128) := UTL_RAW.cast_to_raw('abcdefgh');

l_encrypted_raw RAW(2048);
l_decrypted_raw RAW(2048);
DBMS_OUTPUT.put_line('Original : ' || l_visa_card_num);

l_encrypted_raw := DBMS_CRYPTO.encrypt(src => l_ccn_raw,
typ => DBMS_CRYPTO.aes_cbc_pkcs5,
key => l_key);

DBMS_OUTPUT.put_line('Encrypted : ' || RAWTOHEX(UTL_RAW.cast_to_raw(l_encrypted_raw)));

l_decrypted_raw := DBMS_CRYPTO.decrypt(src => l_encrypted_raw,
typ => DBMS_CRYPTO.aes_cbc_pkcs5,
key => l_key);

DBMS_OUTPUT.put_line('Decrypted : ' || UTL_RAW.cast_to_varchar2(l_decrypted_raw));
Original : 0123 4567 8901 2345
Encrypted : 3223041423134363443444777412353453453633251435345435335444343373632333135424533543314545
Decrypted : 0123 4567 8901 2345

PL/SQL procedure successfully completed.
Using these functions it is possible to verify the integrity of an Oracle database and also meet the PCI requirements.


Anonymous said...

All dbms_output statements lack concatenation symbol (||)

Craig S Wright said...

Thanks, I missed that they did not come through.

HTML in Blogger not as simple at times as could be hoped. Many Symbols seem to be dropped or consumed.

In this case,
The | char needed to be replaced with "& # 124;" (no quotes).