Using DBMS_CRYPTO for column encryption

The DBMS_CRYPTO PL/SQL package is an important component of Oracle Cryptographic API. DBMS_CRYPTO can be wrapped in your own packages and used for encryption and decryption. It is mainly used for hindering data access using encryption on designated columns. Consider it as a selective method of encryption—the columns are stored in encrypted format on storage and remain encrypted during data access unless they are decrypted with the appropriate function.

In this recipe we will create a table EMPLOYEES_ENC and encrypt and decrypt the salary and commission_pct columns of this table by using DBMS_CRYPTO wrapped in two functions.

Getting Ready

All steps will be performed on the HACKDB database.

How to do it...

  1. As root create a directory named hashkeydir and make the oracle user the owner:
    mkdir /hashkeydir 
chown oracle:oinstall /hashkeydir
  2. Connect as system and create a directory named encryption_keys as follows:
    SQL> conn system
Enter password:
Connected.
SQL>
SQL> create or replace directory encryption_keys as '/hashkeydir';
Directory created.
  3. Grant read and write privileges on the encryption_keys directory to the HR user as follows:
    SQL> grant read, write on directory encryption_keys to HR;
  4. Grant the execute privilege on the DBMS_CRYPTO PL/SQL package to HR as follows:
    SQL> grant execute on dbms_crypto to hr;

Grant succeeded.

SQL>
  5. Connect as the HR user and create a table named employees_enc as follows:
    SQL> conn HR
Enter password:
Connected.
SQL> create table employees_enc as select first_name,last_name, salary, commission_pct from employees where salary is not null and commission_pct is not
  2  null and rownum <= 5;

Table created.

SQL>
  6. Next, add two columns enc_salary and enc_commission_pct defined as RAW type. Enc_salary will store the encrypted values for the salary column and enc_commission_pct for the commission_pct column:
    SQL> ALTER TABLE EMPLOYEES_ENC  ADD  (ENC_SALARY RAW(50));

Table altered.

SQL> ALTER TABLE EMPLOYEES_ENC  ADD  (ENC_COMMISSION_PCT RAW(50));

Table altered.

SQL>
  7. At this step we will create a package named column_encryption_pkg and the wrapper function definitions for encryption and decryption implemented with DBMS_CRYPTO. We will explain in detail the scope of its functions and procedures later. Create the package column_encryption_pkg as follows:
    CREATE OR REPLACE
PACKAGE encryption_pkg
IS
  --Generate the encryption key for a given table
PROCEDURE store_encryption_key
  (
    p_dir_name     IN VARCHAR2,
    p_key_filename IN VARCHAR2);
  --Retrieve the encryption key from the local storage
  --PROCEDURE get_encryption_key(p_dir_name IN VARCHAR2,p_key_filename IN VARCHAR2);
  --Function used to encrypt a given string
  FUNCTION encrypt_column
    (
      p_column_value IN VARCHAR2,
      p_dir_name     IN VARCHAR2,
      p_key_filename IN VARCHAR2)
    RETURN raw;
    --Function used to decrypt a given string
  FUNCTION decrypt_column
    (
      p_encrypted_value IN RAW,
      p_dir_name        IN VARCHAR2,
      p_key_filename    IN VARCHAR2)
    RETURN VARCHAR2;
  END column_encryption_pkg;
SQL> /

Package created.

SQL>
  8. Next, create the PACKAGE BODY of column_encryption_pkg as follows: 
    CREATE OR REPLACE
PACKAGE BODY column_encryption_pkg
IS
  SQLERRMSG    VARCHAR2(255);
  SQLERRCDE    NUMBER;
  ENC_TYP_3DES CONSTANT PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_3DES -- use 3DES algorithm for encryption
  + DBMS_CRYPTO.CHAIN_CBC                                       -- use CBC as block cipher chaining mode
  + DBMS_CRYPTO.PAD_PKCS5;                                      -- use PKCS5 type padding
PROCEDURE store_encryption_key
  (
    p_dir_name          IN VARCHAR2,
    p_key_filename      IN VARCHAR2)
                        IS
  var_key_length NUMBER := 256/8; -- key length 256 bits (32 bytes)
  var_encryption_key RAW (32);
  var_file_handler UTL_FILE.FILE_TYPE;
BEGIN
  var_encryption_key := DBMS_CRYPTO.RANDOMBYTES (var_key_length);          -- generate a random 256 bit length key
  var_file_handler   := UTL_FILE.FOPEN(p_dir_name,p_key_filename,'W',256); -- open the file for write
  UTL_FILE.PUT_RAW(var_file_handler,var_encryption_key,TRUE);              -- write the encryption key into the file
  UTL_FILE.FCLOSE(var_file_handler);                                       -- close the file handler
END store_encryption_key;
FUNCTION encrypt_column
  (
    p_column_value IN VARCHAR2,
    p_dir_name     IN VARCHAR2,
    p_key_filename IN VARCHAR2)
  RETURN RAW
IS
  -- Local variables
  var_column_value_to_raw RAW(48);        --initial string converted to raw
  var_encrypted_raw_column_value RAW(48); --encrypted value of the string
  var_encryption_key RAW (32);
  var_file_handler UTL_FILE.FILE_TYPE;
  encryption_key RAW (32);
BEGIN
  var_column_value_to_raw := UTL_I18N.STRING_TO_RAW(p_column_value, 'AL32UTF8'),
  var_file_handler        := UTL_FILE.FOPEN(p_dir_name,p_key_filename,'R',256);
  UTL_FILE. GET_RAW (var_file_handler, var_encryption_key, 32);
  encryption_key                 := var_encryption_key;
  var_encrypted_raw_column_value := DBMS_CRYPTO.ENCRYPT( src => var_column_value_to_raw ,typ => ENC_TYP_3DES ,KEY => encryption_key );
  RETURN var_encrypted_raw_column_value;
EXCEPTION
WHEN OTHERS THEN
  SQLERRMSG := SQLERRM;
  SQLERRCDE := SQLCODE;
  RETURN NULL;
END encrypt_column;
FUNCTION decrypt_column
  (
    p_encrypted_value IN RAW,
    p_dir_name        IN VARCHAR2,
    p_key_filename    IN VARCHAR2)
  RETURN VARCHAR2
IS
  -- Local variables
  var_encryption_key RAW (32);
  var_column_raw_val_to_vr VARCHAR2(200);
  var_decrypted_raw_column_value RAW(200);
  var_file_handler UTL_FILE.FILE_TYPE;
  encryption_key RAW (32);
BEGIN
  var_file_handler := UTL_FILE.FOPEN(p_dir_name,p_key_filename,'R',256);
  UTL_FILE.GET_RAW (var_file_handler, var_encryption_key, 32);
  encryption_key := var_encryption_key;
  --decrypt the encrypted string
  var_decrypted_raw_column_value := DBMS_CRYPTO.DECRYPT( src => P_ENCRYPTED_VALUE ,typ => ENC_TYP_3DES ,KEY => encryption_key );
  --convert the value to varchar2
  var_column_raw_val_to_vr := UTL_I18N.RAW_TO_CHAR(var_decrypted_raw_column_value, 'AL32UTF8'),
  RETURN var_column_raw_val_to_vr;
EXCEPTION
WHEN OTHERS THEN
  SQLERRMSG := SQLERRM;
  SQLERRCDE := SQLCODE;
  RETURN NULL;
END decrypt_column;
END column_encryption_pkg;
SQL> /

Package body created.

SQL>
  9. At this step we should be able to encrypt the salary and commission_pct columns. First we have to generate the encryption key by executing the store_encryption_key procedure. Pass the directory name (ENCRYPTION_KEYS) and the key storage file name (KEYFILE) as follows:
    SQL> execute column_encryption_pkg.store_encryption_key('ENCRYPTION_KEYS','KEYFILE'),

PL/SQL procedure successfully completed.

SQL>
  10. Next, encrypt the salary and commission_pct columns by executing the encrypt_column function in an UPDATE statement as follows:
    SQL> update employees_enc set enc_salary=column_encryption_pkg.encrypt_column(SALARY,'ENCRYPTION_KEYS','KEYFILE'),enc_commission_pct=column_encryption_pkg.encry
pt_column(COMMISSION_PCT,'ENCRYPTION_KEYS','KEYFILE'),

5 rows updated.

SQL> commit
  2  ;

Commit complete.

SQL>
  11. Next, verify that the decryption is working. We should have the same values at return as the original values.
    SELECT first_name,
  last_name,
  column_encryption_pkg.decrypt_column(ENC_SALARY,'ENCRYPTION_KEYS','KEYFILE') AS DEC_SALARY,
  SALARY,
  column_encryption_pkg.decrypt_column(ENC_COMMISSION_PCT,'ENCRYPTION_KEYS','KEYFILE') AS DEC_COMMISSION_PCT,
  COMMISSION_PCT
FROM employees_enc
WHERE salary      =column_encryption_pkg.decrypt_column(ENC_SALARY,'ENCRYPTION_KEYS','KEYFILE')
AND commission_pct=column_encryption_pkg.decrypt_column(ENC_COMMISSION_PCT,'ENCRYPTION_KEYS','KEYFILE'),
  12. If the column values match, you should remove the unencrypted columns and continue to add values from now on to the corresponding encrypted columns by using the encrypt_column function. Also as an additional protection measure you should remove all the code comments and wrap the package and package body to hide the source code.

How it works...

The DBMS_CRYPTO package accepts as input values varchar2 and lob type fields, and implicitly returns RAW type data. Therefore it is necessary to cast the data from the initial type to RAW and cast back at return to the initial data type.

DBMS_CRYPTO.ENCRYPT_RC4:RC4 provides the following encryption algorithms:

  • For AES:
    • DBMS_CRYPTO.ENCRYPT_AES128: AES with 128-bit key size
    • DBMS_CRYPTO.ENCRYPT_AES192: AES with 192-bit key size
    • DBMS_CRYPTO.ENCRYPT_AES256: AES with 192-bit key size 256-bit key size
  • For DES:
    • DBMS_CRYPTO.ENCRYPT_DES: DES wtih 56-bit key size
    • DBMS_CRYPTO.ENCRYPT_3DES_2KEY: 3DES with 112-bit key size
    • DBMS_CRYPTO.ENCRYPT_3DES: 3DES with 168-bit key size

We have briefly described these algorithms in Chapter 2, Defending the Network and Data in Transit.

The supported block cipher chaining modifiers, also known as block cipher modes of operations are ECB, CBC, CFB, and OFB. Cipher modes of operation protect against block replay attacks, enabling repeated and secure use of a block cipher under a single key, making the encryption of one block dependent on all preceding blocks.

The blocks are encrypted using an initialization vector (IV), which is a block of bits used to randomize the encryption. In this way, the resulting ciphertext is different every time even if the input plaintext is the same.

ECB (DBMS_CRYPTO.CHAIN_ECB) is the abbreviation for Electronic Codebook. It is the simplest and weakest cipher chaining modifier. It generates the same ciphertext for the same plaintext being very sensible to replay attacks. Therefore it is not recommended to use it in any circumstances.

CBC (DBMS_CRYPTO.CHAIN_CBC) is the abbreviation for Cipher block chaining. In this mode, on each block of plaintext before encryption an XOR operation is performed using the previous ciphertext block. In this method the encryption is randomized using an initialization vector at the beginning.

CFB (DBMS_CRYPTO.CHAIN_CFB) is the abbreviation for Cipher Feedback. CFB is similar to CBC; the operations are performed as in CBC but in the reverse order.

OFB (DBMS_CRYPTO.CHAIN_OFB) is the abbreviation for Output Feedback. It uses a stream cipher encryption scheme similar to CFB. It generates keystream blocks, which are then XORed with the plaintext blocks to get the ciphertext.

The padding schemes provided by DBMS_CRYPTO are PKCS5, NONE, and NULL.

Padding is used to fill up empty blocks. Usually the size of plaintext to be encrypted is not an exact multiple of the block size. The recommended padding scheme is PKCS5.

There's more...

DBMS_CRYPTO can also be used for integrity check by using MD5 and SHA1 hashes, and Message Authentication Codes (MAC). The difference between hashes and MAC is that hashes are used to guarantee integrity, whereas, a MAC guarantees integrity and authentication. The value generated by a hash is always the same and is based solely on an input value, while a MAC relies on generating the hash using a secret key.

The following is an example of a procedure for generating hash and MAC values using an input password. If the procedure is executed multiple times, it will generate the same hash and different MAC values for the same password.

SQL> Set serveroutput on
 DECLARE
  2  l_pwd VARCHAR2(16) := 'my512pT*;(1)';
  3   l_raw_pwd RAW(128) := utl_raw.cast_to_raw(l_pwd);
  4   l_key RAW(256) := DBMS_CRYPTO.RANDOMBYTES(128);
  5   l_mac_val RAW(2048);
  6   BEGIN
  7   dbms_output.put_line('Password: ' || l_pwd);
  8   dbms_output.put_line('Raw Password: ' || l_raw_pwd);
  9   dbms_output.put_line('Key: ' || l_key);
 10
 11   l_mac_val := DBMS_CRYPTO.MAC(l_raw_pwd,
 12  DBMS_CRYPTO.HMAC_SH1, l_key);
 13   dbms_output.put_line('SHA-1 MAC: ' || l_mac_val);
 14
 15  l_mac_val := DBMS_CRYPTO.MAC(l_raw_pwd,
 16  DBMS_CRYPTO.HMAC_MD5, l_key);
 17   dbms_output.put_line('MD5 MAC: ' || l_mac_val);
 18   END;
 19  /
Password: my512pT*;(1)
Raw Password: 6D7935313270542A3B283129
Key:
3504D8D9D8DDF9696D1DFF26B0A94C44C78C6839663B6315B5656E940F47BBF100EA58F90 3148FE865E9D2D2E3B36A2C73B28C8B0752F5896A50309D082ADA5F
SHA-1 MAC: 75FEAC60E9D6BA11BA562501FB500FF8591E08B6
MD5 MAC: 9A3DC312E2D635E59ADEB997681F5143

PL/SQL procedure successfully completed.

SQL>
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.191.176.5