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.
- 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’.
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,
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.