MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘SQL Development’ tag

Encrypting a Column

with 7 comments

A few years ago, I gave a session on Oracle’s Data Vault. An attendee from that session and I happened to be seated at the same table for lunch last Thursday when I presented at Utah Oracle User Group Fall Symposium. He asked if I’d ever found a way to encrypt a column from the prying eyes of the SYS user. I said yes, and he asked how. It was a bit more than could be explained at lunch and promised to put it on the blog. (Disclaimer: It is possible to unwrap wrapped code but the mechanics change with each release and they require access to the SYS schema and substantial Application DBA knowledge; however, there are scripts published on the Internet to unwrap the code. There are also other vulnerabilities in the example, but I’ll pass on exposing them. After all this was written to illustrate an approach.)

It’s important to note you can now hide columns in Oracle Database 12c, but they’re still visible to the DBA-level staff. That’s why I’ll publish a new article on re-writing this encrypted object as a Java library, and disclose how to hide password exchanges from the SGA area.

This demonstrates how you can encapsulate a column from even the SYS user. I also put it into Appendix D, PL/SQL Built-in Packages and Types to illustrate the DBMS_CRYPTO package. It uses a User Defined Type (UDT) and assumes you have a working knowledge of object types in Oracle 10g forward. If not, you can find them in:

  1. Chapter 11 of my Oracle Database 12c PL/SQL Programming book.
  2. Chapter 14 of my Oracle Database 11g PL/SQL Programming book.

It also assumes basic knowledge of Oracle’s encryption technology, some of which is explained in the new Oracle Database 12c PL/SQL Programming book.

For reference, a good DBA would simply find this clear text password in the SGA. A more secure approach might be hiding the encryption keyword in a one column and table or embedded in some string within a common lookup table column value as a position specific substring. Alas, I don’t have time to write something so elaborate.

The steps are:

  1. You must create a user defined type (UDT), which sets the up a single salary column.
1
2
3
4
5
6
7
8
9
10

CREATE OR REPLACE TYPE masked IS OBJECT
( salary  RAW(1000)
, CONSTRUCTOR FUNCTION masked RETURN SELF AS RESULT
, CONSTRUCTOR FUNCTION masked ( salary  NUMBER ) RETURN SELF AS RESULT
, MEMBER FUNCTION get_raw_salary RETURN RAW
, MEMBER FUNCTION get_salary ( KEY VARCHAR2 ) RETURN NUMBER
, MEMBER PROCEDURE to_string
, ORDER MEMBER FUNCTION equals ( object MASKED ) RETURN NUMBER )
INSTANTIABLE FINAL;
/
  1. As the SYS user, you must grant EXECUTE privilege on the DBMS_CRYPTO package to the target SCHEMA user.
1

GRANT EXECUTE ON dbms_crypto TO schema_name;
  1. You implement the MASKED UDT, as follows below (source unencrypted, don’t worry I show you how to encrypt [wrap] it in a moment).
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105

CREATE OR REPLACE TYPE BODY masked IS
 
  CONSTRUCTOR FUNCTION masked RETURN SELF AS RESULT IS
 
    /* Create a placeholder for a zero salary, like an empty_clob() call. */
    zero MASKED := masked(0);
 
  BEGIN
 
    /* Assign an encrypted zero salary to the instance. */
    self := zero;  
    RETURN;
 
  END masked;
  CONSTRUCTOR FUNCTION masked ( salary NUMBER ) RETURN SELF AS RESULT IS
 
    /* Declare local variables for encryption, object types hold instance
       objects and object body variables are method specific. Placing them
       inside the methods while tedious prevents their disclosure. */
    lv_key_string      VARCHAR2(4000)  := 'Encrypt Me!'; 
    lv_key             RAW(1000);
    lv_raw             RAW(1000);
    lv_encrypted_data  RAW(1000);
 
  BEGIN
    /* Dynamic assignment. */
    lv_raw := UTL_RAW.cast_to_raw(NVL(salary,0));
 
    /* Convert to a RAW 64-character key. */
    lv_key := UTL_RAW.cast_to_raw(lv_key_string);
    lv_key := RPAD(lv_key,64,'0');   
 
    /* Encrypt the salary before assigning it to the object type attribute */
    lv_encrypted_data := DBMS_CRYPTO.ENCRYPT(lv_raw, dbms_crypto.ENCRYPT_AES256 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5,lv_key);
    self.salary := lv_encrypted_data;
 
    RETURN;
  END masked;
 
  MEMBER FUNCTION get_raw_salary RETURN RAW IS 
  BEGIN
    RETURN self.salary;
  END get_raw_salary;    
 
  MEMBER FUNCTION get_salary( key VARCHAR2 ) RETURN NUMBER IS 
 
    /* Declare local variables for encryption, object types hold instance
       objects and object body variables are method specific. Placing them
       inside the methods while tedious prevents their disclosure. */
    lv_key_string      VARCHAR2(4000)  := 'Encrypt Me!'; 
    lv_decrypted_data  RAW(4000);
    lv_key             RAW(1000);
    lv_return_value    NUMBER;
 
  BEGIN
 
    /* Verify key value matches local value before decrypting, substitute
       a zero value when the key doesn't match. */
    IF key = lv_key_string THEN
      lv_key := UTL_RAW.cast_to_raw(lv_key_string);
      lv_key := RPAD(lv_key,64,'0');   
      lv_decrypted_data := DBMS_CRYPTO.DECRYPT(self.salary, dbms_crypto.ENCRYPT_AES256 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5,lv_key);
      lv_return_value := TO_NUMBER(TO_CHAR(UTL_RAW.cast_to_number(lv_decrypted_data),'9999990.00'));
    ELSE
      lv_return_value := 0;    
    END IF;
 
    RETURN lv_return_value;
  END get_salary;
 
  ORDER MEMBER FUNCTION equals ( object MASKED ) RETURN NUMBER IS
 
    /* Declare local variables for encryption, object types hold instance
       objects and object body variables are method specific. Placing them
       inside the methods while tedious prevents their disclosure. */
    lv_key_string      VARCHAR2(4000)  := 'Encrypt Me!'; 
    lv_decrypted_self  RAW(4000);
    lv_decrypted_peer  RAW(4000);
    lv_key             RAW(1000);
 
  BEGIN
 
    /* Decrypt the current and peer object attribute values before
       comparing their values. */  
    lv_key := UTL_RAW.cast_to_raw(lv_key_string);
    lv_key := RPAD(lv_key,64,'0');   
    lv_decrypted_self := DBMS_CRYPTO.DECRYPT(self.salary, dbms_crypto.ENCRYPT_AES256 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5, lv_key);
    lv_decrypted_peer := DBMS_CRYPTO.DECRYPT(object.get_raw_salary(), dbms_crypto.ENCRYPT_AES256 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5, lv_key);
 
    /* Sort order key. */
    IF lv_decrypted_self < lv_decrypted_peer THEN
      RETURN 1;
    ELSE
      RETURN 0;
    END IF;
 
  END equals;
 
  MEMBER PROCEDURE to_string IS
  BEGIN
    /* Provide a to_string method for good practice. */
    DBMS_OUTPUT.put_line('Encrypted value');
  END to_string;
END;
/
  1. You implement the MASKED UDT encrypted by using the DBMS_DDL package, as follows below. This ensures that others can’t read the source code by querying the ALL_, DBA_, or USER_SOURCE views. You should note that I’ve removed comments and unnecessary spaces.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

BEGIN
  DBMS_DDL.create_wrapped(
     'CREATE OR REPLACE TYPE BODY masked IS '
  || 'CONSTRUCTOR FUNCTION masked RETURN SELF AS RESULT IS '
  || 'zero MASKED := masked(0); '
  || 'BEGIN '
  || 'self := zero; '
  || 'RETURN; '
  || 'END masked; '
  || 'CONSTRUCTOR FUNCTION masked ( salary NUMBER ) RETURN SELF AS RESULT IS '
  || 'lv_key_string      VARCHAR2(4000)  := ''Encrypt Me!''; '
  || 'lv_key             RAW(1000); '
  || 'lv_raw             RAW(1000) := RPAD(utl_raw.cast_from_number(salary),32,''0''); '
  || 'lv_encrypted_data  RAW (1000); '
  || 'BEGIN '
  || 'lv_key := utl_raw.cast_to_raw(lv_key_string); '
  || 'lv_key := RPAD(lv_key,64,''0''); '
  || 'lv_encrypted_data := DBMS_CRYPTO.ENCRYPT(lv_raw, dbms_crypto.ENCRYPT_AES256 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5,lv_key); '
  || 'self.salary := lv_encrypted_data; '
  || 'RETURN; '
  || 'END masked; '
  || 'MEMBER FUNCTION get_raw_salary RETURN RAW IS '
  || 'BEGIN '
  || 'RETURN self.salary; '
  || 'END get_raw_salary; '
  || 'MEMBER FUNCTION get_salary( key VARCHAR2 ) RETURN NUMBER IS '
  || 'lv_key_string      VARCHAR2(4000)  := ''Encrypt Me!''; '
  || 'lv_decrypted_data  RAW(4000); '
  || 'lv_key             RAW(1000); '
  || 'lv_return_value    NUMBER; '
  || 'BEGIN '
  || 'IF key = lv_key_string THEN '
  || 'lv_key := utl_raw.cast_to_raw(lv_key_string); '
  || 'lv_key := RPAD(lv_key,64,''0''); '
  || 'lv_decrypted_data := DBMS_CRYPTO.DECRYPT(self.salary, dbms_crypto.ENCRYPT_AES256 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5,lv_key); '
  || 'lv_return_value := TO_NUMBER(TO_CHAR(utl_raw.cast_to_number(lv_decrypted_data),''9999990.00'')); '
  || 'ELSE '
  || 'lv_return_value := 0; '
  || 'END IF; '
  || 'RETURN lv_return_value; '
  || 'END get_salary; '
  || 'ORDER MEMBER FUNCTION equals ( object MASKED ) RETURN NUMBER IS '
  || 'lv_key_string      VARCHAR2(4000)  := ''Encrypt Me!''; '
  || 'lv_decrypted_self  RAW(4000); '
  || 'lv_decrypted_peer  RAW(4000); '
  || 'lv_key             RAW(1000); '
  || 'BEGIN '
  || 'lv_key := utl_raw.cast_to_raw(lv_key_string);'
  || 'lv_key := RPAD(lv_key,64,''0''); '
  || 'lv_decrypted_self := DBMS_CRYPTO.DECRYPT(self.salary, dbms_crypto.ENCRYPT_AES256 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5, lv_key); '
  || 'lv_decrypted_peer := DBMS_CRYPTO.DECRYPT(object.get_raw_salary(), dbms_crypto.ENCRYPT_AES256 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5, lv_key); '
  || 'IF lv_decrypted_self < lv_decrypted_peer THEN '
  || 'RETURN 1; '
  || 'ELSE '
  || 'RETURN 0; '
  || 'END IF; '
  || 'END equals; '
  || 'MEMBER PROCEDURE to_string IS '
  || 'BEGIN '
  || 'dbms_output.put_line(''Encrypted value''); '
  || 'END to_string; '
  || 'END; ');
END;
/

You can read more about wrapping PL/SQL in Appendix F of Oracle Database 12c PL/SQL Programming.

  1. You can test a single instance with this anonymous PL/SQL block.
1
2
3
4
5
6
7
8
9

DECLARE
  o MASKED := masked(82000.12);
BEGIN
  DBMS_OUTPUT.put('Override:  ');
  o.to_string();
  DBMS_OUTPUT.put_line('Decrypted: '||o.get_salary('Encrypt Me!'));
  DBMS_OUTPUT.put_line('Bad Key:   '||o.get_salary('Incorrect'));
END;
/
It prints the following: 

Override:  Encrypted value
Decrypted: 82000.12
Bad Key:   0
  1. You can test a series of instances by making them persistent objects, or columns in a table, and then query values from them. It also lets you you test the sorting feature provided in the UDT.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

CREATE TABLE sort_demo (salary MASKED);
 
INSERT INTO sort_demo VALUES (masked(82000.24));
INSERT INTO sort_demo VALUES (masked(61000.12));
INSERT INTO sort_demo VALUES (masked(93000.36));
 
SELECT salary AS "Encrypted" FROM sort_demo;
 
COLUMN unordered FORMAT 9,999,990.00 HEADING "Unordered|List"
 
SELECT TREAT(salary AS MASKED).get_salary('Encrypt Me!') AS Unordered
FROM   sort_demo;
 
COLUMN ordered FORMAT 9,999,990.00 HEADING "Ordered|List"
 
SELECT TREAT(salary AS MASKED).get_salary('Encrypt Me!') AS Ordered
FROM   sort_demo
ORDER BY 1;
It prints the following: 

Encrypted(SALARY)
--------------------------------------------------------------------------
MASKED('1798C04F8342C53A826144568075CBDB145D0C9BD226B410C8C7167B76382D86')
MASKED('82F783F2E117AA60955B0A2E73545506936D6F8FFBEC5D9E0D8E70B82D4B694D')
MASKED('1551F350AAEB30ADFC1527F25CAA935732243858AF1C5D724A78B997A4394EAD')
 
    Unordered
         List
-------------
    82,000.24
    61,000.12
    93,000.36
 
 
      Ordered
         List
-------------
    61,000.12
    82,000.24
    93,000.36

If you want to retest this, make sure you drop the SORT_DEMO table first. Hope this helps any interested in an elaborate solution.

Written by maclochlainn

September 8th, 2012 at 5:58 pm

Posted in Objects,Oracle,Oracle 11g,Oracle 12c,pl/sql

Tagged with , , , ,