What can I accomplish with Microsoft Purview DevOps policies?
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.
.
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.
.
Mapping of popular DMVs and DMFs
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 | ||
| Get Error Log (not yet supported) | sys.sp_readerrorlog | |
| 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:
- SQL Performance Monitor: Use Microsoft Purview to provide at-scale access to performance data in Azure SQL and SQL Server
- SQL Security Auditor: Security-related dynamic management views and functions
Next steps
To get started with DevOps policies, consult the following resources:
- Try DevOps policies for Azure SQL Database: Quickstart guide.
- See other videos, blogs, and articles.