Securing SQL Server, Second Edition DBAs Defending the Database (Peter A. Carter)（Z-Library）

【One-Line Pitch】 A practical, defense-in-depth guide for SQL Server professionals—DBAs, developers, and architects—who want to protect their databases from modern threats, from threat modeling and access control to encryption, auditing, and compliance. 【Book Arc】 - **Opening (~0%–9%)**: Introduces the book’s mission—defending SQL Server against attacks—and begins with threat analysis using a fictional web application (CarterSecureSafe). Covers identifying assets, understanding attack vectors, and introducing compliance considerations like GDPR. - **Early (~9%–28%)**: Dives into the SQL Server security model: principals (logins, users, roles), permissions (GRANT/DENY/REVOKE), and practical issues like orphaned users during failover. Includes creating server roles and contained database users. - **Early–Middle (~28%–38%)**: Focuses on SQL Server Audit—configuring server and database audit specifications, action groups for tracking schema changes, logins, and data access. Shows how to create custom audit events for specific business rules. - **Middle (~38%–53%)**: Explores data-level security: row-level security (filter and block predicates), dynamic data masking (functions like default, partial, email, random), and encryption basics—symmetric keys, TDE, and Always Encrypted. - **Late (~53%–end)**: Covers security metadata (finding effective permissions, code signing, audit/encryption metadata) and likely wraps up with credential protection and countermeasures against common attacks like SQL injection and backup theft. 【Key Takeaways】 - **Threat modeling is the first line of defense** (Early): Use STRIDE to systematically identify spoofing, tampering, repudiation, information disclosure, DoS, and elevation of privilege risks. Document threats in a risk register and prioritize based on DREAD ratings. - **GRANT, DENY, and REVOKE have distinct semantics** (Early): DENY always overrides GRANT, and REVOKE only removes direct permissions—not those inherited from roles. Understanding this prevents accidental privilege escalation or lockouts. - **SQL Logins cause orphaned users on failover** (Early): Unlike Windows principals, SQL logins have instance-specific SIDs. Use `ALTER USER ... WITH LOGIN` to remap users after moving databases to another instance. - **SQL Server Audit enables non-repudiation** (Early–Middle): Use action groups like `SCHEMA_OBJECT_ACCESS_GROUP` and `DATABASE_PRINCIPAL_CHANGE_GROUP` to track who did what. Custom audit events via `USER_DEFINED_AUDIT_GROUP` capture business-specific actions. - **Row-level security uses two predicate types** (Middle): Filter predicates silently hide rows (affecting SELECT/UPDATE/DELETE), while block predicates raise errors on violations. Use SCHEMABINDING for security functions to simplify permissions. - **Dynamic data masking pushes logic to the database** (Middle): Functions like `partial`, `email`, and `random` obfuscate sensitive data (e.g., credit card numbers) at query time, reducing middle-tier overhead and improving reusability. - **Encryption is layered, not monolithic** (Middle): Symmetric keys are the weakest form; TDE protects data at rest, while Always Encrypted protects data in use. Choose based on threat model and performance trade-offs. - **Security metadata helps verify enforcement** (Late): Query catalog views to find effective permissions, audit configuration, and encryption status—ensuring policies are actually applied and not just documented. 【Reading Tips】 - **Skim the threat analysis chapter** if you already know STRIDE/DREAD; focus on the CarterSecureSafe example to see how to apply it to your own systems. - **Deep-read the security model chapter** (Chapter 2) for GRANT/DENY/REVOKE nuances and orphaned user handling—these are common real-world pitfalls. - **Use the audit chapter as a reference** rather than reading end-to-end; bookmark the action group tables and custom audit event examples for when you need to implement specific tracking. - **Pay attention to row-level security and dynamic data masking** if you deal with multi-tenant apps or PII; the predicate and masking function examples are directly reusable. - **Skip the encryption chapter if you’re not implementing TDE/Always Encrypted yet**, but revisit it when planning data-at-rest protection—the metadata sections are useful for verification. 【Coverage Limits】 This guide covers the book’s core security topics (threat analysis, access control, auditing, data-level security, encryption, metadata) but does not detail every script or advanced scenario (e.g., full Resource Governor discussion, which the book itself defers to another title).