Search the Site


bottom corner

Obfuscate sensitive data in Oracle

This article provides simple functions to obfuscate sensitive data stored in an Oracle database table.

This page is filed under keyword(s): oracle.

If business needs requires you to store sensitive data such as social security numbers, bank routing/account numbers, and so on, you should ensure the data is stored in a safe way. Below are a set of two simple functions to encrypt/obfuscate such data to get your started.

To encrypt a varchar2 string with a specific encryption phrase (or "key"):

create or replace function your_schema.encrypt(clear_varchar_ varchar2, key_ varchar2) return varchar2 
	v_clear_varchar varchar2(2000);
	v_enc_raw		raw(2000);
	v_enc_varchar	varchar2(2000);
	if (mod(length(clear_varchar_), 8) != 0) then
		v_clear_varchar := rpad(clear_varchar_, length(clear_varchar_) + 8 - mod(length(clear_varchar_), 8), chr(0));
		v_clear_varchar := clear_varchar_;
	end if;
	dbms_obfuscation_toolkit.desencrypt(input => utl_raw.cast_to_raw(rpad(v_clear_varchar, 64, ' ')),
		key => utl_raw.cast_to_raw(key_), 
		encrypted_data => v_enc_raw);
		v_enc_varchar := utl_raw.cast_to_varchar2(v_enc_raw);
	return v_enc_varchar;

The following function decrypts; you must use the same key that was used to encrypt it.

create or replace function your_schema.decrypt(enc_varchar_ varchar2, key_ varchar2) return varchar2 
	v_tmp_raw    	 raw(2048);
	v_clear_varchar	varchar2(4000);
	dbms_obfuscation_toolkit.desdecrypt(input => utl_raw.cast_to_raw(enc_varchar_),
		key =>  utl_raw.cast_to_raw(key_), 
		decrypted_data => v_tmp_raw);
	v_clear_varchar := replace(trim(utl_raw.cast_to_varchar2(v_tmp_raw)),chr(0),'');
	return v_clear_varchar;

Here is an example usage: The following SQL statement inserts an obfuscated password into a table that stores user data.

insert into your_schema.user_accounts (username, password)
	your_schema.encrypt('tiger', '_seCret!keY:3')

And below is how you would retrieve and decrypt the password.

select your_schema.decrypt(password, '_seCret!keY:3') from your_schema.user_accounts where username='scott';

Security is a serious matter and it warrants extensive research. This article merely offers the awareness that sensitive data should not be stored in clear text, and hopefully provides a good starting point.

Did you find this page useful? Please consider browsing other articles or subscribing to the RSS feed to keep up with latest.

This page is filed under keyword(s): oracle.
Author: C. Peter Chen
Last updated: 12 Mar 2015

bottom corner