Govern your data in SQL database in Microsoft Fabric with protection policies in Microsoft Purview
Microsoft Purview’s protection policies help you safeguard sensitive data in Microsoft Fabric items, including SQL databases. In this article, we’ll explain how these policies override Microsoft Fabric item permissions for users, apps, and groups, limiting their actions within the database. If you are not familiar with how item permissions and workspace roles work in SQL databases, please refer to our earlier blog post: Manage access for SQL databases in Microsoft Fabric with workspace roles and item permissions.
What is Microsoft Purview
Microsoft Purview is a family of data governance, risk, and compliance solutions that can help your organization govern, protect, and manage your entire data estate. Many customers have been using Microsoft Purview with Microsoft 365 and Power BI, for example, to protect sensitive information in SharePoint files, Power BI reports, or emails. Microsoft Fabric and Microsoft Purview working together extend those benefits to the world of data, including operational databases.
The comprehensive functionality of Microsoft Purview is accessible through various applications within the Microsoft Purview portal. One such application is Microsoft Purview Information Protection, which allows compliance administrators in your organization to create sensitivity labels for categorizing Microsoft Fabric items. These administrators can then set up protection policies that control access to items based on their sensitivity labels. For example, they can restrict access to full-time employee or specific teams within the organization.
To start using Information Protection, your organization needs to meet several pre-requisites, for example, acquiring an appropriate license. For details, refer to Information protection in Fabric – Microsoft Fabric | Microsoft Learn.
How protection policies work in SQL database
Let’s review an example that explains how protection policies work for SQL database items. In the example, we’ll reference a database, named Database01, labeled with the Confidential label.
In the Microsoft Fabric portal, you can view an item’s label from the flyout menu on the database page (Figure 1) or from the database settings, if you have the Read item permission. To apply a label, you need the Write item permission.
Let’s assume there is a protection policy associated with the Confidential label in the organization. Access control settings are the key components defining each protection policy.
These settings include two sections:
- Allow users to retain read access – Users or groups specified in this section are allowed to retain read access to a labeled item, provided they already have the Read permission for the item.
- Allow users to retain full control – Users or groups specified in this section are allowed to retain full control of a labeled item, provided they already have the full control (Write) permission for the item. If they don’t have the Write permission, they retain all permissions they have.
Figure 2 shows the access control settings for the protection policy associated with the Confidential label in Microsoft Purview portal. Note that User1 is specified in the first section and User2 is specified in the latter section.
In our example, assume User1, User2, and User3 (not listed in the policy) are all in the Contributor role in the workspace containing the database – see Figure 3. They all have both Read and Write permissions.
Next, we will examine how the protection policy affects the permissions and capabilities of each user with regard to the labeled database.
User1 is listed in the Allow users to retain read access section, but not in the Allows users to retain full control section. Consequently, the policy revokes user’s Write permission while retaining all other permissions.
As a result, User1:
- Can view the database and its properties in Microsoft Fabric portal but cannot edit any properties or remove the database.
- Can run read-only queries; any update queries will fail (Figure 4).
User2 appears in both policy sections, so the policy does not affect the user’s effective permissions.
User3 is not listed in either section of the protection policy. Hence, User3 loses all database access, specifically:
- If the user opens the workspace in the portal, the database will not show in the list of items.
- If the user tries to connect to the database from an application or SQL Server Management Studio – SSMS (see Figure 5), they will receive an error. This is because the policy results in revoking the Read permission that is required for a connection.
Wrapping up
Microsoft Purview’s protection policies allow organizations to restrict access to Microsoft Fabric items, including SQL databases, based on their sensitivity labels. They work by effectively revoking the Read and/or Write item permissions granted in Microsoft Fabric, preventing users from connecting to the database (and reading data) and/or updating the database.
For simplicity the provided example demonstrates a policy that limits access for specific users. In practice, protection policies often limit access to groups, such as all employees or specific departments, to ensure sensitive data stays within the organization’s or team’s boundaries.
The current limitation in SQL database is that protection policies do not alter permissions granted via SQL native access controls (For information about SQL native access controls, see Manage access in SQL database in Microsoft Fabric with SQL native authorization controls.) For instance, if a user is added to the db_owner native database role, a protection policy will not remove their admin access.
Next steps
- If you haven’t signed up for a Microsoft Fabric trial account yet, you can do that at Getting Started.
- Watch SQL Database in Fabric – Secured and governed by default | Data Exposed.
- Explore the documentation and end-to-end tutorials for SQL database. Specifically, to learn more about the topic of this article, refer to Protect sensitive data in SQL database in Microsoft Fabric with Microsoft Purview protection policies.
- For general information about information protection in Microsoft Fabric, refer to Information protection in Fabric.
- Navigate to https://ideas.fabric.microsoft.com to submit your feedback on access control or any other functionality in SQL database. (Make sure you select Database > SQL database.)
