Separation of Duties (SoD) and role-based security conception in SQL Server

Writer: Andreas Wolter

Published: December 7^th 2017

Applies to: SQL Server 2016-2017

Introduction

With the upcoming implementation of the European General Data Protection Regulation (GDPR) in May 2018, having a security concept in place is essentially required by law. Microsoft SQL Server, just like other database systems, carries the main asset to protect: the data itself. Therefore, it is time for an article from a more strategic angle in terms of how I go about protecting SQL Server and its data.

There are several known security principles in IT Technology when designing for security. The most common ones, and those are the ones that I keep in mind when designing for security, are probably the following:

Least Privilege
Separation / Segregation of Duties
Reconstruction of Events
Delegation of Authority
Reality Checks
External Inspection
Well-formed Transactions
(Continuity of Operation)

The first two can be seen very closely related and complementing each other when it comes to implementation and will be the main subject of this paper.

Separation of Duties and Least Privilege Security principles

The principle of “Least Privilege” essentially means that users should not have more privileges than needed to complete their daily task. To secure data and the system in general from potential damage, it is essential to identify a comprehensive hierarchy of users and separate duties and to provide each individual with his or her own user ID and with permissions as minimal as possible to complete his or her daily task.
This is then also called “Separation of Duties” or “Segregation of Duties”.

DAC vs MAC

In the Microsoft SQL Server domain, a role-based security concept is generally being used for implementing the above. It enforces security using the GRANT/DENY-system of SQL Server, which is also known as Discretionary Access Control (DAC)

Another common concept, especially used in government-agencies, is label-based access control (LBAC)/-security.

Label-based security can be implemented by the use of row- and cell-level security using technologies like Always Encrypted and Row Level Security (RLS) within SQL Server. It allows for a more granular level of control than Discretionary Access Control and works by classifying data, also called Mandatory Access Control (MAC).

Label-based Security will not be discussed further in this paper. I recommend the Whitepaper Implementing Row- and Cell-Level Security in Classified Databases as a start. Though it is based on SQL Server 2008 it gives a good idea on the concept itself.

Role-based security

Coming back to role-based security, it should be noted that nobody is exempt from the principle of “least privilege”. It does not only apply to the end users of the database application but includes administrators, support-personnel and even developers.
It is in my view easier to develop a secure database if the developers and DBAs use only an account that gives them sufficient privileges for the role they are taking on, referred to as using the “least-privileged user account”.
In other words, “role-based security” does not only mean:

User = [“has”] Role

but rather:

User1 + TaskA = [“performs”] RoleA

And

User1 + TaskB = [“performs”] RoleB

Sometimes it helps to visualize:

I.e. DBAs should only use the SysAdmin role when its privileges are essential for the action they need to take. Developers who have access to privileged accounts should only use the more powerful accounts when they really need one of its privileges, and should use a normal account with fewer privileges for their regular work.
It may feel more complicated at first, because people will be forced out of the “comfort zone” of having all permissions, but after a period of adaption the outcome will be a far more secure system.

By using appropriately restricted accounts during development rather than “root”/”superuser” powers, a developer will become aware of potential security problems early in stage and also accidental deletion or alteration of certain objects will be prevented.

Now for a successful real-world implementation, there is one more principle: KISS: Keep it simple, stupid:

“reality-check”

If the User / Role-separation is being followed to the extreme, it is like with the “6^th normal form”: great advantages in theory, but totally impractically. Having the user switch to a different Role by using a second, third and so on login to create a table while his regular job maybe altering procedure code, will not make sense usually.
In fact the cases where one can really “live” this principle should be restricted to the most sensitive tasks.
The best example is a “SecurityDeployment-Role” vs the regular DBA. This is crucial for a system that needs to be tamper-proof for example.
Another common example is the Deployment-process from Development to Test/to Production, which is only run at certain times. Or in a Datawarehouse-system the ETL-process: when done right, security-wise, it will only succeed when run via the intended Job/Proxy-Account, and not when a regular user or even developer is executing it, because of the different areas accessed by it.

With that comes an organizational challenge: how do you ensure adherence of your rules?
The answer to that lies in the big topic compliance, which goes beyond the scope of this paper, although I will give some hints at certain points.

Now with all this in the back of your mind, the following ideas of mine, which I am sharing here, will hopefully make more sense.

Role Concept

To control security in a SQL Server environment, the use of roles is an ultimate principle. SQL Server provides Server roles and database roles which can be customized for ones needs by the use of the extensive permission system that came with SQL Server 2005.

When I work with customers, one of the first steps is to identify processes. Yes, processes, not yet roles.
Because the roles are just a means of enabling processes and users to do their work. (Obviously a “process” can be a technical user as well as a logical definition of a workflow. When necessary I will define the use case more accurately.)

Over the years of working in this area, several roles have emerged as typically needed.
Here are a few examples that are often in use:

Development/ “Developer”

Develops the database objects, Analysis Services cubes, Reporting Services Reports, Integration Services packages and other

Deployment

Transfers releases from one environment to the other

Application/Project Support, “App-Support”

Conduct support for one project at a time. One project can be one or more applications, mostly one.

Monitoring/Operations

Support on Server-Level.

ETL-Processes

Unattended routines for import & export of data, especially in Datawarehouse-systems

Auditor

Solely for conducting Audits on the whole Server. Will not change anything anywhere.

And you will always have:

Database Administrator

Has extensive permissions but no (daily) security related duties

System Administrators/ “Sysadmins”

have full permissions on the systems

Enduser

only access the data via Reports, Excel or other Frontends

This may sound simple, and SQL Server has a huge set of permissions, but the devil is in the details.

Role examples, obstacles and hints to solutions

Following are some examples of roles and the obstacles that you will meet when trying to secure them.

Monitoring

How can you enable certain roles (i.e. Developers) to get Performance Data from Production or Test-Environment without granting excessive permissions? For Extended Event sessions you have ALTER TRACE, but that allows access to data that may be sensitive. And how about Performance Monitor which is on Windows Level? There are 2 possible roles: Performance Monitor Users and Performance Log Users.

A possible solution that I have successfully implemented is to have a set of PerfMon Data Collector Sets with Performance Counters and Extended Event Traces prepared to be started via special jobs. Access to the Result-Files has to be implemented on an Windows File Share basis.
Speaking of files: don’t forget the output-files of SQL Agent Jobs which might contain valuable information for troubleshooting.

For viewing Server State data based on the commonly used DMVs (sys.dm_exec_requests, sys.dm_exec_query_stats, sys.dm_os_memory_clerks and many more) there is a necessity to filter those to the appropriate databases by project.
In order to accomplish that one can implement a set of special stored procedures, which naturally need to be properly signed with a certificate to access data outside the current database scope and prevent a path for privilege elevation. The outcome can also be stored in a custom Database. By Sarpedon Quality Lab Methology the code and data for these eventually resides in different Databases: SQL_Analysis_Code, SQL_Analysis_Data.

An alternative can be the use of a Third-Party Monitoring Software with a built-in role-concept that a professional tool like SQLSentry® offers.

SQL Agent Jobs

Any SQL Server will contain Jobs for maintenance, but also very frequently for scheduled processes that are connected to an application. That can be ETL (Import, Export) or other batch-processes.

One thing is, to transfer those jobs from development server to production, which will be covered in the next part, another thing is who gets permissions to start them manually, to check the history for troubleshooting-purposes or others and maybe change them when necessary. The built-in Security system for SQL Server Agent is very limited and not really flexible. For example:

a job can have exactly one owner (Cannot be a Windows Group Login/role although a Group can have permissions)
one can either read all history or the history of jobs owned (again: but only a single Login can own a job)
one can either start all jobs or the ones owned.

Here again is a case where a custom security framework that involves some coding is needed to allow for more freedom on who can do what.

Deployment

The problem with the Deployment-process is not a “CREATE TABLE/PROCEDURE” etc. Those permissions can easily be granted on Database-level. It does get more complicated in cases of multi-tenant-databases, but cases of different permissions on different schemas are certainly an absolute exception. From my experience, the use of schemas as security-boundaries itself is still rare (more on that here: Schema-design for SQL Server: recommendations for Schema design with security in mind). Having to use a combination of Database-Level-Permissions to use Schema-Level Permissions certainly does not help, but that is a rather minor issue.

The real problems arise when Developers create Logins, Databases and other Server-Level objects, especially security-related ones. You can grant the necessary permissions like ALTER ANY LOGIN, ALTER ANY SERVER ROLE and CREATE SERVER ROLE to the Deployment-Role, but you will need an organizational process to make sure that only validated commands of this kind are run at the Production Server, and nobody hides any “backdoor-admin” within a huge ALTER TABLE-Script.

On Database-Level, the db_owner-membership is necessary to edit role memberships for built-in roles (like db_datareader). This may not be acceptable for your security-level. If an application uses custom, user-defined roles, membership in the roles db_accessadmin and db_securityadmin (for granting permissions) can be used. The experienced Security specialist will realize that this bears a risk of privilege elevation though. So again, you cannot blindly trust the permission set and hence need a custom process around it.

Now another thing on SQL Agent Jobs in msdb: You need to be aware that at Deployment/Creation time, Jobs will be owned by the very creator. But mostly you will want Jobs to be able to be started by a different Role (like a real Person that has a “Support”-Role).
That means the Owner needs to be changed after creation. Steps with access to subsystems like CMD have to be set to use the appropriate proxy account.
And who will have permissions to do that? To secure this routine again one can use signed stored procedures to avoid tampering.

By now, the persistent reader will have realized that truly securing a system is real work… 😉

Maintenance

Maintenance naturally involves a lot of power on a system. You won’t get away with less than sysadmin for that, unless you go the extra mile to grant permissions on each database. I.e. you need ALTER TABLE to REBUILD/REGORGANIZE Indexes (and ALTER ANY CONNECTION for the “ABORT_AFTER_WAIT = BLOCKERS” – option) and UPDATE STATISTICS. For DBCC CHECKDB, db_owner is the minimum though. Then you will think about cleaning up in system tables and output files on OS-levels, and come to the conclusion that using sysadmin via SQL Agent is mostly acceptable.

Database Administrator

To control Database administrators, a strict separation from “Security Administrators” has to be followed.
Unfortunately the CONTROL SERVER command cannot be considered completely safe at the time of this writing (SQL Server 2017).

In SQL Azure Database the concept is slightly different. A dbmanager for example does not necessarily own all databases. For creating Logins there is the loginmanager-role which can be granted separately.

Also, it may be necessary to encrypt the content of certain sensitive tables in the database. For that means Always Encrypted has the ability to keep the decryption key completely outside SQL Server and is a good example of separation between those who own the data (and can read it) and those who manage the data (but should not be able to read it).

Hotfixing

Now whether “Hotfixing” is considered a valid use-case certainly depends on the strictness of one’s development lifecycle. I have found this exception to be a useful one.

The challenge here is to grant very sensitive permissions with 2 conditions:

only after clearance by a superior
only for a limited time.

This a nice use-case for a time-based permission system that can be implemented with custom code in SQL Server.

Besides that, Auditing is absolutely crucial here because of the high permissions involved. – Auditing is a built-in Feature of SQL Server, based on Extended Events infrastructure. In fact, for certain cases one will have to use Extended Events additionally.

Auditing

Final remarks

These were some real-life examples of roles that I implement for customers with a need for an (almost) bullet-proof Separation of Duties. Of course, there are more roles to consider.
While SQL Server has indeed earned its title of the most secure database in many years now (by count of vulnerabilities), attacks from inside are a very present and constant danger to any system. To protect against such, the pure absence of security-related bugs is far from sufficient if there are no borders or only weak borders implemented. That can be the commonly shared sa-account (“every day is emergency day”), other high privileged accounts, or the pure possibility to change data in production when running an update.

A few remarks on Reporting Services and Analysis:

Reporting Services also allows the definition of custom Roles but the concept is quite different from SQL Server, where a User is a member of the role throughout the whole database: in SSRS a user fulfills a certain role on a specified level in the (folder-)hierarchy and a different role one on another. A very interesting approach that is easy to follow because of the strictly hierarchical structure and the almost trivial permissions compared to the one of a complex RDBMS like SQL Server.

Analysis Services knows customizable roles on Database-level only with even less permissions, which can be difficult to secure all the way, especially when it comes to certain administrative tasks. When it comes to the Endusers, a kind of label-based security can be implemented on Hierarchy-/Cell level.

There are a few official whitepapers on SoD in SQL Server which I absolutely recommend as a technical background to this subject:

SQL Server Separation of Duties by Lara Rubbelke
Engine Separation of Duties for the Application Developer by Craig Gick, Jack Richins
Separation of Duties in SQL Server 2014 by Bob Beauchemin

I also recommend Erland Sommarskog’s article on Packaging Permissions in Stored Procedures which is an essential technique to be used for almost all of the depicted solutions.

I hope my article is a helpful addition in terms of practical scenarios.
Be sure to also read my other articles on security, especially:
CONTROL SERVER vs. sysadmin/sa: permissions, system procedures, DBCC, automatic schema creation and privilege escalation caveats and New Permissions in SQL Server 2014: IMPERSONATE ANY LOGIN, SELECT ALL USER SECURABLES, CONNECT ANY DATABASE and the old CONTROL SERVER

Wishlist to the security team

I would like to close with a wishlist for the database security teams in Redmond and Israel.

1) As certain roles can be dangerous to combine, and there is no way to prevent accidentally granting specific roles, all that one can do is to conduct regular checks. For that, Policy-based Management with ExecuteSQL-Expressions or plain SQL-Jobs can be scheduled. I would wish for a kind of anti-affinity, and “exception-bit” technique for (mainly server-) roles.

2) Msdb is a real pain to handle. First of all, it can be used for privilege elevation attacks, and secondly the whole ownership/permission-set for Jobs is far too limited. A hierarchical system for jobs (similar to SSRS and SSISDB somewhat) with permissions on certain levels would be great.
For many use-cases it would certainly be great to have jobs residing within a user-database. Maybe the solution is to have certain jobs within a user-database, and others within their own database (but not msdb). I understand though that there is a lot of complexity involved. Because somehow SQL Agent needs to access every database and check for jobs then. Part one would be great already.

3) Extended Events on Database level. That one should be almost ready to ship, if I look at SQL Azure Database 🙂

4) An “originating database” bit in DMVs for automatic filtering – similar to how system tables and permissions work.

5) Finish the work on decoupling certain system procedures/DBCC commands from the sysadmin-bit. A lot has been done in this area already, so I am confident this will eventually be finished.

6) In general, I would like to have more built-in roles, but more important is the possibility to craft “just the permissions needed”. So, I go for “more permissions” to enable the above wishes.

Andreas Wolter, Sarpedon Quality Lab

0 replies

Want to join the discussion?
Feel free to contribute!