Security Best Practices

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;

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%'
  );

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%';

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()

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;