nw-security-and-governance

# 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
-- 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.

```sql
-- 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)

```python
# 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