Thursday, February 16, 2012

Using DBMS_CRYPTO package to encrypt data in a table

We have a trigger, which fires whenever a DDL, GRANT and REVOKE statement is executed and stores some session information in a table. The trigger is under SYS schema:
CREATE OR REPLACE TRIGGER SYS.DDL_MONITOR
   AFTER CREATE OR DROP OR TRUNCATE OR ALTER OR GRANT OR REVOKE
   ON DATABASE
DECLARE
   V_TERMINAL   V$SESSION.TERMINAL%TYPE;
   V_OSUSER     V$SESSION.OSUSER%TYPE;
   V_MACHINE    V$SESSION.MACHINE%TYPE;
   V_IP         VARCHAR2 (20);
   V_MODULE     V$SESSION.MODULE%TYPE;
   V_DATABASE   V$PARAMETER.VALUE%TYPE;
   CRLF         VARCHAR2 (2) := CHR (13) || CHR (10);
BEGIN
   SELECT VALUE
     INTO V_DATABASE
     FROM V$PARAMETER
    WHERE NAME = 'db_name';

   V_OSUSER := SYS_CONTEXT ('USERENV', 'OS_USER');
   V_MACHINE := SYS_CONTEXT ('USERENV', 'TERMINAL');
   V_IP := SYS_CONTEXT ('USERENV', 'IP_ADDRESS');

   INSERT INTO HELPDESK.S_DDL_MONITOR (DATE_TIME,
                                       DDL_EVENT,
                                       OS_USER,
                                       MACHINE)
        VALUES (
                  SYSDATE,
                     'Username: ('
                  || ORA_LOGIN_USER
                  || ')   '
                  || 'Action: ('
                  || ORA_SYSEVENT
                  || ')   '
                  || 'Object: ('
                  || ORA_DICT_OBJ_OWNER
                  || '.'
                  || ORA_DICT_OBJ_NAME
                  || ')   '
                  || 'Type: ('
                  || ORA_DICT_OBJ_TYPE
                  || ')',
                  V_OSUSER,
                  V_IP || ' (' || V_MACHINE || ')');
EXCEPTION
   WHEN OTHERS
   THEN
      NULL;
END;
/

The table, in which information is stored is:
SQL> DESC HELPDESK.S_DDL_MONITOR

 Name                    Null? Type
 ----------------------- ----- -------------
 DATE_TIME                     DATE
 DDL_EVENT                     VARCHAR2(500)
 OS_USER                       VARCHAR2(30)
 MACHINE                       VARCHAR2(150)

Some users have the SELECT ANY TABLE system privilege and we cannot revoke it from them.
But we do not want them to read these data. So, we decided to encrypt all columns in this table, except DATE_TIME.
We will use DBMS_CRYPTO package to achieve this. All procedures we will create will be under SYSTEM schema, so, as SYSDBA, grant execute to the package:
SQL> GRANT EXECUTE ON SYS.DBMS_CRYPTO TO SYSTEM;

Grant succeeded.

First, we will need an encryption key. Obviously, this key should be stored and not to be given to anyone not trusted.
SQL> SELECT DBMS_CRYPTO.RANDOMBYTES(24) FROM DUAL;

DBMS_CRYPTO.RANDOMBYTES(24)
------------------------------------------------
FCF50295B0A28167FF88218A2EF8575102A20874305C8F2A

The following function converts a string to its encrypted value, using the key we just generated.
The encryption key should be stored in the function's code and not be passed as an input variable.
CREATE OR REPLACE FUNCTION SYSTEM.ENC_VARCHAR (V_VARCHAR IN VARCHAR2)
   RETURN RAW
IS
   V_ENC_VARCHAR   RAW(500);
   V_KEY           RAW(24) := 'FCF50295B0A28167FF88218A2EF8575102A20874305C8F2A';
   V_MOD           NUMBER
      :=   SYS.DBMS_CRYPTO.ENCRYPT_AES
         + SYS.DBMS_CRYPTO.CHAIN_CBC
         + SYS.DBMS_CRYPTO.PAD_PKCS5;
BEGIN
   V_ENC_VARCHAR :=
      SYS.DBMS_CRYPTO.ENCRYPT (UTL_RAW.CAST_TO_RAW (V_VARCHAR), V_MOD, V_KEY);
   RETURN V_ENC_VARCHAR;
END;
/

The next function takes the encrypted value and the key (in this case, it should be passed as an input variable) and returns the original string:
CREATE OR REPLACE FUNCTION SYSTEM.DEC_VARCHAR (V_RAW IN RAW, V_KEY IN RAW)
   RETURN VARCHAR2
IS
   V_RET       VARCHAR2 (500);
   V_DEC_RAW   RAW (500);
   V_MOD       NUMBER
      :=   DBMS_CRYPTO.ENCRYPT_AES
         + DBMS_CRYPTO.CHAIN_CBC
         + DBMS_CRYPTO.PAD_PKCS5;
BEGIN
   V_DEC_RAW := DBMS_CRYPTO.DECRYPT (V_RAW, V_MOD, V_KEY);
   V_RET := UTL_RAW.CAST_TO_VARCHAR2 (V_DEC_RAW);
   RETURN V_RET;
END;
/

We should wrap these functions, so their code and, most importantly, the encryption key will not be readable by anyone:
SQL> DECLARE
    BEGIN
DBMS_DDL.CREATE_WRAPPED('CREATE OR REPLACE FUNCTION SYSTEM.ENC_VARCHAR (V_VARCHAR IN VARCHAR2)
   RETURN RAW
IS
   V_ENC_VARCHAR   RAW(500);
   V_KEY           RAW(24) := ''FCF50295B0A28167FF88218A2EF8575102A20874305C8F2A'';
   V_MOD           NUMBER
      :=   SYS.DBMS_CRYPTO.ENCRYPT_AES
         + SYS.DBMS_CRYPTO.CHAIN_CBC
         + SYS.DBMS_CRYPTO.PAD_PKCS5;
BEGIN
   V_ENC_VARCHAR :=
      SYS.DBMS_CRYPTO.ENCRYPT (UTL_RAW.CAST_TO_RAW (V_VARCHAR), V_MOD, V_KEY);
   RETURN V_ENC_VARCHAR;
  END;');
END;
/
PL/SQL procedure successfully completed.

SQL> DECLARE
    BEGIN
DBMS_DDL.CREATE_WRAPPED('CREATE OR REPLACE FUNCTION SYSTEM.DEC_VARCHAR (V_RAW IN RAW, V_KEY IN RAW)
   RETURN VARCHAR2
IS
   V_RET       VARCHAR2 (500);
   V_DEC_RAW   RAW (500);
   V_MOD       NUMBER
      :=   DBMS_CRYPTO.ENCRYPT_AES
         + DBMS_CRYPTO.CHAIN_CBC
         + DBMS_CRYPTO.PAD_PKCS5;
BEGIN
   V_DEC_RAW := DBMS_CRYPTO.DECRYPT (V_RAW, V_MOD, V_KEY);
   V_RET := UTL_RAW.CAST_TO_VARCHAR2 (V_DEC_RAW);
   RETURN V_RET;
END;');
END;
/
PL/SQL procedure successfully completed.

Check, if we can see the code:
SQL> SELECT DBMS_METADATA.GET_DDL('FUNCTION','ENC_VARCHAR','SYSTEM') FROM DUAL;

DBMS_METADATA.GET_DDL('FUNCTION','ENC_VARCHAR','SYSTEM')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FUNCTION "SYSTEM"."ENC_VARCHAR" wrapped
a000000
25
abcd
ab

SQL> SELECT DBMS_METADATA.GET_DDL('FUNCTION','DEC_VARCHAR','SYSTEM') FROM DUAL;

DBMS_METADATA.GET_DDL('FUNCTION','DEC_VARCHAR','SYSTEM')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FUNCTION "SYSTEM"."DEC_VARCHAR" wrapped
a000000
25
abcd
ab

We check, if they work, by encrypting and decrypting string "This is a test".
SQL> SELECT SYSTEM.ENC_VARCHAR('This is a test') FROM DUAL;

SYSTEM.ENC_VARCHAR('THISISATEST')
----------------------------------
10009EB5AA82E2D10047A7B662720E29

SQL> SELECT SYSTEM.DEC_VARCHAR (
          '10009EB5AA82E2D10047A7B662720E29',
          'FCF50295B0A28167FF88218A2EF8575102A20874305C8F2A')
  FROM DUAL; 

SYSTEM.DEC_VARCHAR(
-------------------
This is a test

And if we use a wrong key:
SQL> SELECT SYSTEM.DEC_VARCHAR (
          '10009EB5AA82E2D10047A7B662720E29',
          'FCF50295B0A28167FF88218A2EF8575102A20874305C8F2B')
  FROM DUAL;
SELECT SYSTEM.DEC_VARCHAR (
       *
ERROR at line 1:
ORA-28817: PL/SQL function returned an error.
ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 67
ORA-06512: at "SYS.DBMS_CRYPTO", line 41
ORA-06512: at "SYSTEM.DEC_VARCHAR", line 11

Now, as SYSDBA we change the trigger to use our encryption function:
CREATE OR REPLACE TRIGGER SYS.DDL_MONITOR
   AFTER CREATE OR DROP OR TRUNCATE OR ALTER OR GRANT OR REVOKE
   ON DATABASE
DECLARE
   V_TERMINAL        V$SESSION.TERMINAL%TYPE;
   V_OSUSER          V$SESSION.OSUSER%TYPE;
   V_MACHINE         V$SESSION.MACHINE%TYPE;
   V_IP              VARCHAR2 (20);
   V_MODULE          V$SESSION.MODULE%TYPE;
   V_DATABASE        V$PARAMETER.VALUE%TYPE;
   CRLF              VARCHAR2 (2) := CHR (13) || CHR (10);
   V_DDL_EVENT       VARCHAR2 (500);
   V_DDL_EVENT_ENC   RAW (500);
   V_OSUSER_ENC      RAW (500);
   V_MACHINE2        VARCHAR2 (150);
   V_MACHINE_ENC     RAW (500);
BEGIN
   SELECT VALUE
     INTO V_DATABASE
     FROM V$PARAMETER
    WHERE NAME = 'db_name';

   V_OSUSER := SYS_CONTEXT ('USERENV', 'OS_USER');
   V_MACHINE := SYS_CONTEXT ('USERENV', 'TERMINAL');
   V_IP := SYS_CONTEXT ('USERENV', 'IP_ADDRESS');
   V_DDL_EVENT :=
         'Username: ('
      || ORA_LOGIN_USER
      || ')   '
      || 'Action: ('
      || ORA_SYSEVENT
      || ')   '
      || 'Object: ('
      || ORA_DICT_OBJ_OWNER
      || '.'
      || ORA_DICT_OBJ_NAME
      || ')   '
      || 'Type: ('
      || ORA_DICT_OBJ_TYPE
      || ')';
   V_MACHINE2 := V_IP || ' (' || V_MACHINE || ')';
   V_DDL_EVENT_ENC := SYSTEM.ENC_VARCHAR (V_DDL_EVENT);
   V_OSUSER_ENC := SYSTEM.ENC_VARCHAR (V_OSUSER);
   V_MACHINE_ENC := SYSTEM.ENC_VARCHAR (V_MACHINE2);

   INSERT INTO HELPDESK.S_DDL_MONITOR (DATE_TIME,
                                       DDL_EVENT,
                                       OS_USER,
                                       MACHINE)
        VALUES (SYSDATE,
                UTL_RAW.CAST_TO_VARCHAR2 (V_DDL_EVENT_ENC),
                UTL_RAW.CAST_TO_VARCHAR2 (V_OSUSER_ENC),
                UTL_RAW.CAST_TO_VARCHAR2 (V_MACHINE_ENC));
EXCEPTION
   WHEN OTHERS
   THEN
      NULL;
END;
/

We execute a few DDL statements and query the table:
SQL> SELECT DATE_TIME, DDL_EVENT, OS_USER, MACHINE FROM HELPDESK.S_DDL_MONITOR;
  
DATE_TIMEDDL_EVENTOS_USERMACHINE
16-FEB-12ξΎΉ‰G« ω―ζ œCXά ’fƒp­0CΡηΌE)b¤Ιΐ ¨ψ‰ψ’ ψζˆ,κΣ¤ ί¥L—£pμί¦eΦ&ΚY*<<ό©¶Y /]― C@ˆιCc;d«>8υRπ`WXΪ,¥ΔLg…£JmΜ B»βν¬žciβ k|![•±Ώβ 6·Ζτ
16-FEB-12ξΎΉ‰G« ω―z fs2¥nEc+p)Ι ψΈR8¨iCT+&P0Y Ω˜Ε gΙ t*¶tΰΗkΩγΎ bιƒΦ Φ‡kΖ _z™ώΈ˜;ςpŒ/ϋ #uKλΐΘd«>8υRπ`WXΪ,¥ΔLg…£JmΜ B»βν¬žciβ k|![•±Ώβ 6·Ζτ

All info in this table, except the DATE_TIME column, is encrypted and unreadable without the encryption key.
Let's use it to decrypt our data:
SQL> SELECT DATE_TIME,
          DEC_VARCHAR (UTL_RAW.CAST_TO_RAW (DDL_EVENT),
                    'FCF50295B0A28167FF88218A2EF8575102A20874305C8F2A')
          "DDL_EVENT",
       DEC_VARCHAR (UTL_RAW.CAST_TO_RAW (OS_USER),
                    'FCF50295B0A28167FF88218A2EF8575102A20874305C8F2A')
          "OS_USER",
       DEC_VARCHAR (UTL_RAW.CAST_TO_RAW (MACHINE),
                    'FCF50295B0A28167FF88218A2EF8575102A20874305C8F2A')
          "MACHINE"
  FROM HELPDESK.S_DDL_MONITOR;
  
DATE_TIMEDDL_EVENTOS_USERMACHINE
16-FEB-12Username: (SYSTEM)   Action: (TRUNCATE)   Object: (HELPDESK.S_DDL_MONITOR)   Type: (TABLE)etsekouras10.100.25.65 (ETSEKOUR)
16-FEB-12Username: (SYSTEM)   Action: (ALTER)   Object: (APPS.ZEBRAGETPRINTERFROMXML)   Type: (FUNCTION)etsekouras10.100.25.65 (ETSEKOUR)

If you want to query with a WHERE clause in an encrypted column, e.g. OS_USER, you can:
SELECT DATE_TIME,
       DEC_VARCHAR (UTL_RAW.CAST_TO_RAW (DDL_EVENT),
                    'FCF50295B0A28167FF88218A2EF8575102A20874305C8F2A')
          "DDL_EVENT",
       DEC_VARCHAR (UTL_RAW.CAST_TO_RAW (OS_USER),
                    'FCF50295B0A28167FF88218A2EF8575102A20874305C8F2A')
          "OS_USER",
       DEC_VARCHAR (UTL_RAW.CAST_TO_RAW (MACHINE),
                    'FCF50295B0A28167FF88218A2EF8575102A20874305C8F2A')
          "MACHINE"
  FROM HELPDESK.S_DDL_MONITOR
 WHERE UPPER (
          DEC_VARCHAR (UTL_RAW.CAST_TO_RAW (OS_USER),
                       'FCF50295B0A28167FF88218A2EF8575102A20874305C8F2A')) =
          'ETSEKOURAS';

No comments:

Post a Comment