Database Security

Database security protects data from unauthorized access, corruption, and theft. With data breaches making headlines regularly, understanding database security is essential for every developer.

This chapter covers authentication, authorization, encryption, SQL injection, auditing, and security best practices.

---

Learning Objectives

After completing this chapter, you will be able to:

• Implement authentication and authorization.
• Differentiate between encryption at rest and in transit.
• Prevent SQL injection attacks.
• Use auditing for compliance.
• Apply the principle of least privilege.
• Answer security-related interview questions.

---

Authentication

Authentication verifies who you are.

Methods

Method	Example	Security Level
Password	Username + password	Basic
Certificate	SSL/TLS client certificate	High
Kerberos	Enterprise single sign-on	High
LDAP/Active Directory	Corporate directory integration	High
OAuth/SSO	Third-party identity provider	High
Multi-Factor	Password + OTP	Very High

Best Practices

• Use strong password hashing (bcrypt, Argon2).
• Enforce password complexity and rotation.
• Use MFA for privileged accounts.
• Never embed credentials in application code — use environment variables or secret managers.

---

Authorization

Authorization determines what you can do.

Privileges

Level	Examples
System	CREATE DATABASE, DROP TABLE, CREATE USER
Object	SELECT, INSERT, UPDATE, DELETE on specific tables
Column	SELECT on specific columns (salary, SSN)
Row	Row-level security policies

SQL Examples

-- Grant table-level access
GRANT SELECT, INSERT ON Customers TO app_user;
GRANT ALL PRIVILEGES ON Database Sales TO admin;

-- Grant column-level access
GRANT SELECT (Name, Email) ON Customers TO support_agent;

-- Revoke
REVOKE DELETE ON Customers FROM app_user;

---

Role-Based Access Control (RBAC)

RBAC assigns permissions to roles, not individual users.

Users → Roles → Permissions

CREATE ROLE read_only;
CREATE ROLE read_write;
CREATE ROLE admin;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write;

CREATE USER rahul WITH PASSWORD '...';
GRANT read_only TO rahul;

CREATE USER priya WITH PASSWORD '...';
GRANT read_write TO priya;

---

Encryption at Rest

Data stored on disk should be encrypted so that unauthorized access to the storage medium does not expose data.

Transparent Data Encryption (TDE)

• The database automatically encrypts data before writing to disk and decrypts on read.
• Transparent to the application (no code changes).
• Supported by: SQL Server TDE, Oracle TDE, MySQL InnoDB encryption, PostgreSQL pgcrypto.

File-Level Encryption

• Encrypt the entire database file at the filesystem level.
• LUKS (Linux), BitLocker (Windows), FileVault (macOS).

Column-Level Encryption

• Encrypt specific sensitive columns (SSN, credit card numbers).
• Application must manage encryption/decryption keys.
• Example: pgcrypto in PostgreSQL.

---

Encryption in Transit

Data sent between the application and the database must be encrypted to prevent eavesdropping.

TLS/SSL

# PostgreSQL
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

# MySQL
[mysqld]
ssl-ca = ca.pem
ssl-cert = server-cert.pem
ssl-key = server-key.pem

Connection Strings

# Without SSL (insecure — never use in production)
postgresql://user:pass@localhost/db

# With SSL
postgresql://user:pass@localhost/db?sslmode=require
mysql://user:pass@localhost/db?ssl-mode=REQUIRED

---

SQL Injection

SQL Injection is the most common database security vulnerability. It occurs when user input is directly concatenated into SQL queries.

Vulnerable Code

# NEVER DO THIS
name = request.form['name']
query = f"SELECT * FROM Users WHERE Name = '{name}'"

If the user enters: ' OR '1'='1

The query becomes:

SELECT * FROM Users WHERE Name = '' OR '1'='1'

This returns all users.

Even worse:

-- Input: '; DROP TABLE Users; --
SELECT * FROM Users WHERE Name = ''; DROP TABLE Users; --'

Prevention

1. Parameterized Queries (Prepared Statements)

# Python with psycopg2
cursor.execute("SELECT * FROM Users WHERE Name = %s", (name,))

# Java with JDBC
PreparedStatement ps = conn.prepareStatement(
    "SELECT * FROM Users WHERE Name = ?"
);
ps.setString(1, name);

# Node.js with pg
client.query("SELECT * FROM Users WHERE Name = $1", [name]);

2. ORM (Object-Relational Mapping)

# SQLAlchemy
User.query.filter(User.name == name).all()

3. Input Validation

import re
if not re.match(r'^[a-zA-Z0-9_]+$', username):
    raise ValueError("Invalid username")

---

Auditing

Auditing records all database activities for security review and compliance.

What to Audit

Event	Why
Failed login attempts	Detect brute-force attacks
DDL operations (CREATE, ALTER, DROP)	Track schema changes
DML on sensitive tables	Track access to financial/medical data
Privilege changes (GRANT, REVOKE)	Track permission changes
Data exports	Prevent data exfiltration

Example (PostgreSQL)

-- Enable logging
ALTER SYSTEM SET logging_collector = 'on';
ALTER SYSTEM SET log_statement = 'ddl';  -- Log all DDL

-- Create an audit trigger
CREATE TABLE audit_log (
    event_time TIMESTAMP,
    user_name TEXT,
    table_name TEXT,
    operation TEXT,
    old_data JSONB,
    new_data JSONB
);

CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log VALUES (now(), current_user, TG_TABLE_NAME, TG_OP,
        row_to_json(OLD)::JSONB, row_to_json(NEW)::JSONB);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

---

Principle of Least Privilege

Each user or application should have only the minimum permissions needed to do its job.

Examples

User/Role	Permissions Needed
Web application	SELECT, INSERT, UPDATE on specific tables — no DDL
Read-only dashboard	SELECT only
DBA	Full access (but audit everything)
Support agent	SELECT on customer contact info — no access to passwords
Data analyst	SELECT on denormalized reporting views

Why It Matters

• Limits damage from compromised accounts.
• Prevents accidental data loss or modification.
• Simplifies compliance (HIPAA, GDPR, PCI-DSS).
• Makes security audits more tractable.

---

Common Security Mistakes

Mistake	Impact	Fix
SQL injection in queries	Data theft, deletion	Use parameterized queries
Weak passwords	Account takeover	Strong password policy + MFA
Default credentials	Complete access	Change all defaults immediately
No TLS	Eavesdropping on traffic	Always use TLS
Direct DB access from public app	Exposed credentials	Use application server as middleware
Unlimited login attempts	Brute force	Rate limiting, account lockout

---

Interview Deep Dive

---

Key Takeaways

• Authentication verifies identity; authorization controls access.
• Use parameterized queries to prevent SQL injection — always.
• Encrypt data at rest (TDE, file-level) and in transit (TLS).
• Implement RBAC for manageable permission management.
• Apply the principle of least privilege — minimum permissions for each role.
• Audit sensitive operations for security and compliance.
• Never embed credentials in code; use secret managers.
• Default credentials must be changed immediately on deployment.