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


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

  FUNCTION encrypt (p_text  IN  VARCHAR2) RETURNRAW;
     PROCEDURE update_user_password (
      p_username       IN   VARCHAR2,
      p_new_password   IN   VARCHAR2);
END user_security;
  g_key     RAW(32767)  :=UTL_RAW.cast_to_raw('12345678');
  g_pad_chr VARCHAR2(1) := '~';
  PROCEDURE padstring (p_text  IN OUT  VARCHAR2);
    l_text       VARCHAR2(32767) := p_text;
    l_encrypted  RAW(32767);
    DBMS_OBFUSCATION_TOOLKIT.desencrypt(input         => 
                                       key            => g_key,
                                       encrypted_data => l_encrypted);
    RETURN l_encrypted;
    l_decrypted  VARCHAR2(32767);
    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);
  PROCEDURE padstring (p_text  IN OUT  VARCHAR2)IS
    l_units  NUMBER;
    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;
   PROCEDURE update_user_password (
      p_username       IN   VARCHAR2,
      p_new_password   IN   VARCHAR2
      v_rowid   ROWID;
      SELECT     ROWID
            INTO v_rowid
            FROM encryption t
           WHERE t.uname = (p_username)
      UPDATE encryption
         SET encryption.password =encrypt(p_new_password)
       WHERE ROWID = v_rowid;
         raise_application_error (-20000,'Invalid username/password.');
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


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-2024  |  Page Processed at 2024-05-24 10:40:51 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search