(part 1 of my series of articles on security principles in Microsoft SQL Servers & Databases)
The Principle of Least Privilege (POLP)
The first security principle that I am going to discuss is one that most System Administrators are familiar with: the “principle of least privilege” (short: POLP). It demands that the required permissions for a task shall only grant access to the needed information or resources that a task requires. When permissions are granted, we shall grant the least privileges possible.
POLP is so crucial because initially it is the privileges that any attacker is targeting. When developing an application, using a least-privileged user account (LUA) is the first rule of engagement.
Note
User Account Control (UAC) in Windows is a feature that Microsoft developed to assist administrators in working with least-privileges by default and elevate to higher permission only when needed.
You may also know that Microsoft recommends separating service accounts. This security best practice is generally referred to as service account isolation and is related to POLP: Using distinct service accounts prevents increased privileges, which happens easily when you share an account to be used for multiple purposes and as a consequence, the privileges are merged. - This would violate the principle of least privilege.
Both POLP and service account isolation help reducing the attack surface (aka attack surface reduction).
- Read more on this topic here: SQL Server security - SQL Server | Microsoft Docs
and here: Surface Area Configuration - SQL Server | Microsoft Docs
Service account isolation also prevents lateral movement between services if an attacker gained access to one service. You see how one thing is connected to another in Security?
Note
Lateral movement is a common attack strategy to move from one target to the next: If the main target (for example the database server) cannot be breached into directly, attackers try to gain foothold in some other server in the system within the same network and then launch other attacks to try to get to the final goal, server by server or service by service.
Principle of Least Privilege in the SQL realm
Let’s look at some examples within the SQL Server engine (applying to on-prem as well as our Azure SQL-products):
Example 1, read-access to data
A typical example within SQL Server would be: To allow a User to only read data from a small set of tables, ideally defined by a schema-boundary, we have the SELECT-Permission, grantable at the schema- (or even table-) level. There is no need to grant SELECT at the whole database, or to grant anything other than SELECT.
In the code-snippet below we see that there are many tables in different schemas (Application, Purchasing, Sales) within the database WideWorldImporters. Instead of granting Select in the whole database, we chose to grant the user Shakti the permission at the schema scope. As long as this schema really contains only objects that Shakti needs access to, this is a best practice as it greatly reduces the management and reporting overhead compared to granting permissions at the object-level.
Tip
An alternative is to use stored procedures for all data access, which would allow even better control and completely hide the schema from Users.
That was easy, wasn’t it?
Example 2, creating user accounts
Unfortunately, not everything is implemented to always ensure POLP.
Let’s take another example:
You want to delegate the ability to create new Logins in SQL Server.
The minimal permission available is ALTER ANY LOGIN. Ok, so now this person can create new Logins, and maybe also Dropping them is ok.
But: With this permission comes the ability to change the password of any SQL Login (“ALTER LOGIN … WITH PASSWORD='NewPassword').
This can be an unwanted scenario as this would enable this person to essentially take over other accounts.
Note
This would not work if the Account were a Windows Domain or Azure Active Directory account. This is where a separation of the authenticating system from SQL Server has a real advantage. (This is a great example of Separation of Duties btw.)
Example 3, changing table structures
What about the following?
Say you want to allow a developer to add a set of new columns to the existing tables. (For example, for logging purposes, you need to include the timestamp of any new row.)
The minimal permission to change tables/add new columns is the ALTER-permission on each individual table (it cannot be done on schema-level).
In my example you can see that adding new columns works fine, also dropping the table is not allowed.
But:
Instead of adding new columns this user could also drop existing columns. This is covered under the same least permission/privilege:
Or: You want them to only create new tables but disallow to change existing tables. Since the required permissions for that are: CREATE TABLE on Database + ALTER on the schema, they could also drop tables. With permissions alone this cannot be solved. This is a common reason for the use of DDL Triggers as a preventative control. (I demonstrated an example of a DDL Trigger in this Blog-Article: Logging Schema-changes in a Database using DDL Trigger, which can easily be adjusted to prevent certain statements altogether by rolling them back.)
Conclusion
The more you dive into this subject and real-world implementation, the more you will realize that this apparently basic security principle is much tougher than you may initially have expected.
The permission system of SQL Server is very granular, vast, and continuously growing. (SQL Server 2019 provides 248 permissions and Azure SQL Database exposes 254 permissions as of December 2020.)
While some of the examples above are reasonable, we need to balance every decision for every new permission and look at it from multiple angles whenever a new functionality or command is implemented. For example:
- Which other commands and tasks are covered under the same permission?
- How do they relate to the functionality at hand?
- Is the use of the new functionality/command alone a common scenario?
Having permissions on parts of Table-structures, like adding columns but not dropping them, would increase the complexity of the permission-system and hence the compromise to have just one ALTER-permission on table DDL was made.
That said, I know there are examples where the balance is not right, and SQL Server can be improved, like TRUNCATE TABLE requiring ALTER on the table as well and others.
Feel free to let me know where you believe that POLP is seriously unbalanced, and more granularity is required to reach compliance.
Happy securing
Andreas
Resources
- Wikipedia: https://en.wikipedia.org/wiki/Principle_of_least_privilege
- Schema-design for SQL Server: recommendations for Schema design with security in mind
- Security Considerations - SQL Server
- Azure identity & access security best practices
- Understand and use Lateral Movement Paths with Microsoft Defender for Identity
- Detecting threats with Azure Security Center | Azure Blog and Updates
- SQL Server security - SQL Server | Microsoft Docs
- Surface Area Configuration - SQL Server | Microsoft Docs