Custom RBAC to access QPI’s query text with minimal permissions

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

Query Performance Insight helps you to quickly identify what your longest running queries are, how they change over time, and what waits are affecting them. As a pre-requisite of using Query Performance Insight, data must exist in the Query Store.

As per the documentation, Owner or Contributor permissions are required to view the text of the queries in Query Performance Insight. With a Reader role, a user can view charts and tables but not query text.

QPI - query textQPI - query text

 

There are situations when, for security reasons, we might need to come up with a custom Azure Role-Based Access Control (RBAC) to

grant a user with minimal permissions in order to be able to view the query text, without giving full access with owner or contributor role. This is when the need of having a custom role with minimal permissions to access the query text arises.

 

To start a custom role from scratch, please follow the steps below.

 

  1. In the Azure portal, open the SQL Server where you need the custom role to be assignable and then open Access control (IAM).

  2. Click Roles and then right-click on any role in the given list and select Clone.  

     

    create custom rolecreate custom role

    3. This opens the custom roles editor. Select Start from scratch option, then click on the JSON tab. 

    strat from scratch.PNG

     

    4. This will open your custom role in JSON format. Click on Edit button and add the following permissions to you JSON.

    edit JSONedit JSON
    5. Add the following permissions and hit Save. You can also download your custom role as JSON file.
    { "properties": { "roleName": "QPI_CustomRole", "description": "minimal permissions necessary to view the query text in QPI", "assignableScopes": [ "/subscriptions/<Subscription ID>/resourceGroups/<Resource Group Name>/providers/Microsoft.Sql/servers/<Server Name>" ], "permissions": [ { "actions": [ "Microsoft.Sql/servers/read", "Microsoft.Sql/servers/automaticTuning/read", "Microsoft.Support/*", "Microsoft.Insights/metrics/read", "Microsoft.Insights/metricDefinitions/read", "Microsoft.Sql/locations/databaseOperationResults/read", "Microsoft.Authorization/*/read", "Microsoft.Sql/locations/*/read", "Microsoft.Sql/servers/databases/read", "Microsoft.Sql/servers/databases/providers/Microsoft.Insights/logDefinitions/read", "Microsoft.Sql/servers/databases/queryStore/read", "Microsoft.Sql/servers/databases/queryStore/write", "Microsoft.Sql/servers/databases/queryStore/queryTexts/read", "Microsoft.Sql/servers/databases/topQueries/read", "Microsoft.Sql/servers/databases/topQueries/statistics/read", "Microsoft.Sql/servers/databases/topQueries/queryText/action", "Microsoft.Sql/servers/databases/schemas/tables/columns/read", "Microsoft.Sql/servers/databases/schemas/read", "Microsoft.Sql/servers/databases/schemas/tables/read", "Microsoft.Sql/servers/databases/extensions/read" ], "notActions": [ "Microsoft.Sql/managedInstances/databases/currentSensitivityLabels/*", "Microsoft.Sql/managedInstances/databases/recommendedSensitivityLabels/*", "Microsoft.Sql/managedInstances/databases/schemas/tables/columns/sensitivityLabels/*", "Microsoft.Sql/managedInstances/databases/securityAlertPolicies/*", "Microsoft.Sql/managedInstances/databases/sensitivityLabels/*", "Microsoft.Sql/managedInstances/databases/vulnerabilityAssessments/*", "Microsoft.Sql/managedInstances/securityAlertPolicies/*", "Microsoft.Sql/managedInstances/vulnerabilityAssessments/*", "Microsoft.Sql/servers/databases/auditingSettings/*", "Microsoft.Sql/servers/databases/auditRecords/read", "Microsoft.Sql/servers/databases/currentSensitivityLabels/*", "Microsoft.Sql/servers/databases/dataMaskingPolicies/*", "Microsoft.Sql/servers/databases/extendedAuditingSettings/*", "Microsoft.Sql/servers/databases/recommendedSensitivityLabels/*", "Microsoft.Sql/servers/databases/schemas/tables/columns/sensitivityLabels/*", "Microsoft.Sql/servers/databases/securityAlertPolicies/*", "Microsoft.Sql/servers/databases/securityMetrics/*", "Microsoft.Sql/servers/databases/sensitivityLabels/*", "Microsoft.Sql/servers/databases/vulnerabilityAssessments/*", "Microsoft.Sql/servers/databases/vulnerabilityAssessmentScans/*", "Microsoft.Sql/servers/databases/vulnerabilityAssessmentSettings/*", "Microsoft.Sql/servers/vulnerabilityAssessments/*" ], "dataActions": [], "notDataActions": [] } ] } }​

    6. Click on Review + Create.

Note: If you get the error message "No more role assignments can be created (code: RoleAssignmentLimitExceeded)" when you try to assign a role, try to reduce the number of role assignments in the subscription. Azure supports up to 2000 role assignments per subscription. This limit includes role assignments at the subscription, resource group, and resource scopes. The 2000 role assignments limit per subscription is fixed and cannot be increased. Check the following document to see how you can reduce the number of role assignments.

 

Hope you'll find it useful!

 

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.