Database Management SystemsDatabase Security

Database Security

LevelAdvanced

Duration90 mins

TopicDatabase Security

5 / 5

Security Best Practices

From Controls to Culture

We've explored the pillars of database security: authentication verifies identity, authorization controls access, encryption protects confidentiality, and auditing provides visibility. But individual security controls, no matter how well-configured, don't constitute security. True database security is a system—a coordinated set of practices, processes, and habits that work together to protect data.

This page synthesizes everything into actionable best practices. These aren't merely technical configurations—they're the operational disciplines that distinguish organizations with robust security postures from those waiting to become the next breach headline. Security isn't a destination; it's a continuous practice.

What You Will Learn

By the end of this page, you will understand defense-in-depth strategies for database protection, master security hardening across database platforms, implement vulnerability management and patching processes, apply data classification to drive security decisions, design incident response procedures for database security events, and build sustainable security practices that scale with organizational growth.

Defense in Depth

Defense in Depth is the foundational security architecture principle: deploy multiple layers of security controls so that if one fails, others continue to provide protection. No single control is foolproof—attackers find vulnerabilities, configurations have gaps, and humans make mistakes. Layered defenses ensure any single failure doesn't result in complete compromise.

Database Security Layers:

Converting Mermaid diagram...

Layer-Specific Controls:

Defense in Depth Control Matrix
Layer	Primary Controls	Failure Mode Protection
Network	Firewalls, segmentation, VPN, IP whitelisting	Limits attack surface; prevents direct database access
Authentication	Strong passwords, MFA, certificates, SSO	Ensures only verified identities connect
Authorization	RBAC, row-level security, least privilege	Limits damage from compromised credentials
Encryption (Transit)	TLS 1.2+, certificate validation	Protects against network interception
Encryption (Rest)	TDE, column encryption, backup encryption	Protects against physical/storage theft
Application	Parameterized queries, input validation	Prevents SQL injection, application-layer attacks
Auditing	Comprehensive logging, SIEM integration	Detects attacks, enables investigation
Monitoring	Anomaly detection, performance monitoring	Identifies unusual patterns indicating attack

Assume Breach Mindset

Design security assuming attackers are already inside your network. This 'assume breach' posture drives investment in detection, segmentation, and encryption—controls that matter when perimeter defenses fail. Ask: 'If an attacker has this credential, what can they access?' Then minimize that blast radius.

Database Server Hardening

Database hardening is the process of reducing attack surface by eliminating unnecessary features, configuring secure defaults, and applying security-specific configurations. A hardened database presents fewer opportunities for exploitation.

Universal Hardening Principles:

Core Hardening Actions

•Remove/disable unused features — Linked servers, XP_CMDSHELL, CLR integration, external scripts—disable anything not explicitly needed.
•Change default ports — While security through obscurity isn't sufficient alone, non-standard ports reduce automated scanning attacks.
•Rename/disable default accounts — SA, postgres, root accounts are primary targets. Rename, disable, or use complex credentials.
•Apply CIS Benchmarks — Center for Internet Security provides detailed hardening guides for each database platform.
•Restrict network binding — Listen only on required interfaces; don't bind to 0.0.0.0 if local-only access is sufficient.
•Implement connection limits — Prevent denial of service through connection exhaustion.
•Secure file permissions — Database files, configuration files, and log files should be readable only by the database service account.

hardening_checklist.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
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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
-- ================================================
-- SQL Server Hardening Checklist
-- Based on CIS Benchmark for SQL Server
-- ================================================
 
-- 1. DISABLE DANGEROUS FEATURES
 
-- Disable xp_cmdshell (command execution)
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;
 
-- Disable CLR integration (unless needed)
EXEC sp_configure 'clr enabled', 0;
RECONFIGURE;
 
-- Disable OLE Automation
EXEC sp_configure 'Ole Automation Procedures', 0;
RECONFIGURE;
 
-- Disable Ad Hoc Distributed Queries
EXEC sp_configure 'Ad Hoc Distributed Queries', 0;
RECONFIGURE;
 
-- Disable Remote Access (server-to-server)
EXEC sp_configure 'remote access', 0;
RECONFIGURE;
 
-- Disable Database Mail (unless needed)
EXEC sp_configure 'Database Mail XPs', 0;
RECONFIGURE;
 
-- 2. AUTHENTICATION HARDENING
 
-- Disable SA account
ALTER LOGIN sa DISABLE;
 
-- Or rename SA and set complex password
ALTER LOGIN sa WITH NAME = [disabled_sa_DO_NOT_USE];
ALTER LOGIN sa WITH PASSWORD = 'RandomComplexString#$%^789';
 
-- Enable failed login auditing
EXEC xp_instance_regwrite 
    N'HKEY_LOCAL_MACHINE', 
    N'Software\Microsoft\MSSQLServer\MSSQLServer', 
    N'AuditLevel', REG_DWORD, 3;
 
-- Require strong password policy
CREATE LOGIN AppAccount WITH PASSWORD = 'Complex!Password#2024'
    CHECK_POLICY = ON,
    CHECK_EXPIRATION = ON;
 
-- 3. NETWORK HARDENING
 
-- Force protocol encryption (via Configuration Manager)
-- Or use T-SQL to verify
EXEC sp_configure 'force encryption';
 
-- Hide instance from SQL Browser
-- (Configure in SQL Server Configuration Manager)
 
-- 4. PERMISSION HARDENING
 
-- Revoke public permissions on system procedures
REVOKE EXECUTE ON xp_fileexist FROM PUBLIC;
REVOKE EXECUTE ON xp_dirtree FROM PUBLIC;
REVOKE EXECUTE ON xp_fixeddrives FROM PUBLIC;
REVOKE EXECUTE ON xp_regread FROM PUBLIC;
 
-- Remove guest access from user databases
USE YourDatabase;
REVOKE CONNECT FROM guest;
 
-- 5. VERIFY HARDENING STATUS
 
-- Check dangerous configurations
SELECT name, value_in_use 
FROM sys.configurations 
WHERE name IN (
    'xp_cmdshell', 
    'clr enabled',
    'Ole Automation Procedures',
    'Ad Hoc Distributed Queries',
    'remote access',
    'Database Mail XPs'
);
 
-- Check login account status
SELECT name, is_disabled, is_policy_checked, is_expiration_checked
FROM sys.sql_logins;

Test Before Production

Hardening changes can break applications that rely on disabled features. Always test hardening configurations in a staging environment with full application integration testing before applying to production. Document which features are required by which applications.

Data Classification and Protection

Not all data requires the same level of protection. Data classification categorizes data by sensitivity, enabling appropriate security controls for each category. This prevents both under-protection (breach risk) and over-protection (operational friction and cost).

Classification Framework:

Data Classification Levels
Classification	Description	Examples	Required Controls
Public	Intended for public disclosure	Marketing materials, public APIs	Integrity protection, availability
Internal	Business information, not for external sharing	Internal documentation, non-sensitive analytics	Access control, basic auditing
Confidential	Sensitive business data requiring protection	Financial data, contracts, internal strategies	Encryption, RBAC, auditing, retention limits
Restricted/PII	Legally protected or highly sensitive	SSN, health records, payment data, credentials	Strong encryption, strict access, comprehensive audit, MFA
Secret/Regulated	Highest sensitivity, regulatory requirements	Trade secrets, classified data, covered data	Maximum controls, compartmentalization, monitoring

Implementing Classification:

Data classification isn't theoretical—it drives concrete security decisions:

Classification-Driven Security

•Schema Design — Separate classified data into dedicated schemas or databases with different access controls.
•Column-Level Encryption — Apply encryption specifically to Restricted/PII columns, not entire tables.
•Audit Policies — Audit all access to Confidential+ data; summarize access to Internal data.
•Access Approval — Require management approval for Confidential+ data access; self-service for Internal.
•Data Retention — Apply different retention and deletion policies by classification.
•Backup Handling — Require encrypted, geographically restricted backups for Restricted+ data.

data_classification.sql
PostgreSQL
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
64
65
66
67
68
69
70
71
-- ================================================
-- Implementing Data Classification in PostgreSQL
-- ================================================
 
-- 1. Create schemas per classification level
CREATE SCHEMA internal;    -- Internal business data
CREATE SCHEMA confidential; -- Sensitive business data  
CREATE SCHEMA restricted;  -- PII, payment data, regulated
 
-- 2. Set default permissions per schema
 
-- Internal: broad read access within organization
GRANT USAGE ON SCHEMA internal TO internal_users;
GRANT SELECT ON ALL TABLES IN SCHEMA internal TO internal_users;
ALTER DEFAULT PRIVILEGES IN SCHEMA internal 
    GRANT SELECT ON TABLES TO internal_users;
 
-- Confidential: specific role required
GRANT USAGE ON SCHEMA confidential TO confidential_access;
GRANT SELECT ON ALL TABLES IN SCHEMA confidential TO confidential_access;
ALTER DEFAULT PRIVILEGES IN SCHEMA confidential 
    GRANT SELECT ON TABLES TO confidential_access;
 
-- Restricted: highly controlled, individual grants
GRANT USAGE ON SCHEMA restricted TO restricted_admin;
-- NO default grants - explicit table-level grants only
 
-- 3. Use comments to document classification
COMMENT ON SCHEMA restricted IS 
    'CLASSIFICATION: RESTRICTED - Contains PII and payment data. 
     Access requires security training and management approval.';
 
COMMENT ON TABLE restricted.customers IS 
    'Contains SSN, DOB, financial information. 
     Encrypted columns: ssn, dob, bank_account.';
 
-- 4. Row-level security for compartmentalization
ALTER TABLE restricted.customers ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY department_isolation ON restricted.customers
    USING (department_id = current_setting('app.department_id')::INT);
 
-- 5. Column-level encryption for PII
-- (Encrypt at application layer, store encrypted value)
CREATE TABLE restricted.customers (
    id SERIAL PRIMARY KEY,
    name TEXT,
    email TEXT,
    ssn_encrypted BYTEA,        -- Encrypted SSN
    dob_encrypted BYTEA,        -- Encrypted DOB
    ssn_hash BYTEA,             -- For lookup (blind index)
    classification TEXT DEFAULT 'RESTRICTED',
    created_at TIMESTAMPTZ DEFAULT NOW()
);
 
-- 6. Query to find potentially misclassified data
-- Look for PII patterns in non-restricted schemas
SELECT 
    table_schema,
    table_name,
    column_name
FROM information_schema.columns
WHERE table_schema NOT IN ('restricted', 'pg_catalog', 'information_schema')
  AND (
    column_name ILIKE '%ssn%'
    OR column_name ILIKE '%social%security%'
    OR column_name ILIKE '%credit%card%'
    OR column_name ILIKE '%password%'
    OR column_name ILIKE '%dob%'
    OR column_name ILIKE '%date%birth%'
  );

Data Discovery Tools

Use automated data discovery tools to identify potentially sensitive data that may be misclassified. Tools like AWS Macie, Azure Purview, Microsoft Purview, and open-source options can scan databases for patterns matching PII, payment data, and other sensitive information.

Vulnerability Management and Patching

Database vulnerabilities are discovered regularly—vendor patches are released, security researchers publish exploits, and attackers actively scan for unpatched systems. Vulnerability management is the continuous process of identifying, evaluating, and remediating security vulnerabilities in your database environment.

Vulnerability Management Lifecycle:

Converting Mermaid diagram...

Patching Best Practices:

Database Patching Strategy

•Subscribe to security bulletins — Monitor vendor security announcements (Microsoft Security Updates, PostgreSQL Security List, Oracle Critical Patch Updates).
•Establish patching SLAs — Critical vulnerabilities: 72 hours. High: 1 week. Medium: 1 month. Low: next maintenance window.
•Test patches in staging — Never apply patches directly to production. Full regression testing is mandatory.
•Maintain rollback plans — Have tested rollback procedures for every patch. Some patches require downtime to revert.
•Use vulnerability scanners — Regular scans with tools like Nessus, Qualys, or Tenable identify unpatched systems.
•Track patch status — Maintain inventory of database versions and patch levels across all environments.
•Consider zero-day response — Have procedures for emergency patching when critical vulnerabilities are announced.

Common Database Vulnerability Categories
Category	Examples	Mitigation Approach
Authentication Bypass	Default credentials, weak authentication	Strong passwords, MFA, certificate auth
Privilege Escalation	Exploits gaining higher privileges	Least privilege, regular patching
SQL Injection	Application-layer injection attacks	Parameterized queries, WAF, input validation
Denial of Service	Resource exhaustion attacks	Rate limiting, connection limits, monitoring
Information Disclosure	Unintended data exposure	Access controls, encryption, error handling
Remote Code Execution	Critical exploits allowing code execution	Immediate patching, network segmentation

version_check.sql
Multi-Database
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
-- ================================================
-- Database Version and Patch Level Queries
-- ================================================
 
-- SQL Server
SELECT 
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    SERVERPROPERTY('ProductLevel') AS PatchLevel,
    SERVERPROPERTY('Edition') AS Edition,
    SERVERPROPERTY('ProductUpdateLevel') AS CumulativeUpdate,
    SERVERPROPERTY('ProductUpdateReference') AS KBNumber
;
 
-- Check for known vulnerability (example: CVE-2020-1472)
-- Compare version to patched version from Microsoft Security Bulletin
 
-- PostgreSQL
SELECT version();
 
-- Detailed version info
SHOW server_version;
SHOW server_version_num;  -- Numeric for comparison
 
-- MySQL
SELECT VERSION();
SHOW VARIABLES LIKE 'version%';
 
-- ================================================
-- Automated Version Inventory Script (PowerShell)
-- ================================================
 
-- $servers = Get-Content "C:\ServerList.txt"
-- $results = foreach ($server in $servers) {
--     $version = Invoke-Sqlcmd -ServerInstance $server -Query "SELECT @@VERSION" 
--     [PSCustomObject]@{
--         Server = $server
--         Version = $version.Column1
--         Timestamp = Get-Date
--     }
-- }
-- $results | Export-Csv "DatabaseVersionInventory.csv" -NoTypeInformation
 
-- ================================================
-- Check for Dangerous Configurations
-- ================================================
 
-- SQL Server: Check for xp_cmdshell (often exploited)
SELECT name, value_in_use 
FROM sys.configurations 
WHERE value_in_use = 1 
  AND name IN ('xp_cmdshell', 'Ad Hoc Distributed Queries', 'clr enabled');
 
-- PostgreSQL: Check for dangerous extensions
SELECT extname, extversion 
FROM pg_extension 
WHERE extname IN ('plpythonu', 'plperlu');  -- Untrusted languages
 
-- MySQL: Check for dangerous plugins
SELECT PLUGIN_NAME, PLUGIN_STATUS 
FROM INFORMATION_SCHEMA.PLUGINS 
WHERE PLUGIN_NAME LIKE '%auth%' OR PLUGIN_NAME LIKE '%udf%';

Don't Defer Critical Patches

Unpatched databases are a leading cause of breaches. The Equifax breach (143 million records) resulted from an unpatched Apache Struts vulnerability that had a patch available for months. Treat critical database vulnerabilities with the urgency they deserve—attackers begin scanning for vulnerable systems within hours of CVE publication.

Secure Development Practices

Many database security incidents originate in application code—SQL injection, credential exposure, and misconfigured connections. Secure development practices prevent vulnerabilities before they reach production.

Critical Application Security Practices:

Secure Database Access from Applications

•Parameterized Queries Always — Never concatenate user input into SQL strings. Use prepared statements, stored procedures with parameters.
•Credential Management — Never hardcode database credentials. Use secrets managers (Vault, AWS Secrets Manager), environment variables, or managed identity.
•Connection String Security — Encrypt connection strings in configuration. Don't log connection strings. Use integrated authentication where possible.
•Minimum Privilege Accounts — Application accounts should have only required permissions. No DDL rights, no access to unneeded tables.
•Connection Pooling — Use connection pools to prevent credential exposure through connection exhaustion attacks.
•Error Handling — Never expose database errors to users. Log details internally, show generic messages externally.
•ORM Security — Even with ORMs, review generated SQL. Avoid dynamic query building. Validate all inputs.

secure_coding.py
Python
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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
# ================================================
# Secure Database Access Patterns
# ================================================
 
import os
from contextlib import contextmanager
import psycopg2
from psycopg2 import pool
import logging
 
# Configure logging to NOT include sensitive data
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)
 
# ✅ GOOD: Credentials from environment/secrets manager
def get_database_url():
    """Retrieve database credentials from secure source."""
    # Option 1: Environment variables
    return os.environ.get('DATABASE_URL')
    
    # Option 2: Secrets manager (AWS example)
    # import boto3
    # client = boto3.client('secretsmanager')
    # secret = client.get_secret_value(SecretId='prod/db/credentials')
    # return secret['SecretString']
 
# ❌ BAD: Hardcoded credentials - NEVER DO THIS
# DATABASE_URL = "postgresql://admin:password123@db.example.com/prod"
 
# ✅ GOOD: Connection pooling
db_pool = pool.ThreadedConnectionPool(
    minconn=5,
    maxconn=20,
    dsn=get_database_url()
)
 
@contextmanager
def get_connection():
    """Get connection from pool with proper cleanup."""
    conn = db_pool.getconn()
    try:
        yield conn
    finally:
        db_pool.putconn(conn)
 
# ✅ GOOD: Parameterized queries
def get_user_by_email(email: str):
    """Safely query user by email using parameterized query."""
    with get_connection() as conn:
        with conn.cursor() as cur:
            # Parameters are safely escaped by the driver
            cur.execute(
                "SELECT id, name, email FROM users WHERE email = %s",
                (email,)  # Tuple of parameters
            )
            return cur.fetchone()
 
# ❌ BAD: SQL Injection vulnerability - NEVER DO THIS
def get_user_by_email_vulnerable(email: str):
    """VULNERABLE - Do not use!"""
    with get_connection() as conn:
        with conn.cursor() as cur:
            # Attacker can inject: ' OR '1'='1
            cur.execute(f"SELECT * FROM users WHERE email = '{email}'")
            return cur.fetchone()
 
# ✅ GOOD: Secure error handling
def create_user(name: str, email: str):
    """Create user with secure error handling."""
    try:
        with get_connection() as conn:
            with conn.cursor() as cur:
                cur.execute(
                    "INSERT INTO users (name, email) VALUES (%s, %s) RETURNING id",
                    (name, email)
                )
                user_id = cur.fetchone()[0]
                conn.commit()
                logger.info(f"Created user {user_id}")  # Don't log PII
                return user_id
    except psycopg2.IntegrityError as e:
        logger.error(f"Integrity error creating user: {type(e).__name__}")
        raise ValueError("User with this email already exists")
    except psycopg2.Error as e:
        # Log full error internally, but don't expose to user
        logger.error(f"Database error: {e}")
        raise RuntimeError("Unable to create user. Please try again.")
 
# ✅ GOOD: Using stored procedures for sensitive operations
def update_user_password(user_id: int, password_hash: str):
    """Use stored procedure for sensitive operations."""
    with get_connection() as conn:
        with conn.cursor() as cur:
            cur.execute(
                "CALL update_user_password(%s, %s)",
                (user_id, password_hash)
            )
            conn.commit()

Security Code Review

Include database security in code reviews. Check for: string concatenation in queries, credential handling, error message content, logging of sensitive data, connection/transaction handling, and input validation. Use static analysis tools (Semgrep, CodeQL, SonarQube) to automate detection of common vulnerabilities.

Database Security Incident Response

Despite best efforts, security incidents occur. Incident response is the structured approach to detecting, containing, investigating, and recovering from security events. Having documented procedures before an incident occurs dramatically improves response effectiveness.

Incident Response Phases:

Database Incident Response Phases
Phase	Activities	Key Actions
Preparation	Plans, training, tools ready	Documented runbooks, contact lists, access to logs, backup verification
Detection	Identify potential incidents	SIEM alerts, anomaly detection, user reports, threat intelligence
Containment	Limit damage spread	Isolate affected systems, revoke compromised credentials, block attacker IPs
Eradication	Remove threat	Patch vulnerabilities, remove backdoors, change all potentially compromised credentials
Recovery	Restore normal operations	Restore from known-good backups, validate data integrity, gradual re-enablement
Lessons Learned	Improve for future	Post-incident review, update procedures, implement additional controls

Database-Specific Incident Scenarios:

Common Database Security Incidents

•Credential Compromise — Attacker obtains valid database credentials. Response: Immediately reset password, review access logs, check for data exfiltration, rotate all credentials that may have been exposed together.
•SQL Injection Attack — Application vulnerability exploited. Response: Block attacking IPs, patch application, review audit logs for data accessed, check for persistence mechanisms.
•Data Exfiltration — Large data extract detected. Response: Identify scope of data accessed, legal/compliance notification, forensic preservation, customer notification if required.
•Ransomware/Encryption — Database files encrypted by malware. Response: Isolate system, restore from offline backups, investigate entry vector, DO NOT pay ransom.
•Insider Threat — Authorized user exfiltrating data. Response: Revoke access, legal/HR involvement, forensic copy of access logs, preserve evidence chain of custody.

incident_response.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
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
64
65
66
67
68
69
70
71
72
73
74
-- ================================================
-- Incident Response Queries
-- ================================================
 
-- SCENARIO: Suspected credential compromise for user 'john.doe'
 
-- 1. Immediately disable the account
ALTER LOGIN [john.doe] DISABLE;
 
-- 2. Kill all active sessions
DECLARE @kill_sessions NVARCHAR(MAX) = '';
SELECT @kill_sessions = @kill_sessions + 'KILL ' + CAST(session_id AS NVARCHAR) + ';'
FROM sys.dm_exec_sessions
WHERE login_name = 'john.doe';
EXEC sp_executesql @kill_sessions;
 
-- 3. Review recent activity from this account
SELECT 
    event_time,
    action_id,
    succeeded,
    database_name,
    object_name,
    statement,
    client_ip,
    application_name
FROM sys.fn_get_audit_file('C:\AuditLogs\*.sqlaudit', DEFAULT, DEFAULT)
WHERE server_principal_name = 'john.doe'
  AND event_time > DATEADD(DAY, -7, GETDATE())
ORDER BY event_time DESC;
 
-- 4. Look for data exfiltration patterns
SELECT 
    database_name,
    object_name,
    COUNT(*) AS access_count,
    SUM(CAST(affected_rows AS BIGINT)) AS total_rows
FROM sys.fn_get_audit_file('C:\AuditLogs\*.sqlaudit', DEFAULT, DEFAULT)
WHERE server_principal_name = 'john.doe'
  AND action_id = 'SL'  -- SELECT
  AND event_time > DATEADD(DAY, -7, GETDATE())
GROUP BY database_name, object_name
ORDER BY total_rows DESC;
 
-- 5. Check for any privilege escalation
SELECT *
FROM sys.fn_get_audit_file('C:\AuditLogs\*.sqlaudit', DEFAULT, DEFAULT)
WHERE server_principal_name = 'john.doe'
  AND action_id IN ('G', 'ADGR', 'CR')  -- Grant, Add Group, Create
ORDER BY event_time DESC;
 
-- 6. Identify other sessions from same client IP
SELECT DISTINCT 
    server_principal_name,
    client_ip,
    COUNT(*) AS session_count
FROM sys.fn_get_audit_file('C:\AuditLogs\*.sqlaudit', DEFAULT, DEFAULT)
WHERE client_ip IN (
    SELECT DISTINCT client_ip 
    FROM sys.fn_get_audit_file('C:\AuditLogs\*.sqlaudit', DEFAULT, DEFAULT)
    WHERE server_principal_name = 'john.doe'
)
GROUP BY server_principal_name, client_ip;
 
-- 7. Export evidence for forensic analysis
-- (Run from command line)
-- bcp "SELECT * FROM sys.fn_get_audit_file('C:\AuditLogs\*.sqlaudit', DEFAULT, DEFAULT) 
--      WHERE server_principal_name = 'john.doe'" 
--      queryout "C:\Evidence\john_doe_audit.csv" -c -T -S localhost
 
-- 8. After investigation, if account was victim (not attacker):
-- Reset password with new complex value
-- ALTER LOGIN [john.doe] WITH PASSWORD = 'NewComplex!Password#2024' MUST_CHANGE;
-- ALTER LOGIN [john.doe] ENABLE;

Preserve Evidence

During incident response, maintain chain of custody for potential legal proceedings. Create forensic copies of audit logs before analysis. Document all response actions with timestamps. Don't modify evidence systems—work on copies. Involve legal counsel early for incidents that may involve law enforcement or litigation.

Continuous Security Improvement

Database security isn't a project with an end date—it's an ongoing program requiring continuous attention and improvement. Threats evolve, new vulnerabilities are discovered, business requirements change, and staff turnover introduces knowledge gaps.

Building a Security Program:

Continuous Security Activities

•Regular Access Reviews (Quarterly) — Audit user permissions against current job requirements. Remove unnecessary access. Validate service account permissions.
•Vulnerability Scanning (Weekly/Monthly) — Automated scans to identify misconfigurations, missing patches, and known vulnerabilities.
•Penetration Testing (Annually) — Engage third-party security testers to attempt database compromise and identify weaknesses.
•Security Training (Ongoing) — Ensure DBAs, developers, and administrators understand security practices and emerging threats.
•Configuration Baseline Monitoring — Detect drift from secure configuration baselines. Alert on unauthorized changes.
•Threat Intelligence Integration — Monitor for new vulnerabilities affecting your database versions. Subscribe to vendor security lists.
•Post-Incident Reviews — After any security event, identify root causes and implement improvements to prevent recurrence.

Security Metrics to Track
Metric	Target	Frequency	Purpose
Patch currency	95% patched within SLA	Weekly	Vulnerability exposure
Failed login rate	Baseline + anomaly detection	Daily	Attack detection
Privileged account count	Minimize over time	Monthly	Least privilege
Mean time to patch (critical)	<72 hours	Per patch	Response capability
Access reviews completed	100%	Quarterly	Access hygiene
Audit log retention	100% compliant	Monthly	Compliance
Encryption coverage	100% for classified data	Quarterly	Data protection

Security Documentation:

Maintain current documentation for:

Essential Security Documentation

•Database inventory — All databases, versions, locations, owners, classifications.
•Security architecture — Network diagrams, authentication flows, encryption configuration.
•Access control matrix — Who has access to what and why.
•Hardening standards — Expected configuration for each database platform.
•Incident response playbooks — Step-by-step procedures for common incident types.
•Recovery procedures — Tested backup and restore documentation.
•Change management — Process for security-related changes and approvals.

Security as Code

Where possible, define security configurations as code (Infrastructure as Code, Database as Code). Version-controlled security configurations enable review, rollback, consistency across environments, and audit trails. Tools like Terraform, Ansible, and database-specific migration frameworks support this approach.

Summary: Security Best Practices Mastery

Database security is a comprehensive discipline requiring technical controls, operational practices, and organizational commitment. Let's consolidate the essential knowledge:

Key Takeaways

•Defense in depth protects against single failures — Layer network, access, encryption, and detection controls so one breach doesn't mean total compromise.
•Hardening reduces attack surface — Disable unused features, secure defaults, apply CIS benchmarks. Test before production deployment.
•Data classification drives security investment — Apply strongest controls to most sensitive data. Not everything needs maximum protection.
•Vulnerability management is continuous — Subscribe to security bulletins, patch promptly, scan regularly, maintain version inventory.
•Secure development prevents application-layer attacks — Parameterized queries, credential management, error handling, code review.
•Incident response plans save critical time — Document procedures before incidents. Practice response. Preserve evidence.
•Security requires continuous improvement — Regular reviews, testing, training, and metrics drive ongoing security posture improvement.

Module Conclusion:

You've now completed a comprehensive exploration of database security—from authentication fundamentals through authorization, encryption, auditing, and holistic best practices. This knowledge forms the foundation for protecting your organization's most valuable digital assets: its data.

Remember that security is not a destination but a journey. Threats evolve, technologies change, and vigilance must be constant. Apply these principles consistently, stay current with emerging threats, and build security into every aspect of your database operations.

Module Complete

Congratulations! You've mastered database security fundamentals. You understand authentication mechanisms, authorization models, encryption strategies, auditing frameworks, and security best practices. You can implement comprehensive security controls across major database platforms and build sustainable security programs that protect organizational data assets.

5 / 5

Loading learning content...

Database Management SystemsDatabase Security

Database Security

LevelAdvanced

Duration90 mins

TopicDatabase Security

5 / 5

Security Best Practices

From Controls to Culture

What You Will Learn

Defense in Depth

Database Security Layers:

Converting Mermaid diagram...

Layer-Specific Controls:

Defense in Depth Control Matrix
Layer	Primary Controls	Failure Mode Protection
Network	Firewalls, segmentation, VPN, IP whitelisting	Limits attack surface; prevents direct database access
Authentication	Strong passwords, MFA, certificates, SSO	Ensures only verified identities connect
Authorization	RBAC, row-level security, least privilege	Limits damage from compromised credentials
Encryption (Transit)	TLS 1.2+, certificate validation	Protects against network interception
Encryption (Rest)	TDE, column encryption, backup encryption	Protects against physical/storage theft
Application	Parameterized queries, input validation	Prevents SQL injection, application-layer attacks
Auditing	Comprehensive logging, SIEM integration	Detects attacks, enables investigation
Monitoring	Anomaly detection, performance monitoring	Identifies unusual patterns indicating attack

Assume Breach Mindset

Database Server Hardening

Universal Hardening Principles:

Core Hardening Actions

•Remove/disable unused features — Linked servers, XP_CMDSHELL, CLR integration, external scripts—disable anything not explicitly needed.
•Change default ports — While security through obscurity isn't sufficient alone, non-standard ports reduce automated scanning attacks.
•Rename/disable default accounts — SA, postgres, root accounts are primary targets. Rename, disable, or use complex credentials.
•Apply CIS Benchmarks — Center for Internet Security provides detailed hardening guides for each database platform.
•Restrict network binding — Listen only on required interfaces; don't bind to 0.0.0.0 if local-only access is sufficient.
•Implement connection limits — Prevent denial of service through connection exhaustion.
•Secure file permissions — Database files, configuration files, and log files should be readable only by the database service account.

hardening_checklist.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
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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
-- ================================================
-- SQL Server Hardening Checklist
-- Based on CIS Benchmark for SQL Server
-- ================================================
 
-- 1. DISABLE DANGEROUS FEATURES
 
-- Disable xp_cmdshell (command execution)
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;
 
-- Disable CLR integration (unless needed)
EXEC sp_configure 'clr enabled', 0;
RECONFIGURE;
 
-- Disable OLE Automation
EXEC sp_configure 'Ole Automation Procedures', 0;
RECONFIGURE;
 
-- Disable Ad Hoc Distributed Queries
EXEC sp_configure 'Ad Hoc Distributed Queries', 0;
RECONFIGURE;
 
-- Disable Remote Access (server-to-server)
EXEC sp_configure 'remote access', 0;
RECONFIGURE;
 
-- Disable Database Mail (unless needed)
EXEC sp_configure 'Database Mail XPs', 0;
RECONFIGURE;
 
-- 2. AUTHENTICATION HARDENING
 
-- Disable SA account
ALTER LOGIN sa DISABLE;
 
-- Or rename SA and set complex password
ALTER LOGIN sa WITH NAME = [disabled_sa_DO_NOT_USE];
ALTER LOGIN sa WITH PASSWORD = 'RandomComplexString#$%^789';
 
-- Enable failed login auditing
EXEC xp_instance_regwrite 
    N'HKEY_LOCAL_MACHINE', 
    N'Software\Microsoft\MSSQLServer\MSSQLServer', 
    N'AuditLevel', REG_DWORD, 3;
 
-- Require strong password policy
CREATE LOGIN AppAccount WITH PASSWORD = 'Complex!Password#2024'
    CHECK_POLICY = ON,
    CHECK_EXPIRATION = ON;
 
-- 3. NETWORK HARDENING
 
-- Force protocol encryption (via Configuration Manager)
-- Or use T-SQL to verify
EXEC sp_configure 'force encryption';
 
-- Hide instance from SQL Browser
-- (Configure in SQL Server Configuration Manager)
 
-- 4. PERMISSION HARDENING
 
-- Revoke public permissions on system procedures
REVOKE EXECUTE ON xp_fileexist FROM PUBLIC;
REVOKE EXECUTE ON xp_dirtree FROM PUBLIC;
REVOKE EXECUTE ON xp_fixeddrives FROM PUBLIC;
REVOKE EXECUTE ON xp_regread FROM PUBLIC;
 
-- Remove guest access from user databases
USE YourDatabase;
REVOKE CONNECT FROM guest;
 
-- 5. VERIFY HARDENING STATUS
 
-- Check dangerous configurations
SELECT name, value_in_use 
FROM sys.configurations 
WHERE name IN (
    'xp_cmdshell', 
    'clr enabled',
    'Ole Automation Procedures',
    'Ad Hoc Distributed Queries',
    'remote access',
    'Database Mail XPs'
);
 
-- Check login account status
SELECT name, is_disabled, is_policy_checked, is_expiration_checked
FROM sys.sql_logins;

Test Before Production

Data Classification and Protection

Classification Framework:

Data Classification Levels
Classification	Description	Examples	Required Controls
Public	Intended for public disclosure	Marketing materials, public APIs	Integrity protection, availability
Internal	Business information, not for external sharing	Internal documentation, non-sensitive analytics	Access control, basic auditing
Confidential	Sensitive business data requiring protection	Financial data, contracts, internal strategies	Encryption, RBAC, auditing, retention limits
Restricted/PII	Legally protected or highly sensitive	SSN, health records, payment data, credentials	Strong encryption, strict access, comprehensive audit, MFA
Secret/Regulated	Highest sensitivity, regulatory requirements	Trade secrets, classified data, covered data	Maximum controls, compartmentalization, monitoring

Implementing Classification:

Data classification isn't theoretical—it drives concrete security decisions:

Classification-Driven Security

•Schema Design — Separate classified data into dedicated schemas or databases with different access controls.
•Column-Level Encryption — Apply encryption specifically to Restricted/PII columns, not entire tables.
•Audit Policies — Audit all access to Confidential+ data; summarize access to Internal data.
•Access Approval — Require management approval for Confidential+ data access; self-service for Internal.
•Data Retention — Apply different retention and deletion policies by classification.
•Backup Handling — Require encrypted, geographically restricted backups for Restricted+ data.

data_classification.sql
PostgreSQL
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
64
65
66
67
68
69
70
71
-- ================================================
-- Implementing Data Classification in PostgreSQL
-- ================================================
 
-- 1. Create schemas per classification level
CREATE SCHEMA internal;    -- Internal business data
CREATE SCHEMA confidential; -- Sensitive business data  
CREATE SCHEMA restricted;  -- PII, payment data, regulated
 
-- 2. Set default permissions per schema
 
-- Internal: broad read access within organization
GRANT USAGE ON SCHEMA internal TO internal_users;
GRANT SELECT ON ALL TABLES IN SCHEMA internal TO internal_users;
ALTER DEFAULT PRIVILEGES IN SCHEMA internal 
    GRANT SELECT ON TABLES TO internal_users;
 
-- Confidential: specific role required
GRANT USAGE ON SCHEMA confidential TO confidential_access;
GRANT SELECT ON ALL TABLES IN SCHEMA confidential TO confidential_access;
ALTER DEFAULT PRIVILEGES IN SCHEMA confidential 
    GRANT SELECT ON TABLES TO confidential_access;
 
-- Restricted: highly controlled, individual grants
GRANT USAGE ON SCHEMA restricted TO restricted_admin;
-- NO default grants - explicit table-level grants only
 
-- 3. Use comments to document classification
COMMENT ON SCHEMA restricted IS 
    'CLASSIFICATION: RESTRICTED - Contains PII and payment data. 
     Access requires security training and management approval.';
 
COMMENT ON TABLE restricted.customers IS 
    'Contains SSN, DOB, financial information. 
     Encrypted columns: ssn, dob, bank_account.';
 
-- 4. Row-level security for compartmentalization
ALTER TABLE restricted.customers ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY department_isolation ON restricted.customers
    USING (department_id = current_setting('app.department_id')::INT);
 
-- 5. Column-level encryption for PII
-- (Encrypt at application layer, store encrypted value)
CREATE TABLE restricted.customers (
    id SERIAL PRIMARY KEY,
    name TEXT,
    email TEXT,
    ssn_encrypted BYTEA,        -- Encrypted SSN
    dob_encrypted BYTEA,        -- Encrypted DOB
    ssn_hash BYTEA,             -- For lookup (blind index)
    classification TEXT DEFAULT 'RESTRICTED',
    created_at TIMESTAMPTZ DEFAULT NOW()
);
 
-- 6. Query to find potentially misclassified data
-- Look for PII patterns in non-restricted schemas
SELECT 
    table_schema,
    table_name,
    column_name
FROM information_schema.columns
WHERE table_schema NOT IN ('restricted', 'pg_catalog', 'information_schema')
  AND (
    column_name ILIKE '%ssn%'
    OR column_name ILIKE '%social%security%'
    OR column_name ILIKE '%credit%card%'
    OR column_name ILIKE '%password%'
    OR column_name ILIKE '%dob%'
    OR column_name ILIKE '%date%birth%'
  );

Data Discovery Tools

Vulnerability Management and Patching

Vulnerability Management Lifecycle:

Converting Mermaid diagram...

Patching Best Practices:

Database Patching Strategy

•Subscribe to security bulletins — Monitor vendor security announcements (Microsoft Security Updates, PostgreSQL Security List, Oracle Critical Patch Updates).
•Establish patching SLAs — Critical vulnerabilities: 72 hours. High: 1 week. Medium: 1 month. Low: next maintenance window.
•Test patches in staging — Never apply patches directly to production. Full regression testing is mandatory.
•Maintain rollback plans — Have tested rollback procedures for every patch. Some patches require downtime to revert.
•Use vulnerability scanners — Regular scans with tools like Nessus, Qualys, or Tenable identify unpatched systems.
•Track patch status — Maintain inventory of database versions and patch levels across all environments.
•Consider zero-day response — Have procedures for emergency patching when critical vulnerabilities are announced.

Common Database Vulnerability Categories
Category	Examples	Mitigation Approach
Authentication Bypass	Default credentials, weak authentication	Strong passwords, MFA, certificate auth
Privilege Escalation	Exploits gaining higher privileges	Least privilege, regular patching
SQL Injection	Application-layer injection attacks	Parameterized queries, WAF, input validation
Denial of Service	Resource exhaustion attacks	Rate limiting, connection limits, monitoring
Information Disclosure	Unintended data exposure	Access controls, encryption, error handling
Remote Code Execution	Critical exploits allowing code execution	Immediate patching, network segmentation

version_check.sql
Multi-Database
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
-- ================================================
-- Database Version and Patch Level Queries
-- ================================================
 
-- SQL Server
SELECT 
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    SERVERPROPERTY('ProductLevel') AS PatchLevel,
    SERVERPROPERTY('Edition') AS Edition,
    SERVERPROPERTY('ProductUpdateLevel') AS CumulativeUpdate,
    SERVERPROPERTY('ProductUpdateReference') AS KBNumber
;
 
-- Check for known vulnerability (example: CVE-2020-1472)
-- Compare version to patched version from Microsoft Security Bulletin
 
-- PostgreSQL
SELECT version();
 
-- Detailed version info
SHOW server_version;
SHOW server_version_num;  -- Numeric for comparison
 
-- MySQL
SELECT VERSION();
SHOW VARIABLES LIKE 'version%';
 
-- ================================================
-- Automated Version Inventory Script (PowerShell)
-- ================================================
 
-- $servers = Get-Content "C:\ServerList.txt"
-- $results = foreach ($server in $servers) {
--     $version = Invoke-Sqlcmd -ServerInstance $server -Query "SELECT @@VERSION" 
--     [PSCustomObject]@{
--         Server = $server
--         Version = $version.Column1
--         Timestamp = Get-Date
--     }
-- }
-- $results | Export-Csv "DatabaseVersionInventory.csv" -NoTypeInformation
 
-- ================================================
-- Check for Dangerous Configurations
-- ================================================
 
-- SQL Server: Check for xp_cmdshell (often exploited)
SELECT name, value_in_use 
FROM sys.configurations 
WHERE value_in_use = 1 
  AND name IN ('xp_cmdshell', 'Ad Hoc Distributed Queries', 'clr enabled');
 
-- PostgreSQL: Check for dangerous extensions
SELECT extname, extversion 
FROM pg_extension 
WHERE extname IN ('plpythonu', 'plperlu');  -- Untrusted languages
 
-- MySQL: Check for dangerous plugins
SELECT PLUGIN_NAME, PLUGIN_STATUS 
FROM INFORMATION_SCHEMA.PLUGINS 
WHERE PLUGIN_NAME LIKE '%auth%' OR PLUGIN_NAME LIKE '%udf%';

Don't Defer Critical Patches

Secure Development Practices

Critical Application Security Practices:

Secure Database Access from Applications

•Parameterized Queries Always — Never concatenate user input into SQL strings. Use prepared statements, stored procedures with parameters.
•Credential Management — Never hardcode database credentials. Use secrets managers (Vault, AWS Secrets Manager), environment variables, or managed identity.
•Connection String Security — Encrypt connection strings in configuration. Don't log connection strings. Use integrated authentication where possible.
•Minimum Privilege Accounts — Application accounts should have only required permissions. No DDL rights, no access to unneeded tables.
•Connection Pooling — Use connection pools to prevent credential exposure through connection exhaustion attacks.
•Error Handling — Never expose database errors to users. Log details internally, show generic messages externally.
•ORM Security — Even with ORMs, review generated SQL. Avoid dynamic query building. Validate all inputs.

secure_coding.py
Python
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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
# ================================================
# Secure Database Access Patterns
# ================================================
 
import os
from contextlib import contextmanager
import psycopg2
from psycopg2 import pool
import logging
 
# Configure logging to NOT include sensitive data
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)
 
# ✅ GOOD: Credentials from environment/secrets manager
def get_database_url():
    """Retrieve database credentials from secure source."""
    # Option 1: Environment variables
    return os.environ.get('DATABASE_URL')
    
    # Option 2: Secrets manager (AWS example)
    # import boto3
    # client = boto3.client('secretsmanager')
    # secret = client.get_secret_value(SecretId='prod/db/credentials')
    # return secret['SecretString']
 
# ❌ BAD: Hardcoded credentials - NEVER DO THIS
# DATABASE_URL = "postgresql://admin:password123@db.example.com/prod"
 
# ✅ GOOD: Connection pooling
db_pool = pool.ThreadedConnectionPool(
    minconn=5,
    maxconn=20,
    dsn=get_database_url()
)
 
@contextmanager
def get_connection():
    """Get connection from pool with proper cleanup."""
    conn = db_pool.getconn()
    try:
        yield conn
    finally:
        db_pool.putconn(conn)
 
# ✅ GOOD: Parameterized queries
def get_user_by_email(email: str):
    """Safely query user by email using parameterized query."""
    with get_connection() as conn:
        with conn.cursor() as cur:
            # Parameters are safely escaped by the driver
            cur.execute(
                "SELECT id, name, email FROM users WHERE email = %s",
                (email,)  # Tuple of parameters
            )
            return cur.fetchone()
 
# ❌ BAD: SQL Injection vulnerability - NEVER DO THIS
def get_user_by_email_vulnerable(email: str):
    """VULNERABLE - Do not use!"""
    with get_connection() as conn:
        with conn.cursor() as cur:
            # Attacker can inject: ' OR '1'='1
            cur.execute(f"SELECT * FROM users WHERE email = '{email}'")
            return cur.fetchone()
 
# ✅ GOOD: Secure error handling
def create_user(name: str, email: str):
    """Create user with secure error handling."""
    try:
        with get_connection() as conn:
            with conn.cursor() as cur:
                cur.execute(
                    "INSERT INTO users (name, email) VALUES (%s, %s) RETURNING id",
                    (name, email)
                )
                user_id = cur.fetchone()[0]
                conn.commit()
                logger.info(f"Created user {user_id}")  # Don't log PII
                return user_id
    except psycopg2.IntegrityError as e:
        logger.error(f"Integrity error creating user: {type(e).__name__}")
        raise ValueError("User with this email already exists")
    except psycopg2.Error as e:
        # Log full error internally, but don't expose to user
        logger.error(f"Database error: {e}")
        raise RuntimeError("Unable to create user. Please try again.")
 
# ✅ GOOD: Using stored procedures for sensitive operations
def update_user_password(user_id: int, password_hash: str):
    """Use stored procedure for sensitive operations."""
    with get_connection() as conn:
        with conn.cursor() as cur:
            cur.execute(
                "CALL update_user_password(%s, %s)",
                (user_id, password_hash)
            )
            conn.commit()

Security Code Review

Database Security Incident Response

Incident Response Phases:

Database Incident Response Phases
Phase	Activities	Key Actions
Preparation	Plans, training, tools ready	Documented runbooks, contact lists, access to logs, backup verification
Detection	Identify potential incidents	SIEM alerts, anomaly detection, user reports, threat intelligence
Containment	Limit damage spread	Isolate affected systems, revoke compromised credentials, block attacker IPs
Eradication	Remove threat	Patch vulnerabilities, remove backdoors, change all potentially compromised credentials
Recovery	Restore normal operations	Restore from known-good backups, validate data integrity, gradual re-enablement
Lessons Learned	Improve for future	Post-incident review, update procedures, implement additional controls

Database-Specific Incident Scenarios:

Common Database Security Incidents

•Credential Compromise — Attacker obtains valid database credentials. Response: Immediately reset password, review access logs, check for data exfiltration, rotate all credentials that may have been exposed together.
•SQL Injection Attack — Application vulnerability exploited. Response: Block attacking IPs, patch application, review audit logs for data accessed, check for persistence mechanisms.
•Data Exfiltration — Large data extract detected. Response: Identify scope of data accessed, legal/compliance notification, forensic preservation, customer notification if required.
•Ransomware/Encryption — Database files encrypted by malware. Response: Isolate system, restore from offline backups, investigate entry vector, DO NOT pay ransom.
•Insider Threat — Authorized user exfiltrating data. Response: Revoke access, legal/HR involvement, forensic copy of access logs, preserve evidence chain of custody.

incident_response.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
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
64
65
66
67
68
69
70
71
72
73
74
-- ================================================
-- Incident Response Queries
-- ================================================
 
-- SCENARIO: Suspected credential compromise for user 'john.doe'
 
-- 1. Immediately disable the account
ALTER LOGIN [john.doe] DISABLE;
 
-- 2. Kill all active sessions
DECLARE @kill_sessions NVARCHAR(MAX) = '';
SELECT @kill_sessions = @kill_sessions + 'KILL ' + CAST(session_id AS NVARCHAR) + ';'
FROM sys.dm_exec_sessions
WHERE login_name = 'john.doe';
EXEC sp_executesql @kill_sessions;
 
-- 3. Review recent activity from this account
SELECT 
    event_time,
    action_id,
    succeeded,
    database_name,
    object_name,
    statement,
    client_ip,
    application_name
FROM sys.fn_get_audit_file('C:\AuditLogs\*.sqlaudit', DEFAULT, DEFAULT)
WHERE server_principal_name = 'john.doe'
  AND event_time > DATEADD(DAY, -7, GETDATE())
ORDER BY event_time DESC;
 
-- 4. Look for data exfiltration patterns
SELECT 
    database_name,
    object_name,
    COUNT(*) AS access_count,
    SUM(CAST(affected_rows AS BIGINT)) AS total_rows
FROM sys.fn_get_audit_file('C:\AuditLogs\*.sqlaudit', DEFAULT, DEFAULT)
WHERE server_principal_name = 'john.doe'
  AND action_id = 'SL'  -- SELECT
  AND event_time > DATEADD(DAY, -7, GETDATE())
GROUP BY database_name, object_name
ORDER BY total_rows DESC;
 
-- 5. Check for any privilege escalation
SELECT *
FROM sys.fn_get_audit_file('C:\AuditLogs\*.sqlaudit', DEFAULT, DEFAULT)
WHERE server_principal_name = 'john.doe'
  AND action_id IN ('G', 'ADGR', 'CR')  -- Grant, Add Group, Create
ORDER BY event_time DESC;
 
-- 6. Identify other sessions from same client IP
SELECT DISTINCT 
    server_principal_name,
    client_ip,
    COUNT(*) AS session_count
FROM sys.fn_get_audit_file('C:\AuditLogs\*.sqlaudit', DEFAULT, DEFAULT)
WHERE client_ip IN (
    SELECT DISTINCT client_ip 
    FROM sys.fn_get_audit_file('C:\AuditLogs\*.sqlaudit', DEFAULT, DEFAULT)
    WHERE server_principal_name = 'john.doe'
)
GROUP BY server_principal_name, client_ip;
 
-- 7. Export evidence for forensic analysis
-- (Run from command line)
-- bcp "SELECT * FROM sys.fn_get_audit_file('C:\AuditLogs\*.sqlaudit', DEFAULT, DEFAULT) 
--      WHERE server_principal_name = 'john.doe'" 
--      queryout "C:\Evidence\john_doe_audit.csv" -c -T -S localhost
 
-- 8. After investigation, if account was victim (not attacker):
-- Reset password with new complex value
-- ALTER LOGIN [john.doe] WITH PASSWORD = 'NewComplex!Password#2024' MUST_CHANGE;
-- ALTER LOGIN [john.doe] ENABLE;

Preserve Evidence

Continuous Security Improvement

Building a Security Program:

Continuous Security Activities

•Regular Access Reviews (Quarterly) — Audit user permissions against current job requirements. Remove unnecessary access. Validate service account permissions.
•Vulnerability Scanning (Weekly/Monthly) — Automated scans to identify misconfigurations, missing patches, and known vulnerabilities.
•Penetration Testing (Annually) — Engage third-party security testers to attempt database compromise and identify weaknesses.
•Security Training (Ongoing) — Ensure DBAs, developers, and administrators understand security practices and emerging threats.
•Configuration Baseline Monitoring — Detect drift from secure configuration baselines. Alert on unauthorized changes.
•Threat Intelligence Integration — Monitor for new vulnerabilities affecting your database versions. Subscribe to vendor security lists.
•Post-Incident Reviews — After any security event, identify root causes and implement improvements to prevent recurrence.

Security Metrics to Track
Metric	Target	Frequency	Purpose
Patch currency	95% patched within SLA	Weekly	Vulnerability exposure
Failed login rate	Baseline + anomaly detection	Daily	Attack detection
Privileged account count	Minimize over time	Monthly	Least privilege
Mean time to patch (critical)	<72 hours	Per patch	Response capability
Access reviews completed	100%	Quarterly	Access hygiene
Audit log retention	100% compliant	Monthly	Compliance
Encryption coverage	100% for classified data	Quarterly	Data protection

Security Documentation:

Maintain current documentation for:

Essential Security Documentation

•Database inventory — All databases, versions, locations, owners, classifications.
•Security architecture — Network diagrams, authentication flows, encryption configuration.
•Access control matrix — Who has access to what and why.
•Hardening standards — Expected configuration for each database platform.
•Incident response playbooks — Step-by-step procedures for common incident types.
•Recovery procedures — Tested backup and restore documentation.
•Change management — Process for security-related changes and approvals.

Security as Code

Summary: Security Best Practices Mastery

Database security is a comprehensive discipline requiring technical controls, operational practices, and organizational commitment. Let's consolidate the essential knowledge:

Key Takeaways

•Defense in depth protects against single failures — Layer network, access, encryption, and detection controls so one breach doesn't mean total compromise.
•Hardening reduces attack surface — Disable unused features, secure defaults, apply CIS benchmarks. Test before production deployment.
•Data classification drives security investment — Apply strongest controls to most sensitive data. Not everything needs maximum protection.
•Vulnerability management is continuous — Subscribe to security bulletins, patch promptly, scan regularly, maintain version inventory.
•Secure development prevents application-layer attacks — Parameterized queries, credential management, error handling, code review.
•Incident response plans save critical time — Document procedures before incidents. Practice response. Preserve evidence.
•Security requires continuous improvement — Regular reviews, testing, training, and metrics drive ongoing security posture improvement.

Module Conclusion:

Module Complete

5 / 5