Oracle Database 10g provides means to encrypt and decrypt your sensitive data using the built-in API called DBMS_CRYPTO. Using DBMS_CRYPTO API one can encrypt sensitive information like SSN, Credit Card Numbers, Debit Card Numbers, etc stored in the database as these are not supposed to be stored in plain text for security reasons.
Let’s see how DBMS_CRYPTO can be used to encrypt data and also consider its performance implications.
create table emp(empid number, ename varchar2(100), ssn varchar2(80));
Table created.
insert into emp values (1, 'SMITH', 123456701);
1 row created.
insert into emp values (2, 'ALLEN', 123456702);
1 row created.
insert into emp values (3, 'WARD', 123456703);
1 row created.
commit;
select * from emp;
EMPID ENAME SSN
---------- ------------------------------ -------------
1 SMITH 123456701
2 ALLEN 123456702
3 WARD 123456703
The SSN has been inserted into the table in plain text and can be read by anybody having SELECT privilege on EMP table.
Now, let’s create a package using DBMS_CRYPTO API to handle encryption and decryption.
Create or replace package cryptit is
Function encrypt_data( p_data IN VARCHAR2 ) Return RAW DETERMINISTIC;
Function decrypt_data( p_data IN RAW ) Return VARCHAR2 DETERMINISTIC;
End cryptit;
/
Package created.
Create or replace package body cryptit is
V_Key RAW(128) := UTL_RAW.cast_to_raw('testkey1'); -- Key
Function encrypt_data( p_data IN VARCHAR2 ) Return RAW DETERMINISTIC
IS
l_data RAW(2048) := utl_raw.cast_to_raw(p_data);
l_encrypted RAW(2048);
BEGIN
NULL;
l_encrypted := dbms_crypto.encrypt -- Algorithm
( src => l_data,
typ => DBMS_CRYPTO.DES_CBC_PKCS5,
key => V_KEY );
Return l_encrypted;
END encrypt_data;
Function decrypt_data( p_data IN RAW ) Return VARCHAR2 DETERMINISTIC
IS
l_decrypted RAW(2048);
BEGIN
l_decrypted := dbms_crypto.decrypt -- Algorithm
( src => p_data,
typ => DBMS_CRYPTO.DES_CBC_PKCS5,
key => V_KEY );
Return utl_raw.cast_to_varchar2(l_decrypted);
END decrypt_data;
End cryptit;
/
Package body created.
The package body consists of functions to encrypt and decrypt data along with a key. As these functions will always output the same value for a specific input, we can make them Deterministic functions. (A Deterministic Function always returns the same result any time they are called with a specific set of input values.)
Let's now make use of these functions to encrypt our sensitive data.
update emp set ssn = cryptit.encrypt_data(ssn);
3 rows updated.
select * from emp;
EMPID ENAME SSN
---------- ------------------------------ ----------------------------------
1 SMITH 5F3168C22E54060DE7D97B31F7E38BB6
2 ALLEN 5F3168C22E54060D0C2527FBBD3DCD6C
3 WARD 5F3168C22E54060D8166D3757932A112
When inserting new records we simply make use of "cryptit.encrypt_data" function in the INSERT statement.
insert into emp values( 4, 'NADVI', cryptit.encrypt_data(123456704));
1 row created.
commit;
#
Commit complete.
select * from emp;
EMPID ENAME SSN
---------- ------------------------------ ----------------------------------------
1 SMITH 5F3168C22E54060DE7D97B31F7E38BB6
2 ALLEN 5F3168C22E54060D0C2527FBBD3DCD6C
3 WARD 5F3168C22E54060D8166D3757932A112
4 NADVI 5F3168C22E54060D2CFF7E7A35B14187
select empid, ename, cryptit.decrypt_data(ssn) decrypted_ssn, ssn from emp;
EMPID ENAME DECRYPTED_SSN SSN
---------- ------------------------------ --------------- ----------------------------------------
1 SMITH 123456701 5F3168C22E54060DE7D97B31F7E38BB6
2 ALLEN 123456702 5F3168C22E54060D0C2527FBBD3DCD6C
3 WARD 123456703 5F3168C22E54060D8166D3757932A112
4 NADVI 123456704 5F3168C22E54060D2CFF7E7A35B14187
All is fine as far as encryption is concerned, but lets see what happens when SSN is used in the WHERE clause of queries.
We will create an index on SSN and run a query against it.
create index t_ssn on emp(ssn);
Index created.
select * from emp where ssn = cryptit.encrypt_data('123456701');
EMPID ENAME SSN
---------- ------------------------------ ----------------------------------------
1 SMITH 5F3168C22E54060DE7D97B31F7E38BB6
Execution Plan
----------------------------------------------------------
Plan hash value: 2894032564
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 107 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 107 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_SSN | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SSN"=RAWTOHEX("CRYPTIT"."ENCRYPT_DATA"('123456701')))
Note
-----
- dynamic sampling used for this statement
Well, the index "T_SSN" has been selected by the optimizer to be cost efficient. When a range of values is to be scanned, optimizer falls flat on its face as it has no idea and picks up FTS.
select * from emp where cryptit.decrypt_data(ssn) between '123456702' and '123456704';
EMPID ENAME SSN
---------- ------------------------------ ----------------------------------------
2 ALLEN 5F3168C22E54060D0C2527FBBD3DCD6C
3 WARD 5F3168C22E54060D8166D3757932A112
4 NADVI 5F3168C22E54060D2CFF7E7A35B14187
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 107 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 107 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CRYPTIT"."DECRYPT_DATA"(HEXTORAW("SSN"))>='123456702'
AND "CRYPTIT"."DECRYPT_DATA"(HEXTORAW("SSN"))<='123456704')
Note
-----
- dynamic sampling used for this statement
We can also create Function Based Indexes on encrypted columns such that data is accessed faster with a better execution plan. This is the reason I have marked both ENCRYPT_DATA and DECRYPT_DATA as DETERMINISTIC functions.
create index f_ssn_idx on emp(cryptit.decrypt_data(ssn));
Index created.
select * from emp where cryptit.decrypt_data(ssn) between '123456702' and '123456704';
EMPID ENAME SSN
---------- ------------------------------ ----------------------------------------
2 ALLEN 5F3168C22E54060D0C2527FBBD3DCD6C
3 WARD 5F3168C22E54060D8166D3757932A112
4 NADVI 5F3168C22E54060D2CFF7E7A35B14187
Execution Plan
----------------------------------------------------------
Plan hash value: 9274740
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 107 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 107 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | F_SSN_IDX | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CRYPTIT"."DECRYPT_DATA"(HEXTORAW("SSN"))>='123456702' AND
"CRYPTIT"."DECRYPT_DATA"(HEXTORAW("SSN"))<='123456704')
Note
-----
- dynamic sampling used for this statement
The optimizer has rightly picked up the Function based index to access requested data quickly.
DBMS_CRYPTO is a great way to encrypt sensitive data and we can also create indexes on the encrypted columns to speedup our queries. Lastly, remember to wrap the CRYPTIT package body so that the key is not exposed.
DBMS_CRYPTO can also be used to encrypted data recursively. Let us try to encrypt the already encrypted SSN from the above example.
select * from emp;
EMPID ENAME SSN
---------- -------- --------------------------------------------------------------------------------
1 SMITH 5F3168C22E54060DE7D97B31F7E38BB6
2 ALLEN 5F3168C22E54060D0C2527FBBD3DCD6C
3 WARD 5F3168C22E54060D8166D3757932A112
4 NADVI 5F3168C22E54060D2CFF7E7A35B14187
update emp set ssn = cryptit.encrypt_data(ssn);
4 rows updated.
select * from emp;
EMPID ENAME SSN
---------- -------- --------------------------------------------------------------------------------
1 SMITH 455E357D80B1C81F91D52088DA0FE03A0C133D129CFC3879EE7A8DE993FD459DB5C9E1EBEF37062C
2 ALLEN 455E357D80B1C81F91D52088DA0FE03A98FC410A71CE64D57935691692D433B770C5F8AF07C9113F
3 WARD 455E357D80B1C81F91D52088DA0FE03A9D32D14FE64F41DD66BE7FEF4C31384F032C16826EAA4830
4 NADVI 455E357D80B1C81F91D52088DA0FE03A04D5C0AD79E689EABD46F8EC335308E32D6F7E94167F6EDF
commit;
Commit complete.
select empid, ename, cryptit.decrypt_data(cryptit.decrypt_data(ssn)) decrypted_ssn, ssn from emp;
EMPID ENAME DECRYPTED_SSN SSN
---------- -------- --------------- --------------------------------------------------------------------------------
1 SMITH 123456701 455E357D80B1C81F91D52088DA0FE03A0C133D129CFC3879EE7A8DE993FD459DB5C9E1EBEF37062C
2 ALLEN 123456702 455E357D80B1C81F91D52088DA0FE03A98FC410A71CE64D57935691692D433B770C5F8AF07C9113F
3 WARD 123456703 455E357D80B1C81F91D52088DA0FE03A9D32D14FE64F41DD66BE7FEF4C31384F032C16826EAA4830
4 NADVI 123456704 455E357D80B1C81F91D52088DA0FE03A04D5C0AD79E689EABD46F8EC335308E32D6F7E94167F6EDF
