DBMS_CRYPTO in action

DBMS_CRYPTO is well known package collection that are used in encrypting or hashing values in oracle database. This is true out of the box collection set that really help oracle people in many security tasks.

Regardless there are many nice topics on this subject (one of them is of course Database Security Enhancements in Oracle Database 10g about all important in this area), my intention is to show a little bit different approach for very common problem: encrypting column values and showing them fully, partially or not at all based on custom rule.

The problem

Let us suppose we have a table with some sensitive data we should protect somehow:

SQL>  DESC SEC_DEMO_TBL
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(38)
 A_USERNAME                                NOT NULL VARCHAR2(32)
 A_CREDIT_CARD_NUMBER                      NOT NULL VARCHAR2(32) 

SQL> insert into SEC_DEMO_TBL values (1, 'SMITH',  '0123456-789-0001');

1 row created.

SQL>  insert into SEC_DEMO_TBL values (2, 'PITT',  '0123456-789-0002');

1 row created.

SQL>  insert into SEC_DEMO_TBL values (3, 'JOLIE', '0123456-789-0003');

1 row created.

SQL>  commit;

Commit complete.

SQL> select * from SEC_DEMO_TBL;

        ID A_USERNAME                       A_CREDIT_CARD_NUMBER
---------- -------------------------------- --------------------------------
         1 SMITH                            0123456-789-0001
         2 PITT                             0123456-789-0002
         3 JOLIE                            0123456-789-0003

SQL> 

A_CREDIT_CARD_NUMBER is a column where now exists open credit card numbers. A task is to crypt it and show their values according to 4 types of users:

1. ordinary oracle user cannot see any part of A_CREDIT_CARD_NUMBER column at all!
2. '***********' to ordinary user (see stars only instead of cc number)
3. '******xxxx' to privileged user (see last four numbers of the cc number)
4. 'xxxx****xxxx' to power user (see first and last four numbers of cc number)
5. 'xxxxxxxxxxxxx' to data admin user (see complete cc number)

The implementation

The implementation is divided in next four parts:

1. Implement security policy that will allow four different outputs
2. Create core functions
3. Convert existing data to crypted values
4. Ensure that new incoming data are crypted as well
5. Test results

Implement security policy that will allow four different outputs

As an owner of the table, create view and public synonym on that view in a way that A_CREDIT_CARD_NUMBER will be masked with incomming function

SQL> create or replace force view SEC_DEMO_VW as (
  2    SELECT id, 
  3           a_username, 
  4           cc_security.decrypt_data(A_CREDIT_CARD_NUMBER) CC 
  5      FROM SEC_DEMO_TBL
  6  ); 

Warning: View created with compilation errors.

SQL> show errors;
No errors.

SQL> create or replace public synonym SEC_DEMO for SEC_DEMO_VW;

Synonym created.

SQL> 

Force view is nice oracle feature that allow us to create views based on objects that doesn't exist in the moment of creation. Regardless view is invalid it will become valid on first usage. Beside that masking practice (different public name from real object name) is always a good security feature.

Because I allways like oracle based security solutions, this approach will involve 4 different roles which should be grant to chosen users:

• SEC_CC_0_NUMBERS
• SEC_CC_4_NUMBERS
• SEC_CC_8_NUMBERS
• SEC_CC_ALL_NUMBERS

SQL> CREATE ROLE SEC_CC_0_NUMBERS IDENTIFIED BY VALUES '1F5C90D6079FF8AA';

Role created.

SQL> BEGIN
  2    SYS.DBMS_RESOURCE_MANAGER.clear_pending_area();
  3    SYS.DBMS_RESOURCE_MANAGER.create_pending_area();
  4    SYS.DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP ('SEC_CC_0_NUMBERS', 'DEFAULT_CON
SUMER_GROUP', false);
  5    SYS.DBMS_RESOURCE_MANAGER.submit_pending_area();
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> CREATE ROLE SEC_CC_4_NUMBERS IDENTIFIED BY VALUES '3B078E03C4E9230E';

Role created.

SQL> BEGIN
  2    SYS.DBMS_RESOURCE_MANAGER.clear_pending_area();
  3    SYS.DBMS_RESOURCE_MANAGER.create_pending_area();
  4    SYS.DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP ('SEC_CC_4_NUMBERS', 'DEFAULT_CON
SUMER_GROUP', false);
  5    SYS.DBMS_RESOURCE_MANAGER.submit_pending_area();
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> CREATE ROLE SEC_CC_8_NUMBERS IDENTIFIED BY VALUES '3F5493B72C7BFA18';

Role created.

SQL> BEGIN
  2    SYS.DBMS_RESOURCE_MANAGER.clear_pending_area();
  3    SYS.DBMS_RESOURCE_MANAGER.create_pending_area();
  4    SYS.DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP ('SEC_CC_8_NUMBERS', 'DEFAULT_CON
SUMER_GROUP', false);
  5    SYS.DBMS_RESOURCE_MANAGER.submit_pending_area();
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> CREATE ROLE SEC_CC_ALL_NUMBERS IDENTIFIED BY VALUES 'DE1D7A90367C516C';

Role created.

SQL> BEGIN
  2    SYS.DBMS_RESOURCE_MANAGER.clear_pending_area();
  3    SYS.DBMS_RESOURCE_MANAGER.create_pending_area();
  4    SYS.DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP ('SEC_CC_ALL_NUMBERS', 'DEFAULT_C
ONSUMER_GROUP', false);
  5    SYS.DBMS_RESOURCE_MANAGER.submit_pending_area();
  6  END;
  7  /

PL/SQL procedure successfully completed.

It is very important to create rolas under privileged user who is trusted. In this case I have used owner of the table schema. And of course, secure rolas with password so altering that role can be done only from appropriate user.

For test we'll have 4 users:

1. A_USER_0 (granted "SEC_CC_0_NUMBERS" rola)
2. A_USER_4 (granted "SEC_CC_4_NUMBERS" rola)
3. A_USER_8 (granted "SEC_CC_8_NUMBERS" rola)
4. A_USER_ALL (granted "SEC_CC_ALL_NUMBERS" rola)

SQL> create user a_user_0 identified by a_user_0;

User created.

SQL> create user a_user_4 identified by a_user_4;

User created.

SQL> create user a_user_8 identified by a_user_8;

User created.

SQL> create user a_user_all identified by a_user_all;

User created.

SQL> grant create session to a_user_0;

Grant succeeded.

SQL> grant create session to a_user_4;

Grant succeeded.

SQL> grant create session to a_user_8;

Grant succeeded.

SQL> grant create session to a_user_all;

Grant succeeded.

SQL> 

As you can see user has no other privilege except create session. Then, grant appropriate roles to them:

SQL> grant sec_cc_0_numbers to a_user_0;

Grant succeeded.

SQL> grant sec_cc_4_numbers to a_user_4;

Grant succeeded.

SQL> grant sec_cc_8_numbers to a_user_8;

Grant succeeded.

SQL> grant sec_cc_all_numbers to a_user_all;

Grant succeeded.

SQL> 

Create core functions

When users and security part is done, next comes creation of core functions that we'll use. In this example I'll use one package with only two exposed functions encrypt_data and decrypt_data and this is all I need at all:

CREATE OR REPLACE PACKAGE cc_security
/*--------------------------------------------------------------------------------------------------------------------
 NAME    : cc_security
 PURPOSE : Utility which holds sensitive security data. MUST BE WRAPPED!

 Date    : 02.07.2009.
 Author  : Damir Vadas

 Remarks : owner of package must have grant execute on DBMS_CRYPTO package


 Changes (DD.MM.YYYY, Name, CR/TR#):
 -------------------------------------------------------------------------------------------------------------------- */
IS
    FUNCTION encrypt_data (
                           p_input_string      IN  VARCHAR2
                          ) RETURN VARCHAR2;

    FUNCTION decrypt_data (
                           p_input_string      IN  VARCHAR2
                           ) RETURN VARCHAR2;
END cc_security; 
/

Package body:

CREATE OR REPLACE PACKAGE BODY cc_security
IS
    -- This is most sensitive part because based on this seed all encrypting is done!
    -- Use some oracle random generator for it
    p_seed  VARCHAR2(24) := 'IFDXNJEQETNPTMSBICPSQXJK';
    p_seed_length PLS_INTEGER DEFAULT 24; 

    SQLERRMSG   VARCHAR2(255);
    SQLERRCDE   NUMBER;
    
    -- Defined Encryption Methods:
    -- SH1_ECB_ZERO: Secure Hash Algorithm, Electronic Codebook Cipher chaining, pad with zeroes 
    SH1_ECB_ZERO CONSTANT PLS_INTEGER := DBMS_CRYPTO.HASH_SH1 + DBMS_CRYPTO.CHAIN_OFB + DBMS_CRYPTO.PAD_ZERO;

/*
I allways prefer separate functions because of reusability code. So this function will return: 
   0  ordinary user - norights at all!
   4  privileged user with SEC_CC_4_NUMBERS role
   8  power user with SEC_CC_8_NUMBERS role
  16  admin user with SEC_CC_ALL_NUMBERS 
*/
function level_cc_user_rights (p_username IN DBA_USERS.USERNAME%TYPE
                               ) return INTEGER
IS
  l_retval INTEGER;
BEGIN
  SELECT decode (granted_role, 
                             'SEC_CC_4_NUMBERS',4,
                             'SEC_CC_8_NUMBERS',8,
                             'SEC_CC_ALL_NUMBERS',16,
                             0
                )
   INTO l_retval
   FROM dba_role_privs
   WHERE grantee=p_username;
  return l_retval;
EXCEPTION
  WHEN no_data_found then
    return -1;
  WHEN OTHERS THEN
    return -1;
END level_cc_user_rights;

FUNCTION encrypt_data (
                       p_input_string        IN  VARCHAR2                       
                      ) RETURN VARCHAR2
IS       
  converted_seed      RAW(128);
  converted_string    RAW(128);
  encrypted_string    RAW(128);
BEGIN
  -- Convert incoming string and supplied seed to RAW datatype using the 
  -- new UTLI18N package to convert the string to the AL32UTF8 character set
  converted_string := UTL_I18N.STRING_TO_RAW(p_input_string, 'AL32UTF8');
  IF length(nvl(p_seed,'1'))<>p_seed_length THEN
    RETURN NULL;
  END IF;

  converted_seed   := UTL_I18N.STRING_TO_RAW(p_seed, 'AL32UTF8');

  -- Encrypt the RAW value using the ENCRYPT function and the appropriate
  -- encryption type
  encrypted_string := 
                     DBMS_CRYPTO.ENCRYPT(
                         src => converted_string
                        ,typ => SH1_ECB_ZERO
                        ,key => converted_seed
                        ,iv =>  NULL);
  RETURN to_char(encrypted_string);
EXCEPTION
  WHEN OTHERS THEN
    SQLERRMSG := SQLERRM;
    SQLERRCDE := SQLCODE;
    RETURN NULL;
END encrypt_data;

FUNCTION decrypt_data (
                       p_input_string        IN  VARCHAR2
                      ) RETURN VARCHAR2
IS
  converted_string    VARCHAR2(128);
  decrypted_string    VARCHAR2(128);
  converted_seed      RAW(64);
  l_user              VARCHAR2(32);
  l_cc_level          INTEGER;
BEGIN
  -- Convert incoming string and supplied seed to RAW datatype using the 
  -- new UTLI18N package to convert the string to the AL32UTF8 character
  -- set
  IF length(nvl(p_seed,'1'))<>p_seed_length THEN
    RETURN NULL;
  END IF;

  converted_string := UTL_I18N.STRING_TO_RAW(p_input_string, 'AL32UTF8');

  converted_seed   := UTL_I18N.STRING_TO_RAW(p_seed, 'AL32UTF8');
        
  -- Encrypt the RAW value using the ENCRYPT function and the appropriate
  -- encryption type
  decrypted_string := 
      DBMS_CRYPTO.DECRYPT(
           src => p_input_string
          ,typ => SH1_ECB_ZERO
          ,key => converted_seed
          ,iv =>  NULL);
        
  -- Convert incoming string to RAW datatype, using the UTLI18N package 
  -- to convert the string to the AL32UTF8 character set
  converted_string := UTL_I18N.RAW_TO_CHAR(decrypted_string, 'AL32UTF8');

  -- Check if user is priviledged to decrypt data 
  SELECT sys_context('USERENV', 'SESSION_USER')
    INTO l_user 
    FROM dual;
  
  l_cc_level :=level_cc_user_rights(l_user);   
  IF l_cc_level=0 THEN
    -- ordinary user-shows only stars
    RETURN '***************';
  ELSIF l_cc_level=4 THEN
    -- privileged user-shows only 4 last digits...
    IF LENGTH(converted_string) <= 4 THEN
      RETURN NULL;
    ELSE
      RETURN '***********'|| SUBSTR(converted_string,length(converted_string)-3,4);
    END IF;
  ELSIF l_cc_level=8 THEN
    -- power user-shows only first and last 4 digits...
    IF LENGTH(converted_string) <= 8 THEN
      RETURN NULL;
    ELSE
      RETURN SUBSTR(converted_string,1,4) || '*******'|| SUBSTR(converted_string,length(converted_string)-3,4);
    END IF;  
  ELSIF l_cc_level=16 THEN
    -- admin user-show all!
    RETURN converted_string;
  ELSE
    -- This is for all "classic oracle users" they see null!
    RETURN null;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    SQLERRMSG := SQLERRM;
    SQLERRCDE := SQLCODE;
    RETURN NULL;
END decrypt_data;

END cc_security;
/

For this part of task I strongly suggest to use ready made oracle functions from DBMS_CRYPTO package because they are fully tested and provide full compatibility to any future version and any kind of database upgrade.
Keep in mind that this package body MUST BE WRAPPED because it holds the most sensitive data which should not be available even to DBA's!

As you remember we have created view which now can be completed with grant select for previously created users. So as an table owner perform:

SQL> grant select on SEC_DEMO_VW to a_user_0,a_user_4,a_user_8,a_user_all;

Grant succeeded.

SQL> 

With this grant only named users can even select data from table (there is no select on SEC_DEMO_TBL table at all), so first request is applied (ordinary oracle user has no no query right at all).

Convert existing data to crypted value

Existing data in column A_CREDIT_CARD_NUMBER has to be encrypted first. This is done on a very easy way through simple SQL:

SQL> update SEC_DEMO_TBL
  2  set A_CREDIT_CARD_NUMBER = cc_security.encrypt_data (A_CREDIT_CARD_NUMBER);

3 rows updated.

SQL> commit;

Commit complete.

SQL> select * from SEC_DEMO_TBL;

        ID A_USERNAME                       A_CREDIT_CARD_NUMBER
---------- -------------------------------- --------------------------------
         1 SMITH                            93B38FD3A694DA2A11988AAE1F0E148D
         2 PITT                             93B38FD3A694DA2AE7C11AA4994D5B04
         3 JOLIE                            93B38FD3A694DA2AEB1B968C370095E9

SQL> 

Keep in mind that in this step you loose original A_CREDIT_CARD_NUMBER values so if you haven't done a backup or proper test your code, you might get in trouble retrieving values back!

Ensure that new incoming data are crypted as well

For any new data (or editing existing ones) a trigger is the best way to ensure validity of future actions. This trigger is for upadate and insert as well.

CREATE OR REPLACE TRIGGER SEC_DEMO_TBL$TGBIU
 BEFORE INSERT OR UPDATE
 ON SEC_DEMO_TBL  FOR EACH ROW
BEGIN
  IF inserting THEN
    IF :new.A_CREDIT_CARD_NUMBER is not null THEN
      :new.A_CREDIT_CARD_NUMBER := cc_security.encrypt_data(:new.A_CREDIT_CARD_NUMBER);
    END IF;
  END IF;
  IF updating THEN
    IF :new.A_CREDIT_CARD_NUMBER is not null AND :new.A_CREDIT_CARD_NUMBER != :old.A_CREDIT_CARD_NUMBER THEN
      :new.A_CREDIT_CARD_NUMBER := cc_security.encrypt_data(:new.A_CREDIT_CARD_NUMBER);
    END IF;
  END IF;
END SEC_DEMO_TBL$TGBIU;
/

Do small test and insert data as table owner:

SQL> conn damirv/*****
Connected.

SQL> insert into SEC_DEMO_TBL values (4,'RAMBO', '0123456-789-0004');

1 row created.

SQL> commit;

Commit complete.

SQL> 

Insert seems to be proper!

Test results

Here is the result for all 4 users...Results are obvious!

SQL> conn a_user_0/a_user_0
Connected.
SQL> select * from SEC_DEMO;

        ID A_USERNAME                       CC
---------- -------------------------------- ----------------
         1 SMITH                            ***************
         2 PITT                             ***************
         3 JOLIE                            ***************
         4 RAMBO                            ***************

SQL>

SQL> conn a_user_4/a_user_4
Connected.
SQL> select * from SEC_DEMO;

        ID A_USERNAME                       CC
---------- -------------------------------- ---------------
         1 SMITH                            ***********0001
         2 PITT                             ***********0002
         3 JOLIE                            ***********0003
         4 RAMBO                            ***********0004

SQL> 

SQL> conn a_user_8/a_user_8
Connected.
SQL> select * from SEC_DEMO;

        ID A_USERNAME                       CC
---------- -------------------------------- ---------------
         1 SMITH                            0123*******0001
         2 PITT                             0123*******0002
         3 JOLIE                            0123*******0003
         4 RAMBO                            0123*******0004

SQL> 

SQL> conn a_user_all/a_user_all
Connected.
SQL> select * from SEC_DEMO;

        ID A_USERNAME                       CC
---------- -------------------------------- ----------------
         1 SMITH                            0123456-789-0001
         2 PITT                             0123456-789-0002
         3 JOLIE                            0123456-789-0003
         4 RAMBO                            0123456-789-0004

SQL> 

All looks OK!

The end

The beauty of this solution is that it is bullet proof because it is implemented through Oracle roles-admin tool for user's right administration. In a moment roles can be revoked from user or granted to some other user.

If you connect as owner of table, who own package, table and view but have no proper rola granted, you are unable to see data:

SQL> conn damirv/*****
Connected.
SQL>  select * from SEC_DEMO;

        ID A_USERNAME                       CC
---------- -------------------------------- ----------------
         1 SMITH
         2 PITT
         3 JOLIE
         4 RAMBO

SQL>

As you see even as an owner of procedure and table cannot see data in it's own schema and you are treated as ordinary user! While in the same time, trigger in owner's schema is updating/inserting data without problems! Of course, as owner you can see plain data (with crypted column) as shown at the beggining but if this is a problem, lock this user!

And for the end, the whole administration of this solution is centralized in one package and one trigger what allows easy ugrade path in the future.

Cheers!