Obfuscate sensitive data in 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 
is
	v_clear_varchar varchar2(2000);
	v_enc_raw		raw(2000);
	v_enc_varchar	varchar2(2000);
begin
	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));
	else
		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;
end;

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 
is
	v_tmp_raw    	 raw(2048);
	v_clear_varchar	varchar2(4000);
begin
	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;
end;

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)
values(
	'scott', 
	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.