Chapter 15: Designing an Authentication and Authorization System — Passwords, Sessions, MFA, and Audit Logs

What you'll learn in this chapter

• Distinguish authentication from authorization in your own words
• Design a database that hashes and stores passwords with bcrypt / argon2
• Design a minimal database for session management and multi-factor authentication (MFA / TOTP)
• Design account lockout in compliance with OWASP / NIST 800-63B
• Design an audit log that's tamper-evident

Prerequisites

You should understand Chapter 10: Key Concepts and Chapter 14: Many-to-Many (RBAC).

Authentication and Authorization Are Different Things

Term	Meaning	Question
Authentication	Identifying "who"	Who are you?
Authorization	Deciding "what they can do"	Does this person have the right to do X?

The login process = authentication, and checking "can this person edit this article" = authorization. The two are easy to conflate, so keep in mind, when designing, "which one am I working on right now — authentication or authorization?"

The DB design for authorization (RBAC) was covered in Chapter 14: Many-to-Many. This chapter focuses on authentication.

Always Hash Passwords Before Storing Them

Never store a password in plain text in the database. This is a golden rule made explicit in every modern security guideline (OWASP Authentication Cheat Sheet).

Use bcrypt or Argon2

Use bcrypt or Argon2 for password hashing. Avoid MD5, SHA-1, and SHA-256 (they're too fast, making them weak against brute-force attacks).

Algorithm	Recommendation	Characteristics
Argon2id	Most recommended (OWASP Password Storage Cheat Sheet)	Winner of the 2015 PHC, consumes both CPU and memory
bcrypt	Recommended	A long track record, plenty of libraries
scrypt	Acceptable	Newer than bcrypt, but less commonly adopted
PBKDF2	Acceptable (for legacy compatibility)	Required in FIPS-certified contexts
MD5 / SHA-256	Absolutely not	Too fast, vulnerable to brute-force attacks

users table (PostgreSQL)

CREATE TABLE users (
    id              SERIAL PRIMARY KEY,
    email           VARCHAR(255) UNIQUE NOT NULL,
    password_hash   VARCHAR(255) NOT NULL,            -- bcrypt: 60 chars / argon2: 95+ chars
    display_name    VARCHAR(100) NOT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

An Example of Hashing (Application Layer, Node.js)

import bcrypt from 'bcrypt';

// At registration (OWASP recommends a cost factor of at least 10; this uses 12)
const passwordHash = await bcrypt.hash(plainPassword, 12);
await db.query('INSERT INTO users (email, password_hash) VALUES ($1, $2)', [email, passwordHash]);

// At login
const user = await db.query('SELECT password_hash FROM users WHERE email = $1', [email]);
const isValid = await bcrypt.compare(plainPassword, user.password_hash);

Guard against SQL injection as a separate problem

Password hashing is a countermeasure for "protecting plaintext passwords if the DB is ever leaked." Separately, SQL injection is a countermeasure for "blocking a path to unauthorized access to the DB" — you need both.

SQL injection is prevented with parameterized queries (variable binding, like $1, $2 in the example above). Building SQL by concatenating strings is a critical vulnerability. See the OWASP SQL Injection Cheat Sheet for details.

Session Management

This is the mechanism that keeps a user logged in. The typical approaches are:

Approach	DB design	Characteristics
Server-side sessions (cookie + DB / Redis)	A sessions table, or Redis	Can be invalidated server-side / holds per-user state
JWT (JSON Web Token)	Nothing stored in the DB (self-contained)	Stateless / hard to invalidate immediately server-side
Refresh token (DB) + access token (JWT)	A refresh_tokens table	A hybrid, and the modern mainstream

DB Design for Server-Side Sessions

A sessions table

CREATE TABLE sessions (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    expires_at      TIMESTAMPTZ NOT NULL,
    ip_address      INET,
    user_agent      TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_used_at    TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_sessions_user_expires ON sessions(user_id, expires_at);

Key points:

• Use a hard-to-guess random value (a UUID v4) for the session ID
• A session past expires_at is invalid (set up a batch job to delete these periodically)
• Recording ip_address / user_agent can be used to detect "suspicious logins"

The option of a Redis session

Production-grade services often store sessions in Redis rather than a sessions table. It has the benefit of fast reads, and TTL (expiration) is managed automatically. That said, you need to consider the blast radius if Redis goes down, and your backup strategy.

Multi-Factor Authentication (MFA)

A mechanism that strengthens authentication by combining a password with a "possession factor" (a phone, a token) or a "biometric factor" (a fingerprint). The most widely adopted is TOTP (time-based one-time password, as used by Google Authenticator and similar apps).

DB design for MFA (PostgreSQL)

CREATE TABLE user_mfa_credentials (
    user_id        INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    type           VARCHAR(20) NOT NULL CHECK (type IN ('totp', 'sms', 'webauthn')),
    secret         VARCHAR(255) NOT NULL,                  -- encryption recommended for TOTP
    enabled        BOOLEAN NOT NULL DEFAULT TRUE,
    confirmed_at   TIMESTAMPTZ,                              -- when the user first verified it
    created_at     TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, type)
);

CREATE TABLE user_mfa_backup_codes (
    id              SERIAL PRIMARY KEY,
    user_id         INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    code_hash       VARCHAR(255) NOT NULL,                 -- backup codes are hashed too
    used_at         TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_mfa_backup_user ON user_mfa_backup_codes(user_id) WHERE used_at IS NULL;

Key points:

• Encrypt TOTP's secret at rest in the DB (e.g., AES at the application layer, with the decryption key managed separately)
• Hash backup codes too before storing them (treat them the same as a password)
• A backup code "can't be used again once it's used" (set used_at)

Designing Account Lockout (OWASP / NIST Compliant)

Locking an account is a mechanism for preventing brute-force attacks, but a careless design becomes a launchpad for a DoS attack.

The Problem With the Classic "Lock for M Minutes After N Failures"

A design like "lock for 30 minutes after 5 failures" is easy to understand, but:

• An attacker can deliberately lock someone else's account to make it unusable (a DoS)
• The legitimate account holder can't log in either, disrupting their work

The OWASP / NIST-Recommended Approach

NIST SP 800-63B Rev. 4 requires (SHALL) limiting consecutive failures to 100 or fewer, and lists CAPTCHA and progressive delay as auxiliary measures to prevent locking out legitimate users. OWASP ASVS V6 Authentication similarly calls for throttling / progressive delay.

A login-attempts log

CREATE TABLE login_attempts (
    id            BIGSERIAL PRIMARY KEY,
    email         VARCHAR(255) NOT NULL,
    ip_address    INET NOT NULL,
    success       BOOLEAN NOT NULL,
    attempted_at  TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    user_agent    TEXT
);

CREATE INDEX idx_login_attempts_email_time ON login_attempts(email, attempted_at DESC);
CREATE INDEX idx_login_attempts_ip_time    ON login_attempts(ip_address, attempted_at DESC);

From this log, you aggregate "the number of failures in the last hour" to decide on a CAPTCHA, delay, or block.

-- Number of failures in the past hour (queried from the application layer)
SELECT COUNT(*)
FROM   login_attempts
WHERE  email = $1
  AND  success = FALSE
  AND  attempted_at > NOW() - INTERVAL '1 hour';

A remedy for when someone gets locked out

Building in a design that allows "bypassing a lockout via a password reset" (recommended by OWASP) reduces the effectiveness of an attacker's DoS. If a legitimate user can still log in via a password reset, being locked out isn't a problem for them.

Audit Logs — Designing for Tamper Detection

Security-sensitive operations (login, permission changes, data exports) get recorded in an audit log. A design conscious of tamper detection is the modern standard.

An audit log (PostgreSQL)

CREATE TABLE audit_logs (
    id             BIGSERIAL PRIMARY KEY,
    user_id        INTEGER REFERENCES users(id) ON DELETE SET NULL,
    action         VARCHAR(50) NOT NULL,              -- 'login' / 'logout' / 'role_change' / 'data_export'
    resource_type  VARCHAR(50),                       -- 'user' / 'post' / 'role'
    resource_id    VARCHAR(50),
    ip_address     INET,
    user_agent     TEXT,
    metadata       JSONB,                             -- details of the operation (old value, new value, etc.)
    occurred_at    TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_audit_user_time   ON audit_logs(user_id, occurred_at DESC);
CREATE INDEX idx_audit_action_time ON audit_logs(action, occurred_at DESC);

-- Column-level permissions to prevent tampering (a PostgreSQL example)
-- The regular app user can only INSERT; UPDATE/DELETE are forbidden
REVOKE UPDATE, DELETE ON audit_logs FROM app_user;

Strengthening Tamper Detection

Technique	Description
Append-only	Forbid UPDATE/DELETE via permissions
Hash chaining	Each row includes a hash of the previous row, to detect tampering
External WAL	Also replicate the audit log to a separate DB / S3
Periodic export	Periodically archive to tamper-proof storage (e.g., S3 Object Lock)

For a business system, it's realistic to start with "append-only + replication to a separate DB." Strengthen it in stages depending on the level of legal compliance required (data protection law, SOX, etc.).

Exercises

Exercise 1: Authentication-design quiz

Judge whether each statement is true or false, and if false, give the correct design.

1. When storing a password, applying an MD5 hash makes it safer than plaintext
2. Using JWT means you don't need server-side session management, so logout takes effect immediately too
3. Locking an account permanently after 3 failures completely prevents brute-force attacks
4. As long as the audit log is written via INSERT from the application layer, no one can tamper with it

Sample answer

#	Verdict	Correct design
1	✕	MD5 is too fast and vulnerable to brute force. Use bcrypt / Argon2
2	✕	It's hard for a server to "invalidate before expiry" with JWT. You need something like a refresh token + a revocation list
3	✕	It becomes a launchpad for a DoS where an attacker deliberately locks someone else's account. Progressive delay + CAPTCHA is safer
4	✕	If DB permissions allow UPDATE/DELETE, it can be tampered with. Use REVOKE UPDATE, DELETE to make it append-only

Additional notes:

• 1: A GPU can compute billions of MD5/SHA-256 hashes per second, so it can be broken with rainbow tables plus brute force today. bcrypt/Argon2 are deliberately slow by design
• 2: If you need to invalidate a JWT immediately, a common design is managing a "list of revoked JWT IDs" as a Redis blocklist
• 3: NIST 800-63B requires (SHALL) limiting consecutive failures to 100 or fewer. Progressive delay + CAPTCHA are positioned as auxiliary measures to protect legitimate users from a lockout-based DoS
• 4: If the application layer is compromised via SQL injection, anything becomes possible. Restricting permissions at the DB level adds defense in depth

Exercise 2: Extending the DB to add MFA

You're adding TOTP MFA later to a service that already has a users table. Write the table definitions, and explain the "login flow for a user with MFA enabled" as a bulleted list.

Sample answer

Adding MFA to the existing users table

-- Add the MFA enrollment state to users
ALTER TABLE users ADD COLUMN mfa_enabled BOOLEAN NOT NULL DEFAULT FALSE;

-- MFA credentials table
CREATE TABLE user_mfa_credentials (
    user_id      INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    type         VARCHAR(20) NOT NULL CHECK (type IN ('totp', 'sms', 'webauthn')),
    secret       VARCHAR(255) NOT NULL,                       -- encrypted at the application layer
    enabled      BOOLEAN NOT NULL DEFAULT TRUE,
    confirmed_at TIMESTAMPTZ,
    PRIMARY KEY (user_id, type)
);

-- Backup codes
CREATE TABLE user_mfa_backup_codes (
    id        SERIAL PRIMARY KEY,
    user_id   INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    code_hash VARCHAR(255) NOT NULL,
    used_at   TIMESTAMPTZ
);

Login flow:

1. The user submits their email + password
2. Authenticate with password_hash (bcrypt.compare)
3. Authentication succeeds → check users.mfa_enabled
4. If MFA is disabled → issue a session and complete login
5. If MFA is enabled → issue a temporary token (a short-lived session, before MFA completes)
6. The user submits a TOTP code
7. Verify the TOTP against user_mfa_credentials.secret (current time ± 30 seconds)
8. Match → issue a normal session
9. Mismatch → try matching against a hashed backup code
10. If that fails too → error, log the attempt

Common mistakes:

• Forgetting to count MFA failures: TOTP is also subject to brute force. Record failure counts in login_attempts
• Storing TOTP's secret in plaintext: a DB leak would compromise everyone's MFA. Encrypt it at the application layer
• Storing backup codes in plaintext or with MD5: treat them the same as a password (hash with bcrypt, etc.)
• Setting enabled = TRUE right after configuring MFA: verify with a TOTP code first that "the user set it up correctly" (set confirmed_at) before enabling it

Summary

Here's a recap of what this chapter covered.

• Authentication (who) and authorization (what they can do) are different things
• Hash passwords with bcrypt / Argon2; SQL injection needs to be addressed separately
• It's safer to manage sessions server-side (DB / Redis); JWT needs extra work for invalidation
• TOTP is the standard for MFA; don't forget to encrypt secret and hash backup codes
• OWASP/NIST-compliant account lockout (progressive delay + CAPTCHA + a password-reset remedy) is safer
• Make the audit log append-only (REVOKE UPDATE/DELETE permissions) + replicate to a separate DB for tamper detection

The next chapter covers authentication integration with external services (OAuth 2.0 and OpenID Connect).

Next chapter

Chapter 16: OAuth and OpenID Connect