Separation of Duties for DBA's
Someone recently asked me about the principle of separation of duties (aka segregation of duties) as it applies to SQL Server DBA's, and I thought that would make a good topic for this blog, so here goes...
The idea of separating duties in general is to prevent a single person from being able to complete a task, such as a bank vault combination having 6 numbers, and giving 3 to one employee and 3 to another.1
Separation of duties is not designed into the SQL Server DBA role itself, as a senior SQL Server DBA will have the SQL Server sysadmin built-in server role, which gives a single DBA complete control over SQL Server. However, there are still several ways we can apply separation of duties to the management of SQL Server.
1. You can prevent operating system administrators from gaining easy access to SQL Server. For example, SQL Server 2008 no longer includes a "BUILTIN\Administrators" group that used to give every local Windows administrator full sysadmin privileges in SQL Server by default. (See DoD Database STIG, SRR DG0116.)
2. You can split data among different instances of SQL Server and not allow DBA's from one instance to have permissions on the other. Multiple instances can be on the same server or multiple servers, and multiple servers provides an option for also splitting Windows privileges between two sets of operating system administrators.
3. You can create applications that enforce separation of duties in the external management of data.
4. You can prevent SQL Server DBA's from having local administrator privileges on Windows. This option provides very little value, because if a DBA is not trustworthy they can do far more harm with their DBA privileges than with Windows privileges, and it will interfere with their ability to manage folder permissions and registry edits, which are needed for tasks such as backup file management and installing service packs. If you choose to implement this option, the DBA's will need top-priority responses from Windows administrators to make O\S level changes for them.
5. Install and implement the SQL Server Separation of Duties Framework v2.0. See https://sqlserversod.codeplex.com/. (Thanks to SQLDragon for pointing this out!)
Footnotes:
1. I'll mention here that two other security principles may limit the effectiveness of separating duties, namely, job rotation and mandatory vacation. Both of these ideas will proliferate permissions if they aren't managed very carefully, and proliferating permissions can overcome the goal sought by separating duties.