Database Security

Database security refers to the collective measures used to protect and secure a database from unauthorized access, misuse, corruption, or theft of data. It...

What is Database Security?

Database security refers to the collective measures used to protect and secure a database from unauthorized access, misuse, corruption, or theft of data. It encompasses both technical controls and administrative policies.

Security Layers

Physical Security	Network Security → OS Security → DBMS Security → Application Security
1. Physical	Lock server rooms, restrict data center access
2. Network	Firewalls, VPNs, TLS encryption in transit
3. OS	Patch OS, disable unused services, least-privilege
4. DBMS	Authentication, authorization, audit logging
5. Application	Parameterized queries (prevent SQL injection)

Authentication

Authentication is the process of verifying the identity of a user or application attempting to connect to the database.

Database Authentication Methods

sql example

-- 1. Password Authentication (most common)
CREATE USER 'alice'@'localhost' IDENTIFIED BY 'SecurePass@123';

-- 2. OS-level Authentication (trust local system user)
-- PostgreSQL: CREATE USER alice;  (maps to OS user alice)

-- 3. LDAP / Active Directory Integration
-- DBMS verifies credentials against enterprise directory

-- 4. Certificate-based (mTLS)
-- Client presents SSL certificate for authentication

-- 5. Multi-Factor Authentication (MFA)
-- Password + OTP token (available in cloud databases)

Password Security Best Practices

sql example

-- Enforce strong passwords in MySQL
SET GLOBAL validate_password.policy = STRONG;
SET GLOBAL validate_password.length = 12;
-- STRONG policy requires: uppercase, lowercase, digits, special chars

-- Set password expiry
ALTER USER 'alice'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;

-- Account lockout after failed attempts
CREATE USER 'bob'@'%' FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;

Authorization

Authorization determines what operations an authenticated user is permitted to perform on which database objects.

Discretionary Access Control (DAC)

The owner of a database object controls who has access.

sql example

-- GRANT privileges
GRANT SELECT ON employees TO 'analyst'@'%';
GRANT SELECT, INSERT, UPDATE ON orders TO 'sales_app'@'%';
GRANT ALL PRIVILEGES ON finance.* TO 'finance_admin'@'%';

-- GRANT with GRANT OPTION (pass privilege to others)
GRANT SELECT ON employees TO 'manager'@'%' WITH GRANT OPTION;

-- REVOKE privileges
REVOKE INSERT ON orders FROM 'sales_app'@'%';
REVOKE ALL PRIVILEGES ON finance.* FROM 'finance_admin'@'%';

-- Column-level privilege
GRANT SELECT (emp_id, name, dept) ON employees TO 'intern'@'%';
-- (intern cannot see salary column)

Role-Based Access Control (RBAC)

Group privileges into roles; assign roles to users.

sql example

-- Create roles
CREATE ROLE 'read_only';
CREATE ROLE 'data_analyst';
CREATE ROLE 'db_admin';

-- Assign privileges to roles
GRANT SELECT ON *.* TO 'read_only';
GRANT SELECT, INSERT ON analytics.* TO 'data_analyst';
GRANT ALL PRIVILEGES ON *.* TO 'db_admin';

-- Assign roles to users
GRANT 'read_only' TO 'intern1'@'%';
GRANT 'data_analyst' TO 'alice'@'%';

-- Activate role
SET DEFAULT ROLE 'data_analyst' TO 'alice'@'%';

Mandatory Access Control (MAC)

System enforces access based on classification levels (military/government use):

Example

Classification Levels: Top Secret > Secret > Confidential > Unclassified Rule: A user can only read data at or below their clearance level. A user can only write data at or above their clearance level. (Bell-LaPadula Model)

SQL Injection — A Critical Threat

SQL Injection is an attack where malicious SQL code is inserted into input fields to manipulate the database.

Attacker inputs	' OR '1'='1
Resulting query	SELECT * FROM users WHERE username = '' OR '1'='1'
Worse input	'; DROP TABLE users; --
Resulting query	SELECT * FROM users WHERE username = ''; DROP TABLE users; --'

Prevention — Parameterized Queries

Example

-- SAFE: Use prepared statements (parameterized queries) -- PHP PDO: $stmt = $pdo->prepare("SELECT * FROM users WHERE username = ?"); $stmt->execute([$username]); -- Python: cursor.execute("SELECT * FROM users WHERE username = %s", (username,)) -- Java: PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE username = ?"); ps.setString(1, username); -- The parameter is NEVER interpreted as SQL code. -- Input: ' OR '1'='1 → Treated as literal string, not SQL

Encryption

Encryption at Rest

Data stored on disk is encrypted:

sql example

-- MySQL InnoDB encryption (tablespace level)
ALTER TABLE employees ENCRYPTION='Y';

-- Column-level encryption
UPDATE employees SET ssn = AES_ENCRYPT(ssn, 'encryption_key');
SELECT AES_DECRYPT(ssn, 'encryption_key') FROM employees;

-- Full disk encryption handled at OS/storage level (transparent)

Encryption in Transit (SSL/TLS)

sql example

-- Force SSL for a specific user
ALTER USER 'analyst'@'%' REQUIRE SSL;

-- Require specific cipher
ALTER USER 'analyst'@'%' REQUIRE CIPHER 'AES256-SHA256';

-- Check SSL status
SHOW STATUS LIKE 'Ssl_cipher';

Audit Logging

Track who did what and when — essential for compliance (GDPR, HIPAA, PCI-DSS):

sql example

-- MySQL Enterprise Audit Log (logs all queries)
-- audit_log plugin records to XML/JSON file

-- PostgreSQL: pg_audit extension
-- Logs: SELECT, INSERT, UPDATE, DELETE, DDL
-- Log format: timestamp, user, database, object, query

-- Example audit log entry:
-- 2024-01-15 14:23:05 | alice | employees | SELECT | SELECT * FROM employees WHERE dept_id=1
-- 2024-01-15 14:25:10 | bob   | payroll   | UPDATE | UPDATE payroll SET salary=90000 WHERE id=101

Data Masking

Hiding sensitive data values for non-production environments:

sql example

-- Dynamic Data Masking (SQL Server / MySQL)
-- Real value stored, masked on display for certain users

-- Masking email: alice.johnson@company.com → a***@***.com
-- Masking phone: 9876543210 → ****3210
-- Masking SSN:   123-45-6789 → ***-**-6789

CREATE TABLE employees_masked AS
SELECT emp_id,
       name,
       CONCAT(LEFT(email, 1), '***@***.com') AS email,
       CONCAT('****', RIGHT(phone, 4)) AS phone
FROM employees;

Security Summary

┌──────────────────────────────────────────────────────┐ │ Database Security Checklist │ ├──────────────────────────────────────────────────────┤ │ ✓ Strong password policy + account lockout │ │ ✓ Least privilege (users get only what they need) │ │ ✓ Role-based access control (RBAC) │ │ ✓ Parameterized queries (prevent SQL injection) │ │ ✓ Encryption at rest (AES-256) │ │ ✓ Encryption in transit (TLS 1.2+) │ │ ✓ Audit logging enabled │ │ ✓ Regular backups tested and encrypted │ │ ✓ Regular patching of DBMS software │ │ ✓ Network isolation (DB not directly internet-facing)│ │ ✓ Data masking for dev/test environments │ │ ✓ Monitor for anomalous query patterns │ └──────────────────────────────────────────────────────┘

What is Database Security?

Security Layers

Physical Security	Network Security → OS Security → DBMS Security → Application Security
1. Physical	Lock server rooms, restrict data center access
2. Network	Firewalls, VPNs, TLS encryption in transit
3. OS	Patch OS, disable unused services, least-privilege
4. DBMS	Authentication, authorization, audit logging
5. Application	Parameterized queries (prevent SQL injection)

Authentication

Authentication is the process of verifying the identity of a user or application attempting to connect to the database.

Database Authentication Methods

sql example

-- 1. Password Authentication (most common)
CREATE USER 'alice'@'localhost' IDENTIFIED BY 'SecurePass@123';

-- 2. OS-level Authentication (trust local system user)
-- PostgreSQL: CREATE USER alice;  (maps to OS user alice)

-- 3. LDAP / Active Directory Integration
-- DBMS verifies credentials against enterprise directory

-- 4. Certificate-based (mTLS)
-- Client presents SSL certificate for authentication

-- 5. Multi-Factor Authentication (MFA)
-- Password + OTP token (available in cloud databases)

Password Security Best Practices

sql example

-- Enforce strong passwords in MySQL
SET GLOBAL validate_password.policy = STRONG;
SET GLOBAL validate_password.length = 12;
-- STRONG policy requires: uppercase, lowercase, digits, special chars

-- Set password expiry
ALTER USER 'alice'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;

-- Account lockout after failed attempts
CREATE USER 'bob'@'%' FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;

Authorization

Authorization determines what operations an authenticated user is permitted to perform on which database objects.

Discretionary Access Control (DAC)

The owner of a database object controls who has access.

sql example

-- GRANT privileges
GRANT SELECT ON employees TO 'analyst'@'%';
GRANT SELECT, INSERT, UPDATE ON orders TO 'sales_app'@'%';
GRANT ALL PRIVILEGES ON finance.* TO 'finance_admin'@'%';

-- GRANT with GRANT OPTION (pass privilege to others)
GRANT SELECT ON employees TO 'manager'@'%' WITH GRANT OPTION;

-- REVOKE privileges
REVOKE INSERT ON orders FROM 'sales_app'@'%';
REVOKE ALL PRIVILEGES ON finance.* FROM 'finance_admin'@'%';

-- Column-level privilege
GRANT SELECT (emp_id, name, dept) ON employees TO 'intern'@'%';
-- (intern cannot see salary column)

Role-Based Access Control (RBAC)

Group privileges into roles; assign roles to users.

sql example

-- Create roles
CREATE ROLE 'read_only';
CREATE ROLE 'data_analyst';
CREATE ROLE 'db_admin';

-- Assign privileges to roles
GRANT SELECT ON *.* TO 'read_only';
GRANT SELECT, INSERT ON analytics.* TO 'data_analyst';
GRANT ALL PRIVILEGES ON *.* TO 'db_admin';

-- Assign roles to users
GRANT 'read_only' TO 'intern1'@'%';
GRANT 'data_analyst' TO 'alice'@'%';

-- Activate role
SET DEFAULT ROLE 'data_analyst' TO 'alice'@'%';

Mandatory Access Control (MAC)

System enforces access based on classification levels (military/government use):

Example

SQL Injection — A Critical Threat

SQL Injection is an attack where malicious SQL code is inserted into input fields to manipulate the database.

Attacker inputs	' OR '1'='1
Resulting query	SELECT * FROM users WHERE username = '' OR '1'='1'
Worse input	'; DROP TABLE users; --
Resulting query	SELECT * FROM users WHERE username = ''; DROP TABLE users; --'

Prevention — Parameterized Queries

Example

Encryption

Encryption at Rest

Data stored on disk is encrypted:

sql example

-- MySQL InnoDB encryption (tablespace level)
ALTER TABLE employees ENCRYPTION='Y';

-- Column-level encryption
UPDATE employees SET ssn = AES_ENCRYPT(ssn, 'encryption_key');
SELECT AES_DECRYPT(ssn, 'encryption_key') FROM employees;

-- Full disk encryption handled at OS/storage level (transparent)

Encryption in Transit (SSL/TLS)

sql example

-- Force SSL for a specific user
ALTER USER 'analyst'@'%' REQUIRE SSL;

-- Require specific cipher
ALTER USER 'analyst'@'%' REQUIRE CIPHER 'AES256-SHA256';

-- Check SSL status
SHOW STATUS LIKE 'Ssl_cipher';

Audit Logging

Track who did what and when — essential for compliance (GDPR, HIPAA, PCI-DSS):

sql example

-- MySQL Enterprise Audit Log (logs all queries)
-- audit_log plugin records to XML/JSON file

-- PostgreSQL: pg_audit extension
-- Logs: SELECT, INSERT, UPDATE, DELETE, DDL
-- Log format: timestamp, user, database, object, query

-- Example audit log entry:
-- 2024-01-15 14:23:05 | alice | employees | SELECT | SELECT * FROM employees WHERE dept_id=1
-- 2024-01-15 14:25:10 | bob   | payroll   | UPDATE | UPDATE payroll SET salary=90000 WHERE id=101

Data Masking

Hiding sensitive data values for non-production environments:

sql example

-- Dynamic Data Masking (SQL Server / MySQL)
-- Real value stored, masked on display for certain users

-- Masking email: alice.johnson@company.com → a***@***.com
-- Masking phone: 9876543210 → ****3210
-- Masking SSN:   123-45-6789 → ***-**-6789

CREATE TABLE employees_masked AS
SELECT emp_id,
       name,
       CONCAT(LEFT(email, 1), '***@***.com') AS email,
       CONCAT('****', RIGHT(phone, 4)) AS phone
FROM employees;

Database Security

What is Database Security?

Security Layers

Authentication

Database Authentication Methods

Password Security Best Practices

Authorization

Discretionary Access Control (DAC)

Role-Based Access Control (RBAC)

Mandatory Access Control (MAC)

SQL Injection — A Critical Threat

Prevention — Parameterized Queries

Encryption

Encryption at Rest

Encryption in Transit (SSL/TLS)

Audit Logging

Data Masking

Security Summary

Study next in this module

Database Security

What is Database Security?

Security Layers

Authentication

Database Authentication Methods

Password Security Best Practices

Authorization

Discretionary Access Control (DAC)

Role-Based Access Control (RBAC)

Mandatory Access Control (MAC)

SQL Injection — A Critical Threat

Prevention — Parameterized Queries

Encryption

Encryption at Rest

Encryption in Transit (SSL/TLS)

Audit Logging

Data Masking

Security Summary

Study next in this module