THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Lara Rubbelke

Interesting Things in the World of SQL Server

You want to grant someone permissions to do WHAT?!?!

Have you ever heard of these types of requests?  True story! I have had each of these and many more:

  • A customer needed to grant a business user the rights to issue a KILL command – without giving them sysadmin or CONTROL SERVER. 
  • A customer wanted to grant a user the rights to update a job – just one job – without any other changes to the job.
  • There was the case where a customer wanted to give a set of junior admins the rights to unlock a set of logins – without granting any additional rights to alter logins. 
  • And of course, there are many, many customers who are facing internal and external regulations that dictate the DBAs should not have rights to view sensitive data.  Period. 

Managing security is never easy, and these additional requirements can cause a lot of distress to those who are trying to provide the right level of security while protecting their data, databases, and server infrastructure.  Grant too many privileges, and you open up your environment to a host of potential issues.  Grant too few privileges, and the users and administrators are unable to do their jobs. 

Enter the Separation of Duties Framework.  The Separation of Duties Framework was originally designed to address the separation of DBA from sysadmin, but this framework may also be used to temporarily grant users elevation of privileges in a controlled and auditable environment.  The SQL Server Separation of Duties Framework will ease the process of setting up a restrictive environment while providing a predefined set of processes a DBA may use to manage restricted instances and sensitive databases. The Separation of Duties Framework is designed to empower the DBA team (or users) to be productive and responsive with processes that are auditable, secure, and extensible while being easy to implement and manage. 

The Separation of Duties Framework was originally released in November 2010. Brian Davis (blog and twitter) and I just released v2.0 of the framework.  The framework will create database roles, signed stored procedures, and the securables needed to support the environment.  The framework is set up in the following steps:

  1. Define the roles and tasks.  Each organization will have different regulations that stipulate the security boundaries for individuals and groups. Prior to installing the Separation of Duties Framework, it is necessary to define the types of roles that will engage with SQL Server and the tasks that each role is permitted to execute.
  2. Create folders to represent the defined roles.  Create folders in a Procedures directory that will mimic the security roles you identified in the previous step. Remember that these folders are hierarchical, and each folder level will inherit the privileges of the parent folders. The Separation of Duties Framework will create roles based on the folder structure under the Procedures directory.
  3. Add stored procedures sql files to the folders created in the previous step. Create procedures or use existing example procedures available in the framework that represent the tasks each role is allowed to execute.  Place these in the appropriate folder which represents the users who are permitted to execute the task.  The Separation of Duties Framework install script will create each procedure, sign the procedures with a certificate, and grant EXECUTE permissions to the appropriate roles. 
  4. Execute the PowerShell install script. 
  5. Place the appropriate users and groups into the newly created Database Roles.

More details on the installation process are available with the download.  Brian Davis and I will also be following up with some additional blogs with details on the framework over the next few weeks. 

Published Sunday, January 23, 2011 9:32 PM by Lara Rubbelke



Thomas Rushton said:

I've had the Kill one before.  Used it as the basis for a talk at the inaugural meeting of SQL Server South West (in the UK).  For added fun, had to be SQL 2000-compatible.

September 4, 2013 2:31 PM

Gordon Everest said:

I am so proud of you, every time I see something like this.  Great contribution.  P.S. say hi to Bill for me.

September 6, 2013 12:17 AM
New Comments to this post are disabled
Privacy Statement