Security and Governance

Defense-in-Depth Security Model

Layered security, each layer provides independent protection:

Encryption at rest (TDE) — protects against physical media theft
Encryption in transit (TLS/SSL) — protects against network interception
Access control (RBAC/ABAC) — enforces least privilege
SQL injection prevention — protects against application-layer attacks
Audit logging — accountability and forensic capability

Encryption at Rest (TDE)

Encrypts DB files on disk without application changes. Encrypts data pages before writing, decrypts on read into memory. AES 128/256-bit symmetric encryption. Transparent to applications.

Key Hierarchy (SQL Server)

Service Master Key (Windows DPAPI) -> 2. Database Master Key -> 3. Certificate -> 4. Database Encryption Key (DEK)

Implementation

-- SQL Server TDE (key hierarchy: Service Master Key -> DB Master Key -> Certificate -> DEK)
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;
ALTER DATABASE [YourDB] SET ENCRYPTION ON;
-- PostgreSQL: pgcrypto for column-level, full TDE in v17+ | Oracle: ALTER SYSTEM SET ENCRYPTION KEY

Best Practices

Back up certificates/keys immediately — loss means unrecoverable data
Store backups in separate secure location | Implement key rotation policy
Use customer-managed keys (BYOK) for regulatory compliance
Monitor performance impact (typically 3-5% overhead)
TDE does not protect data in memory — use column-level encryption for highly sensitive fields

Encryption in Transit (TLS)

Configuration Checklist

TLS 1.2+ enforced (disable 1.0/1.1)
Valid certificates from trusted CA (not self-signed in prod)
Certificate rotation policy established
Client cert auth for server-to-server connections
Connection string enforces SSL (sslmode=require in PostgreSQL)

Access Control

RBAC (Role-Based)

Assign permissions to roles, roles to users. Standard in all major DBs.

-- PostgreSQL RBAC: create roles with specific grants, assign to users
CREATE ROLE app_readonly; GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;
CREATE ROLE app_readwrite; GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO app_readwrite;
GRANT app_readonly TO reporting_user; GRANT app_readwrite TO application_user;

ABAC (Attribute-Based)

Access decisions based on attributes of user, resource, environment. More flexible than RBAC for complex scenarios (multi-tenant, data classification).

Least Privilege

Application accounts: DML only (SELECT/INSERT/UPDATE/DELETE)
Migration accounts: DDL + DML, time-limited
Admin accounts: full access, MFA required, audit logged
Reporting accounts: SELECT only on specific schemas/views

SQL Injection Prevention (OWASP)

Parameterized Queries (Primary Defense)

# VULNERABLE - string concatenation (SQL injection risk)
query = f"SELECT * FROM users WHERE name = '{user_input}'"

# SAFE - parameterized (all languages: Python %s, Java ?, C# @param, Node.js $1)
cursor.execute("SELECT * FROM users WHERE id = %s AND status = %s", (user_id, 'active'))

Additional Defenses (OWASP)

Input validation: whitelist allowed chars/formats | Stored procedures: reduce direct SQL exposure | Least privilege: no DDL for app accounts | WAF rules | Never expose DB error messages to end users

Data Governance

Data Lineage

Track data from source through transformations to consumption:

Technical lineage: Column-level mapping through ETL/ELT pipelines
Business lineage: Business meaning and ownership
Tools: Apache Atlas, OpenLineage, Marquez, dbt lineage, AWS Glue, Azure Purview

Purpose: Regulatory compliance (GDPR Article 30) | Impact analysis (downstream schema change effects) | Root cause analysis (bad data origin) | Audit trails

Data Quality Dimensions

Dimension	Definition	Example Check
Accuracy	Correctly represents real-world entities	Email format validation
Completeness	Required fields populated	NOT NULL checks, completeness %
Consistency	Same data across systems agrees	Cross-system reconciliation
Timeliness	Current and available when needed	Freshness SLAs
Uniqueness	No unintended duplicates	Duplicate detection on business keys
Validity	Conforms to defined rules/formats	Range checks, enum validation

Master Data Management (MDM)

Establish single source of truth for core entities (customer, product, location) | Define golden record resolution rules | Implement data stewardship roles | Use MDM platform or reference data services

Compliance Frameworks

GDPR (EU)

Right to erasure (Art. 17): Hard-delete capability including backups/replicas
Data portability (Art. 20): Export in machine-readable format (JSON, CSV)
Consent management: Track per processing purpose with timestamps
Data minimization: Collect/retain only necessary personal data
Privacy by design: Pseudonymization, encryption, access controls from initial design
Breach notification: 72-hour requirement, implement detection/alerting

CCPA (California)

Right to know (disclose collected data) | Right to delete | Right to opt-out of data sale | Non-discrimination regardless of privacy choices

HIPAA (Health Data)

PHI encryption at rest and in transit | Role-based access with minimum necessary standard | Audit all PHI access | Business associate agreements for third-party processors

Implementation Checklist

Data classification schema (public, internal, confidential, restricted)
Retention policies per classification
Deletion procedures (including backups, replicas, caches)
Consent tracking mechanism
Audit logging for sensitive data access
Data processing records (GDPR Art. 30)
Breach response procedure documented and tested
Privacy impact assessment for new processing activities

Backup and Recovery

3-2-1 Rule

3 copies of data | 2 different storage types | 1 copy offsite

Backup Types

Full: Complete DB copy, baseline for recovery
Incremental: Changed blocks since last backup, fast/small, recovery requires chain
Differential: Changes since last full, faster recovery than incremental chain
Transaction log: Enables point-in-time recovery (PITR)

Recovery Validation

Test recovery regularly (monthly minimum) | Document RTO and RPO | Encrypt backup files | Store encryption keys separately from backups

nw-security-and-governance