Introduction into security principles in the context of database systems
By Andreas Wolter
While many of us are practicing ‘social distancing’, and spend lots of time at home, I am finally finding the time to share some of the topics with the public that I have been working on since I joined Microsoft at the end of 2018.
In the recent years and with increasing frequency, one of the asks in terms of Security to the SQL Engine On-Prem as well as SQL Azure Database has been coming up with solutions to help accomplish “Separation of Duties”. This is a good thing, because it reassures my point of view that Separation of Duties is becoming increasingly important in IT and specifically Cloud-based systems.
On the other hand, we noticed that there is not a broad understanding in the technical community yet as to what Separation of Duties (aka SoD) really means and how it can be accomplished today. It occurs to me that the understanding is often vague and sometimes even contradicting depending on who you ask. It might therefore help to provide some context and guidance on what SoD really is and how it relates to other commonly referenced security principles that have been established over the last decades in IT.
If you are not already an expert in IT security, I hope that you will find this series useful.
Security principles in information technology or cybersecurity (I won’t touch on physical security in these articles) exist as guidelines to assist design and decision processes in architecture, implementation, and reactive procedures when incidents happen. The purpose is to help designing for security in the first place, by using common proven patterns, and to be able to effectively assess a systems security.
Building secure systems from the start can be an expensive task, but over the years we have all seen security incidents which can cost millions and cause companies or even banks to go out of business. (i.e. see https://www.ibm.com/ae-en/security/data-breach )
One word of caution: Simply complying with these security principles provides no guarantee of preventing successful attacks. Some attackers invest a lot of time thinking to come up with ever new methods and exploit attack vectors which may not have been considered before.
But following these security principles helps to reach the following objectives:
- Reducing the blast radius of an attack
- i.e. attackers may not gain access to all targeted services because of partitioning or may not be able to elevate to all permissions to gain access to all documents
- Increasing the time for a successful attack
- this also goes back to #1 as it becomes harder to gain sufficient access
- Increasing the chances of early detection (!!)
- More controls and audits usually mean more chances of raising triggers or errors along the way
- Improving forensic abilities after detection
- Better audit trails allow for more successful investigations
Security at first
Therefore, I strongly advise to implement the proper security controls from start. And this is not just because it is common knowledge among IT architects that changing running systems is more expensive than making sure that Security is a main pillar in the architecture from the start.
To take it one step further: security should be THE FIRST pillar to be implemented. What I mean by that is that, ideally, nothing gets deployed before all security measures have been put in place. Otherwise, it is easily possible to admit backdoors or other security issues in the foundation, purposefully or not, that remain undetected. The very first measure therefore should be to put Auditing in place. We will talk more about Auditing in a later article.
This series of articles will provide an overview on the most commonly cited security principles and concepts which are often used when talking about Separation of Duties – or even intermingled with it – and briefly clarify their meaning and relation. Expect a lot of keywords (not buzzwords though, I promise)
Principle of Least Privilege (POLP) Need to know
Delegation of Authority Separation of Privilege
Audit Trail Separation of Duties
- These articles will be released one by one over the next weeks and the links will then be updated one by one as well.
One more principle you should keep in mind when designing security: “KISS” - Keep it simple, stupid
As already mentioned in my article from 2017 (Separation of Duties (SoD) and role-based security conception in SQL Server), it is absolutely vital to keep the user experience as simple as possible. Anything “too much” of an effort (and that can be just “too many clicks”) will lead to users to try to find ways around it. And they will.
A common example of that is the shared Admin account. Instead of having one elevated account per person, often especially in small shops, developers share one common privileged account. Among other things this renders Auditing almost useless as no one can really tell who did what.
Separation of concerns (SoC)
Over time I have heard it being used when actually “Separation of Duties” was meant. SoC is NOT a security principle and rather a basic programming design principle which leads to modular (or “functional”) programming. Hopefully, this clears up this common mix-up.
Let me know if you find this series helpful and what else you want to hear about in the future.
Special thanks to
Raul Garcia, former SQL Security PM and “honorable member for life” :) – your knowledge in Security and SQL Security helped me make sure to not overlook anything important and meet a certain quality bar ;)
Steven Gott, one of our most senior Security Engineers, for your critical viewpoints which help me look ahead, although I know I can’t possible mention everything.
Ralf Dietrich from Sarpedon Quality Lab® Germany for countless hours of brainstorming about secure architectures even while being based in separate time zones.
"Security Logo" by pixabay is licensed under CC0