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,

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.

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.

Related articles

• Encrypt/Scramble sensitive data in Oracle Apps (oraclemaniac.com)
• Oracle Advanced Queue (AQ) demo (oraclemaniac.com)