Separation of Duties (SoD) and role-based security conception in SQL Server
Writer: Andreas Wolter
Published: December 7th 2017
Applies to: SQL Server 2016-2017
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.
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
User1 + TaskA = [“performs”] RoleA
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:
If the User / Role-separation is being followed to the extreme, it is like with the “6th 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.
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:
- Develops the database objects, Analysis Services cubes, Reporting Services Reports, Integration Services packages and other
- 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.
- Support on Server-Level.
- Unattended routines for import & export of data, especially in Datawarehouse-systems
- Solely for conducting Audits on the whole Server. Will not change anything anywhere.
And you will always have:
- Has extensive permissions but no (daily) security related duties
System Administrators/ “Sysadmins”
- have full permissions on the systems
- 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.
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
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