You're reading...
PL/SQL

Encrypt data in Oracle tables

We were creating a custom application and we needed to create users for this application. For ensuring that the user passwords were secured we used Oracle encryption to keep the passwords encrypted. This process will ensure that anyone with database access will be unable to recognize the password on querying the table.

  1. Create the user table to store the user data
CREATE TABLE user_data (user_id NUMBER,
user_name VARCHAR2(40),
encrypted_password VARCHAR(100)
);

Encryption

Now create a user and encrypt her password. The password is set to ‘P@ssw0rd’ for the user name ‘TEST_USER’.

DECLARE
   v_user_id            user_data.user_id%TYPE              := 1;
   v_password           VARCHAR2 (50)                       := 'P@ssw0rd';
   v_user_name          user_data.user_name%TYPE            := 'TEST_USER';
   v_encrypted_string   user_data.encrypted_password%TYPE;
  -- Encryption key has to be multiples of 8 char
   v_key_string         VARCHAR2 (8)                        := '12345678';
BEGIN
   DBMS_OBFUSCATION_TOOLKIT.desencrypt (input_string                  => v_password,
                                        key_string                    => v_key_string,
                                        encrypted_string              => v_encrypted_string
                                       );

   INSERT INTO user_data
               (user_id, user_name, encrypted_password
               )
        VALUES (v_user_id, v_user_name, v_encrypted_string
               );
END;

Now let us check the data in the table,

Encrypted data

 

Decryption

When this user tries to login to the application she will pass her user name and password into the application. Then the password stored in the database has to be decrypted and compared with the user entered password to determine whether the correct password was entered. The decryption logic is given below.

DECLARE
   v_user_id            user_data.user_id%TYPE              := 1;
   v_password           user_data.encrypted_password%TYPE;
   v_user_name          user_data.user_name%TYPE;
   v_decrypted_string   user_data.encrypted_password%TYPE;
  -- Encryption key has to be multiples of 8 char.
  -- The key has to be be the same as encryption
   v_key_string         VARCHAR2 (8)                        := '12345678';
BEGIN
   SELECT encrypted_password
     INTO v_password
     FROM user_data
    WHERE user_id = v_user_id;

   DBMS_OBFUSCATION_TOOLKIT.desdecrypt (input_string                  => v_password,
                                        key_string                    => v_key_string,
                                        decrypted_string              => v_decrypted_string
                                       );
   DBMS_OUTPUT.put_line ('The decrypted password is ' || v_decrypted_string);
END;

The output is shown on screen.

Decrypted password

This method is illustrated for password encryption and decryption. This can be used to encrypt any kind of sensitive data but do keep in mind that the decryption routine has to be used to view the data.

About these ads

About Abhijit Ray

I love sleeping, watching Hollywood blockbusters, my Wii, road trips and watching my 4 year old son grow up. In between I try to squeeze in some time to go to work.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Traffic

Site Stats

  • 500,547 views since Feb 2012

Archives

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 175 other followers

Flags

Free counters!
Follow

Get every new post delivered to your Inbox.

Join 175 other followers

%d bloggers like this: