CodeSnip: Easy Data Encryption Using the DBMS Obfuscation Toolkit from Oracle
page 1 of 1
Published: 13 Mar 2006
Unedited - Community Contributed
Abstract
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.
by Steven Swafford
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 25123/ 46

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.



User Comments

No comments posted yet.






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2017 ASPAlliance.com  |  Page Processed at 2017-03-30 8:43:01 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search