Selective Encryption Part 2 of 3

In my previous post I talked about the importance of encryption in the database.  To be fair, sometimes it really isn't important, its just required for some reason.  This is more of a solution to the 2nd, as part of an overall security scheme.  Also, for a scenario where some of the data in a table is replicated to other databases/platforms, etc, this could be a way to auto-obfuscate.  The encrypted data would be moved through GG or w/e CDC you use without a key...making it very secure after it leaves the database.

We're going to use a block cipher...so first, you need to pick a cipher algorithm (here are the block options), a block cipher chaining modifier and a padding modifier from below, and add up their associated numbers. (ie: AES256 with CBC and PKCS5 would be 8+256+4096=4360)  We'll use 4360 for our example.

Here are some of the options in dbms_crypto:

    -- Block Cipher Algorithms
    ENCRYPT_DES                            :=     1;
    ENCRYPT_3DES_2KEY              :=     2;
    ENCRYPT_3DES                          :=     3;
    ENCRYPT_AES                            :=     4;
    ENCRYPT_PBE_MD5DES          :=     5;
    ENCRYPT_AES128                      :=     6;
    ENCRYPT_AES192                      :=     7;
    ENCRYPT_AES256                      :=     8;

    -- Block Cipher Chaining Modifiers
    CHAIN_CBC                                 :=   256;
    CHAIN_CFB                                 :=   512;
    CHAIN_ECB                                 :=   768;
    CHAIN_OFB                                 :=  1024;

    -- Block Cipher Padding Modifiers
    PAD_PKCS5                                  :=  4096;
    PAD_NONE                                   :=  8192;
    PAD_ZERO                                    := 12288;
    PAD_ORCL                                    := 16384;

1. First, create a key and capture the string that gets generated...don't lose this key:

set serveroutput on size 1000000
declare
  v_Number number := 256/8; --256 bit key
  v_Key RAW(32);
begin
  v_Key := DBMS_CRYPTO.RANDOMBYTES(v_Number);
  dbms_output.put_line(v_Key);
end;

2. Now replace the red string below with the output generated above.  This is meant as a very simple example.  In real life you'd want to do everything you can to make this complex and difficult to read. Putting it in a package and wrapping it would make it slightly less simple to get at.  Putting this data somewhere else encypted (like the OS or a different db) would be better.  How you do this is up to you...but just creating a plain text function isn't sufficient, and depending on plsql wrap is a bad idea...as Tom Kyte put it, "Using the latest wrap would provide the highest degree of "protection".  That is all.  I put "protection" in quotes because I've always been amused by 'wrapping' code."  I'll explain that more in a different post.

create or replace function blackan.my_key return varchar2 as 

begin 

  return '0F9157255C4CB6292D5C7E5FE96D90B2B4DE33CF4FEF2742366705F36D40A143'; 

end;

3. Now generate the base table and the decrypting view on the table:

drop table blackan.hacker_booty_base;

create table blackan.hacker_booty_base (EmpName raw(120), Emp_Num raw(88), SSN raw(80), Credit_Card raw(120), CC_Ex_Date raw(88), Address raw(120), PW raw(120));

create or replace view blackan.hacker_booty as

select 

  SUBSTRB(UTL_I18N.RAW_TO_CHAR (DBMS_CRYPTO.DECRYPT(EmpName,4360,my_key), 'AL32UTF8'),1,30) EmpName,

  SUBSTRB(UTL_I18N.RAW_TO_CHAR (DBMS_CRYPTO.DECRYPT(Emp_Num,4360,my_key), 'AL32UTF8'),1,22) Emp_Num,

  SUBSTRB(UTL_I18N.RAW_TO_CHAR (DBMS_CRYPTO.DECRYPT(SSN,4360,my_key), 'AL32UTF8'),1,20) SSN,

  SUBSTRB(UTL_I18N.RAW_TO_CHAR (DBMS_CRYPTO.DECRYPT(Credit_Card,4360,my_key), 'AL32UTF8'),1,30) Credit_Card,

  SUBSTRB(UTL_I18N.RAW_TO_CHAR (DBMS_CRYPTO.DECRYPT(CC_Ex_Date,4360,my_key), 'AL32UTF8'),1,30) CC_Ex_Date,

  SUBSTRB(UTL_I18N.RAW_TO_CHAR (DBMS_CRYPTO.DECRYPT(Address,4360,my_key), 'AL32UTF8'),1,30) Address,

  SUBSTRB(UTL_I18N.RAW_TO_CHAR (DBMS_CRYPTO.DECRYPT(PW,4360,my_key), 'AL32UTF8'),1,30) PW

from hacker_booty_base;

4. Now create the encrypting "instead of" trigger on the view to redirect the dml to the base table after encrypting it:

CREATE OR REPLACE TRIGGER blackan.ioiudr_hacker_booty

INSTEAD OF INSERT OR UPDATE OR DELETE

ON blackan.hacker_booty

FOR EACH ROW

BEGIN

  if INSERTING then

    insert into hacker_booty_base values (

      DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW(:NEW.EmpName,'AL32UTF8'),4360,my_key),

      DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.Emp_Num,  'AL32UTF8'),4360,my_key),

      DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.SSN,  'AL32UTF8'),4360,my_key),

      DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.Credit_Card,  'AL32UTF8'),4360,my_key),

      DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.CC_Ex_Date,  'AL32UTF8'),4360,my_key),

      DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.Address,  'AL32UTF8'),4360,my_key),

      DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.PW,  'AL32UTF8'),4360,my_key));

  elsif updating then

    update hacker_booty_base set 

      EmpName=DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW(:NEW.EmpName,'AL32UTF8'),4360,my_key),

      Emp_Num=DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.Emp_Num,  'AL32UTF8'),4360,my_key),

      SSN=DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.SSN,  'AL32UTF8'),4360,my_key),

      Credit_Card=DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.Credit_Card,  'AL32UTF8'),4360,my_key),

      CC_Ex_Date=DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.CC_Ex_Date,  'AL32UTF8'),4360,my_key),

      Address=DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.Address,  'AL32UTF8'),4360,my_key),

      PW=DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.PW,  'AL32UTF8'),4360,my_key)

    where SSN=:OLD.SSN;

  else

    delete from hacker_booty_base where SSN=:OLD.SSN;

  end if;    

end;

...and that's it.  Let's insert a row of fake data:

insert into blackan.hacker_booty values ('Richy Rich',1,'123-45-6789','111 2222 3333 4444', sysdate+365, '1 Main St, St Louis, MO, 12345', 'Shhh');

When you look at the actual data-at-rest, its encrypted:

select empname, emp_num, ssn from blackan.hacker_booty_base;

EMPNAME	EMP_NUM	SSN
454460100E82C91F8B26AEE3E35EE3E8	9F1BE1034151056A760A880E5D7BC5C3	29B471D31D8E2255D1B0C7B6D5084759

When you look at the data via your application, which would use the view, you see the data exactly as you entered it:

select empname, emp_num, ssn from blackan.hacker_booty;

EMPNAME,EMP_NUM,SSN

Richy Rich    1       123-45-6789

SDE Part 1
YOU ARE HERE>> SDE Part 2
SDE Part 3