Database Security Skill — Database Architect
Overview
This skill provides comprehensive guidance for implementing database security controls. It covers encryption, access control, audit logging, compliance, data masking, row-level security, and database hardening following CISSP and CDP certifications.
When to Use
Use this skill when:
- Implementing database encryption
- Setting up access control and RBAC
- Creating audit logging for databases
- Ensuring compliance (GDPR, HIPAA, PCI-DSS)
- Implementing data masking
- Setting up row-level security
- Encrypting data at rest
- Encrypting data in transit
- Implementing database hardening
- Setting up database monitoring
- Creating database security policies
- Implementing database firewalls
- Setting up database activity monitoring
- Implementing secret management
- Setting up database backups securely
- Implementing database patch management
- Creating database security documentation
- Implementing database vulnerability scanning
- Setting up database security alerts
- Implementing database access review
- Creating database security training
- Implementing database security testing
- Setting up database security governance
- Implementing database security architecture
Do NOT use this skill when:
- Writing application business logic (use domain-driven skill)
- Designing database schemas (use database-design skill)
- Writing API endpoint specifications (use api-design skill)
- Performing security audits (use security-auditor skill)
- Writing SQL queries (use sql-best-practices skill)
- Designing multi-page website layouts (use design-system skill)
- Analyzing deployment configurations (use deployment skill)
- Writing documentation (use documentation-best-practices skill)
Database Security Controls
Encryption
┌─────────────────────────────────────────────────────────────┐ │ Database Encryption │ ├─────────────────────────────────────────────────────────────┤ │ Encryption at Rest: │ │ - Database-level encryption │ │ - Table-level encryption │ │ - Column-level encryption │ │ - Filesystem-level encryption │ │ │ │ Encryption in Transit: │ │ - TLS for database connections │ │ - SSL certificates │ │ - Certificate validation │ │ - Certificate pinning │ │ │ │ Encryption Keys: │ │ - Key management service │ │ - Key rotation │ │ - Key access control │ │ - Key audit logging │ └─────────────────────────────────────────────────────────────┘
Access Control
┌─────────────────────────────────────────────────────────────┐ │ Access Control Models │ ├─────────────────────────────────────────────────────────────┤ │ Role-Based Access Control (RBAC): │ │ - Roles: admin, developer, readonly, auditor │ │ - Permissions: CRUD, DDL, DCL │ │ - Role hierarchy │ │ │ │ Row-Level Security (RLS): │ │ - Per-row access control │ │ - Tenant isolation │ │ - Data segmentation │ │ │ │ Column-Level Security: │ │ - Column masking │ │ - Column encryption │ │ - Column access control │ └─────────────────────────────────────────────────────────────┘
Audit Logging
Audit Log Schema
sql-- Audit log table CREATE TABLE audit_log ( id SERIAL PRIMARY KEY, user_id UUID, action VARCHAR(50), table_name VARCHAR(100), record_id UUID, old_values JSONB, new_values JSONB, ip_address INET, user_agent TEXT, timestamp TIMESTAMPTZ DEFAULT NOW() ); -- Audit trigger function CREATE OR REPLACE FUNCTION audit_trigger() RETURNS TRIGGER AS $$ BEGIN IF TG_OP = 'INSERT' THEN INSERT INTO audit_log (user_id, action, table_name, record_id, new_values) VALUES (current_user_id(), 'INSERT', TG_TABLE_NAME, NEW.id, to_jsonb(NEW)); RETURN NEW; ELSIF TG_OP = 'UPDATE' THEN INSERT INTO audit_log (user_id, action, table_name, record_id, old_values, new_values) VALUES (current_user_id(), 'UPDATE', TG_TABLE_NAME, NEW.id, to_jsonb(OLD), to_jsonb(NEW)); RETURN NEW; ELSIF TG_OP = 'DELETE' THEN INSERT INTO audit_log (user_id, action, table_name, record_id, old_values) VALUES (current_user_id(), 'DELETE', TG_TABLE_NAME, OLD.id, to_jsonb(OLD)); RETURN OLD; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql;
Audit Log Retention
┌─────────────────────────────────────────────────────────────┐ │ Audit Log Retention │ ├─────────────────────────────────────────────────────────────┤ │ Compliance Requirements: │ │ - GDPR: 1 year │ │ - HIPAA: 6 years │ │ - PCI-DSS: 1 year │ │ - SOX: 7 years │ │ │ │ Retention Strategy: │ │ - Hot storage: 90 days (fast access) │ │ - Warm storage: 1 year (compressed) │ │ - Cold storage: 7 years (archived) │ │ - Destruction: Secure deletion with audit │ └─────────────────────────────────────────────────────────────┘
Data Masking
Dynamic Data Masking
sql-- Mask credit card numbers ALTER TABLE customers ALTER COLUMN credit_card ADD MASKED FUNCTION = 'partial(4,"XXXX-XXXX-XXXX-",4)'; -- Mask email addresses ALTER TABLE customers ALTER COLUMN email ADD MASKED FUNCTION = 'email()'; -- Mask phone numbers ALTER TABLE customers ALTER COLUMN phone ADD MASKED FUNCTION = 'partial(3,"XXX-XXX-",4)'; -- Mask social security numbers ALTER TABLE customers ALTER COLUMN ssn ADD MASKED FUNCTION = 'partial(0,"XXX-XX-",4)';
Static Data Masking
sql-- Mask data in development environment UPDATE users SET email = CONCAT('user', id, '@example.com'), phone = CONCAT('555-555-', LPAD(id::text, 4, '0')), ssn = CONCAT('XXX-XX-', LPAD(id::text, 4, '0'));
Compliance Frameworks
GDPR Compliance
┌─────────────────────────────────────────────────────────────┐ │ GDPR Compliance │ ├─────────────────────────────────────────────────────────────┤ │ Data Protection Requirements: │ │ - Data minimization │ │ - Purpose limitation │ │ - Data accuracy │ │ - Storage limitation │ │ - Integrity and confidentiality │ │ - Accountability │ │ │ │ Rights Implementation: │ │ - Right to access │ │ - Right to rectification │ │ - Right to erasure (right to be forgotten) │ │ - Right to restriction of processing │ │ - Right to data portability │ │ - Right to object │ │ │ │ Documentation: │ │ - Data protection impact assessments │ │ - Records of processing activities │ │ - Data processing agreements │ └─────────────────────────────────────────────────────────────┘
PCI-DSS Compliance
┌─────────────────────────────────────────────────────────────┐ │ PCI-DSS Compliance │ ├─────────────────────────────────────────────────────────────┤ │ Requirement 3: Protect stored cardholder data │ │ - Encrypt stored cardholder data │ │ - Use strong cryptography │ │ - Secure key management │ │ │ │ Requirement 7: Restrict access to cardholder data │ │ - Role-based access control │ │ - Least privilege principle │ │ - Access review and certification │ │ │ │ Requirement 10: Track and monitor all access │ │ - Audit logging │ │ - Log management │ │ - Log review and analysis │ └─────────────────────────────────────────────────────────────┘
Database Hardening
Hardening Checklist
┌─────────────────────────────────────────────────────────────┐ │ Database Hardening Checklist │ ├─────────────────────────────────────────────────────────────┤ │ Configuration: │ │ [ ] Remove default accounts │ │ [ ] Change default passwords │ │ [ ] Disable unused features │ │ [ ] Configure secure settings │ │ [ ] Enable logging │ │ │ │ Network: │ │ [ ] Use firewalls │ │ [ ] Enable TLS │ │ [ ] Restrict IP access │ │ [ ] Use VPN for remote access │ │ │ │ Authentication: │ │ [ ] Use strong passwords │ │ [ ] Enable MFA │ │ [ ] Use certificate authentication │ │ [ ] Implement account lockout │ │ │ │ Monitoring: │ │ [ ] Enable audit logging │ │ [ ] Set up alerts │ │ [ ] Monitor for anomalies │ │ [ ] Regular security reviews │ └─────────────────────────────────────────────────────────────┘
Real-World Impact
Before this skill:
- Unencrypted sensitive data
- Weak access controls
- No audit logging
- Compliance violations
- Data breaches
After this skill:
- Encrypted data at rest and in transit
- Proper access controls
- Comprehensive audit logging
- Full compliance
- Secure database infrastructure
Cross-References
database-design- For database schema designsecurity-auditor- For security auditsdevops-pipeline- For secure database deployments
