Database Security: Authentication, Authorization, and Encryption

Database security involves protecting data from unauthorized access and breaches. It includes authentication mechanisms, authorization through roles and privileges, encryption of data at rest and in transit, and comprehensive audit logging.

Database Security: Authentication, Authorization, and Encryption

Database security is the practice of protecting database systems from unauthorized access, data breaches, and malicious attacks. Databases store the most valuable asset of any organization: data. Customer information, financial records, intellectual property, and business secrets all reside in databases. A single security breach can lead to massive fines, reputational damage, legal liability, and loss of customer trust.

Effective database security requires a defense-in-depth approach: multiple layers of security working together. This includes strong authentication, granular authorization, encryption at rest and in transit, audit logging, network security, and regular security assessments. To understand database security properly, it is helpful to be familiar with authentication mechanisms, authorization, encryption basics, and security compliance.

Database security overview:

┌─────────────────────────────────────────────────────────────┐
│                   Database Security                          │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  Authentication:          Authorization:                    │
│  • Strong passwords       • Role-Based Access Control       │
│  • Multi-factor           • Principle of least privilege    │
│  • Certificate auth       • Row-level security              │
│                                                              │
│  Encryption:              Auditing:                         │
│  • Data at rest (TDE)     • Login attempts                  │
│  • Data in transit (TLS)  • DDL changes                     │
│  • Column-level           • Data access logs                │
│  • Backup encryption      • Privilege changes               │
│                                                              │
└─────────────────────────────────────────────────────────────┘

What Is Database Security

Database security encompasses the measures, controls, and tools used to protect database confidentiality, integrity, and availability. It spans authentication (verifying who is accessing), authorization (controlling what they can do), encryption (protecting data), auditing (tracking activities), and network security (controlling access paths).

Confidentiality: Ensuring only authorized users can view data.
Integrity: Preventing unauthorized modification of data.
Availability: Ensuring the database remains accessible when needed.
Authentication: Verifying the identity of users and applications.
Authorization: Controlling what authenticated users can do.
Auditability: Tracking who did what and when.

Why Database Security Matters

Data breaches are increasingly common and costly. The average cost of a data breach exceeds millions of dollars. Database security is not optional; it is a business necessity.

Prevent Data Breaches: Stop unauthorized access to sensitive data.
Meet Compliance Requirements: GDPR, HIPAA, PCI-DSS, SOX all require database security controls.
Protect Reputation: Security breaches destroy customer trust and brand value.
Avoid Financial Penalties: Regulatory fines can reach millions or percentage of global revenue.
Prevent Ransomware: Secure databases are less vulnerable to encryption-based attacks.
Protect Intellectual Property: Trade secrets and proprietary algorithms must be protected.

Authentication

Authentication verifies the identity of users and applications attempting to connect to the database. Strong authentication is the first line of defense.

PostgreSQL authentication methods:

# pg_hba.conf authentication configurations

# Password authentication (scram-sha-256 recommended)
host    all             all             0.0.0.0/0               scram-sha-256

# Certificate authentication (mutual TLS)
hostssl all             all             0.0.0.0/0               cert

# LDAP authentication (centralized user management)
host    all             all             0.0.0.0/0               ldap ldapserver=ldap.example.com

# GSSAPI (Kerberos) for enterprise environments
host    all             all             0.0.0.0/0               gss

MySQL authentication:

-- Create user with strong authentication
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED WITH caching_sha2_password BY 'StrongPassword123!';

-- Require SSL for connection
ALTER USER 'app_user'@'192.168.1.%' REQUIRE SSL;

-- Set password expiration
ALTER USER 'app_user'@'192.168.1.%' PASSWORD EXPIRE INTERVAL 90 DAY;

-- Account locking after failed attempts
ALTER USER 'app_user'@'192.168.1.%' FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1 DAY;

SQL Server authentication:

-- Create login with strong password
CREATE LOGIN app_user WITH PASSWORD = 'StrongPassword123!', 
     CHECK_POLICY = ON, 
     CHECK_EXPIRATION = ON;

-- Create user from login
CREATE USER app_user FOR LOGIN app_user;

-- Enforce Windows Authentication only (no SQL logins)
ALTER LOGIN sa DISABLE;

Authorization and Access Control

Authorization controls what authenticated users can do. Follow the principle of least privilege: grant only the minimum permissions necessary.

PostgreSQL role-based access control:

-- Create roles
CREATE ROLE read_only;
CREATE ROLE read_write;
CREATE ROLE admin;

-- Grant privileges to roles
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin;

-- Grant role to users
GRANT read_only TO user_read;
GRANT read_write TO user_write;

-- Row-level security (RLS)
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_own_data ON users
    USING (user_id = current_user_id())
    WITH CHECK (user_id = current_user_id());

MySQL privileges:

-- Minimal privileges for application user
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app_user'@'%';

-- No DDL privileges (cannot alter schema)
-- No administrative privileges (cannot create users)

-- Create read-only user for reporting
CREATE USER 'report_user'@'%' IDENTIFIED BY 'reportpass';
GRANT SELECT ON myapp.* TO 'report_user'@'%';

-- Grant only on specific columns for sensitive data
GRANT SELECT (id, name, email) ON users TO 'support_user'@'%';

SQL Server permissions:

-- Database-level roles
CREATE ROLE DataReader;
GRANT SELECT ON SCHEMA::dbo TO DataReader;

CREATE ROLE DataWriter;
GRANT INSERT, UPDATE, DELETE ON SCHEMA::dbo TO DataWriter;

-- Object-level permissions
GRANT SELECT ON dbo.sensitive_table TO auditor;
DENY SELECT ON dbo.sensitive_table TO app_role;

Encryption at Rest

Encryption at rest protects data stored on disk. If physical media is stolen or an attacker gains file system access, encrypted data remains unreadable without the encryption key.

Transparent Data Encryption (TDE) examples:

-- SQL Server TDE
-- Create database encryption key
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
   ENCRYPTION BY SERVER CERTIFICATE TDECert;

-- Enable encryption
ALTER DATABASE mydb SET ENCRYPTION ON;

-- MySQL TDE (MySQL 8.0+)
ALTER TABLE users ENCRYPTION='Y';

-- PostgreSQL (using cluster encryption or LUKS)
# Encrypt entire data directory with LUKS
cryptsetup luksFormat /dev/sdb1
cryptsetup open /dev/sdb1 postgres_data
# Mount encrypted volume to data directory

Column-level encryption:

-- PostgreSQL (using pgcrypto)
CREATE EXTENSION pgcrypto;

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT,
    ssn TEXT ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = CEK_AES_256)
);

-- Encrypt data on insert
INSERT INTO users (name, ssn) VALUES (
    'John Doe',
    pgp_sym_encrypt('123-45-6789', 'encryption_key')
);

-- Decrypt on select
SELECT name, pgp_sym_decrypt(ssn, 'encryption_key') AS ssn
FROM users;

Encryption in Transit (TLS/SSL)

Encryption in transit protects data as it travels between the application and the database server. Without TLS, passwords and data can be intercepted on the network.

PostgreSQL TLS configuration:

# postgresql.conf
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'ca.crt'

# pg_hba.conf (require SSL)
hostssl    all    all    0.0.0.0/0    scram-sha-256

MySQL TLS configuration:

# my.cnf
[mysqld]
ssl-ca = /etc/mysql/ca-cert.pem
ssl-cert = /etc/mysql/server-cert.pem
ssl-key = /etc/mysql/server-key.pem
require_secure_transport = ON

# Require SSL for specific users
ALTER USER 'app_user'@'%' REQUIRE SSL;

SQL Server TLS configuration:

# Using SQL Server Configuration Manager
# Force protocol encryption
# Force all connections to use encrypted channels

# Or via T-SQL
ALTER ENDPOINT [TSQL Default TCP] 
    FOR TSQL() 
    LISTENER (ENCRYPTION = REQUIRED);

Network Security

Network security controls which clients can connect to the database. Firewall rules, network segmentation, and connection whitelisting reduce attack surface.

PostgreSQL pg_hba.conf (host-based authentication):

# Allow only specific IP ranges
hostssl    mydb    app_user    192.168.1.0/24        scram-sha-256
hostssl    mydb    app_user    10.0.0.0/8            scram-sha-256

# Deny all others
host       all     all        0.0.0.0/0              reject

# Bind only to specific interface
listen_addresses = '192.168.1.100'

MySQL bind configuration:

# my.cnf - Bind to specific interface
bind-address = 192.168.1.100

# Skip network entirely for local-only databases
skip-networking

# Firewall rules (iptables)
iptables -A INPUT -p tcp --dport 3306 -s 192.168.1.0/24 -j ACCEPT
iptables -A INPUT -p tcp --dport 3306 -j DROP

Audit Logging

Audit logs track who did what and when. They are essential for security investigations, compliance, and detecting unauthorized access.

PostgreSQL audit logging (pgAudit):

# Install pgAudit extension
CREATE EXTENSION pgaudit;

# Log all DDL and SELECT on sensitive tables
ALTER SYSTEM SET pgaudit.log = 'ddl, read';
ALTER SYSTEM SET pgaudit.log_relation = ON;

# Log specific tables
ALTER SYSTEM SET pgaudit.log_relation = ON;
SELECT pgaudit.audit_table('users', 'read,write');

# Log failed logins
ALTER SYSTEM SET log_connections = on;
ALTER SYSTEM SET log_disconnections = on;

MySQL audit logging:

# Enable general query log (not for production, use audit plugin)
general_log = 0

# Use MySQL Enterprise Audit or MariaDB Audit Plugin
INSTALL PLUGIN server_audit SONAME 'server_audit.so';

SET GLOBAL server_audit_logging = ON;
SET GLOBAL server_audit_events = 'CONNECT,QUERY_DDL,QUERY_DML';

# Log to syslog
SET GLOBAL server_audit_output_type = syslog;

SQL Server audit:

-- Create server audit
CREATE SERVER AUDIT SecurityAudit
TO FILE (FILEPATH = 'C:\Audit\')
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);

-- Create database audit specification
CREATE DATABASE AUDIT SPECIFICATION DBAudit
FOR SERVER AUDIT SecurityAudit
ADD (SELECT, INSERT, UPDATE, DELETE ON dbo.sensitive_table BY public)
WITH (STATE = ON);

Backup Security

Backups contain all your data and must be secured as strictly as the live database. Unencrypted backups are a common vulnerability.

Encrypting backups:

# PostgreSQL encrypted backup
pg_dump mydb | gpg --symmetric --cipher-algo AES256 > mydb.sql.gpg

# MySQL encrypted backup
mysqldump mydb | openssl enc -aes-256-cbc -salt -out mydb.sql.enc

# SQL Server encrypted backup
BACKUP DATABASE mydb TO DISK = 'D:\backup\mydb.bak'
WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = BackupCert);

# Store encryption keys separately from backups

Security Best Practices

Use Strong Authentication: Enforce strong passwords, use multi-factor authentication where possible.
Principle of Least Privilege: Grant only the minimum permissions needed. No blanket SELECT * grants.
Encrypt Everything: Data at rest, data in transit, and backups.
Enable Audit Logging: Track who accessed what and when.
Regular Security Updates: Apply database patches and security updates promptly.
Remove Default Accounts: Disable or change passwords for default accounts (sa, postgres, root).
Network Segmentation: Place databases in private subnets, not directly accessible from internet.
Regular Security Assessments: Perform vulnerability scans and penetration tests.

Security checklist:

□ Default accounts removed or secured
□ Strong password policy enforced
□ SSL/TLS enabled for all connections
□ Network access restricted by IP whitelist
□ Principle of least privilege applied
□ Audit logging enabled
□ Backups encrypted
□ Security patches up to date
□ No sensitive data in application logs
□ Database not exposed directly to internet
□ Connection limits configured
□ Failed login attempts logged and monitored

Common Security Mistakes to Avoid

Default Credentials: Leaving default passwords (root, sa, postgres) is a critical vulnerability.
Over-privileged Accounts: Applications running with DBA privileges are a major risk.
No Encryption: Unencrypted data and backups are vulnerable to theft.
Ignoring Audit Logs: Without monitoring, you cannot detect breaches.
Exposing Database Directly: Databases should never be directly accessible from the internet.
Hardcoded Credentials: Database passwords in source code or config files in version control.
No Backup Security: Backups stored unencrypted in the same location as the database.

Frequently Asked Questions

What is the difference between authentication and authorization?
Authentication verifies who you are (login). Authorization determines what you can do (permissions). Both are essential for database security.
Should I encrypt the entire database?
Yes, using TDE or filesystem encryption for data at rest. Additionally, encrypt sensitive columns individually for defense in depth.
What is SQL injection and how do I prevent it?
SQL injection occurs when untrusted input is concatenated into SQL queries. Prevent using parameterized queries/prepared statements and input validation.
How do I securely store database credentials?
Use secrets management tools (Hashicorp Vault, AWS Secrets Manager). Never hardcode credentials. Use IAM roles or certificate authentication where possible.
What is the principle of least privilege?
Grant users and applications only the minimum permissions necessary to perform their functions. No blanket admin or write access unless required.
What should I learn next after database security?
After mastering database security, explore security compliance, penetration testing, incident response, and audit logging best practices for comprehensive security management.

Database Security: Authentication, Authorization, and Encryption