/nw-security-and-governance

name: nw-security-and-governance
description: Database security (encryption, access control, injection prevention), data governance (lineage, quality, MDM), and compliance frameworks (GDPR, CCPA, HIPAA)
user-invocable: false
disable-model-invocation: true

Security and Governance

Defense-in-Depth Security Model

Layered security, each layer provides independent protection: 1. **Encryption at rest** (TDE) — protects against physical media theft 2. **Encryption in transit** (TLS/SSL) — protects against network interception 3. **Access control** (RBAC/ABAC) — enforces least privilege 4. **SQL injection prevention** — protects against application-layer attacks 5. **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)

1. 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)
• [ ] Ret