Forum Discussion
Post PatronPermissions in Silver layer (canonical layer)
Dears,
Silver is my canonical layer, its composed of one workspace which has inside only one lakehouse
In the lakehouse you have multiple tables from multiple different sources which were brought fro m bronze and all landed in the DBO schema of this lakehouse
The majority of the user will not consume from this layer. They will instead consume from Gold and PBI reports
But there are some users and apps (data scientists, data analysts, Apps) which may consume from it
So far, for silver, we have only Entra ID groups for permissions at workspace level : Member, Contributor and Viewer and we add the users to this groups
But there are some cases where I want to allow users to only see certain tables, not all the tables , hence not even the workspace viewer should be allowed. For example, some users should only see some tables, other ony other tables, etc.. Not needs RLS only OLS
Problem is that we have hundreds of tables, hence creating a Entra-ID group (to map the users to it) per each object (table) is not possible. On the other hand, I would want to avoid having to implement OLS via onelake security and add user by user...
What do you think is the best approach?
We have organization users (data scientists) but as well service principal (apps)
Thank you so much, Pedro
Hi fabricpribeiro,
Thank you for reaching out to the Microsoft fabric community forum.
Currently, Lakehouse access in Microsoft Fabric is mainly controlled at the workspace level, so once a user or service principal has access, they can view all tables in that Lakehouse. There is no fully supported way to apply table‑level security (OLS) across all access paths like Lakehouse UI, notebooks, and SQL endpoint within the same Lakehouse.
Solutions using SQL endpoint DENY permissions will work only for SQL access but will not block access through Lakehouse UI or Spark, so they are not a complete security solution. Because of this limitation, the recommended approach is to limit direct access to the Silver layer and use it as an internal canonical layer, while exposing data through Gold Lakehouses, views, or semantic models, where security can be applied in a scalable way. If certain tables must be isolated, the practical option is to separate them into different Lakehouses or workspaces, instead of trying per‑table security inside one Lakehouse. This aligns with current Fabric security capabilities and avoids unmanageable permission setups.
Kindly refer to the below documentation links for better understanding:
Lakehouse sharing and permission management - Microsoft Fabric | Microsoft Learn
Hope the above provided information help you resolve the issue, if you have any further concerns or queries, please feel free to reach out to us.
Regards,
Community Support Team.Hi fabricpribeiro ,
In the Silver (canonical) layer, workspace‑level Entra ID groups (Member, Contributor, Viewer) are too coarse when you need object‑level security. Creating hundreds of groups per table is not scalable, and assigning users individually is unmanageable. The best approach is to leverage table‑level permissions via Lakehouse SQL endpoint combined with role‑based groups that map to sets of tables, not individual ones. For example, create functional groups (Data Science, Finance Apps, Claims Apps) and grant each group access only to the relevant tables. Service principals can be added to these groups for app access. This avoids per‑table groups while still enforcing object‑level security. If finer granularity is needed, consider OneLake object‑level security (OLS) but manage it through automation scripts or policies rather than manual assignment. In short, group users by function, assign them to roles, and apply table‑level permissions at scale—this balances manageability with security.
Thanks,
Sai Teja
5 Replies
SrisakthiSuper User
Hi fabricpribeiro ,
Why dont you try OLS using sql endpoint?
Let's say i have 15 users and 110 tables. Firstly I will create usermapping table which will contain user id and tables they should not have access. Create Stored Procedure which will have Deny users to objects statement which in turn fetch users and tables from the mapping table.
Note: This will work only on SQL endpoint, still they can view the data at lakehouse UI level
Regards,
Srisakthi
Hi fabricpribeiro,
Thank you for reaching out to the Microsoft fabric community forum.
Currently, Lakehouse access in Microsoft Fabric is mainly controlled at the workspace level, so once a user or service principal has access, they can view all tables in that Lakehouse. There is no fully supported way to apply table‑level security (OLS) across all access paths like Lakehouse UI, notebooks, and SQL endpoint within the same Lakehouse.
Solutions using SQL endpoint DENY permissions will work only for SQL access but will not block access through Lakehouse UI or Spark, so they are not a complete security solution. Because of this limitation, the recommended approach is to limit direct access to the Silver layer and use it as an internal canonical layer, while exposing data through Gold Lakehouses, views, or semantic models, where security can be applied in a scalable way. If certain tables must be isolated, the practical option is to separate them into different Lakehouses or workspaces, instead of trying per‑table security inside one Lakehouse. This aligns with current Fabric security capabilities and avoids unmanageable permission setups.
Kindly refer to the below documentation links for better understanding:
Lakehouse sharing and permission management - Microsoft Fabric | Microsoft Learn
Hope the above provided information help you resolve the issue, if you have any further concerns or queries, please feel free to reach out to us.
Regards,
Community Support Team.Hi fabricpribeiro,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.
Regards,
Community Support Team.Hi fabricpribeiro,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.
Regards,
Community Support Team.
SaiTejaTalasilaHi fabricpribeiro ,
In the Silver (canonical) layer, workspace‑level Entra ID groups (Member, Contributor, Viewer) are too coarse when you need object‑level security. Creating hundreds of groups per table is not scalable, and assigning users individually is unmanageable. The best approach is to leverage table‑level permissions via Lakehouse SQL endpoint combined with role‑based groups that map to sets of tables, not individual ones. For example, create functional groups (Data Science, Finance Apps, Claims Apps) and grant each group access only to the relevant tables. Service principals can be added to these groups for app access. This avoids per‑table groups while still enforcing object‑level security. If finer granularity is needed, consider OneLake object‑level security (OLS) but manage it through automation scripts or policies rather than manual assignment. In short, group users by function, assign them to roles, and apply table‑level permissions at scale—this balances manageability with security.
Thanks,
Sai Teja