How To securely encrypt and store passwords in an Oracle database

To securely encrypt and store passwords in an Oracle database, follow these best practices:

---

🔹 Best Approach: Use Hashing (One-Way Encryption)

Instead of encryption (which can be decrypted), use hashing with salt to securely store passwords. Oracle supports SHA-2 (SHA-256, SHA-512) for hashing.

---

1️⃣ Hash Password Using DBMS_CRYPTO

You can store the password as a SHA-256 hash using DBMS_CRYPTO.HASH:

CREATE OR REPLACE FUNCTION hash_password(p_password IN VARCHAR2) RETURN VARCHAR2 IS
    v_hashed_password RAW(256);
BEGIN
    v_hashed_password := DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW(p_password, 'AL32UTF8'), DBMS_CRYPTO.HASH_SH256);
    RETURN RAWTOHEX(v_hashed_password); -- Convert to hex for storage
END;
/

or

CREATE OR REPLACE FUNCTION hash_password(p_password IN VARCHAR2) RETURN VARCHAR2 IS
    v_hashed_password RAW(256);
BEGIN
    v_hashed_password := DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW(p_password, 'AL32UTF8'), DBMS_CRYPTO.HASH_SH1);
    RETURN RAWTOHEX(v_hashed_password); -- Convert to hex for storage
END;
/

🔹 Storing the Hashed Password in a Table

CREATE TABLE users (
    user_id NUMBER PRIMARY KEY,
    username VARCHAR2(50) UNIQUE NOT NULL,
    password_hash VARCHAR2(64) NOT NULL
);

🔹 Inserting Hashed Password

INSERT INTO users (user_id, username, password_hash) 
VALUES (1, 'john_doe', hash_password('MySecureP@ssword'));

---

2️⃣ Verify Password at Login

To verify a password during login:

SELECT COUNT(*) FROM users 
WHERE username = 'john_doe' 
AND password_hash = hash_password('MySecureP@ssword');

🔹 If COUNT = 1, the password matches.

---

3️⃣ Encrypting Password (Two-Way Encryption)

If you need two-way encryption (not recommended for passwords), use DBMS_CRYPTO.ENCRYPT and DBMS_CRYPTO.DECRYPT.

🔹 Encrypt Function

CREATE OR REPLACE FUNCTION encrypt_password(p_password IN VARCHAR2) RETURN RAW IS
    v_key RAW(32) := UTL_RAW.CAST_TO_RAW('1234567890123456'); -- 16-byte key (AES-128)
    v_encrypted RAW(2000);
BEGIN
    v_encrypted := DBMS_CRYPTO.ENCRYPT(
        src => UTL_I18N.STRING_TO_RAW(p_password, 'AL32UTF8'),
        typ => DBMS_CRYPTO.ENCRYPT_AES128 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5,
        key => v_key
    );
    RETURN v_encrypted;
END;
/

🔹 Decrypt Function

CREATE OR REPLACE FUNCTION decrypt_password(p_encrypted RAW) RETURN VARCHAR2 IS
    v_key RAW(32) := UTL_RAW.CAST_TO_RAW('1234567890123456'); -- Same key used for encryption
    v_decrypted RAW(2000);
BEGIN
    v_decrypted := DBMS_CRYPTO.DECRYPT(
        src => p_encrypted,
        typ => DBMS_CRYPTO.ENCRYPT_AES128 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5,
        key => v_key
    );
    RETURN UTL_RAW.CAST_TO_VARCHAR2(v_decrypted);
END;
/

🔹 Insert Encrypted Password

INSERT INTO users (user_id, username, password_hash)
VALUES (2, 'alice', encrypt_password('MySecret123'));

🔹 Decrypt and Check

SELECT decrypt_password(password_hash) FROM users WHERE username = 'alice';

---

🔹 Which Method to Use?

Method	Use Case	Pros	Cons
SHA-256 Hashing	Best for password storage	Irreversible, secure	Cannot retrieve original password
AES Encryption	Store sensitive data (not passwords)	Can decrypt when needed	Requires key management

---

🔹 Final Recommendation

✅ Use hashing (DBMS_CRYPTO.HASH) for passwords to prevent unauthorized retrieval.
🚫 Avoid encryption for passwords, as it makes them vulnerable if the key is compromised.