{"id":104782,"date":"2024-12-04T20:45:00","date_gmt":"2024-12-04T20:45:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=104782"},"modified":"2024-12-01T21:41:51","modified_gmt":"2024-12-01T21:41:51","slug":"granting-permissions-in-a-fabric-data-warehouse-and-lakehouse","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/business-intelligence\/microsoft-fabric\/granting-permissions-in-a-fabric-data-warehouse-and-lakehouse\/","title":{"rendered":"Granting Permissions in a Fabric Data Warehouse and Lakehouse"},"content":{"rendered":"

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.<\/p>\n

However, this is far from a good idea.<\/p>\n

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.<\/p>\n

The Solution<\/h2>\n

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<\/p>\n

\"A<\/p>\n

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.<\/p>\n

But what user?<\/p>\n

Fabric works in the same tenant as Azure, with the same security environment. Every user available in Azure is also available in Fabric.<\/p>\n

For example, the statement below adds a user to the db_datareader role in a data warehouse<\/p>\n

alter role db_datareader add member [dennes2@dennesbufaloinfocom.onmicrosoft.com]<\/p>\n

After Adding the user<\/h2>\n

You can use the system table sys.database_principles<\/strong> to identify the users. An Azure user will not appear in this table, unless it received some kind of permission in the warehouse.<\/p>\n

For example:<\/p>\n

select name, principal_id, type_desc from sys.database_principals<\/p>\n

\"A<\/p>\n

Danger:<\/strong> 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.<\/p>\n

\"A<\/p>\n

More Granular Access Permissions<\/h2>\n

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?<\/p>\n

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.<\/p>\n

The code will be like this:<\/p>\n

--remove the user from the current role\n\nalter role db_datareader drop member [dennes2@dennesbufaloinfocom.onmicrosoft.com]\n\n-- create a custom role\n\ncreate role MyCustomRole\n\n-- Grant permission to the custom role\n\nGrant SELECT on dbo.customer to MyCustomRole\n\n-- add the user to the custom role\n\nalter role MyCustomRole add member [dennes2@dennesbufaloinfocom.onmicrosoft.com]<\/pre>\n
\"A<\/p>\n
Checking Group Membership of a User<\/h2>\n
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.<\/p>\n
We can do this making some joins with system tables, but it’s not a so simple query:<\/p>\n
SELECT\n    p.name AS PrincipalName,\n    p.type_desc AS PrincipalType,\n    r.name AS RoleName\nFROM\n   sys.database_principals p\n   LEFT JOIN\n   sys.database_role_members rm \n        ON p.principal_id = rm.member_principal_id\n   LEFT JOIN\n   sys.database_principals r \n        ON rm.role_principal_id = r.principal_id\nORDER BY p.name;<\/pre>\n
The table sys.database_role_members<\/strong> 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.<\/p>\n
\"A<\/p>\n
Lakehouse<\/h2>\n
All these features also work in a SQL Endpoint of a lakehouse, but with some additional details:<\/p>\n
    \n
  • The SQL Endpoint in a lakehouse is already read-only<\/li>\n
  • 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.<\/li>\n<\/ul>\n
    Summary<\/h2>\n
    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<\/p>\n","protected":false},"excerpt":{"rendered":"
    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……<\/p>\n","protected":false},"author":50808,"featured_media":104788,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[159164],"tags":[123645,158998,158997,4619],"coauthors":[6810],"class_list":["post-104782","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-microsoft-fabric","tag-data-warehouse","tag-lakehouse","tag-microsoft-fabric","tag-security"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/104782","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=104782"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/104782\/revisions"}],"predecessor-version":[{"id":104789,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/104782\/revisions\/104789"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/104788"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=104782"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=104782"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=104782"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=104782"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}