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.
Listing 1: Create Encryption Table
create table encryption
(
ID number,
uname varchar2(25),
password varchar2(32)
);
Next you will need to create the package and package body.
Simply cut and paste the PL/SQL in Listing 2.
Listing 2: Create Package and Package Body
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;
/Now all we need to do is test the code.
Figure 1: Before Encryption
Figure 2: Testing the Procedure
Figure 3: After Encryption
Summary
This code snippet demonstrates the ease of data encryption
and decryption using the DBMS Obfuscation Toolkit.
