We are becoming used to being a bit lazy when granting permissions to Data Warehouses and lakehouses in Fabric. We only go to the workspace level and add the user as a viewer or member.
However, this is far from a good idea.
This practice not only gives the user access to all tables in the warehouse or lakehouse, but also gives access to all objects in the workspace. Bad idea.
The Solution
The solution is on the old and good SQL security practices. A Fabric Data Warehouse has database roles in a very similar way as Azure SQL/SQL Server
You can add to the roles users which have no access in the workspace. In this way, the user will receive access to the Data Warehouse/Lakehouse, but not to the other objects in the workspace.
But what user?
Fabric works in the same tenant as Azure, with the same security environment. Every user available in Azure is also available in Fabric.
For example, the statement below adds a user to the db_datareader role in a data warehouse
alter role db_datareader add member [dennes2@dennesbufaloinfocom.onmicrosoft.com]
After Adding the user
You can use the system table sys.database_principles to identify the users. An Azure user will not appear in this table, unless it received some kind of permission in the warehouse.
For example:
select name, principal_id, type_desc from sys.database_principals
Danger: It’s interesting to notice this user doesn’t appear in the option Manage Permissions in the Data Warehouse/lakehouse. The granular permission management is good, but it can also be dangerous because you can lose track of who has access to what.
More Granular Access Permissions
The Database Roles available give permission to read all tables or write on all tables. What if you would like to have more granular control than this?
You also have the DB Custom Roles. You can create a completely custom role; define the granular permissions you would like and add the user to the custom role.
The code will be like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
--remove the user from the current role alter role db_datareader drop member [dennes2@dennesbufaloinfocom.onmicrosoft.com] -- create a custom role create role MyCustomRole -- Grant permission to the custom role Grant SELECT on dbo.customer to MyCustomRole -- add the user to the custom role alter role MyCustomRole add member [dennes2@dennesbufaloinfocom.onmicrosoft.com] |
Checking Group Membership of a User
We need to be capable of checking the groups the user belongs to. This is especially important because these permissions don’t appear inside Manage Permissions.
We can do this making some joins with system tables, but it’s not a so simple query:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT p.name AS PrincipalName, p.type_desc AS PrincipalType, r.name AS RoleName FROM sys.database_principals p LEFT JOIN sys.database_role_members rm ON p.principal_id = rm.member_principal_id LEFT JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id ORDER BY p.name; |
The table sys.database_role_members needs to be joined twice with sys.database_principals, one to retrieve the name of the user who belongs to a group and the other to retrieve the name of the group.
Lakehouse
All these features also work in a SQL Endpoint of a lakehouse, but with some additional details:
- The SQL Endpoint in a lakehouse is already read-only
- There were restrictions about if spark code in a notebook would respect the rules. This evolves all the time, take care and check the current state of this.
Summary
We can and we should be managing permissions in our Fabric environment in a granular way. We need to leave behind old practices from Power BI era and increase the security level of our solutions
Load comments