Authentication - Database Security | OneNoughtOne

0/278

Database Management SystemsDatabase Security

Database Security

LevelAdvanced

Duration90 mins

TopicDatabase Security

1 / 5

Authentication

The Gateway to Database Security

Every security breach begins with a fundamental failure: someone gained access who shouldn't have. In the realm of database security, authentication stands as the critical first line of defense—the mechanism that answers the foundational question: Who are you, and can you prove it?

Database authentication isn't merely about passwords. Modern enterprise database systems employ sophisticated multi-layered authentication architectures that combine cryptographic protocols, hardware tokens, biometric verification, and network-layer security to establish identity with mathematical certainty. Understanding these mechanisms isn't optional for database professionals—it's essential for protecting the most valuable assets in any organization: its data.

What You Will Learn

By the end of this page, you will understand the fundamental principles of database authentication, master the differences between authentication methods (password-based, certificate-based, integrated, and multi-factor), comprehend the cryptographic foundations underlying secure credential storage, and recognize how enterprise database systems implement authentication at scale.

Authentication Fundamentals

Authentication is the process of verifying the claimed identity of a user, service, or system attempting to access database resources. It addresses the fundamental security question: Is this entity who they claim to be?

Authentication differs fundamentally from authorization (which we'll cover in the next page). While authentication establishes identity, authorization determines permissions. A user might successfully authenticate but still be denied access to specific data based on authorization rules.

The Authentication Triad:

Secure authentication relies on verifying one or more of these factors:

Authentication Factors

•Something You Know — Secrets like passwords, PINs, or security questions. The most common factor, but also the most vulnerable to social engineering, phishing, and brute-force attacks.
•Something You Have — Physical tokens, smart cards, mobile devices, or cryptographic keys. Requires possession of a specific item, adding a physical barrier to unauthorized access.
•Something You Are — Biometric identifiers like fingerprints, retinal patterns, facial recognition, or voice prints. Unique to individuals but raises privacy concerns and can't be changed if compromised.

Multi-Factor Authentication (MFA):

Modern database security best practices mandate combining multiple authentication factors. When a user must provide both a password (something they know) and a one-time code from an authenticator app (something they have), the security posture improves dramatically. An attacker who steals a password still cannot authenticate without the second factor.

The Authentication Lifecycle:

Database authentication isn't a single event—it's a lifecycle encompassing:

Authentication Lifecycle Phases
Phase	Description	Security Considerations
Identity Creation	Establishing user accounts with initial credentials	Secure enrollment, identity verification, initial password policies
Credential Submission	User presents authentication credentials	Encrypted transmission, replay attack prevention, timing attack mitigation
Verification	System validates credentials against stored values	Constant-time comparison, rate limiting, lockout policies
Session Establishment	Creating authenticated session after successful verification	Session token security, timeout policies, session binding
Credential Management	Ongoing password changes, resets, expirations	Secure reset mechanisms, password history, rotation policies
Revocation	Terminating authentication privileges	Immediate effect, session invalidation, audit trail

Authentication Is Not Enough

Authentication only verifies identity—it doesn't protect data in transit, prevent SQL injection, or stop authorized users from exfiltrating data. Authentication is one layer in a defense-in-depth strategy that must include authorization, encryption, auditing, and network security.

Password-Based Authentication

Password-based authentication remains the most prevalent mechanism for database access despite its well-documented vulnerabilities. Understanding how passwords should be handled—and how they're commonly mishandled—is essential for database security.

Why Passwords Persist:

Despite decades of security research demonstrating password weaknesses, they remain dominant because:

Users understand the concept intuitively
Implementation is straightforward
No additional hardware or infrastructure required
Compatible with virtually all systems

However, this simplicity comes with significant security tradeoffs that database administrators must actively mitigate.

Secure Password Storage:

A cardinal rule of database security: Never store passwords in plaintext. If an attacker gains access to the authentication database, plaintext passwords immediately compromise all user accounts. Instead, databases store cryptographic derivatives of passwords using these techniques:

Password Hashing Best Practices

•Cryptographic Hash Functions — One-way functions that produce fixed-size outputs from arbitrary inputs. Given a hash, computing the original input is computationally infeasible. Common algorithms: SHA-256, SHA-3.
•Salting — Adding a random value (salt) unique to each password before hashing. Even identical passwords produce different hashes, defeating precomputed rainbow table attacks. Salts are stored alongside hashes.
•Key Derivation Functions (KDFs) — Purpose-built algorithms for password hashing that include built-in salting and are intentionally slow to resist brute-force attacks. Examples: bcrypt, scrypt, Argon2.
•Work Factors — KDFs accept a computational cost parameter. Higher values make each hash verification slower, dramatically increasing brute-force attack time while adding minimal overhead for legitimate users.
•Pepper — A secret value (stored separately from the database) added during hashing. Even if the password database is stolen, the pepper remains unknown, preventing offline attacks.

password_hashing.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
import bcrypt
import secrets
import hashlib
 
# INSECURE: Never do this - plaintext storage
def store_password_bad(password: str) -> str:
    return password  # TERRIBLE: plaintext storage
 
# INSECURE: Simple hashing without salt
def store_password_still_bad(password: str) -> str:
    return hashlib.sha256(password.encode()).hexdigest()  # Vulnerable to rainbow tables
 
# SECURE: Using bcrypt with automatic salting
def store_password_good(password: str) -> bytes:
    """
    bcrypt automatically:
    1. Generates a cryptographically secure random salt
    2. Applies the Blowfish cipher iteratively (work factor)
    3. Combines salt + hash in the output
    """
    salt = bcrypt.gensalt(rounds=12)  # Work factor of 12 (~250ms on modern hardware)
    hashed = bcrypt.hashpw(password.encode('utf-8'), salt)
    return hashed
 
def verify_password(password: str, stored_hash: bytes) -> bool:
    """
    bcrypt.checkpw extracts the salt from stored_hash,
    rehashes the input password, and compares securely.
    Uses constant-time comparison to prevent timing attacks.
    """
    return bcrypt.checkpw(password.encode('utf-8'), stored_hash)
 
# BEST PRACTICE: Adding a pepper for defense-in-depth
PEPPER = secrets.token_hex(32)  # Store this in environment/secrets manager, NOT in code
 
def store_password_with_pepper(password: str) -> bytes:
    """Combine password with pepper before bcrypt hashing."""
    peppered = hashlib.sha256((password + PEPPER).encode()).hexdigest()
    return bcrypt.hashpw(peppered.encode('utf-8'), bcrypt.gensalt(rounds=12))
 
# Example usage
password = "user_secret_password"
hashed = store_password_good(password)
print(f"Original: {password}")
print(f"Stored Hash: {hashed}")
print(f"Verification: {verify_password(password, hashed)}")  # True
print(f"Wrong Password: {verify_password('wrong_password', hashed)}")  # False

Argon2: The Modern Standard

Argon2, winner of the 2015 Password Hashing Competition, is now considered the gold standard. It provides configurable memory-hardness (making GPU attacks expensive), time cost (iteration count), and parallelism. Use Argon2id variant for the best balance of resistance against side-channel and GPU attacks.

Database-Native Authentication

Each major database management system implements its own authentication subsystem with distinct characteristics. Understanding these differences is crucial for DBAs managing heterogeneous database environments.

Authentication Architecture Patterns:

Modern databases typically support multiple authentication modes:

Database Authentication Methods Comparison
Database	Native Auth	Integrated Auth	Certificate Auth	Plugin/PAM
PostgreSQL	md5, scram-sha-256	GSSAPI (Kerberos), SSPI	SSL certificates	PAM, LDAP, RADIUS
MySQL	mysql_native_password, caching_sha2_password	Windows (via plugin)	X.509 certificates	PAM, LDAP plugins
SQL Server	SQL Server Authentication	Windows Authentication	Certificate mapping	Contained DB users
Oracle	Oracle Database Authentication	OS, Kerberos	SSL/TLS certificates	RADIUS, directory services
MongoDB	SCRAM-SHA-1, SCRAM-SHA-256	Kerberos, LDAP	X.509 certificates	Custom mechanisms

PostgreSQL Authentication (pg_hba.conf):

PostgreSQL uses a host-based authentication configuration file (pg_hba.conf) that provides fine-grained control over authentication methods based on connection type, database, user, and network address:

pg_hba.conf

PostgreSQL

# PostgreSQL Host-Based Authentication Configuration
# TYPE  DATABASE    USER        ADDRESS          METHOD
 
# Local connections - Unix socket
local   all         postgres                     peer
local   all         all                          scram-sha-256
 
# IPv4 local connections - require strong authentication
host    all         all         127.0.0.1/32     scram-sha-256
 
# IPv4 internal network - use LDAP for corporate users
host    production  all         10.0.0.0/8       ldap ldapserver=ldap.corp.com
                                                      ldapbasedn="dc=corp,dc=com"
                                                      ldapsearchattribute=uid
 
# IPv4 application servers - certificate authentication
hostssl webapp_db   app_user    192.168.1.0/24   cert clientcert=verify-full
 
# Remote DBA access - require certificate + password (MFA concept)
hostssl all         dba_        0.0.0.0/0        cert clientcert=verify-full
 
# Replication connections - only from specific IPs with strong auth
host    replication repl_user   10.0.1.10/32     scram-sha-256
host    replication repl_user   10.0.1.11/32     scram-sha-256
 
# Reject all other connections explicitly
host    all         all         0.0.0.0/0        reject
hostssl all         all         0.0.0.0/0        reject
 
# AUTHENTICATION METHOD EXPLANATIONS:
# peer       - OS username must match database username (local only)
# scram-sha-256 - Salted Challenge Response Auth (recommended)
# md5        - MD5 hash-based (deprecated, use scram-sha-256)
# password   - Plaintext password (NEVER use except over SSL)
# gss        - GSSAPI (Kerberos)
# cert       - Client SSL certificate
# ldap       - LDAP directory authentication
# radius     - RADIUS server authentication
# pam        - Pluggable Authentication Modules
# reject     - Explicitly deny connection
# trust      - Accept without password (DANGEROUS - dev only)

SCRAM-SHA-256: The Modern Standard:

SCRAM (Salted Challenge Response Authentication Mechanism) represents the current best practice for password-based database authentication. It provides several security advantages over older methods:

SCRAM Security Properties

•Channel Binding — Cryptographically binds authentication to the TLS channel, preventing man-in-the-middle attacks even if an attacker has valid credentials.
•No Plaintext Transmission — Password never sent over the wire, even in encrypted form. Only cryptographic proofs are exchanged.
•Mutual Authentication — Both client and server prove their identity to each other, preventing server impersonation.
•Stored Secret Protection — Even the stored verifier cannot be used directly to authenticate; it's not equivalent to having the password.
•Iteration Count — Configurable computational cost for password hashing, similar to bcrypt work factor.

Deprecating MD5 Authentication

If you're still using MD5 authentication in PostgreSQL, migrate immediately to SCRAM-SHA-256. MD5's cryptographic weaknesses are well-documented, and the stored hash format allows password recovery. PostgreSQL 14+ defaults to scram-sha-256, and future versions may remove MD5 entirely.

Integrated and External Authentication

Enterprise environments rarely manage database credentials in isolation. Instead, they integrate with centralized identity providers—Active Directory, LDAP directories, or modern identity platforms—to enforce consistent authentication policies across all systems.

Benefits of Integrated Authentication:

Advantages

•Single Sign-On (SSO) experience
•Centralized credential management
•Consistent password policies
•Simplified user provisioning/deprovisioning
•Reduced password fatigue
•Audit trail consolidation
•MFA enforcement at identity provider

Challenges

•Single point of failure (IdP outage)
•Complex network configuration
•Cross-domain trust requirements
•Service account management complexity
•Kerberos ticket lifetime management
•Clock synchronization dependencies
•Debugging authentication failures

Kerberos Authentication:

Kerberos is the de facto standard for enterprise authentication, used by Active Directory and supported by all major databases. It provides secure, ticket-based authentication without transmitting passwords:

Converting Mermaid diagram...

LDAP Authentication:

Lightweight Directory Access Protocol (LDAP) enables databases to authenticate against directory services like Active Directory, OpenLDAP, or cloud directories. This is commonly used when Kerberos infrastructure isn't available:

ldap_authentication.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- MySQL LDAP Authentication Configuration
-- Requires mysql_ldap_authentication plugin
 
-- Install the LDAP authentication plugin
INSTALL PLUGIN authentication_ldap_sasl
  SONAME 'authentication_ldap_sasl.so';
 
-- Configure LDAP server connection
SET GLOBAL authentication_ldap_sasl_server_host = 'ldap.corp.example.com';
SET GLOBAL authentication_ldap_sasl_server_port = 636;  -- LDAPS (SSL)
SET GLOBAL authentication_ldap_sasl_bind_base_dn = 'dc=corp,dc=example,dc=com';
 
-- Create user that authenticates via LDAP
CREATE USER 'john.doe'@'%'
  IDENTIFIED WITH authentication_ldap_sasl
  BY 'cn=john.doe,ou=users,dc=corp,dc=example,dc=com';
 
-- Alternative: Simple LDAP bind (less secure)
CREATE USER 'jane.smith'@'%'
  IDENTIFIED WITH authentication_ldap_simple
  AS 'uid=jane.smith,ou=people,dc=corp,dc=example,dc=com';
 
-- Grant appropriate permissions
GRANT SELECT, INSERT, UPDATE ON production.* TO 'john.doe'@'%';
 
-- View LDAP plugin status
SHOW VARIABLES LIKE 'authentication_ldap%';

LDAPS vs StartTLS

Always use encrypted LDAP connections. LDAPS (port 636) provides SSL/TLS from connection start. StartTLS (port 389) upgrades an unencrypted connection to TLS. LDAPS is preferred as it prevents downgrade attacks where an attacker strips the StartTLS upgrade request.

Certificate-Based Authentication

Certificate-based authentication uses public key infrastructure (PKI) to verify identity without transmitting secrets. Each client holds a private key and presents a signed certificate; the database verifies the certificate against trusted Certificate Authorities (CAs).

Why Certificate Authentication?

Certificate authentication provides significant security advantages for service-to-service communication:

Certificate Authentication Advantages

•No Password Transmission — Private keys never leave the client system. Even over compromised networks, credentials remain secure.
•Mutual Authentication — Both client and server verify each other's identity, preventing impersonation attacks in either direction.
•Automated Rotation — Certificate lifecycle management tools enable automatic renewal without human intervention.
•Strong Cryptographic Identity — RSA-2048 or ECDSA-256 keys provide mathematical security far exceeding any password.
•Hardware Protection — Private keys can be stored in HSMs or TPMs, making extraction virtually impossible.

Certificate Authentication Architecture:

Converting Mermaid diagram...

certificate_setup.sh
Bash
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
#!/bin/bash
# Complete PostgreSQL Certificate Authentication Setup
 
# 1. Create Certificate Authority (for development/testing)
# In production, use your organization's CA
openssl req -new -x509 -days 3650 -nodes \
    -out ca.crt \
    -keyout ca.key \
    -subj "/CN=Database Root CA/O=Corp/C=US"
 
# 2. Create Server Certificate
# Generate private key
openssl genrsa -out server.key 2048
chmod 400 server.key
 
# Create certificate signing request
openssl req -new -key server.key \
    -out server.csr \
    -subj "/CN=dbserver.corp.com/O=Corp/C=US"
 
# Sign with CA
openssl x509 -req -in server.csr \
    -CA ca.crt -CAkey ca.key -CAcreateserial \
    -out server.crt -days 365
 
# 3. Create Client Certificate for Application
openssl genrsa -out client.key 2048
 
openssl req -new -key client.key \
    -out client.csr \
    -subj "/CN=app_service_account/O=Corp/C=US"
 
openssl x509 -req -in client.csr \
    -CA ca.crt -CAkey ca.key -CAcreateserial \
    -out client.crt -days 365
 
# 4. Configure PostgreSQL (postgresql.conf)
cat >> postgresql.conf << EOF
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'ca.crt'
ssl_crl_file = ''  # Certificate Revocation List (if using)
EOF
 
# 5. Update pg_hba.conf for certificate authentication
cat >> pg_hba.conf << EOF
# Require valid client certificate, map CN to database user
hostssl all all 0.0.0.0/0 cert clientcert=verify-full
EOF
 
# 6. Create pg_ident.conf to map certificate CN to database user
cat >> pg_ident.conf << EOF
# MAPNAME       SYSTEM-USERNAME     PG-USERNAME
cert_map        app_service_account  app_db_user
EOF
 
# 7. Connect using certificate authentication
PGSSLMODE=verify-full \
PGSSLCERT=client.crt \
PGSSLKEY=client.key \
PGSSLROOTCERT=ca.crt \
psql -h dbserver.corp.com -U app_db_user -d production

Private Key Security

Private keys must NEVER be committed to version control, shared via email, or stored with overly permissive file permissions. Use secrets management solutions (HashiCorp Vault, AWS Secrets Manager, Azure Key Vault) for production key storage. Set file permissions to 400 (read-only by owner) at minimum.

Multi-Factor Authentication (MFA)

Multi-Factor Authentication requires users to provide multiple independent proofs of identity before granting access. In database contexts, MFA significantly reduces the risk of credential compromise leading to data breach.

MFA in Database Environments:

Unlike web applications where MFA is commonplace, implementing MFA for database connections presents unique challenges:

MFA Implementation Approaches for Databases
Approach	Description	Use Case	Complexity
Identity Provider MFA	MFA enforced at Kerberos/LDAP level before ticket issuance	Enterprise SSO environments	Medium
VPN + MFA Gateway	Require MFA-authenticated VPN before database network access	Remote DBA access	Low
Jump Server/Bastion	MFA-protected bastion host as mandatory gateway	Privileged access management	Medium
Database Proxy with MFA	Proxy layer enforces MFA before forwarding connections	All application connections	High
Password + Certificate	Require both password and client certificate	Service accounts, high-security	Medium
Custom Authentication Plugin	Database plugin integrating TOTP/hardware tokens	Native database MFA	Very High

Practical MFA Implementation:

Most organizations implement MFA for database access through architectural patterns rather than database-native features:

Converting Mermaid diagram...

Azure AD MFA for SQL Server:

Microsoft SQL Server integrates with Azure Active Directory, enabling native MFA support:

azure_ad_mfa.sql
SQL Server
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-- Configure SQL Server for Azure AD authentication
 
-- 1. (In Azure Portal) Set Azure AD admin for SQL Server
 
-- 2. Create contained database user from Azure AD
USE TargetDatabase;
GO
 
-- Create user from Azure AD individual account
CREATE USER [john.doe@contoso.com] FROM EXTERNAL PROVIDER;
GRANT SELECT, INSERT, UPDATE ON SCHEMA::app TO [john.doe@contoso.com];
 
-- Create user from Azure AD group (recommended)
CREATE USER [DB_Readers_Group] FROM EXTERNAL PROVIDER;
GRANT SELECT ON SCHEMA::reporting TO [DB_Readers_Group];
 
-- 3. Connect with Azure AD authentication (MFA triggered by Azure AD)
-- Connection string for applications:
-- Server=your-server.database.windows.net;
-- Database=TargetDatabase;
-- Authentication=Active Directory Interactive;
 
-- For service principals (no interactive MFA, use managed identity or certificate):
-- Authentication=Active Directory Service Principal
 
-- 4. Verify Azure AD authentication mode
SELECT 
    session_id,
    login_name,
    original_login_name,
    auth_scheme,
    client_net_address
FROM sys.dm_exec_sessions
WHERE auth_scheme = 'ADAL' OR auth_scheme = 'AAD';

Conditional Access Policies

When using Azure AD/Entra ID, leverage Conditional Access policies to enforce MFA only under specific conditions (untrusted networks, sensitive databases, privileged roles). This balances security with user experience—trusted devices on corporate networks might bypass MFA while external access always requires it.

Authentication Security Hardening

Beyond choosing the right authentication method, proper hardening ensures the authentication system itself doesn't become a vulnerability. These practices apply across all database platforms:

Authentication Hardening Checklist

•Account Lockout Policies — Lock accounts after 5-10 failed attempts. Implement exponential backoff (1 min, 5 min, 30 min) rather than permanent lockout to prevent denial-of-service.
•Password Complexity Requirements — Enforce minimum 12 characters, mixed case, numbers, and symbols. Modern guidance favors length over complexity (NIST 800-63B).
•Password Expiration — Controversial: NIST now recommends against forced rotation unless breach is suspected. If required by policy, use 90+ day cycles.
•Password History — Prevent reuse of last 12-24 passwords. Prevents users from cycling through passwords to return to favorites.
•Connection Encryption — Enforce TLS 1.2+ for all connections. Reject unencrypted connections even on internal networks.
•Service Account Hygiene — Use unique credentials per application. Rotate service account credentials regularly. Never embed in source code.
•Default Account Elimination — Remove or rename default accounts (sa, postgres, root). Disable guest/anonymous access.
•Login Auditing — Log all authentication attempts (successful and failed) with source IP, timestamp, and username.

hardening_examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- PostgreSQL Authentication Hardening
 
-- 1. Set password encryption to strongest available
ALTER SYSTEM SET password_encryption = 'scram-sha-256';
SELECT pg_reload_conf();
 
-- 2. Log all authentication attempts
ALTER SYSTEM SET log_connections = on;
ALTER SYSTEM SET log_disconnections = on;
ALTER SYSTEM SET log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h ';
 
-- 3. Limit connection attempts (via pg_hba.conf and connection limits)
ALTER ROLE suspicious_user CONNECTION LIMIT 5;
 
-- 4. Disable password authentication for superusers
-- In pg_hba.conf: require certificate auth for superusers
-- hostssl all postgres 0.0.0.0/0 cert clientcert=verify-full
 
-- 5. Set password valid duration (PostgreSQL 13+)
ALTER ROLE web_app_user VALID UNTIL '2024-12-31';
 
-- 6. Force password change on first login
ALTER ROLE new_user PASSWORD 'TempPass123!' VALID UNTIL '2024-01-15';
 
-- 7. Revoke default public permissions
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;
 
-- 8. View authentication-related settings
SELECT name, setting, context 
FROM pg_settings 
WHERE name LIKE '%auth%' OR name LIKE '%password%' OR name LIKE '%ssl%';

Never Disable Authentication for Convenience

Under no circumstances should authentication be disabled 'temporarily' for development or troubleshooting. Trust authentication (PostgreSQL), noauth (MongoDB), or blank SA passwords have led to countless data breaches. Always maintain authentication, even in development environments.

Summary: Authentication Mastery

Database authentication is the critical first gate in your security architecture. Let's consolidate the essential knowledge:

Key Takeaways

•Authentication verifies identity — It answers 'who are you?' and differs fundamentally from authorization (which answers 'what can you do?').
•Never store plaintext passwords — Use purpose-built KDFs (Argon2, bcrypt, scrypt) with salting and appropriate work factors.
•SCRAM-SHA-256 is the modern standard — Upgrade from MD5 or mysql_native_password to prevent credential theft.
•Integrated authentication centralizes security — Kerberos and LDAP enable SSO and consistent policy enforcement across the enterprise.
•Certificate authentication eliminates password transmission — Ideal for service-to-service communication and high-security environments.
•MFA multiplies security — Combine factors (password + certificate, SSO + hardware token) to make compromise exponentially harder.
•Hardening is continuous — Lockout policies, encryption requirements, audit logging, and default account elimination are ongoing practices.

What's Next:

Authentication establishes who a user is, but it doesn't determine what they're allowed to do. The next page covers Authorization—the complementary security layer that enforces access control, implements the principle of least privilege, and ensures users can only access the data they need for their role.

Page Complete

You now understand database authentication fundamentals, from password hashing to multi-factor authentication. You can evaluate authentication methods for different scenarios and implement security hardening across major database platforms. Next, we'll explore how authorization controls what authenticated users can actually do.

Database Management SystemsDatabase Security

Database Security

LevelAdvanced

Duration90 mins

TopicDatabase Security

1 / 5

Authentication

The Gateway to Database Security

What You Will Learn

Authentication Fundamentals

The Authentication Triad:

Secure authentication relies on verifying one or more of these factors:

Authentication Factors

•Something You Know — Secrets like passwords, PINs, or security questions. The most common factor, but also the most vulnerable to social engineering, phishing, and brute-force attacks.
•Something You Have — Physical tokens, smart cards, mobile devices, or cryptographic keys. Requires possession of a specific item, adding a physical barrier to unauthorized access.
•Something You Are — Biometric identifiers like fingerprints, retinal patterns, facial recognition, or voice prints. Unique to individuals but raises privacy concerns and can't be changed if compromised.

Multi-Factor Authentication (MFA):

The Authentication Lifecycle:

Database authentication isn't a single event—it's a lifecycle encompassing:

Authentication Lifecycle Phases
Phase	Description	Security Considerations
Identity Creation	Establishing user accounts with initial credentials	Secure enrollment, identity verification, initial password policies
Credential Submission	User presents authentication credentials	Encrypted transmission, replay attack prevention, timing attack mitigation
Verification	System validates credentials against stored values	Constant-time comparison, rate limiting, lockout policies
Session Establishment	Creating authenticated session after successful verification	Session token security, timeout policies, session binding
Credential Management	Ongoing password changes, resets, expirations	Secure reset mechanisms, password history, rotation policies
Revocation	Terminating authentication privileges	Immediate effect, session invalidation, audit trail

Authentication Is Not Enough

Password-Based Authentication

Why Passwords Persist:

Despite decades of security research demonstrating password weaknesses, they remain dominant because:

Users understand the concept intuitively
Implementation is straightforward
No additional hardware or infrastructure required
Compatible with virtually all systems

However, this simplicity comes with significant security tradeoffs that database administrators must actively mitigate.

Secure Password Storage:

Password Hashing Best Practices

•Cryptographic Hash Functions — One-way functions that produce fixed-size outputs from arbitrary inputs. Given a hash, computing the original input is computationally infeasible. Common algorithms: SHA-256, SHA-3.
•Salting — Adding a random value (salt) unique to each password before hashing. Even identical passwords produce different hashes, defeating precomputed rainbow table attacks. Salts are stored alongside hashes.
•Key Derivation Functions (KDFs) — Purpose-built algorithms for password hashing that include built-in salting and are intentionally slow to resist brute-force attacks. Examples: bcrypt, scrypt, Argon2.
•Work Factors — KDFs accept a computational cost parameter. Higher values make each hash verification slower, dramatically increasing brute-force attack time while adding minimal overhead for legitimate users.
•Pepper — A secret value (stored separately from the database) added during hashing. Even if the password database is stolen, the pepper remains unknown, preventing offline attacks.

password_hashing.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
import bcrypt
import secrets
import hashlib
 
# INSECURE: Never do this - plaintext storage
def store_password_bad(password: str) -> str:
    return password  # TERRIBLE: plaintext storage
 
# INSECURE: Simple hashing without salt
def store_password_still_bad(password: str) -> str:
    return hashlib.sha256(password.encode()).hexdigest()  # Vulnerable to rainbow tables
 
# SECURE: Using bcrypt with automatic salting
def store_password_good(password: str) -> bytes:
    """
    bcrypt automatically:
    1. Generates a cryptographically secure random salt
    2. Applies the Blowfish cipher iteratively (work factor)
    3. Combines salt + hash in the output
    """
    salt = bcrypt.gensalt(rounds=12)  # Work factor of 12 (~250ms on modern hardware)
    hashed = bcrypt.hashpw(password.encode('utf-8'), salt)
    return hashed
 
def verify_password(password: str, stored_hash: bytes) -> bool:
    """
    bcrypt.checkpw extracts the salt from stored_hash,
    rehashes the input password, and compares securely.
    Uses constant-time comparison to prevent timing attacks.
    """
    return bcrypt.checkpw(password.encode('utf-8'), stored_hash)
 
# BEST PRACTICE: Adding a pepper for defense-in-depth
PEPPER = secrets.token_hex(32)  # Store this in environment/secrets manager, NOT in code
 
def store_password_with_pepper(password: str) -> bytes:
    """Combine password with pepper before bcrypt hashing."""
    peppered = hashlib.sha256((password + PEPPER).encode()).hexdigest()
    return bcrypt.hashpw(peppered.encode('utf-8'), bcrypt.gensalt(rounds=12))
 
# Example usage
password = "user_secret_password"
hashed = store_password_good(password)
print(f"Original: {password}")
print(f"Stored Hash: {hashed}")
print(f"Verification: {verify_password(password, hashed)}")  # True
print(f"Wrong Password: {verify_password('wrong_password', hashed)}")  # False

Argon2: The Modern Standard

Database-Native Authentication

Authentication Architecture Patterns:

Modern databases typically support multiple authentication modes:

Database Authentication Methods Comparison
Database	Native Auth	Integrated Auth	Certificate Auth	Plugin/PAM
PostgreSQL	md5, scram-sha-256	GSSAPI (Kerberos), SSPI	SSL certificates	PAM, LDAP, RADIUS
MySQL	mysql_native_password, caching_sha2_password	Windows (via plugin)	X.509 certificates	PAM, LDAP plugins
SQL Server	SQL Server Authentication	Windows Authentication	Certificate mapping	Contained DB users
Oracle	Oracle Database Authentication	OS, Kerberos	SSL/TLS certificates	RADIUS, directory services
MongoDB	SCRAM-SHA-1, SCRAM-SHA-256	Kerberos, LDAP	X.509 certificates	Custom mechanisms

PostgreSQL Authentication (pg_hba.conf):

pg_hba.conf

PostgreSQL

# PostgreSQL Host-Based Authentication Configuration
# TYPE  DATABASE    USER        ADDRESS          METHOD
 
# Local connections - Unix socket
local   all         postgres                     peer
local   all         all                          scram-sha-256
 
# IPv4 local connections - require strong authentication
host    all         all         127.0.0.1/32     scram-sha-256
 
# IPv4 internal network - use LDAP for corporate users
host    production  all         10.0.0.0/8       ldap ldapserver=ldap.corp.com
                                                      ldapbasedn="dc=corp,dc=com"
                                                      ldapsearchattribute=uid
 
# IPv4 application servers - certificate authentication
hostssl webapp_db   app_user    192.168.1.0/24   cert clientcert=verify-full
 
# Remote DBA access - require certificate + password (MFA concept)
hostssl all         dba_        0.0.0.0/0        cert clientcert=verify-full
 
# Replication connections - only from specific IPs with strong auth
host    replication repl_user   10.0.1.10/32     scram-sha-256
host    replication repl_user   10.0.1.11/32     scram-sha-256
 
# Reject all other connections explicitly
host    all         all         0.0.0.0/0        reject
hostssl all         all         0.0.0.0/0        reject
 
# AUTHENTICATION METHOD EXPLANATIONS:
# peer       - OS username must match database username (local only)
# scram-sha-256 - Salted Challenge Response Auth (recommended)
# md5        - MD5 hash-based (deprecated, use scram-sha-256)
# password   - Plaintext password (NEVER use except over SSL)
# gss        - GSSAPI (Kerberos)
# cert       - Client SSL certificate
# ldap       - LDAP directory authentication
# radius     - RADIUS server authentication
# pam        - Pluggable Authentication Modules
# reject     - Explicitly deny connection
# trust      - Accept without password (DANGEROUS - dev only)

SCRAM-SHA-256: The Modern Standard:

SCRAM (Salted Challenge Response Authentication Mechanism) represents the current best practice for password-based database authentication. It provides several security advantages over older methods:

SCRAM Security Properties

•Channel Binding — Cryptographically binds authentication to the TLS channel, preventing man-in-the-middle attacks even if an attacker has valid credentials.
•No Plaintext Transmission — Password never sent over the wire, even in encrypted form. Only cryptographic proofs are exchanged.
•Mutual Authentication — Both client and server prove their identity to each other, preventing server impersonation.
•Stored Secret Protection — Even the stored verifier cannot be used directly to authenticate; it's not equivalent to having the password.
•Iteration Count — Configurable computational cost for password hashing, similar to bcrypt work factor.

Deprecating MD5 Authentication

Integrated and External Authentication

Benefits of Integrated Authentication:

Advantages

•Single Sign-On (SSO) experience
•Centralized credential management
•Consistent password policies
•Simplified user provisioning/deprovisioning
•Reduced password fatigue
•Audit trail consolidation
•MFA enforcement at identity provider

Challenges

•Single point of failure (IdP outage)
•Complex network configuration
•Cross-domain trust requirements
•Service account management complexity
•Kerberos ticket lifetime management
•Clock synchronization dependencies
•Debugging authentication failures

Kerberos Authentication:

Converting Mermaid diagram...

LDAP Authentication:

ldap_authentication.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- MySQL LDAP Authentication Configuration
-- Requires mysql_ldap_authentication plugin
 
-- Install the LDAP authentication plugin
INSTALL PLUGIN authentication_ldap_sasl
  SONAME 'authentication_ldap_sasl.so';
 
-- Configure LDAP server connection
SET GLOBAL authentication_ldap_sasl_server_host = 'ldap.corp.example.com';
SET GLOBAL authentication_ldap_sasl_server_port = 636;  -- LDAPS (SSL)
SET GLOBAL authentication_ldap_sasl_bind_base_dn = 'dc=corp,dc=example,dc=com';
 
-- Create user that authenticates via LDAP
CREATE USER 'john.doe'@'%'
  IDENTIFIED WITH authentication_ldap_sasl
  BY 'cn=john.doe,ou=users,dc=corp,dc=example,dc=com';
 
-- Alternative: Simple LDAP bind (less secure)
CREATE USER 'jane.smith'@'%'
  IDENTIFIED WITH authentication_ldap_simple
  AS 'uid=jane.smith,ou=people,dc=corp,dc=example,dc=com';
 
-- Grant appropriate permissions
GRANT SELECT, INSERT, UPDATE ON production.* TO 'john.doe'@'%';
 
-- View LDAP plugin status
SHOW VARIABLES LIKE 'authentication_ldap%';

LDAPS vs StartTLS

Certificate-Based Authentication

Why Certificate Authentication?

Certificate authentication provides significant security advantages for service-to-service communication:

Certificate Authentication Advantages

•No Password Transmission — Private keys never leave the client system. Even over compromised networks, credentials remain secure.
•Mutual Authentication — Both client and server verify each other's identity, preventing impersonation attacks in either direction.
•Automated Rotation — Certificate lifecycle management tools enable automatic renewal without human intervention.
•Strong Cryptographic Identity — RSA-2048 or ECDSA-256 keys provide mathematical security far exceeding any password.
•Hardware Protection — Private keys can be stored in HSMs or TPMs, making extraction virtually impossible.

Certificate Authentication Architecture:

Converting Mermaid diagram...

certificate_setup.sh
Bash
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
#!/bin/bash
# Complete PostgreSQL Certificate Authentication Setup
 
# 1. Create Certificate Authority (for development/testing)
# In production, use your organization's CA
openssl req -new -x509 -days 3650 -nodes \
    -out ca.crt \
    -keyout ca.key \
    -subj "/CN=Database Root CA/O=Corp/C=US"
 
# 2. Create Server Certificate
# Generate private key
openssl genrsa -out server.key 2048
chmod 400 server.key
 
# Create certificate signing request
openssl req -new -key server.key \
    -out server.csr \
    -subj "/CN=dbserver.corp.com/O=Corp/C=US"
 
# Sign with CA
openssl x509 -req -in server.csr \
    -CA ca.crt -CAkey ca.key -CAcreateserial \
    -out server.crt -days 365
 
# 3. Create Client Certificate for Application
openssl genrsa -out client.key 2048
 
openssl req -new -key client.key \
    -out client.csr \
    -subj "/CN=app_service_account/O=Corp/C=US"
 
openssl x509 -req -in client.csr \
    -CA ca.crt -CAkey ca.key -CAcreateserial \
    -out client.crt -days 365
 
# 4. Configure PostgreSQL (postgresql.conf)
cat >> postgresql.conf << EOF
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'ca.crt'
ssl_crl_file = ''  # Certificate Revocation List (if using)
EOF
 
# 5. Update pg_hba.conf for certificate authentication
cat >> pg_hba.conf << EOF
# Require valid client certificate, map CN to database user
hostssl all all 0.0.0.0/0 cert clientcert=verify-full
EOF
 
# 6. Create pg_ident.conf to map certificate CN to database user
cat >> pg_ident.conf << EOF
# MAPNAME       SYSTEM-USERNAME     PG-USERNAME
cert_map        app_service_account  app_db_user
EOF
 
# 7. Connect using certificate authentication
PGSSLMODE=verify-full \
PGSSLCERT=client.crt \
PGSSLKEY=client.key \
PGSSLROOTCERT=ca.crt \
psql -h dbserver.corp.com -U app_db_user -d production

Private Key Security

Multi-Factor Authentication (MFA)

MFA in Database Environments:

Unlike web applications where MFA is commonplace, implementing MFA for database connections presents unique challenges:

MFA Implementation Approaches for Databases
Approach	Description	Use Case	Complexity
Identity Provider MFA	MFA enforced at Kerberos/LDAP level before ticket issuance	Enterprise SSO environments	Medium
VPN + MFA Gateway	Require MFA-authenticated VPN before database network access	Remote DBA access	Low
Jump Server/Bastion	MFA-protected bastion host as mandatory gateway	Privileged access management	Medium
Database Proxy with MFA	Proxy layer enforces MFA before forwarding connections	All application connections	High
Password + Certificate	Require both password and client certificate	Service accounts, high-security	Medium
Custom Authentication Plugin	Database plugin integrating TOTP/hardware tokens	Native database MFA	Very High

Practical MFA Implementation:

Most organizations implement MFA for database access through architectural patterns rather than database-native features:

Converting Mermaid diagram...

Azure AD MFA for SQL Server:

Microsoft SQL Server integrates with Azure Active Directory, enabling native MFA support:

azure_ad_mfa.sql
SQL Server
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-- Configure SQL Server for Azure AD authentication
 
-- 1. (In Azure Portal) Set Azure AD admin for SQL Server
 
-- 2. Create contained database user from Azure AD
USE TargetDatabase;
GO
 
-- Create user from Azure AD individual account
CREATE USER [john.doe@contoso.com] FROM EXTERNAL PROVIDER;
GRANT SELECT, INSERT, UPDATE ON SCHEMA::app TO [john.doe@contoso.com];
 
-- Create user from Azure AD group (recommended)
CREATE USER [DB_Readers_Group] FROM EXTERNAL PROVIDER;
GRANT SELECT ON SCHEMA::reporting TO [DB_Readers_Group];
 
-- 3. Connect with Azure AD authentication (MFA triggered by Azure AD)
-- Connection string for applications:
-- Server=your-server.database.windows.net;
-- Database=TargetDatabase;
-- Authentication=Active Directory Interactive;
 
-- For service principals (no interactive MFA, use managed identity or certificate):
-- Authentication=Active Directory Service Principal
 
-- 4. Verify Azure AD authentication mode
SELECT 
    session_id,
    login_name,
    original_login_name,
    auth_scheme,
    client_net_address
FROM sys.dm_exec_sessions
WHERE auth_scheme = 'ADAL' OR auth_scheme = 'AAD';

Conditional Access Policies

Authentication Security Hardening

Beyond choosing the right authentication method, proper hardening ensures the authentication system itself doesn't become a vulnerability. These practices apply across all database platforms:

Authentication Hardening Checklist

•Account Lockout Policies — Lock accounts after 5-10 failed attempts. Implement exponential backoff (1 min, 5 min, 30 min) rather than permanent lockout to prevent denial-of-service.
•Password Complexity Requirements — Enforce minimum 12 characters, mixed case, numbers, and symbols. Modern guidance favors length over complexity (NIST 800-63B).
•Password Expiration — Controversial: NIST now recommends against forced rotation unless breach is suspected. If required by policy, use 90+ day cycles.
•Password History — Prevent reuse of last 12-24 passwords. Prevents users from cycling through passwords to return to favorites.
•Connection Encryption — Enforce TLS 1.2+ for all connections. Reject unencrypted connections even on internal networks.
•Service Account Hygiene — Use unique credentials per application. Rotate service account credentials regularly. Never embed in source code.
•Default Account Elimination — Remove or rename default accounts (sa, postgres, root). Disable guest/anonymous access.
•Login Auditing — Log all authentication attempts (successful and failed) with source IP, timestamp, and username.

hardening_examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- PostgreSQL Authentication Hardening
 
-- 1. Set password encryption to strongest available
ALTER SYSTEM SET password_encryption = 'scram-sha-256';
SELECT pg_reload_conf();
 
-- 2. Log all authentication attempts
ALTER SYSTEM SET log_connections = on;
ALTER SYSTEM SET log_disconnections = on;
ALTER SYSTEM SET log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h ';
 
-- 3. Limit connection attempts (via pg_hba.conf and connection limits)
ALTER ROLE suspicious_user CONNECTION LIMIT 5;
 
-- 4. Disable password authentication for superusers
-- In pg_hba.conf: require certificate auth for superusers
-- hostssl all postgres 0.0.0.0/0 cert clientcert=verify-full
 
-- 5. Set password valid duration (PostgreSQL 13+)
ALTER ROLE web_app_user VALID UNTIL '2024-12-31';
 
-- 6. Force password change on first login
ALTER ROLE new_user PASSWORD 'TempPass123!' VALID UNTIL '2024-01-15';
 
-- 7. Revoke default public permissions
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;
 
-- 8. View authentication-related settings
SELECT name, setting, context 
FROM pg_settings 
WHERE name LIKE '%auth%' OR name LIKE '%password%' OR name LIKE '%ssl%';

Never Disable Authentication for Convenience

Summary: Authentication Mastery

Database authentication is the critical first gate in your security architecture. Let's consolidate the essential knowledge:

Key Takeaways

•Authentication verifies identity — It answers 'who are you?' and differs fundamentally from authorization (which answers 'what can you do?').
•Never store plaintext passwords — Use purpose-built KDFs (Argon2, bcrypt, scrypt) with salting and appropriate work factors.
•SCRAM-SHA-256 is the modern standard — Upgrade from MD5 or mysql_native_password to prevent credential theft.
•Integrated authentication centralizes security — Kerberos and LDAP enable SSO and consistent policy enforcement across the enterprise.
•Certificate authentication eliminates password transmission — Ideal for service-to-service communication and high-security environments.
•MFA multiplies security — Combine factors (password + certificate, SSO + hardware token) to make compromise exponentially harder.
•Hardening is continuous — Lockout policies, encryption requirements, audit logging, and default account elimination are ongoing practices.

What's Next:

Page Complete