Oracle Free Tier云自治事务数据库基于ORDS的应用用户认证数据存储与验证最佳实践问询

Hey there! Let's break down your questions step by step since you're just getting started with Oracle Autonomous Transactional Database (ADB) and user authentication—great choice going with the Free Tier, by the way!

1. Dedicated Credential Table: Yes, This is Non-Negotiable #

You should absolutely create a dedicated table to store user authentication data (separate from your core business tables). This keeps your auth logic clean, makes it easier to apply security controls, and avoids mixing sensitive credential data with other application data.

A basic, secure table structure might look like this:

CREATE TABLE app_users (
    user_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username VARCHAR2(50) NOT NULL UNIQUE,
    password_hash RAW(64) NOT NULL, -- Stores SHA-256 hash output
    password_salt RAW(32) NOT NULL, -- Unique random salt per user
    email VARCHAR2(100) NOT NULL UNIQUE,
    is_active NUMBER(1) DEFAULT 1 CHECK (is_active IN (0,1)),
    created_at TIMESTAMP DEFAULT SYSTIMESTAMP,
    last_login_at TIMESTAMP,
    failed_login_attempts NUMBER(3) DEFAULT 0
);

The UNIQUE constraints on username and email prevent duplicate registrations, and the is_active flag lets you disable accounts if needed.

2. Password Handling: Hash, Don't Encrypt (Never Store Plaintext!) #

Forget about "encrypting passwords"—you should hash them instead. Encryption is reversible (you can recover the original password), but hashing is a one-way process. Even if an attacker gains access to your table, they can't reverse the hash to get the actual password.

Oracle’s DBMS_CRYPTO package handles secure hashing perfectly. Always use a unique salt per user (stored in the password_salt field above) to block rainbow table attacks. Stick to strong algorithms like SHA-256, SHA-3, or PBKDF2—avoid outdated ones like MD5 or SHA-1.

3. SQL for User Registration (POST Request) #

When a user signs up, generate a random salt, hash the password + salt combination, then insert the data into your table. Here’s an example (run this via your REST API’s POST handler):

DECLARE
    v_salt RAW(32) := DBMS_CRYPTO.RANDOMBYTES(32); -- Generate 32-byte random salt
BEGIN
    INSERT INTO app_users (username, password_hash, password_salt, email)
    VALUES (
        :p_username, -- Input from user's registration form
        DBMS_CRYPTO.HASH(
            UTL_I18N.STRING_TO_RAW(:p_password || UTL_RAW.CAST_TO_VARCHAR2(v_salt), 'AL32UTF8'),
            DBMS_CRYPTO.HASH_SH256
        ),
        v_salt,
        :p_email -- Input from user's registration form
    );
    COMMIT;
END;
/

Always use bind parameters (:p_username, etc.) to prevent SQL injection attacks!

4. SQL for Credential Verification (POST Request) #

When a user logs in, fetch their stored salt, hash the input password with that salt, and compare it to the stored hash. Here’s the safe way to do it:

SELECT user_id, username
FROM app_users
WHERE username = :p_username
AND password_hash = DBMS_CRYPTO.HASH(
    UTL_I18N.STRING_TO_RAW(:p_password || UTL_RAW.CAST_TO_VARCHAR2(password_salt), 'AL32UTF8'),
    DBMS_CRYPTO.HASH_SH256
)
AND is_active = 1;

• If this query returns a row, the credentials are valid.
• If not, either the username is wrong, the password is incorrect, or the account is disabled.
• Add logic to increment failed_login_attempts on failed tries, and lock accounts if the count exceeds a threshold (e.g., 5 attempts).

5. Extra Security Tips #

• Restrict table access: Only your application’s dedicated database user should have read/write permissions on app_users. Use Oracle’s row-level security (RLS) if you need to limit access further.
• Rotate hashing algorithms over time: Start with SHA-256, then migrate to SHA-3 as it becomes more standard.
• Enforce HTTPS for all REST endpoints: Never send passwords over plain HTTP—encrypt the connection end-to-end.

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