Securing data using Oracle’s DBMS_OBFUSCATION_TOOLKIT

OK, so here’s the deal: you (and by “you” I mean “I”) work with an Oracle database and share access to it with others. You may barely have enough permissions in it to create objects; or you may own it right down to having root permissions on the host machine (lucky you). But here’s your dilemma: you (or whoever controls the security in the DB) find it convenient for some users to have “SELECT ANY TABLE” permissions, yet you want to make sure some of the data is only readable to a set of users that you can choose. For example: the support team you are on has access to several different accounts in several different systems and you want to keep all those passwords in one place. You like the idea of having the data in a place where you can all get to it, where it’s going to be highly available and backed up regularly, etc., but you don’t want it out there for everyone to see. You even want to integrate the password data with structured information about the systems the accounts are part of, so you want to put it in a relational database. Your solution: encrypt the data and share the key.

First of all, you’ll want to have the ability to use the key for the duration of a session and only have to set it once.  DBMS_SESSION gives you a means to do this:

exec dbms_session.set_identifier(‘MYSECRET’)

The two functions that follow will use this string (‘MYSECRET’ or any other string of your choosing) to encrypt and decrypt your data.

CREATE OR REPLACE FUNCTION “DECRYPT” (input_string IN CHAR) return CHAR AS
decrypted_string  VARCHAR2(2048);
Key8 VARCHAR2(255);
BEGIN
SELECT sys_context(‘USERENV’, ‘CLIENT_IDENTIFIER’)
into key8 FROM dual;
WHILE mod(length(key8),8) > 0 LOOP
key8:=key8 || ‘ ‘;
END LOOP;
dbms_obfuscation_toolkit.DESDecrypt(
input_string => input_string,
key_string => key8,
decrypted_string => decrypted_string );
return rtrim(decrypted_string);
END;
/

CREATE OR REPLACE FUNCTION “ENCRYPT” (input_string IN CHAR) return CHAR AS
encrypted_string VARCHAR2(2048);
input8 VARCHAR2(255);
Key8 VARCHAR2(255);
BEGIN
SELECT sys_context(‘USERENV’, ‘CLIENT_IDENTIFIER’)
into key8 FROM dual;
WHILE mod(length(key8),8) > 0 LOOP
key8:=key8 || ‘ ‘;
END LOOP;
input8:=rtrim(input_string);
WHILE mod(length(input8),8) > 0 LOOP
input8:=input8 || ‘ ‘;
END LOOP;
dbms_obfuscation_toolkit.DESEncrypt(
input_string => input8,
key_string => key8,
encrypted_string => encrypted_string );
return encrypted_string;
END;
/

Nothing tricky here. Plain text is passed in to the “ENCRYPT” function which gives you the encrypted string back. The “DECRYPT” function does the same in reverse, provided that the key set in your session is the same one used for encrypting your data. To be honest, it’s been a long time since I wrote this code, and I don’t remember exactly why the key is padded to a length divisible by eight inside the functions. I think the DESEncrypt function expects it that way. The good news is that the functions work so beautifully that I haven’t had to go back and troubleshoot them, ever.

Of course, you’ll need a table to store the data in:

CREATE TABLE MY$PASSWORDS
(USERNAME VARCHAR2(30),
DOMAIN VARCHAR2(50),
PASSWORD VARCHAR2(30));

And rather than call the DECRYPT function every time you SELECT from the table, you create a view (with a more convenient name) to do that for you:

CREATE OR REPLACE FORCE VIEW “MY_PASSWORDS”
AS  SELECT
username, domain, decrypt(password) as password
from MY$PASSWORDS;

And you’ll want that same transparency of encryption/decryption to work for insert/update operations:

CREATE OR REPLACE TRIGGER “PASSWORD_UPDATE”
instead of update on MY_PASSWORDS
for each row
begin
update MY$PASSWORDS set
domain=  :new.domain,
USERNAME=   :new.USERNAME,
password=  encrypt(:new.password)
where domain=:old.domain and USERNAME=:old.USERNAME;
end;
/

CREATE OR REPLACE TRIGGER “PASSWORD_INSERT”
instead of insert on MY_PASSWORDS for each row
begin
insert into MY$PASSWORDS
(domain,USERNAME,password)
values (:new.domain,:new.USERNAME,encrypt(:new.password));
end;
/

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *