Radical Development

Technical Without the Technicalities

CodeSnip: Easy Data Encryption Using the DBMS Obfuscation Toolkit from Oracle

If you use Oracle as your datastore and need to encrypt and decrypt sensitive data, then Steven has a stored procedure code snippet that you can use in no time.

Introduction

Encryption and decryption is fairly common in many web applications today. If you are using an Oracle Database as your data store, then you can easily implement encryption and decryption at the database level using the DBMS Obfuscation Toolkit provided by Oracle.

Establish Table and Package

Create the table. Simply cut and paste the PL/SQL as shown in Listing 1.

create table encryption
(
ID number,
uname varchar2(25),
password varchar2(32)
);

Listing 1: Create Encryption Table

Next you will need to create the package and package body. Simply cut and paste the PL/SQL in Listing 2.

CREATE OR REPLACE PACKAGE user_security AS

FUNCTION encrypt (p_text IN VARCHAR2) RETURNRAW;
FUNCTION decrypt (p_raw IN RAW) RETURNVARCHAR2;

PROCEDURE update_user_password (
p_username IN VARCHAR2,
p_new_password IN VARCHAR2);

END user_security;
/
CREATE OR REPLACE PACKAGE BODY user_security AS
g_key RAW(32767) :=UTL_RAW.cast_to_raw('12345678');
g_pad_chr VARCHAR2(1) := '~';

PROCEDURE padstring (p_text IN OUT VARCHAR2);

FUNCTION encrypt (p_text IN VARCHAR2) RETURNRAW IS
l_text VARCHAR2(32767) := p_text;
l_encrypted RAW(32767);
BEGIN
padstring(l_text);
DBMS_OBFUSCATION_TOOLKIT.desencrypt(input =>
TL_RAW.cast_to_raw(l_text),
key => g_key,
encrypted_data => l_encrypted);
RETURN l_encrypted;
END;

FUNCTION decrypt (p_raw IN RAW) RETURNVARCHAR2 IS
l_decrypted VARCHAR2(32767);
BEGIN
DBMS_OBFUSCATION_TOOLKIT.desdecrypt(input=> p_raw,
key => g_key,
decrypted_data => l_decrypted);

RETURNRTrim(UTL_RAW.cast_to_varchar2(l_decrypted), g_pad_chr);
END;

PROCEDURE padstring (p_text IN OUT VARCHAR2)IS
l_units NUMBER;
BEGIN
IF LENGTH(p_text) MOD 8 > 0 THEN
l_units := TRUNC(LENGTH(p_text)/8) + 1;
p_text := RPAD(p_text, l_units * 8,g_pad_chr);
END IF;
END;

PROCEDURE update_user_password (
p_username IN VARCHAR2,
p_new_password IN VARCHAR2
)
AS
v_rowid ROWID;
BEGIN
SELECT ROWID
INTO v_rowid
FROM encryption t
WHERE t.uname = (p_username)
FOR UPDATE;

UPDATE encryption
SET encryption.password =encrypt(p_new_password)
WHERE ROWID = v_rowid;

EXCEPTION
WHEN NO_DATA_FOUND
THEN
raise_application_error (-20000,'Invalid username/password.');
END;

END user_security;
/

Listing 2: Create Package and Package Body

Now all we need to do is test the code.

fig1_13052006_3

Figure 1: Before Encryption

fig2_13052006_3

Figure 2: Testing the Procedure

fig3_13052006_3

Figure 3: After Encryption

This code snippet demonstrates the ease of data encryption and decryption using the DBMS Obfuscation Toolkit.