What amuses Tom Kyte? (aka: To wrap, or not to wrap?) Selective Encryption Part 3 of 3

So...this post is not about rap, its about wrapping plsql.  If you've come here looking for a music site...run away! There's nothing about Jay-Z, the Beyonce formation Tour, Kanye West, Lil Wayne or Drake.

If you think you're getting security by wrapping your plsql, stick around...its not protection.  AS Tom said, "I put "protection" in quotes because I've always been amused by 'wrapping' code."

In my previous post, I had a function that returned a 256-bit key, and mentioned a few ways of protecting it...but cautioned about using plsql wrap.  This is only slightly better than doing nothing.

The function in the previous post is:

create or replace function blackan.my_key return varchar2 as
begin
  return '0F9157255C4CB6292D5C7E5FE96D90B2B4DE33CF4FEF2742366705F36D40A143';
end;

I run the wrap utility and it creates my_function.plb:

wrap iname=my_function.sql

Taking a peek in the file I see:

create or replace function blackan.my_key wrapped
a000000
367
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
7e ba
mtACEgpb2btQRc85YlHfKReZyCowgyr6f8tqynSm3f80Fbor4H4vlzbU3ANp9BtM2jLlkqnk
vF5IANpFigWMarvPqiRJdFXQs+5cQGjZZxauuFyxO9bW1UkgQotGR+YJ78l+VqExIcanEJXy
LoQv715qogTgWUFi22C8R47oRG4zYWj5tlQcWQ==
/

You might be thinking, "Wow!  That sure looks secure!  Comparing it to the original, I can't see anything that's the same!"  Don't think that...its a false sense of security.  There are TONS of post/utilities...even websites that will "unwrap" your plsql.  

Here's one:
http://www.codecrete.net/UnwrapIt

If you paste the wrapped code above in to the website, you'll see:

FUNCTION my_key RETURN VARCHAR2 AS 
BEGIN 
  RETURN '0F9157255C4CB6292D5C7E5FE96D90B2B4DE33CF4FEF2742366705F36D40A143'; 
END;

So...don't trust the security of wrapping plsql.

On the other hand, this allows you to get insight...when metalink is failing you, you may be able to paste the internal wrapped plsql packages from Oracle and diagnose your own issues.

I hope this helps you see you shouldn't trust the plsql wrap to keep your data secure.

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

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

Selective Encryption - Part 1 of 3

Encryption in databases is nothing new, but its becoming a more prominent topic every day.  One of my first experiences with it was in dealing with a database that had extremely confidential data, and it was a known target to foreign countries who were actively attempting to access the network to get to the database.  Not to be overly dramatic, but if this data was compromised, people would be killed. I wasn't working for a Gov't agency at the time...so don't read too much into all I just said.  The point is, every company stores data that needs to be secured, and the reality of it is, security is an illusion we can tell ourselves we have until we're proven wrong.  So from that you have two choices:

1. Give up, its inevitable our data will be breached.
2. Make it as difficult as possible for our data to be breached, and if it is, encrypt everything valuable to make it more difficult at that point.

I choose 2.  :)  In the scenario I mentioned, we had security layer on security layer..and I don't mean to say this is the complete solution to make your data secured.  Given the data in your database will eventually be in the hands of people who took it, what can you do?  Oracle  sells TDE, which does a lot of the work for you, but its a licensed feature.  It used to include transparent data encryption at rest and data in flight.  Thanks to a security issue a few years ago that exposed encrypted network traffic, encryption of network traffic in flight is now part of your normal db license (aka no additional cost.)

   Strongauthentication services (Kerberos, PKI, and RADIUS) and network encryption (native           network encryption and SSL/TLS) are no longer part of Oracle Advanced Security andare available in all licensed editions of all supported releases of the Oracle database.

What about data at rest and backups?  Every company keeps PII (personal identifiable information) on their employees...Name, SSN, Address, Phone number, etc.  What about your company's customers?  If your unencrypted data is breached, your company is required by law to notify the people who may have had PII stolen. Would your company lose future business if this happened? I've been told the FCC requires databases storing network endpoint addresses to be 256-bit encrypted. For almost all dba's, encryption isn't really optional anymore.

So...We talked about TDE...there's also something Oracle refers to as SDE (selective data encryption) ...and this is free (1930738.1).  This is really easy to implement...its not perfect, but its better than nothing.  I'll show you how in the next post.

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