Oracle SQL中使用表内password字段值加密指定列的实现方案问询

Hey there! Great question—yes, this is totally feasible, but we’ll need to adjust your existing encryption package to use each customer’s password as a dynamic encryption key instead of the fixed one you currently have. Let’s break this down step by step, with practical code and best practices.

First, let’s address the core issue: your current package uses a hardcoded key, but we need to swap that out for each customer’s unique password. Also, we’ll make some security improvements (like ditching ECB mode for the more secure CBC mode) and handle password length requirements for AES256.

Step 1: Update the Encryption Package to Support Dynamic Password Keys #

We’ll modify the package to accept a password as a parameter, convert it into a valid 32-byte AES256 key using SHA-256 hashing, and add proper initialization vector (IV) handling for CBC mode:

CREATE OR REPLACE PACKAGE field_encrypt_decrypt AS
    -- Add p_key parameter to pass in the customer's password
    FUNCTION encrypt (p_plainText VARCHAR2, p_key VARCHAR2) RETURN RAW DETERMINISTIC;
    FUNCTION decrypt (p_encryptedText RAW, p_key VARCHAR2) RETURN VARCHAR2 DETERMINISTIC;
END field_encrypt_decrypt;
/

CREATE OR REPLACE PACKAGE BODY field_encrypt_decrypt AS
    -- Use CBC mode (more secure than ECB) with AES256 and PKCS5 padding
    encryption_type PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5;

    -- Convert password to a valid 32-byte AES256 key using SHA-256 hashing
    FUNCTION get_secure_key(p_password VARCHAR2) RETURN RAW IS
    BEGIN
        RETURN DBMS_CRYPTO.HASH(UTL_RAW.CAST_TO_RAW(p_password), DBMS_CRYPTO.HASH_SH256);
    END get_secure_key;

    FUNCTION encrypt (p_plainText VARCHAR2, p_key VARCHAR2) RETURN RAW DETERMINISTIC IS
        encrypted_raw RAW (2000);
        secure_key RAW(32) := get_secure_key(p_key);
        iv RAW(16) := DBMS_CRYPTO.RANDOMBYTES(16); -- Generate random IV for CBC mode
    BEGIN
        -- Prepend IV to encrypted data so we can retrieve it for decryption
        encrypted_raw := iv || DBMS_CRYPTO.ENCRYPT (
            src => UTL_RAW.CAST_TO_RAW (p_plainText),
            typ => encryption_type,
            key => secure_key,
            iv => iv
        );
        RETURN encrypted_raw;
    END encrypt;

    FUNCTION decrypt (p_encryptedText RAW, p_key VARCHAR2) RETURN VARCHAR2 DETERMINISTIC IS
        decrypted_raw RAW (2000);
        secure_key RAW(32) := get_secure_key(p_key);
        iv RAW(16) := SUBSTR(p_encryptedText, 1, 16); -- Extract IV from start of encrypted data
        encrypted_data RAW(2000) := SUBSTR(p_encryptedText, 17); -- Get the actual encrypted payload
    BEGIN
        decrypted_raw := DBMS_CRYPTO.DECRYPT (
            src => encrypted_data,
            typ => encryption_type,
            key => secure_key,
            iv => iv
        );
        RETURN UTL_RAW.CAST_TO_VARCHAR2 (decrypted_raw);
    END decrypt;
END field_encrypt_decrypt;
/

Step 2: Prepare Your Customer Table #

Since encrypted data is stored as RAW type, you’ll need columns to hold the encrypted values. You can either add new columns or modify existing ones (backup data first if modifying existing columns):

-- Add new columns for encrypted data
ALTER TABLE customer ADD (
    encrypted_credit_card RAW(2000),
    encrypted_income RAW(2000)
);

Step 3: Encrypt Existing Data #

Run an update to encrypt each customer’s credit_card_number and income using their own password:

UPDATE customer
SET 
    encrypted_credit_card = field_encrypt_decrypt.encrypt(credit_card_number, password),
    encrypted_income = field_encrypt_decrypt.encrypt(TO_CHAR(income), password) -- Convert number to string for encryption
WHERE password IS NOT NULL; -- Skip rows with no password

Step 4: Decrypt Data When Needed #

To retrieve the plaintext values, use the same password to decrypt:

SELECT 
    customer_id,
    customer_name,
    contact_number,
    field_encrypt_decrypt.decrypt(encrypted_credit_card, password) AS credit_card_number,
    TO_NUMBER(field_encrypt_decrypt.decrypt(encrypted_income, password)) AS income -- Convert back to number
FROM customer
WHERE customer_id = 123; -- Replace with your target customer ID

Critical Security & Practical Notes #

• Never store plain-text passwords: Storing passwords in plain text is a massive security risk. Instead, store a salted hash of the password, and derive the encryption key from that hash. If you need users to log in, use the hash for authentication, not the plain password.
• Handle password changes: If a user updates their password, you must re-encrypt their credit_card_number and income with the new password—otherwise, you’ll lose access to the encrypted data.
• Error handling: Add exception blocks to the package to handle cases like NULL passwords, invalid encrypted data, or missing permissions on DBMS_CRYPTO.
• Permissions: Ensure the user executing these functions has EXECUTE privileges on DBMS_CRYPTO and your field_encrypt_decrypt package.

内容的提问来源于stack exchange，提问作者VinRocka