Double Abstraction + Principle of Least Priviledge = Easy-to-manage Security Part I

Introduction

Securing a SQL Server instance is critical to every business.  Whether to comply with regulatory requirements, pass external audits with flying colors or to ensure proper restrictions on access to sensitive or financial data, it is imperative that database resources are only accessed by those with the need to do so and in a manner matching requirements.  This is the first in a series of articles on managing Security in SQL Server.

Challenges

Your database is accessed by a wide variety of people with different needs.  You need to be able to secure database assets in a  manageable way.  All too often we see implementations where every user has serveradmin rights and/or database owner (dbo) rights on one or more databases in some SQL Server instance.  This is a recipe for disaster!  Even the most conscientious user will make mistakes and the careless, disgruntled and even criminally-oriented users can wreak havoc, destroy valuable data or even siphon it off to sell to the highest bidder.

We know that SQL Server has the tools to GRANT or DENY access to its objects but all too often get frustrated managing user-by-user.  This is where the double-abstraction approach shines.

Double Abstraction

The first part of Double Abstraction (DA for short) can be found by reversing the letters in the acronym.  AD, or Active Directory allows us to create groups so that birds of a feather can flock together -- that is, so that users with similar requirements can be managed as a unit by placing them into a suitable group.  So, the first step, is to identify the sorts of actions different groups might need.  A reporting team needs to be able to read some tables from one or more databases; an ETL job needs to be able to load tables; a web application needs to be able to query and possibly update certain data; auditors need to be able to monitor the security of the database; administrators at various levels of responsibility need to manage the server, create databases, backup/restore and so on.  Each of these functions (and probably others, depending on your requirements) deserves its own group.

The second part of DA exploits database roles and server roles.  SQL Server comes with many out of the box, but for our purposes it makes sense to define custom database roles and (in SQL Server 2012) custom server roles.  Each role would be given just the privileges corresponding to the actual needs of the various groups.  For example, access to certain schemas, execute access on some stored procedures, the ability to create databases, tables and other objects, the ability to view all of these privileges and so on.  We set up these roles using the Principle of Least Privilege: each role has just the privileges actually required and no more.

To bring these together -- AD groups and DB/Server roles -- we use the ALTER ROLE ADD MEMBER command (or sp_addrolemember system stored procedure).  Say that we have created an AD group Corp\SalesReports and a database role in the Sales database called SalesReaders.  Then we can:

1. Add domain users (and other groups) to the Corp\SalesReports group
2. Add the AD group to the SalesReaders role like this:

ALTER ROLE SalesReaders Add Member [Corp\SalesReports]

Next time, we'll dig deeper and add more examples.

Summary

Managing security in SQL Server is made easier with the principle of Double Abstraction: Add users to groups; create roles in the database to cover the functions; add the groups to roles.