When using the Query Store in a production environment, sometimes customers need to delegate very specifically who can do what in terms of Performance Analysis and/or Tuning.
The Query Store is a component that can be activated per database and is used for troubleshooting query performance. If you want to understand, how Query Store works and how it can help, you can start with this page: Monitoring performance by using the Query Store.
In this article we will demonstrate, how customers can segregate the typical activities around the Query Store and delegate minimal sets of permissions to groups of users that are mandated to certain tasks.
Who and what: Identify roles
When it comes to security, to generalize, it helps to first identify the roles that will work with the feature in question.
- I am using the term role as a term to summarize tasks that are similar and will most likely be undertaken by the same group of people. Usually it will end up being represented as a database role in SQL.
Typically, there are 3 aspects to the work with the Query Store, which can be reflected in roles:
1) Configuration – turning Query Store on and off, clearing the contents, flushing its contents to disk and changing its settings.
2) Viewing the reports or using the DMVs to analyze the Query Store contents (queries, plans and wait statistics) to gain insights but not necessarily having the authority to change anything
3) Actively change Plans by forcing or un-forcing, based on the information obtained from (2)
Your mileage may vary. Most of the times, roles 2 and 3 will be fulfilled by the same group of people. In smaller environments with less segregation of duties, everything will be in the same hands.
Required Permissions
Once we know what we need to separate permission-wise, we can identify the minimally required permissions to adhere to the principle of least privileges.
Quick Takeaway
By now, you may realize, that there are 2 very fundamental security principals guiding this article: Separation of Duties (SoD) and Principle of Least Privileges (PoLP).
Role 1, let’s call it DatabaseAdmin (indicating that most likely this will not be the only duty of the role in real life) will require to run commands such as:
ALTER DATABASE QS_SoD SET QUERY_STORE = ON
- To turn on the Query Store. Note: In Azure SQL Database, Query Store is enabled by default.
ALTER DATABASE [QS_SoD] SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 200)
ALTER DATABASE [QS_SoD] SET QUERY_STORE (QUERY_CAPTURE_MODE = ALL)
- To change the size or the capture mode.
ALTER DATABASE [QS_SoD] SET QUERY_STORE CLEAR ALL
- Purging.
In essence: All these configuration-options are part of the ALTER DATABASE-command.
There is one more thing: Flushing the contents to disk that have not yet been written yet to disk. This is done using the stored procedure sp_query_store_flush_db.
The minimum permission required for all the above, including execution of the provided stored procedure: ALTER on the Database
Quick Takeaway
In SQL Server or Azure SQL Database Managed Instance, you can grant ALTER on a Database in 2 ways: Besides the Database Permission “ALTER”, there is also a Server Permission “ALTER ANY DATABASE”. The Server Permission would make sense to use when you want to grant these permissions to all databases on a given SQL Instance in one command. It has to be granted to a Server Principal (ideally a custom Server role).
Role 2, let’s call it QueryStoreReader will want to use SSMS to use the built-in reports, but also need access to the Query Store DMVs for more complex and bulk analysis. Since the SSMS reports are based on the DMVs this is covered by the same permission.
The list of the Query Store DMVs is:
- sys.database_query_store_options
- sys.query_context_settings
- sys.query_store_plan
- sys.query_store_query
- sys.query_store_query_text
- sys.query_store_wait_stats
- sys.query_store_runtime_stats
- sys.query_store_runtime_stats_interval
The minimum permission required for all the above: VIEW DATABASE STATE
Note on Azure SQL Database
If you are using the Azure Portal Intelligent Performance for analyzing performance and gathering recommendation, this is independent from the Database permission within the SQL engine. Activity in the portal is controlled with Azure RBAC roles. This will be subject in another article.
Role 3, let’s call it TuningTeam is the one who can actively change query plans by forcing and unforcing plans through the Query Store in an easy manner.
Quick Takeaway
As a best practice this should be rather the exception or a quick fix until the query or indexes have been tuned in a way that the Query Optimizer can again automatically come up with a good plan. Therefore, it especially makes sense to not grant too many people this permission.
Forcing a plan can be accomplished via the Query Store Reports, or by calling the respective stored procedure directly.
The following stored procedures are actively changing plans or the stored contents for a specific plan and most likely make sense to be granted to this role:
- sp_query_store_force_plan
- sp_query_store_remove_plan
- sp_query_store_remove_query
- sp_query_store_reset_exec_stats
- sp_query_store_unforce_plan
The minimum permission required for all the above: ALTER on the Database
(Besides this, the TuningTeam also needs to be able to see the information from the Query Store, hence VIEW DATABASE STATE as well.)
The issue: high privileges required
The requirement for the ALTER-Permission on Database for the TuningTeam is the obvious issue: With the current set of permissions required for the Query Store operations, separating DatabaseAdmins from a TuningTeam is not possible out of the box. This is where we need to use a certain technique for customizing granular permissions: wrapping commands into special stored procedures.
Quick Takeaway
The ALTER-Permission on a Database currently grants 54 permissions, which is not only covering Query Store Configuration but also changing other database options up to creating, changing and dropping almost all database objects. It is still less than the 81 permissions that CONTROL implies, but it implies a variety of methods that would enable even further elevation of privileges.
The Solution: Wrapping Code in stored procedures with EXECUTE AS
The following solution has proven to be very handy many scenario since it was introduced in SQL Server 2005: Using the EXECUTE AS clause of a custom stored procedure that contains the commands that require high privileges.
In our scenario the 5 “commands” in question are in fact stored procedures themselves. (sp_query_store_force_plan, sp_query_store_remove_plan, sp_query_store_remove_query, sp_query_store_reset_exec_stats, sp_query_store_unforce_plan).
All of them are executed with at least one parameter, either query_id or plan_id or both. No defaults. Therefore, it is very simple up to trivial to handle and the given example can be adopted without major changes to all the other four stored procedures.
The concept is as follows:
1) We need to have a database principal with the required privileges to run the commands/stored procedures: ALTER DATABASE
- This principal should not be used for interactive work, and hence will be created without login and password.
In the provided example script this user is called internal_principal_ALTER_DB.
Quick Takeaway
As a Best Practice that helps to simplify analyzing and reporting on given permissions, I always, without any exceptions use roles and grant permissions to roles exclusively. This is why in this example I am taking this extra step that required just 2 commands more (CREATE and ALTER role) as well.
In the provided example script this role is called role_internal_principal_ALTER_DB. (Not “pretty”, but concise I hope)
2) We create a stored procedure with EXECUTE AS using the principal created to specify the context under which the module is being run. This requires no extra permissions for the caller of the stored procedure aside from the EXECUTE-Permission itself.
CREATE OR ALTER PROCEDURE db_tuning_tools.up_sp_query_store_force_plan
@query_id bigint
, @plan_id bigint
WITH EXECUTE AS 'internal_principal_ALTER_DB'
AS
EXECUTE sp_query_store_force_plan
@query_id = @query_id
, @plan_id = @plan_id
GO
3) The stored procedure contains one of the listed stored procedures and passes any parameters on to it.
- As a benefit this enables you to add additional operational or business logic like for example:
A) Log Who and When the action was attempted
B) Include further checks like “Block execution during critical business hours” or require a 2nd eye approval
C) Deferring the execution by applying Service Broker queuing instead of direct execution
Just to name a few further possibilities.
4) Grant the Execute-Permission on the wrapper procedures to the TuningTeam-Role.
In the provided example script this role is called Role_TuningTeam.
This should not be done on a per object base but on a schema-scope. We also want to make sure not to use any existing schema to interfere with other objects. Hence the cleanest way is to create a separate schema for those special procedures that the TuningTeam needs access to.
In the provided example script the schema name is db_tuning_tools.
Besides access to these procedures, the TuningTeam also needs to be able to view the contents of the query store, which requires the VIEW DATABASE STATE-permission.
Drawback
One little downside I shall point out though: when using this kind of solution, namely instead of using the built-in stored procedures or commands executing custom code, the graphical UI of Management Studio cannot be used any more. Precisely: Forcing or Unforcing plans via the Query Store reports. The Reports themselves of course can still be opened. But trying to force a plan via the UI will simply result in a permission error and the TuningTeam in this case needs to be educated to use the custom procedures for those actions.
Conclusion
Granting access to just parts of the query store functionalities can be implemented with very little extra work using wrapper stored procedures with EXECUTE AS.
You can find the example solution in the attached scripts below.
Happy Tuning and Securing
Andreas