# Demystifying roles and access in Microsoft Fabric lake house

In Microsoft Fabric, SQL analytics endpoint is automatically generated for a Lakehouse. With SQL analytics endpoint a user can view the Lake house data using TSQL.

When it comes to lakehouse roles and access the lakehouse security model is not completely intertwined but to a certain extent is rather explicit.

Lets check out all the different scenarios through an example.

Note : If you would want to skip the writeup you can instead watch the video demo [here](https://www.azureguru.net/demystifying-security-in-microsoft-fabric-lake-house#heading-video-walkthrough).

### Role Types

In Microsoft Fabric there are four types of default workspace roles

* **Admin** -It is the highest form of privilege. An admin can view, modify, share, and manage all artifacts in a fabric workspace including permissions.
    
* **Member** - Can view, modify, and share all content in the workspace but has no authority to manage workspace permissions.
    
* **Contributor** - Can only view and modify content in the workspace.
    
* **Viewer** - Can view all content in the workspace but cannot perform any modifications
    

### Setup

I have a group in my Entra account named `FabricUsers`. This group has two users, `fabricuser_1` and `fabricuser_2`.

![Fabric Security](https://cdn.hashnode.com/res/hashnode/image/upload/v1734113002019/90462ead-9b18-40d8-b2f4-fca0c5cec0a8.png align="left")

### LakeHouse access without Workspace access

I have a lakehouse named `LakeHouse_1` that resides under workspace `My_Workspace`.

I will add the group `FabricUsers` and grant access to a lakehouse `LakeHouse_1`without any additional permissions selected.

![Fabric Security](https://cdn.hashnode.com/res/hashnode/image/upload/v1734129027392/11ce676d-5bd0-45fc-85db-6fef379ab154.png align="left")

When I login as a `fabricuser_1` , I can access the lakehouse but I cant see or browse any items in the lakehouse which is an expected behavior.

I have explained the reason for this behavior further in the article.

![Fabric Security](https://cdn.hashnode.com/res/hashnode/image/upload/v1734131716379/1f98cadb-acb6-4eff-87b9-2fbb6c6d4d54.png align="left")

### Grant WorkSpace Access

Now, I will grant a Viewer access(which is the least privileged access) to the Workspace `My_Workspace` where the lakehouse `Lakehouse_1` resides.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1734566462447/393a2730-a018-492b-8a0c-a107731302eb.png align="left")

Granting Viewer access to the Workspace, now allows the users of group `FabricUsers` to connect through SQL endpoints and access all the data in the lakehouse through it.

![Fabric Security](https://cdn.hashnode.com/res/hashnode/image/upload/v1734464999356/4de4de6f-7425-4ddc-b69d-68b96c9b167e.png align="left")

**Remember**: The group has **NOT** been granted “Read all SQL Endpoints data” permission.

**Note** : Granting any form of access to a user or group at the workspace level does **NOT** create a corresponding SQL user in the SQL Analytics endpoints.

![Fabric Security](https://cdn.hashnode.com/res/hashnode/image/upload/v1734466712725/c191ac95-d1ab-4c1e-89d1-2fc25e916442.png align="left")

### Default lake house role

OneLake generates a default RBAC Role named `DefaultReader` in every newly created Lakehouse , this group is automatically created and includes any users with any workspace access by default.

For example, if I grant the group `FabricUsers` Contributor access for the workspace

![Fabric Security](https://cdn.hashnode.com/res/hashnode/image/upload/v1734551620452/910385e0-d03d-462f-80b0-048164c1df69.png align="center")

by default the group `FabricUsers` gets added to the `DefaultReader` role in the lakehouse.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1734566100517/44781f00-e9a8-4eaa-9234-13948194db03.png align="left")

***But we DONT want any form of workspace level access for this group.***

Revert the workspace access granted for the Group `FabricUsers` as we want the scope of access limited only to the lakehouse `Lakehouse_1` not to the entire workspace.

Once done the access level for group `FabricUsers` would look like this.

![Fabric Security](https://cdn.hashnode.com/res/hashnode/image/upload/v1734551706999/b9071ecb-0c4d-420f-825b-a5f9e6b65633.png align="left")

So now the question arises as how to ensure that the lakehouse is accessible to the members of Group `FabricUsers` , given that the group `FabricUsers` have no access to the workspace.

### Manage OneLake data access (preview)

Open the Lakehouse and select `Manage OneLake data access (preview)`

![Fabric Security](https://cdn.hashnode.com/res/hashnode/image/upload/v1734465857193/b2aa5c0e-2e02-4f03-b2b2-cf0041df6329.png align="left")

and select the `DefaultReader` role

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1734565841239/32d0fc69-137f-4525-a157-e1246924be3f.png align="left")

and add the group `FabricUsers` as a member to this role.

![Fabric Security](https://cdn.hashnode.com/res/hashnode/image/upload/v1734466049245/04be7b9b-85b9-487b-8569-3cd3a93b5287.png align="left")

`FabricUser` group is added as a user to the `DefaultReader` role

![Fabric Security](https://cdn.hashnode.com/res/hashnode/image/upload/v1734465980071/8828d8f0-5ee3-4a8a-88b3-597b27b20f99.png align="left")

Give couple of minutes for changes to get reflected and then login into the tenant with any user of the group `FabricUsers` and the tables in the lakehouse should now be accessible.

![Fabric Security](https://cdn.hashnode.com/res/hashnode/image/upload/v1734133118778/dd03ef37-cb16-4425-a088-97a33797fb61.png align="left")

And that’s how you can grant granular access to lakehouses in the workspace without explicitly granting any access at the workspace level.

**Note :** The SQL endpoint access will still be denied as the group `FabricUsers` has no workspace access(as we reverted it) and also we haven’t granted any explicit SQL endpoint access.

![Fabric Security](https://cdn.hashnode.com/res/hashnode/image/upload/v1734198580697/c06ad3d9-a89d-47fa-9b6f-40e152ce4547.png align="left")

### Limit workspace access to a folder/s

Next ,what if we want the access to be limited to a single folder or specific folders in the lakehouse ?

Go back to the `DefaultReader` role and select folder/s to which you want the user to have access. I selected `lk_table_1` folder.

![Fabric Security](https://cdn.hashnode.com/res/hashnode/image/upload/v1734164586787/5ceed63f-95b8-4018-9509-95666b313c10.png align="left")

Now login with either user `fabricuser_1` or `fabricuser_2` and both users should be able to view only the table `lk_table_1` and not `lk_table_2`.

![Fabric Security](https://cdn.hashnode.com/res/hashnode/image/upload/v1734194546454/c9e51cda-8eef-4b12-a5fa-3a051ff95dab.png align="center")

**Note** : If you go back and reassign the Group `FabricUsers` workspace level access, the users in the group `FabricUsers` will be able to view data under all items under the lakehouse.

![Fabric Security](https://cdn.hashnode.com/res/hashnode/image/upload/v1734195978760/0339ed66-9dda-4876-ae79-8e0ce5af5d33.png align="left")

This means that access granted at workspace level will **ALWAYS** override access granted to the individual objects irrespective of access granted to them.

### Grant Read all SQL Endpoints data access

Granting “Read all SQL Endpoints data” will allow users of the group `FabricUsers` to query all the tables in the lakehouse.

To grant “Read all SQL Endpoints data” to group, select `Manage Permissions` in the Lakehouse option

![Fabric Security](https://cdn.hashnode.com/res/hashnode/image/upload/v1734205186998/e150dc50-4778-41d9-9c13-0aed2a7469c1.png align="left")

and select Add user

**Note** : Even if the `FabricUsers` group is already a user of the lakehouse, you can re-add it and grant additional access without needing to remove and re-add the user.

![Fabric Security](https://cdn.hashnode.com/res/hashnode/image/upload/v1734467875544/d43febe3-4c4b-4a08-ba42-66e8e86cb653.png align="left")

and grant “Read all SQL Endpoints data” to the Group after entering the group or user name.

![Fabric Security](https://cdn.hashnode.com/res/hashnode/image/upload/v1734467937572/35dbb9e4-5c10-4694-a9ce-fea88dfd5871.png align="left")

This makes the data accessible through SQL endpoints for users in group `FabricUsers`.

**Note** : Granting SQL endpoints access does not create a corresponding SQL user to whom the SQL endpoint access has been granted.

### LakeHouse Custom Roles

Now lets create a custom role in the lakehouse and name it as `MyDefinedRoles`

![Fabric Security](https://cdn.hashnode.com/res/hashnode/image/upload/v1734468171690/3289611c-4222-4d76-b961-4fe294456bcf.png align="left")

and limit the access of this role `MyDefinedRoles` to folder `lk_table_2`

![Fabric Security](https://cdn.hashnode.com/res/hashnode/image/upload/v1734561201585/2ff5ab58-7e27-4690-87a3-148a107ef4b2.png align="left")

Remember the access for role `DefaultReader` is limited to `lk_table_1`

![Fabric Security](https://cdn.hashnode.com/res/hashnode/image/upload/v1734561274323/bfb6c58a-a7fb-4116-bc28-31b02e7eecbe.png align="left")

I will explicitly make user `fabricuser_2` a member of the role `MyDefinedRoles`.

**Note** : That `fabricuser_2` is also a member of role `MyDefinedRoles` whose access is limited only to `lk_table_1`.

So what should happen in this case ? Will `fabricuser_2` user have access to both the folders as he/she is member of two roles with one role having access to `lk_table_1` and the other role having access to `lk_table_2` ?

If your answer is yes, then you are correct.

Data for both the tables `lk_table_1` and `lk_table_2` is accessible to user `fabricuser_2` .

![Fabric Security](https://cdn.hashnode.com/res/hashnode/image/upload/v1734552813248/3d299afc-3d91-4396-84c1-5f2701534f83.png align="center")

and for user `fabricuser_1` , who is member of the group `FabricUsers` and `FabricUsers` group being a member of `DefaultReader` role, only `lk_table_1` data is accessible.

![Fabric Security](https://cdn.hashnode.com/res/hashnode/image/upload/v1734469248459/dbe5c617-ce32-4327-afb0-8e53ddeb579d.png align="left")

***You can watch demonstration of the above use case in the video that I posted*** [***here***](https://youtu.be/7ojKROQZRm8?si=QGhKkb71DhwZQ7R6&t=954)***.***

But then what about SQL Endpoints ?

[Earlier](https://www.azureguru.net/demystifying-security-in-microsoft-fabric-lake-house#heading-grant-read-all-sql-endpoints-data-access) we had explicitly granted SQL endpoints access to the group `FabricUsers`. The tables will stay accessible for both users.

`fabricuser_2` SQL endpoint access :

![Fabric Security](https://cdn.hashnode.com/res/hashnode/image/upload/v1734211728399/880276d7-247b-4c34-9b93-5932844f2e12.png align="left")

`fabricuser_1` SQL endpoint access :

![Fabric Security](https://cdn.hashnode.com/res/hashnode/image/upload/v1734198870285/4cadf844-ce8b-4f2b-bfd9-44d62a10e5a8.png align="center")

If SQL endpoints access is not granted, the lakehouse data stays inaccessible for the users of the group `FabricUsers`.

### Access SQL Endpoints without granting access to the SQL Endpoints

I deleted the `FabricUsers` group from the lakehouse `LakeHouse_1`

![Fabric Security](https://cdn.hashnode.com/res/hashnode/image/upload/v1734555455646/7fffb7c7-94a6-4592-9247-78b632486812.png align="left")

and re added the `FabricUsers` group without granting any access to the SQL endpoints.

![Fabric Security](https://cdn.hashnode.com/res/hashnode/image/upload/v1734555608280/278adb88-f9da-4d2b-9500-4b92359d9be4.png align="center")

This is because I want to limit the scope of access for the objects in SQL endpoints at a granular level without granting explicit SQL endpoint access. By default, lakehouse tables are created under the `dbo` schema.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1734564052004/da19a20f-e58a-4413-b00a-92332c38ccb1.png align="left")

Grant the group `FabricUsers`, SELECT access on schema `dbo`.

```sql
GRANT SELECT ON SCHEMA::dbo TO [FabricUsers]
```

This creates a SQL user with name `FabricUsers`. Maybe this should help to trace user activity through tracing tools.

![Fabric Security](https://cdn.hashnode.com/res/hashnode/image/upload/v1734471490773/30da4d53-1362-4a34-824d-58416642174b.png align="left")

**Note** : Granting SQL endpoints access does not create a corresponding SQL user but `GRANT` or `DENY` does.

As both tables are under schema `dbo`, the tables in the schema `dbo` is accessible for users of group `FabricUsers`.

![Fabric Security](https://cdn.hashnode.com/res/hashnode/image/upload/v1734211501095/7a7471a8-2fc2-462c-b525-3dde5f2ed139.png align="left")

### Privilege precedence in SQL Endpoints

Now I have denied access to the schema `dbo` for group `FabricUsers`

```sql
DENY SELECT ON SCHEMA::dbo TO [FabricUsers]
```

But I granted user `fabricuser_2` explicit access to table `lk_table_1` that is part of the `dbo` schema.

```sql
GRANT SELECT ON [dbo].[lk_table_1] TO [fabricuser_2@azureguru.net];
```

`fabricuser_2` now has no access to any tables under schema `dbo` in spite of being granted exclusive access to the table `lk_table_1`.

![Fabric Security](https://cdn.hashnode.com/res/hashnode/image/upload/v1734214044459/2fa8723d-3618-4794-9cc8-57158a1dfac0.png align="center")

This is expected behavior as least privileges takes precedence irrespective of the hierarchy level where access is defined.

Lets switch the scenario wherein access to the `dbo` schema for `FabricUsers` is allowed.

```sql
GRANT SELECT ON SCHEMA::dbo TO [FabricUsers]
```

But select on table `lk_table_1` is denied for user `fabricuser_2`

```sql
DENY SELECT ON [dbo].[lk_table_1] TO [fabricuser_2@azureguru.net];
```

`DENY` will take precedence disallowing access to `lk_table_1` for user `fabricuser_2` but allowing access to table `lk_table_2` , both of which are part of the `dbo` schema.

![Fabric Security](https://cdn.hashnode.com/res/hashnode/image/upload/v1734214552214/40d0379f-75bb-4431-8a05-834f50dc3443.png align="center")

while the user `fabricuser_1` is able to access both the tables.

![Fabric Security](https://cdn.hashnode.com/res/hashnode/image/upload/v1734474864285/cb1772c8-3793-478b-b43c-df944e9b2d48.png align="left")

**Note** : Any form of explicit access, whether its a `GRANT` or `DENY` for individual users or groups creates corresponding users in the SQL endpoints.

![Fabric Security](https://cdn.hashnode.com/res/hashnode/image/upload/v1734475232017/af7231b3-e5c6-4b6b-9d58-5e34d7058271.png align="left")

### Video Walkthrough

**Part 1 :**

%[https://www.youtube.com/watch?v=7ojKROQZRm8] 

**Part 2** :

%[https://www.youtube.com/watch?v=OimWCChf9vs] 

### Conclusion

Microsoft Fabric offers a robust and integrated security framework to safeguard access to different entities in the workspaces. By leveraging built-in features like Roles, Endpoint access and Workspace level access to ensure high level security you can securely manage the Fabric tenant without much overheard.

I hope this write up was able to provide some level of insights into the intricacies related to different security entities involved in Fabric ecosystem.

Thanks for reading !!!