Blog - comments

Hi Greg,

Thanks

great job Dave ! thanks

greg

Nice writeup

yup
using System; using System.Diagnostics; namespace ConsoleApplication1 { class Program { ...
praveen rathore
sql server is one of the best for utilize the data recovery. and its very informative for all the us...
Server instalattion
Blog David Barbarin SQL Server 2014 : SELECT ALL USERS SECURABLES & DB admins

dbi services Blog

Welcome to the dbi services Blog! This IT blog focuses on database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, EMC 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 our blog postings.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.

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:

 

CREATE SERVER ROLE dba;
GO

 

Then we create a database administrator login dba1:

 

CREATE LOGIN dba1
WITH PASSWORD = 'dba1',
CHECK_POLICY = OFF;
GO

 

We add the login as member of the dba1 fixed role :

 

ALTER SERVER ROLE dba ADD MEMBER dba1;
GO

 

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.

 

GRANT CONTROL SERVER TO dba;
GO
 
DENY SELECT ALL USER SECURABLES TO dba;
GO

 

We can now ensure it works by connecting with the login dba1.

 

SELECT SUSER_NAME() AS login_name, USER_NAME() as [user_name];
GO

 

In my configuration I have a database named SensitiveDB with a table named SensitiveTable.

 

billet2_newsecuritysql14_1

 

USE SensitiveDB;
GO
 
SELECT *
FROM INFORMATION_SCHEMA.TABLES;
GO

 

billet2_newsecuritysql14_2

 

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS;
GO

 

billet2_newsecuritysql14_3

 

SELECT
       SPECIFIC_CATALOG,
       SPECIFIC_SCHEMA,
       SPECIFIC_NAME,
       ROUTINE_TYPE
FROM INFORMATION_SCHEMA.ROUTINES;

 

billet2_newsecuritysql14_4

 

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 :

 

SELECT *
FROM dbo.SensitiveTable;
 
SELECT *
FROM dbo.V_SensitiveTable;

 

billet2_newsecuritysql14_5

 

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 : 

 

-- Check if dba1 is member of a server role
SELECT
       sp.name,
       sp.principal_id as login_principal_id,
       sp.type_desc,
       sp2.name AS role_name,
       sp2.principal_id as role_principal_id
FROM sys.server_principals AS sp
       LEFT JOIN sys.server_role_members AS sr
             ON sp.principal_id = sr.member_principal_id
       LEFT JOIN sys.server_principals AS sp2
             ON sp2.principal_id = sr.role_principal_id
WHERE sp.name = 'dba1';

 

billet2_newsecuritysql14_6

 

 -- Check permissions for all concerned principal_id
SELECT
       grantee_principal_id,
       class_desc,
       [permission_name],
       state_desc
FROM sys.server_permissions
WHERE grantee_principal_id IN(267, 268);

 

billet2_newsecuritysql14_7

 

The login dba1 can now grant itself the “SELECT ALL USER SECURABLES” permission for the securable dba …

 

USE [master];
GO
 
GRANT SELECT ALL USER SECURABLES TO dba;
GO

 

… and read data in the SensitiveTable

 

billet2_newsecuritysql14_8

 

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)

 

USE [master]
GO
 
-- Creation of audit target
CREATE SERVER AUDIT [HIGH_PERMISSIONS_GRANTED]
TO FILE
(      FILEPATH = N'E:AUDIT'
       ,MAXSIZE = 1024 MB
       ,MAX_FILES = 10
       ,RESERVE_DISK_SPACE = ON
)
WITH
(      QUEUE_DELAY = 1000
       ,ON_FAILURE = FAIL_OPERATION
)
WHERE ([statement] LIKE 'GRANT SELECT ALL USER SECURABLES%');
GO
 
ALTER SERVER AUDIT [HIGH_PERMISSIONS_GRANTED] WITH (STATE = ON)
GO
 
-- Creation of audit specification target
CREATE SERVER AUDIT SPECIFICATION [HIGH_PERMISSIONS_GRANTED_specification]
FOR SERVER AUDIT [HIGH_PERMISSIONS_GRANTED]
ADD
(
       SERVER_PERMISSION_CHANGE_GROUP
       -- To be completed by others usefull actions to track
)
WITH (STATE = ON);
GO

 

Now if dba1 need to grant the server permission “SELECT ALL USER SECURABLES” automatically the event is recorded to the corresponded trace file:

 

billet2_newsecuritysql14_9

 

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 !

Rate this blog entry:
2

David Barbarin is Consultant at dbi services. He has more than ten years of experience in Microsoft solutions. He is specialized in SQL Server technologies and associated topics such as installation, migration, security audits, troubleshooting of performance issues, or high availability architectures etc. Furthermore, he has many years of experience in .NET development, SSIS packages deployment, and database design in several sectors like retail, health sector, and other industries. David Barbarin is SQL Server MVP (since 2010), Microsoft Certified Master (MCM) for SQL Server, and Microsoft Certified Trainer (MCT). He holds an BTS in electronic from France and has a degree in computer sciences from CNAM in Lyon. His branch-related experience covers Public Sector, Financial Services / Banking, Automotive, Health Sector, IT, Watch Industry, etc.

Comments

  • No comments made yet. Be the first to submit a comment

Leave your comment

Guest Thursday, 18 September 2014
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)

Contact

Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter