How to Encrypt Password in Oracle?

Here I am giving an example to encrypt the password in Oracle using the DBMS_CRYPTO package.

To demonstrate this, I have created the following table to store user ID (USER_ID) varchar2 data type and to store the encrypted password (ENC_PSW) raw data type.

Create Table

SET DEFINE OFF;
CREATE TABLE ENC_DATA
(
  USER_ID  VARCHAR2(20 BYTE),
  ENC_PSW  RAW(2000)
)
/

ALTER TABLE ENC_DATA ADD (
  CONSTRAINT ENC_DATA_PK
 PRIMARY KEY
 (USER_ID))
/

An Example to Encrypt Password in Oracle

Now through the following PL/SQL program, we will store the password mypassword123 for the user ID SCOTT. Key is the vital part for encryption because to decrypt it you need to use the same key. You can use any alpha-numeric key of 16 character length. For more information on DBMS_CRYPTO package check the Oracle Documentation.

SET SERVEROUTPUT ON;

DECLARE
   l_user_id    enc_data.USER_ID%TYPE := 'SCOTT';
   l_user_psw   VARCHAR2 (2000) := 'mypassword123';

   l_key        VARCHAR2 (2000) := '1234567890999999';
   l_mod NUMBER
         :=   DBMS_CRYPTO.ENCRYPT_AES128
            + DBMS_CRYPTO.CHAIN_CBC
            + DBMS_CRYPTO.PAD_PKCS5;
   l_enc        RAW (2000);
BEGIN
   l_user_psw :=
      DBMS_CRYPTO.encrypt (UTL_I18N.string_to_raw (l_user_psw, 'AL32UTF8'),
                           l_mod,
                           UTL_I18N.string_to_raw (l_key, 'AL32UTF8'));
   
      DBMS_OUTPUT.put_line ('Encrypted=' || l_user_psw);

   INSERT INTO enc_data (user_id, enc_psw)
       VALUES (l_user_id, l_user_psw);

   COMMIT;
END;
/

Output

Encrypted=132BEDB1C2CDD8F23B5A619412C27B60
PL/SQL procedure successfully completed.

Check the data in ENC_DATA table:

SELECT * FROM enc_data;

USER_ID	ENC_PSW
SCOTT	132BEDB1C2CDD8F23B5A619412C27B60

In the above example, it encrypted the password for the user ID SCOTT and stored to the enc_data table. Now below is the PL/SQL program to fetch the password and decrypt it.

An Example to Decrypt Password in Oracle

SET SERVEROUTPUT ON;

DECLARE
   l_user_id    enc_data.user_id%TYPE := 'SCOTT';
   l_user_psw   RAW (2000);

   l_key        VARCHAR2 (2000) := '1234567890999999';
   l_mod NUMBER
         :=   DBMS_CRYPTO.ENCRYPT_AES128
            + DBMS_CRYPTO.CHAIN_CBC
            + DBMS_CRYPTO.PAD_PKCS5;
   l_dec        RAW (2000);
BEGIN
   SELECT enc_psw
     INTO l_user_psw
     FROM enc_data
    WHERE user_id = l_user_id;

   l_dec :=
      DBMS_CRYPTO.decrypt (l_user_psw,
                           l_mod,
                           UTL_I18N.STRING_TO_RAW (l_key, 'AL32UTF8'));
   DBMS_OUTPUT.put_line ('Decrypted=' || UTL_I18N.raw_to_char (l_dec));
END;
/

Output

Decrypted=mypassword123
PL/SQL procedure successfully completed.

See also:

• How to Hide Password in Oracle Forms?
• Change Sys user password in Oracle