Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

SQL Server separation of duties

MSSQLTips author Matteo Lorini By:   |   Read Comments (7)   |   Related Tips: More > Security
Problem

In many organizations there is a need to segment what DBAs can do in certain environments. By default most DBAs are made sysadmins, but this gives them full control over the SQL Server instance. To meet certain regulatory guidelines this is not allowed, because DBAs have access to sensitive data. By default there is not a way to segment out certain DBA tasks vs. viewing user data, so in this tip we look at a Separation of Duty Framework that was developed to allow you to achieve this.

Solution

Separation of duty, as a security principle, has as its primary objective the prevention of fraud and errors. In the face of internal and external regulatory requirements like PCI and SAS 70, organizations are realizing that the security and privileges of the DBA must be minimized for certain types of data that is categorized as sensitive.

SQL Server provides a rich set of security features that allow customers to satisfy their security goals. Everything from encrypting sensitive data to recording all activity (Audit) within a particular database is easily attainable with the features introduced in 2008. However, one topic that many struggle with is in the area of Separation of Duties or Role Separation. The subject frequently comes up when facing regulatory compliance, usually in the form of "How do I prevent my administrators from seeing my data?" While SQL Server does not provide a packaged solution to facilitate Separation of Duties, it does provide functionality that allows for Separation of Duties to be achieved.

The goal of this tip is to share with the community, Lara Rubbelke's idea on how we can achieve Separation of Duty within SQL Server.

At the 2010 PASS Summit, Lara Rubbelke (Data Platform Technology Specialist for Microsoft) presented a SQL Server Separation of Duty Framework that can be used to achieve Separation of Duty. The basic idea consists of delegating certain DBA tasks that require sysadmin rights to a Junior DBA or a non-DBA without granting him/her sysadmin rights.

The Separation of Duty framework can be downloaded for free from http://sqlserversod.codeplex.com/releases/view/55355, and it comes with the following pre-defined procedures:

separation of duty within sql server

For example; sp_who2 requires the VIEW SERVER STATE permission on the server to see all executing sessions on the instance of SQL Server. If you do not have this permission, the user sees only their current session. However, using the Separation of Duty Framework a regular user is able to run a command similar to sp_who2 to see all sessions like a sysadmin, without having to grant the VIEW SERVER STATE permission directly. This methodology therefore allows organizations to create a set of standard procedures that allows a non-sysadmin to run, but still preventing the viewing of sensitive data.

Before installing the Separation of Duty Framework, please read the documentation that comes with it.

When installing, the installation script will ask for the following values:

ServerName The name of the instance where the SQL Server Separation of Duties Framework will be installed.
DatabaseName The name of the database which will be used to manage the signed procedures. This may be an existing database or a new database. It is highly recommended not to use an application database, and to use a database that is specifically designed for DBA management.
KeyBackupFileDestination The directory where the keys will be backed up during the installation.
MasterDMKPassword The Master database Database Master Key password. This will only be used if a Database Master Key does not yet exist in the Master database. If a Database Master Key exists in the Master Database, this password is disregarded.
PKBackupPassword The password used to encrypt the private key when backed up.
UserDMKPassword The password for the Database Master Key that will be created in the database specified in DatabaseName. If a Database Master Key exists in this database, the password is disregarded.

Using PowerShell, run ".\InstallScript" to run the installation.

using powershell to install the separation of duty framework

Once the installation is complete, the script will create the following procedures in the database that you specified in the installation process. In my case, I chose DBASoD as the database name.

the script will create the following procedures in the database

To test, I created a SQL user named DBA1 with the following permissions. (Note: DBA1 is not a sysadmin)

to test, create a sql server user that is not a sysadmin

Let's connect to SQL Server using the newly created user DB1 and run: sp_who2 (I only get my session information)

connect to sql server using the newly created user

Now, let's run: dba.sysproc_sp_who2 (I get back all information this account would see if it were a sysadmin)

this should get you closer to your goal of separating duties in your sql server enviroment

As you can see this is pretty straightforward to setup and hopefully it gets you closer to your goal of separating duties in your SQL Server environment.

Next Steps


Last Update: 2/11/2011


About the author
MSSQLTips author Matteo Lorini
Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Tuesday, June 11, 2013 - 5:38:46 PM - rich Read The Tip

Hello,

Is there a method for implementing 'Separation of Duties', where specific columns (i.e. SSN) are 'masked' so that a sysadmin cannot read the actual value?


Saturday, March 16, 2013 - 9:29:44 AM - Matteo Read The Tip

Sorry I have not tested it for SQL 2012.


Friday, March 15, 2013 - 11:53:14 AM - Patrick Read The Tip

Hi,

 

Does your Separation of Duty Framework works with SQL Server 2012 ?

 

Thanks for your response.

 

Best Regards

 

Patrick


Monday, February 21, 2011 - 10:39:31 AM - Brian Davis Read The Tip

Separation of Duties Framework V2 was released on 1/23/2011 and contains support for tiers within your security architecture.  The latest version can be found here http://sqlserversod.codeplex.com/.  Lara and I are continuing to work on this project and are working on the next release.

Muahmmad - The framework should work the same in SQL Server 2005.  Both documentation and samples are included in the download and should give you a starting point.


Sunday, February 20, 2011 - 5:43:33 PM - Lara Rubbelke Read The Tip

Thursday, February 17, 2011 - 7:09:43 AM - Matteo Read The Tip

Thanks for your comment. I have not tried it with SQL 2005 however, I think it works the same as in SQL 2008


Thursday, February 17, 2011 - 6:45:19 AM - Muahmmad Azim Read The Tip

Hi Lorini

The topic is very interesting and worth implementable

Please give some guide lines for implementing it in SQL Server 2005 DBs.




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.