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

Introduction

In the previous two posts, we discussed the concept of double-abstraction security for SQL Server.  The motivation is that we do not want to be administering permissions at an individual user to individual object basis.  This does not scale well and if you're anything like me, you'll make mistakes with that approach.

The double-abstraction approach makes it much easier.  

1. Put users into groups that reflect their business needs
2. Create database roles to hold the permissions to fit those needs
3. Add groups to roles

In this section, we're going to put all three things together.  We'll be using the same database as in parts I and II:

Double Abstraction Part I
Double Abstraction Part II

Set up users and groups

Before we get to SQL, we need to set up Windows users and groups (Step 1 above).  In Computer Management, we add the user like this:

To keep things simple, we checked both "User cannot change password" and "Password never expires" (not recommended for actual use!).  Next, we'll create a group and add the user to the group:

Now that we have a user and group to work with, let's add a new login to SQL Server for the group:

USE [master] GO CREATE LOGIN [JERRY-PC\SecureGroupReadOnly] FROM WINDOWS WITH DEFAULT_DATABASE=[SecureDB] GO

Next, we need to add the group to the database as a user:

USE [SecureDB] GO CREATE USER [SecureGroupReadOnly] FOR LOGIN [JERRY-PC\SecureGroupReadOnly] WITH DEFAULT_SCHEMA=[SecureSchema] GO

We need a new database role for this read-only group to use. We'll give it only SELECT and EXECUTE permissions in the SecureSchema:

-- Create the new role USE [SecureDB] GO CREATE ROLE [SecureRoleReadOnly] AUTHORIZATION [SecureRole] GO USE [SecureDB] GO -- Add the restricted group to the role ALTER ROLE [SecureRoleReadOnly] ADD MEMBER [SecureGroupReadOnly] GO USE [SecureDB] GO -- Grant EXECUTE and SELECT permissions GRANT EXECUTE ON SCHEMA::[SecureSchema] TO [SecureRoleReadOnly] GO USE [SecureDB] GO GRANT SELECT ON SCHEMA::[SecureSchema] TO [SecureRoleReadOnly] GO

That's it!  Now, all we have to do to manage access is to add/remove users from the Windows (or Active Directory) group!