You're reading...

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)


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

   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';
   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

Now let us check the data in the table,

Encrypted data



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.

   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';
   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);

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

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.


No comments yet.

Leave a Reply

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

You are commenting using your 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


Site Stats

  • 729,893 views since Feb 2012


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

Join 215 other followers


Free counters!

Get every new post delivered to your Inbox.

Join 215 other followers

%d bloggers like this: