DBMS_CRYPTO: Example In the comments from my previous post, Tyler and Tom both mentioned the fact that you should never store sensitive data as plain-text in the database. I used a bad example unfortunately, but Tom did mention the DBMS_CRYPTO package which I have used before.

Since I have used it and published a working example to the OTN forums, I figured I'd put it here as well.

The goal is the encrypt a SSN and store the encrypted string (RAW) in the database. In conjunction with VPD, you should be able to easily control who can see the plain-text version of the SSN. To do that you will need to create another function which decrypts the encrypted SSN.

This is a pretty basic example, but it should help to get you started. You can read more about the DBMS_CRYPTO package in the docs.

CREATE OR REPLACE
PACKAGE BODY p_encrypt
AS
  --DO NOT FORGET TO WRAP THIS BEFORE LOADING INTO DATABASE
  --IF IT IS NOT WRAPPED, THE KEY WILL BE EXPOSED
  --THE WRAP UTILITY IS LOCATED IN THE \BIN DIRECTORY (WRAP.EXE)
  G_KEY RAW(32) := UTL_I18N.STRING_TO_RAW( 'some_random_string_stuff_goes_here', 'AL32UTF8' );
  
  FUNCTION encrypt_ssn( p_ssn IN VARCHAR2 ) RETURN RAW
  IS
    l_ssn RAW(32) := UTL_I18N.STRING_TO_RAW( p_ssn, 'AL32UTF8' );
    l_encrypted RAW(32);
  BEGIN
    NULL;
    l_encrypted := dbms_crypto.encrypt
                   ( src => l_ssn,
                     typ => DBMS_CRYPTO.DES_CBC_PKCS5,
                     key => G_KEY );
                     
    RETURN l_encrypted;
  END encrypt_ssn;
  
  FUNCTION decrypt_ssn( p_ssn IN RAW ) RETURN VARCHAR2
  IS
    l_decrypted RAW(32);
  BEGIN
    l_decrypted := dbms_crypto.decrypt
                    ( src => p_ssn,
                      typ => DBMS_CRYPTO.DES_CBC_PKCS5,
                      key => G_KEY );
    
    RETURN UTL_I18N.RAW_TO_CHAR( l_decrypted, 'AL32UTF8' );
  END decrypt_ssn;
  
END p_encrypt;
/ 
show errors

I mention it in the comments of the code, but do not forget to wrap your PL/SQL before you load it, otherwise someone will be able to easily see your salt/key.

Labels: dbms_crypto, howto, oracle, plsql, sql

  ¶ 4/17/2008 12:24:00 AM