Friday, August 28, 2015

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


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. 

No comments:

Post a Comment