Manage access in SQL database in Microsoft Fabric with SQL native authorization controls
SQL database in Microsoft Fabric supports two different sets of authorization controls, Microsoft Fabric access controls and SQL native access controls.
As we discussed in an earlier blog post, Manage access for SQL databases in Microsoft Fabric with workspace roles and item permissions, Microsoft Fabric access controls are easy to use, but they support only two basic scenarios: provisioning full admin access and read-only access.
In this article, we address SQL native access controls and explain how you can use them to closely adhere to the principle of least privilege, when managing access for your SQL database items in Microsoft Fabric.
Recap on SQL native access controls
SQL native access control mechanisms available in SQL database in Microsoft Fabric include SQL permissions, database-level roles, and row-level security (RLS).
SQL permissions control access to entities called securables, which include databases, schemas, tables, views, and columns. Each securable has a set of permissions that control access to specific operations, such as the SELECT permission for SELECT Transact SQL statements.
The entity that receives permission to a securable is called a principal. SQL database in Microsoft Fabric supports two types of principals: database users and database-level roles. Database users represent Microsoft Entra principals – users, applications, and groups – in the database.
Database-level roles group database users with similar access levels, such as those based on their job function or responsibility within an organization. There are two types of database-level roles: fixed database roles that are built-in, predefined in the database and user-defined, or custom, database roles that you can create.
SQL native row-level security (RLS) allows you to implement restrictions on who can access specific rows in a table or a view.
SQL native access controls offer much greater flexibility compared to Microsoft Fabric workspace roles and item permissions. They allow precise control over who can access specific data elements and administrative operations.
You can configure SQL native access controls in two ways: using Transact-SQL or with a UI provided in the portal.
Setting up SQL native access controls with Transact-SQL
The below example illustrates how you can provision fine-grain access using primarily Transact-SQL.
In the example, we assume that a Microsoft Entra directory contains the ReportDevelopers group. Our goal is to enable members of that group to develop reports on customer data residing in a specific table – SalesLT.Customer. There are multiple ways to achieve that – the example illustrates a method that involves creating a custom role.
Step 1: Enable group members to connect to the database
As we explained in the previous article on security, Manage access for SQ databases in Microsoft Fabric with workspace roles and item permissions, to connect to a database, a user or an application must have the Read item permission in Microsoft Fabric. Without that users/apps cannot connect, even if they have SQL native permissions in the database.
To grant the ReportDevelopers group the Read item permission, simply share the database with the group without selecting any additional permissions. This is depicted in Figure 1. For further information, please refer to the previous blog post.
At this point, members of the ReportDevelopers group can connect to the database, but they cannot access any data.
Step 2: Configure a custom database-level role
Now, let’s create a custom database-level role, called CustomerDataViewer, and grant this role the permission to view (SELECT) data from a table containing customer information.
CREATE ROLE CustomerDataViewer;
GRANT SELECT ON OBJECT::SalesLT.Customer TO CustomerDataViewer;
Step 3: Create a database user
In this step, we create a database user representing the ReportDevelopers group in the database.
CREATE USER [ReportDevelopers] FROM EXTERNAL PROVIDER;
Step 4: Add the database user to the new role
Finally, we add the created ReportDevelopers user to the new custom role.
ALTER ROLE [CustomerDataViewer] ADD MEMBER [ReportDevelopers];
Members of the ReportDevelopers group should now be able to connect to the database and run SELECT queries against the SalesLT.Customer table, but they’re not allowed to access any other data or operations.
Setting up SQL native controls using the Manage SQL security UI in Microsoft Fabric portal
Transact-SQL allows you to use the complete functionality of the SQL native access controls and it’s a preferred way to automate database configuration. However, it can be challenging for developers who do not have Transact-SQL expertise. To address that, Microsoft Fabric portal provides the Manage SQL security UI, which hides the complexity of Transact-SQL, while supporting common access control use cases: managing custom roles that provide access to use schemas and managing role membership.
Let’s illustrate the capabilities of the Manage SQL security UI with another example. This time, our goal is to allow members of a different Microsoft Entra group, called SalesApps, to access all objects within the SalesLT schema.
Step 1: Define a new custom role
- On the database page in Microsoft Fabric portal, we select Security > Manage SQL security, refer to Figure 2.
2. This opens the Manage SQL Security dialog, shown in Figure 3, displaying the list of roles existing in the database.
3. Selecting New role opens a new dialog, shown in Figure 4, that allows us to enter a new role name – SalesDataWriter. Next, we select the SalesLT schema and choose one or more permission the role requires on the selected the schema. Select, Insert, Update, Delete apply to all tables and views in the schema. Execute applies to all functions and stored procedures in the schema.
4. When we click Save, the portal issues several Transact SQL statements (CREATE ROLE, GRANT) on our behalf.
Step 2: Add an Microsoft Entra group to the new role
What is left is to add our Microsoft Entra group to the new role.
- Back in the main Manage SQL security pane, we select the new role and click Manage access, refer to Figure 5.
2. In the new Manage access dialog, we enter the group name, SalesApps, and click Add. We’re now prompted to share the database with the group – remember, doing so is required to enable group members to connect to the database, refer to Figure 6.
3. Clicking Share database opens the familiar Grant people access dialog, shown in Figure 4. Clicking Grant without selecting any additional permissions grants the Read permission to SalesApps group.
5. When we’re brought back to the Manage access dialog and click Save, the portal issues Transact-SQL statements to create a database user for the SalesApps group and add that user to the SalesDataWriter role in the database.
Conclusions
SQL native access controls allow you to closely align authorization settings with the real needs of your user and adhere to the principle of least privilege. By using Transact SQL, you can take advantage of the full functionality of SQL native permissions, database-level roles and row-level security. Alternatively, you can use the Manage SQL security experience in Microsoft Fabric portal, which hides the complexity of Transact-SQL, and it streamlines defining simple custom roles and managing members of both fixed and custom roles.
Next steps
- If you haven’t sign up for a Microsoft Fabric trail yet, you can do so at: Getting Started | Microsoft Fabric.
- Explore the documentation and our end-to-end tutorials. Specifically, for documentation on access control in SQL database, see Authorization in SQL database – Microsoft Fabric | Microsoft Learn.
- Go 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.)
