What can I accomplish with Microsoft Purview DevOps policies?

  • Article

This article describes how to manage access to data sources in your data estate by using the Microsoft Purview governance portal. It focuses on basic concepts of DevOps policies. That is, it provides background information about DevOps policies that you should know before you follow other articles to get configuration steps.

Note

This capability is different from the internal access control in the Microsoft Purview governance portal.

Access to system metadata is crucial for IT and DevOps personnel to ensure that critical database systems are healthy, are performing to expectations, and are secure. You can grant and revoke that access efficiently and at scale through Microsoft Purview DevOps policies.

Any user who holds the Policy Author role at the root collection level in Microsoft Purview can create, update, and delete DevOps policies. After DevOps policies are saved, they're published automatically.

Access policies vs. DevOps policies

Microsoft Purview access policies enable customers to manage access to data systems across their entire data estate, all from a central location in the cloud. You can think of these policies as access grants that can be created through Microsoft Purview Studio, avoiding the need for code. They dictate whether a list of Microsoft Entra principals, such as users and groups, should be allowed or denied a specific type of access to a data source or an asset within it. Microsoft Purview communicates these policies to the data sources, where they're natively enforced.

DevOps policies are a special type of Microsoft Purview access policies. They grant access to database system metadata instead of user data. They simplify access provisioning for IT operations and security auditing personnel. DevOps policies only grant access. They don't deny access.

Elements of a DevOps policy

Three elements define a DevOps policy:

  • Subject

    This is a list of Microsoft Entra users, groups, or service principals that are granted access.

  • Data resource

    This is the scope where the policy is enforced. The data resource path is the composition of subscription > resource group > data source.

    Microsoft Purview DevOps policies currently support SQL-type data sources. You can configure them on individual data sources and on entire resource groups and subscriptions. You can create DevOps policies only after you register the data resource in Microsoft Purview with the Data policy enforcement option turned on.

  • Role

    A role maps to a set of actions that the policy permits on the data resource. DevOps policies support the SQL Performance Monitor and SQL Security Auditor roles. Both of these roles provide access to SQL system metadata, and more specifically to dynamic management views (DMVs) and dynamic management functions (DMFs). But the set of DMVs and DMFs that these roles grant is different. We provide some popular examples later in this article.

    The Create, list, update, and delete Microsoft Purview DevOps policies article details the role definition for each data source type. That is, it provides a mapping of roles in Microsoft Purview to the actions that are permitted in that type of data source. For example, the role definition for SQL Performance Monitor and SQL Security Auditor includes Connect actions at the server and database level on the data source side.

In essence, the DevOps policy assigns the role's related permissions to the subject and is enforced in the scope of the data resource's path.

Hierarchical enforcement of policies

A DevOps policy on a data resource is enforced on the data resource itself and all child resources that it contains. For example, a DevOps policy on an Azure subscription applies to all resource groups, to all policy-enabled data sources within each resource group, and to all databases within each data source.

Example scenario to demonstrate the concept and the benefits

Bob and Alice are involved with the DevOps process at their company. They need to log in to dozens of SQL Server instances on-premises and Azure SQL logical servers to monitor their performance so that critical DevOps processes don't break. Their manager, Mateo, puts all these SQL data sources into Resource Group 1. He then creates a Microsoft Entra group and includes Alice and Bob. Next, he uses Microsoft Purview DevOps policies (Policy 1 in the following diagram) to grant this Microsoft Entra group access to Resource Group 1, which hosts the logical servers.

Diagram that shows an example of DevOps policies on a resource group..

These are the benefits:

  • Mateo doesn't have to create local logins in each server.
  • The policies from Microsoft Purview improve security by limiting local privileged access. They support the principle of least privilege. In the scenario, Mateo grants only the minimum access that Bob and Alice need to perform the task of monitoring system health and performance.
  • When new servers are added to the resource group, Mateo doesn't need to update the policy in Microsoft Purview for it to be enforced on the new servers.
  • If Alice or Bob leaves the organization and the job is backfilled, Mateo just updates the Microsoft Entra group. He doesn't have to make any changes to the servers or to the policies that he created in Microsoft Purview.
  • At any point in time, Mateo or the company's auditor can see all the permissions that were granted directly in Microsoft Purview Studio.
Principle Benefit
Simplify The role definitions SQL Performance Monitor and SQL Security Auditor capture the permissions that typical IT and DevOps personas need to execute their job.
There's less need for permission expertise on each data source type.
Reduce effort A graphical interface lets you move through the data object hierarchy quickly.
Microsoft Purview supports policies on entire Azure resource groups and subscriptions.
Enhance security Access is granted centrally and can be easily reviewed and revoked.
There's less need for privileged accounts to configure access directly at the data source.
DevOps policies support the principle of least privilege via data resource scopes and role definitions.

DevOps policies API

Many sophisticated customers prefer to interact with Microsoft Purview via scripts rather than via the UI. Microsoft Purview DevOps policies now support a REST API that offers full create, read, update, and delete (CRUD) capability. This capability includes listing, policies for SQL Performance Monitor, and policies for SQL Security Auditor. For more information, see the API specification.

Screenshot that shows where to find the DevOps API on the Azure REST API menu..

SQL dynamic metadata includes a list of more than 700 DMVs and DMFs. The following table illustrates some of the most popular ones. The table maps the DMVs and DMFs to their role definitions in Microsoft Purview DevOps policies. It also provides links to reference content.

DevOps role Category Example DMV or DMF
SQL Performance Monitor Query system parameters to understand your system sys.configurations
sys.dm_os_sys_info
Identify performance bottlenecks sys.dm_os_wait_stats
Analyze currently running queries sys.dm_exec_query_stats
Analyze blocking issues sys.dm_tran_locks
sys.dm_exec_requests
sys.dm_os_waiting_tasks
Analyze memory usage sys.dm_os_memory_clerks
Analyze file usage and performance sys.master_files
sys.dm_io_virtual_file_stats
Analyze index usage and fragmentation sys.indexes
sys.dm_db_index_usage_stats
sys.dm_db_index_physical_stats
Manage active user connections and internal tasks sys.dm_exec_sessions
Get procedure execution statistics sys.dm_exec_procedure_stats
Use the Query Store sys.query_store_plan
sys.query_store_query
sys.query_store_query_text
SQL Security Auditor Get audit details sys.dm_server_audit_status
Both SQL Performance Monitor and SQL Security Auditor sys.dm_audit_actions
sys.dm_audit_class_type_map

For more information on what IT support personnel can do when you grant them access via the Microsoft Purview roles, see the following resources:

Next steps

To get started with DevOps policies, consult the following resources: