Blog Post

Azure Database Support Blog
3 MIN READ

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

MelaniaNitu's avatar
MelaniaNitu
Icon for Microsoft rankMicrosoft
Apr 13, 2021

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 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 role

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


     

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

    edit 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!

 

Updated Apr 13, 2021
Version 1.0
  • mbobadillav_bps's avatar
    mbobadillav_bps
    Copper Contributor

    Hi, 

     

    Did you know the RBAC permissions to include in this rol the Monitor > SQL Preview function? 

     

    Thank you.