New granular permissions for SQL Server 2022 and Azure SQL to improve adherence with PoLP

[Guest AndreasWolter]

In the last 3 years, the SQL Security team has put more emphasis on enabling customers to use SQL Server while adhering to the Principle of least Privilege (PolP). As part of that effort, all new features in the next SQL Server release: SQL Server 2022, can be controlled with more granular permissions. SQL Ledger is a good example of such a new feature. It comes with separate permissions for creating (ENABLE LEDGER) vs dropping ledger tables (ALTER LEDGER); a separate permission to view ledger content (VIEW LEDGER CONTENT) for auditing purposes; and another independent permission to generate a ledger digest (GENERATE LEDGER DIGEST).

 

Besides new features, a lot of effort also went into improving the existing set of permissions, namely by making them more granular. This has happened in 3 distinguishable areas:

 

 

 

(1) Access to System Metadata: 10 new permissions (5 on Server- and 5 on Database level)

 

(2) Extended Events: 18 new permissions (9 on Server- and 9 on Database level)

 

(3) Security-related objects: 4 new permissions (3 on Server- and 1 on Database level)

 

 

 

Benefits

 

 

By introducing these newly created granular permissions we enable customers to greatly improve adherence to the Principle of least Privilege. It is now possible to GRANT (or DENY) highly granular activities if required. Specifically, security-wise sensitive information can now be hidden easily while still allowing regular server- and database monitoring and maintenance.

 

Because the new permissions are a subset of existing coarse-grained permissions, existing permission-rollouts will work without changes. Customers can also test blocking individual granular tasks by using the DENY-Syntax on top of existing GRANTs to lower rolled out permissions piece by piece.

 

 

 

Details

 

 

Let’s look at the new permissions in detail.

 

 

 

Access to System Metadata

 

 

System Metadata in SQL Server is exposed mainly via Catalog views, Dynamic Management Views (DMVs) and Functions, as well as a few DBCC commands and extended stored procedures.

 

Until SQL Server 2019, the permission requirement for Catalog Views was VIEW ANY DEFINITION respectively VIEW DEFINITION depending on the metadata’s origin: Server level or per individual database.

 

Similarly for DMVs the minimal required permission was VIEW SERVER STATE respectively DATABASE STATE, depending on the applicable scope of the metadata.

 

 

 

In other words, one single permission covered roughly 300 Catalog Views and another one permission covered about 290 DMVs and DMFs.

 

 

But in reality, system metadata can belong to many different areas. It can relate to performance, high availability features (and those again can be grouped into subareas), fulltextsearch, security, general system information, service broker. And many of those can again be split into subareas such as Availability Groups, Failover Clustering, execution environment, SQL OS, memory and caches etc.

 

 

 

However, coming up with individual permissions for each sub-area would not benefit many customers and unnecessarily bloat the permissions system.

 

There are many scenarios in which it is desirable to grant certain individuals permissions to fully investigate a system’s behavior but also have confidence, that the person (or app/service account) cannot read security-sensitive information.

This is often the case with junior admins, external consultants, outsourced database administration services, performance-monitoring tools and similar which mostly need to see performance-related live or statistical information but not information about user accounts, permissions, cryptographic-properties etc..

While it is usually not a problem if someone who is tasked to analyze memory-usage could also view information about HA-replica states, exposure to security-information is less forgiving.

 

 

 

Therefore, we split the existing permissions into two separate sub-permissions below VIEW SERVER STATE/VIEW DATABASE STATE respectively VIEW ANY DEFINITION/VIEW DEFINITION.

 

The diagram below depicts this change/additional granularity:

 

 

 

Diagram: Split of VIEW SERVER/DATABASE STATE into VIEW SERVER/DATABASE PERFORMANCE STATE and VIEW SERVER/DATABASE SECURITY STATE

 

 

 

The same was done for Catalog views, which before this split were covered by a general VIEW DEFINITION:

 

Diagram: Split of VIEW DEFINITION/ANY DEFINITION into VIEW ANY PERFORMANCE/SECURITY DEFINITION and VIEW PERFORMANCE/SECURITY DEFINITION as well as a new permission VIEW CRYPTOGRAPHICALLY SECURED DEFINITION/ANY CRYPTOGRAPHICALLY SECURED DEFINITION

 

 

 

We are introducing 10 new permissions (5 on server plus 5 on database level):

 

Server level:

 

1. VIEW ANY SECURITY DEFINITION
   
2. VIEW ANY PERFORMANCE DEFINITION
   
3. VIEW SERVER SECURITY STATE
   
4. VIEW SERVER PERFORMANCE STATE
   
5. VIEW ANY CRYPTOGRAPHICALLY SECURED DEFINITION
   

 

Database level:

 

 

 

1. VIEW DATABASE SECURITY STATE
   
2. VIEW DATABASE PERFORMANCE STATE
   
3. VIEW SECURITY DEFINITION
   
4. VIEW PERFORMANCE DEFINITION
   
5. VIEW CRYPTOGRAPHICALLY SECURED DEFINITION
   

 

 

 

What is the difference between VIEW PERFORMANCE STATE and VIEW SECURITY STATE and DEFINITION?

 

 

It's best understood by approaching it from the following angle: From all the DMVs/DMFs and Catalog Views, we made a split between those that contain security-wise sensitive information and literally “everything else”. To put it into numbers: out of approximately 800 system views and functions, about 70 are from now on covered by VIEW SECURITY STATE or SECURITY DEFINITION (depending on whether it’s a DMV/DMF or a Catalog View). Everything else remains covered by one permission: VIEW PERFORMANCE STATE respectively VIEW DEFINITION. (In near future we will also have PERFORMANCE DEFINITION to make this split for Catalog Views more explicit. In CTP2 this permission does not exist yet.)

 

 

 

Why “everything else” and what exactly is it?

 

Ok, you really want to go down this hole ;)

 

Quite simply, everything that is not security-wise sensitive, is covered by “PERFORMANCE”. Even if the content is returning static data, Fulltextsearch word-lists or Cluster Node information. If you look at every DMV in detail you will notice many of them contain a mix of information, crossing subject boundaries. And trying to make up more buckets serves little purpose and would just make life harder for those trying to figure out which permissions to grant with little or no benefit.

 

 

The main scenario that we want to cover: Administrators can grant access to system information without having to worry that the Junior DBA/External Consultant/Remote Admin Service company gains access to information that it could abuse.

 

And that is achieved by separating out the security-wise sensitive objects.

When it comes to performance monitoring, troubleshooting and similar tasks, having access to HA-information usually poses no risks for a company. But information about key encryption strengths should not be available without proper cause.

 

 

 

How the new server roles that were announced recently align to this work

 

In May 2022 I announced 7 new server roles for Azure SQL Database (New server roles for Azure SQL Database and SQL Server 2022 in Public Preview - Microsoft Tech Community). One of these new roles is actually based on these new permissions:

 

 

 

##MS_SecurityDefinitionReader## => VIEW ANY SECURITY DEFINITION

 

If you look at the list of new Server roles for SQL Server 2022 you will find even more roles that align 1:1 to these new granular permissions on server level:

 

##MS_ServerPerformanceStateReader## => VIEW SERVER PERFORMANCE STATE

##MS_ServerSecurityStateReader## => VIEW SERVER SECURITY STATE

##MS_PerformanceDefinitionReader## => VIEW ANY PERFORMANCE DEFINITION

 

Server-level roles - SQL Server | Microsoft Docs

 

 

 

By using those server roles, these new permissions can be used in Azure SQL Database, Managed Instance and SQL Server alike. This is the only way to delegate server level permissions in Azure SQL Database.

 

 

 

What is VIEW CRYPTOGRAPHICALLY SECURED DEFINITION for?

 

 

You may have noticed the permissions VIEW ANY CRYPTOGRAPHICALLY SECURED DEFINITION (on server) and VEW CRYPTOGRAPHICALLY SECURED DEFINITION (on database) and I haven’t covered it under SECURITY DEFINITION.

These permissions cover a very specific subset of information within certain objects. It does not protect any specific view but rather specific columns in some objects.

The background is that there are a few data fields that contain information that is much more sensitive than a username, algorithms strengths or granted permissions are: Hashed passwords and encrypted keys.

 

Currently the following columns require this permission to display the content:

 

1. password_hash from sys.sql_logins
   Viewing the password hash required the CONTROL SERVER permission until now.
   
2. The same applies to the PasswordHash from the LOGINPROPERTY-Function LOGINPROPERTY (Transact-SQL) - SQL Server | Microsoft Docs
   Also this until now required the CONTROL SERVER permission.
   Now access to the password-hash can be granted without granting elevated permissions.
   
3. crypt_property from sys.key_encryptions
   until now this was only covered by Metadata security and of course sysadmin-permissions. Now access to this data can be granted without granting elevated permissions.
   

 

 

 

Extended Events

 

 

Extended Events (aka XEvents) are the tracing technology that since SQL Server 2012 replaces SQL Trace & Profiler. (XEvents overview - SQL Server, Azure SQL Database, and Azure SQL Managed Instance - SQL Server | Microsoft Docs)

 

Since then, managing XEvent sessions were covered by one, respectively two Permissions: ALTER ANY EVENT SESSION – applicable to either the database level (SQL Database) or server level (SQL Server and MI).

 

This permission covers everything from creating new sessions, changing any part of the definition like adding or removing attributes or targets and starting and stopping them.

 

 

 

Note on SQL Auditing

If XEvents are not your main concern, it may help to understand that Security Auditing in SQL is also based on XEvent architecture and therefore shares a lot of the DDL and general workflow.

While I cannot disclose anything specific, it probably makes sense that the permissions for Auditing will profit from this change that XEvents undergo at some point. ;)

 

 

 

Starting with SQL Server 2022, XEvent session management can be controlled by 18 additional permissions (9 on server plus 9 on database level). This allows for a much more fine-grained control over what a user is allowed to do with XEvent sessions.

 

The list of new permissions:

 

Server level:

 

 

 

1. CREATE ANY EVENT SESSION
   
2. DROP ANY EVENT SESSION
   
3. ALTER ANY EVENT SESSION OPTION
   
4. ALTER ANY EVENT SESSION ADD EVENT
   
5. ALTER ANY EVENT SESSION DROP EVENT
   
6. ALTER ANY EVENT SESSION ENABLE
   
7. ALTER ANY EVENT SESSION DISABLE
   
8. ALTER ANY EVENT SESSION ADD TARGET
   
9. ALTER ANY EVENT SESSION DROP TARGET
   

 

All these permissions are under the same parent-permission: ALTER ANY EVENT SESSION

 

 

 

Database level:

 

1. CREATE ANY DATABASE EVENT SESSION
   
2. DROP ANY DATABASE EVENT SESSION
   
3. ALTER ANY DATABASE EVENT SESSION OPTION
   
4. ALTER ANY DATABASE EVENT SESSION ADD EVENT
   
5. ALTER ANY DATABASE EVENT SESSION DROP EVENT
   
6. ALTER ANY DATABASE EVENT SESSION ENABLE
   
7. ALTER ANY DATABASE EVENT SESSION DISABLE
   
8. ALTER ANY DATABASE EVENT SESSION ADD TARGET
   
9. ALTER ANY DATABASE EVENT SESSION DROP TARGET
   

 

All these permissions are under the same parent-permission: ALTER ANY DATABASE EVENT SESSION

 

 

 

Scenarios that the new permissions enable

 

 

Preparing XEvent sessions

It is now possible to prepare XEvent sessions and only grant other Users to start & stop those prepared sessions

 

 

 

Allow users to solely add events or targets

By granting ALTER ANY EVENT SESSION ADD EVENT/ADD TARGET, Users can be allowed to add additional events to capture and add new targets – while at the same time be prevented to remove existing targets (together with the captured data therein) or events.

 

 

 

Allow changing configuration but not what is captured

By granting ALTER ANY EVENT SESSION OPTION, users can change memory usage, latency and similar options, but not influence what is captured and where. – For a list of session options look here: ALTER EVENT SESSION (Transact-SQL) - SQL Server | Microsoft Docs for “event_session_options”.

 

 

 

Adding and modifying predicates falls under ALTER ANY EVENT SESSION ADD EVENT, since that is part of the event configuration. The same applies to adding Actions.

 

To view data from XEvent sessions, depending on the target type used (memory or file), either DMVs using XQuery have to be queried while for the file target a system function is used (Targets for Extended Events in SQL Server - SQL Server | Microsoft Docs). All of those were covered by the VIEW SERVER STATE-permission and from now on under the VIEW SERVER PERFORMANCE STATE-permission respectively the VIEW DATABASE PERFORMANCE STATE-permission. This is part of the work on System Metadata permission from the former topic.

 

 

 

Security-related objects

 

 

The last bucket of new permissions covers a few security-related objects.

 

Here are the 4 new permissions:

 

1. CREATE LOGIN
   

 

This permission is a subset of ALTER ANY LOGIN and allows for delegating the creation of new Logins while at the same time preventing that the user with this permission could also change passwords of existing Logins and hence abuse his powers for Elevation of Privilege attacks.

 

 

 

1. VIEW ANY ERROR LOG
   

 

This quite simply allows the grantee to read the error logs of SQL Server (and SQL Server Agent). This is done via 3 procedures: sys.xp_enumerrorlogs, sys.sp_readerrorlog and sys.xp_readerrorlog. Before SQL Server 2022, the VIEW SERVER STATE permission which also covers DMVs was the minimal required permission.

 

 

 

1. VIEW SERVER SECURITY AUDIT
   

 

This is very useful for Security Auditors who should not have the ability to also change existing SQL Auditing definitions. With this permission, the Audit Log can be read via the system function sys.fn_get_audit_file (Transact-SQL) - SQL Server | Microsoft Docs. Beforehand, the CONTROL permission on the server was required.

 

For the database level Auditing data we added another separate permission:

 

1. VIEW DATABASE SECURITY AUDIT
   

 

 

 

Backwards-compatibility

 

 

By adding the new permissions as alternative, lower permissions, we ensure backwards-compatibility: Existing scripts do not break because all existing Permission-assignments (in the above example: VIEW SERVER STATE) work as before. But those who have a need to assign more granular permissions now can differentiate between security-related system metadata and “everything else”.

 

 

 

Let me know what you think of these new permissions, if you find the approach practical and wishes you may have.

 

Happy securing

 

Andreas

 

 

 

 

 

Thank you to the following people for help with this article:

Language: Michael Howard (Principal Program Manager Azure SQL Security), Diana Putnam (Principal Group PM Manager Azure SQL Security), Tameika Lawrence (Program Manager Azure SQL Security)

Technical correctness: Steven Gott (Senior Software Engineer)

 

Continue reading...