dbi services Blog
Welcome to the dbi services Blog! This blog focuses on IT infrastructure - featuring news, troubleshooting, and tips & tricks. It covers database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on the postings!
SQL Server 2014 : SELECT ALL USERS SECURABLES & DB admins
Microsoft will introduce four new security permissions in SQL Server 2014. One of them called SELECT ALL USERS SECURABLES is the subject of this post. As explained by Microsoft SQL Server 2014 will allow a database administrator to manage data without seeing sensitive data or personally identifiable information. We can achieve a greater compliance but we must take care what is said because we could be wrong about the terms “manage without seeing sensitive data”. Let me explain.
If we have a naïve approach we could misunderstand this terms and think “oh great … with SQL Server 2014 we will prevent a database administrator to see all data (sensitive or not) but in fact I think (personal point of view) on reflection it is not exactly the goal of this server permission.
Basically “SELECT ALL USERS SECURABLES” permission is designed, when granted, to allow a user to view data in all databases he can connect. For auditing purposes, the new permission is very interesting and it can be also used to prevent someone to read. Furthermore, SQL Server 2005 introduced the concept of “securable” that can be the server. One of the new server permission provided is CONTROL SERVER often associated with sysadmin fixed role because it will grant full server level permissions and automatically grant full access to all databases but unlike sysadmin fixed role it allow a more granular approach to granting and denying access to individual securable (login in our case) without bypassing the permission check algorithm. Thus, applying security principles to DBA team staff begins by avoiding to use sysadmin role and to prefer CONTROL SERVER permission. At this point I know, we could claim this is not that easy as CONTROL SERVER has multiple caveats but let me demonstrate by a practical example where we can use the new server permission “SELECT ALL USERS SECURABLES” to restrict database administrators to view data in all databases.
First we can create a server role introduced with SQL Server 2012 for the database administrator team:
Then we create a database administrator login dba1:
We add the login as member of the dba1 fixed role :
Now it’s time to play with both the server permission CONTROL SERVER and SELECT ALL USER SECURABLES to prevent a database administrator to read data in all databases.
We can now ensure it works by connecting with the login dba1.
In my configuration I have a database named SensitiveDB with a table named SensitiveTable.
And so on … At this point we can view all objects in the database and their definitions. Now if we try to read data in the table SensitiveTable or from the view V_SensitiveTable :
As expected we don’t have the permissions to read data in the database or from anywhere. Ok great, we’ve done a good job. Database administrators can manage the SQL Server instance but they cannot read data! But is it really over? The answer is of course no. Why? At this point we have to remember about the server permission CONTROL SERVER. It will grant full permissions on the server and automatically on all the databases. It means a person which have this server permission can grant himself permissions to a securable even if it exists an explicit DENY for this securable. It is easy to retrieve which permissions are denied :
The login dba1 can now grant itself the “SELECT ALL USER SECURABLES” permission for the securable dba …
… and read data in the SensitiveTable
What a surprise !!! Is it a bug, a mistake or a Microsoft marketing lie ? I admit I had this reaction when I saw it and I'm pretty sure others also ! After a long discussion with some others SQL Server MVPS and friends, I don’t think because event if a database administrator does not need the read data to perform its tasks there can be some cases where it may be necessary, for example troubleshooting a long request with a specific set of data. If you need a more restrictive role, you have to look at granting only those subsets of CONTROL SERVER. I remember a discussion with one of my customer about the implementation of the separation of duties for database administrators particularly one question: Is the goal to prevent DBA staff to perform theirs tasks? Of course not but we want to audit action when a database administrator needs to elevate its privileges. Fortunately SQL Server provides an audit feature which can help to detect a malicious use of sysadmin permissions.
We could setup an audit trail that can track the event GRANT SELECT ALL USER SECURABLES permission and records events into the Windows security log or into the audit files which the database administrator has no access. Furthermore since SQL Server 2012 we can fail an action if it cannot be recorded to the audit target (ON FAILURE = FAIL_OPERATION)
Now if dba1 need to grant the server permission “SELECT ALL USER SECURABLES” automatically the event is recorded to the corresponded trace file:
The audit trace in this case ca be used to detect quickly the malicious use of the some grant actions and limit the damages. Of course behind the scene a robust process must be in place before implementing the tool.
To summarize, SQL Server 2014 facilitates the segregation of duties by implementing new server permissions but segregation of duties does not mean prevent in all cases some actions. To be efficient the segregation of duties consists of the implementation of automatic and preventives control. Feel free to share your opinion on this subject !