What can I accomplish with Microsoft Purview DevOps policies?
This article discusses concepts related to managing access to data sources in your data estate using the Microsoft Purview governance portal. In particular, it focuses on DevOps policies.
This capability is different from the internal access control for Microsoft Purview itself, which is described in Access control in Microsoft Purview.
Access to system metadata is crucial for IT/DevOps personnel to ensure that critical database systems are healthy, are performing to expectations and are secure. That access can be granted and revoked efficiently and at-scale through Microsoft Purview DevOps policies.
Microsoft Purview access policies vs. DevOps policies
Microsoft Purview access policies enable customers to manage access to different data systems across their entire data estate, all from a central location in the cloud. You can think about these policies as access grants that can be created through Microsoft Purview Studio, avoiding the need for code. They dictate whether a list of Azure AD principals (users, groups, etc.) should be allowed or denied a specific type of access to a data source or asset within it. These policies get communicated by Microsoft Purview to the data sources, where they get 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, that is, they don't deny access.
Elements of a DevOps policy
A DevOps policy is defined by three elements: The subject, the data resource and the role. In essence, the DevOps policy assigns the role's related permissions to the subject and gets enforced in the scope of the data resource's path.
This is a list of Azure AD users, groups or service principals that are granted access.
The data resource
This is the scope where the policy gets enforced. The data resource path is the composition of subscription > resource group > data source. Microsoft Purview DevOps policies currently support SQL-type data sources and can be configured on individual data sources, but also entire resource groups and subscriptions. DevOps policies can only be created after the data resource is registered in Microsoft Purview with the option Data use management enabled.
The role maps to a set of actions that the policy permits on the data resource. DevOps policies support a couple of roles: SQL Performance Monitor and SQL Security Auditor. Both these roles provide access to SQL's system metadata, and more specifically to Dynamic Management Views (DMFs) and Dynamic Management Functions (DMFs). But the set of DMVs/DMFs granted by these roles is different. We provide some popular examples at the end of this document. Also, the DevOps policies how-to docs detail the role definition for each data source type, that is, the mapping between the role in Microsoft Purview and the actions that get permitted in that type of data source. For example, the role definition for SQL Performance Monitor and SQL Security Auditor includes Connect actions at server and database level on the data source side.
Hierarchical enforcement of policies
A DevOps policy on a data resource is enforced on the data resource itself and all children contained by it. 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 contained within each data source.
A sample scenario to demonstrate the concept and the benefits
Bob and Alice are involved with the DevOps process at their company. Given their role, they need to log in to dozens of SQL servers 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 an Azure AD group and includes Alice and Bob. Next, he uses Microsoft Purview DevOps policies (Policy 1 in the diagram below) to grant this Azure AD group access to Resource Group 1, which hosts the Azure SQL servers.
These are the benefits:
- Mateo doesn't have to create local logins in each SQL server.
- The policies from Microsoft Purview improve security by limiting local privileged access. They support the Principle of Least Privilege (PoLP). In the scenario, Mateo only grants the minimum access necessary that Bob and Alice need to perform the task of monitoring system health and performance.
- When new SQL 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 SQL servers.
- If Alice or Bob leaves their job and get backfilled, Mateo just updates the Azure AD group, without having to make any changes to the servers or to the policies 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.
|Simplify||The role definitions SQL Performance Monitor and SQL Security AuditorData capture the permissions that typical IT/DevOps personas need to execute their job.|
|Reduce the need of permission expertise for each data source type.|
|Reduce effort||Graphical interface lets you navigate the data object hierarchy quickly.|
|Supports policies on entire Azure resource groups and subscriptions.|
|Enhance security||Access is granted centrally and can be easily reviewed and revoked.|
|Reduces the need for privileged accounts to configure access directly at the data source.|
|Supports the Principle of Least Privilege via data resource scopes and the role definitions.|
DevOps policies API
Many sophisticated customers prefer to interface with Microsoft Purview via scripts rather than via the UI. Microsoft Purview DevOps policies now support a REST API offering full CRUD capability (listing, policies for SQL Performance Monitor, policies for SQL Security Auditor). See the spec here.
Mapping of popular DMVs and DMFs
SQL dynamic metadata includes a list of more than 700 DMVs/DMFs. We list here as an illustration some of the most popular ones, mapped to their role definition in Microsoft Purview DevOps policies and provide a URL link to the document that describes them.
|DevOps role||Category||Example DMV / DMF|
|SQL Performance Monitor||Query system parameters to understand your system||sys.configurations|
|Identify performance bottlenecks||sys.dm_os_wait_stats|
|Analyze currently running queries||sys.dm_exec_query_stats|
|Analyze blocking issues||sys.dm_tran_locks|
|Analyze memory usage||sys.dm_os_memory_clerks|
|Analyze file-usage and performance||sys.master_files|
|Analyze index-usage and fragmentation||sys.indexes|
|Active user connections and internal tasks||sys.dm_exec_sessions|
|Procedure execution stats||sys.dm_exec_procedure_stats|
|Use the Query Store||sys.query_store_plan|
|SQL Security Auditor||Returns audit details||sys.dm_server_audit_status|
|Both SQL Performance Monitor and SQL Security Auditor||sys.dm_audit_actions|
Check these documents for more on what your IT support personnel can do when granted access via these Purview roles:
- 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
- DevOps policies can be created, updated and deleted by any user holding Policy Author role at root collection level in Microsoft Purview.
- Once saved, DevOps policies get automatically published.
To get started with DevOps policies, consult the following blogs, videos and guides:
- Try DevOps policies for Azure SQL Database: Quick start guide
- See other videos, blogs and documents
Submit and view feedback for