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

Introduction

In the first three parts of this series, we looked at a method to set up easily-manageable security using two principles:

1. Double abstraction
• Put all users into Windows groups
• Put all permissions into custom database (or server, for SQL Server 2012 and up) roles
• Add the groups to the custom roles
2. Principle of least privilege
• Only grant the minimum privileges needed to get the job done

If you've followed along and implemented or at least thought about implementing this strategy, you may be wondering how to enforce it.  After all, if the option still exists for some DBA to add an individual login to your instance or individual users to a database or grant specific permissions to specific users that are not custom database roles, you want to know if it's happening.

SQL Server Auditing to the rescue!

SQL Server Auditing

Quoting from BOL: SQL Server Audit (Database Engine)

Auditing an instance of the SQL Server Database Engine or an individual database involves tracking and logging events that occur on the Database Engine. SQL Server audit lets you create server audits, which can contain server audit specifications for server level events, and database audit specifications for database level events. Audited events can be written to the event logs or to audit files.

Setting up Auditing is easy.  Let's set up an audit to catch anyone violating the DA-PLP  regime we've established. In SSMS you open the Security/Audits sub tree.  There you create a server audit for this purpose, a server audit specification hooked up to the server audit and enable them both.  Graphically:

In T-SQL

USE [master] GO CREATE SERVER AUDIT [Audit-20150828-112959] TO FILE ( FILEPATH = N'C:\temp' ,MAXSIZE = 0 MB ,MAX_ROLLOVER_FILES = 2147483647 ,RESERVE_DISK_SPACE = OFF ) WITH ( QUEUE_DELAY = 1000 ,ON_FAILURE = CONTINUE ) GO

Now the Specification:

Or, in T-SQL:

USE [master] GO CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20150828-113306] FOR SERVER AUDIT [Audit-20150828-112959] ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP), ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP) GO

Now, simply enable the audit and audit specification (right-click on them, select Enable) and you're ready to catch violaters!  Note, that, if you chose to go with a file as I did, you can use the system function sys.fn_get_audit_file to view it in tabular format.  There you will see anyone (with their login id) who changed anything in these areas and you can easily sort out legitimate changes from rule breakers.

Summary

With this article, we wrap up this series.  We've discussed two important principles: Double Abstraction and the Principle of Least Privilege that together make our lives easier for managing security in SQL Server. 

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!