User Profile
SamyAbdul
Frequent Visitor
Joined 2 years ago
User Widgets
Contributions
Re: RLS,CLS Security Design and Implementation
Thank you oussamahaimoud . Thanks for the response ,the table is a good reference for the pre-GA state of OneLake Security, but it needs updating for May 2026. The picture has changed significantly. OneLake Security reached GA in May 2026 and now supports both RLS and CLS natively across all Fabric engines. The corrected matrix looks like this: LayerRLSCLSNotesOneLake (GA May 2026)✅✅Enforced across Spark, SQL Endpoint (User Identity mode), Direct Lake, Data AgentSQL Endpoint✅✅Supplementary- required for L2 defence-in-depthSemantic Model✅ DAX✅ OLSReport consumer layer -Power BI only The key enabler is SQL Analytics Endpoint in User Identity mode. Once you switch the endpoint from delegated to user identity mode (Security tab → Use OneLake security for tables), OneLake security roles, including RLS predicates and CLS column policies, are fully enforced on all T-SQL queries. SQL GRANT/REVOKE on tables is then ignored; all table-level security flows from OneLake roles. This is confirmed in the GA documentation and post-GA technical writeups (e.g. Joel Tan's May 2026 breakdown: https://joeltan.me/microsoft-fabric-onelake-security-ga/). Two constraints from the GA release that are worth flagging for anyone implementing this: RLS predicate and CLS column policy must be combined in a single OneLake security role ,splitting them across two roles causes query errors. This is a gotcha that catches people out. OneLake RLS applies to Viewer-role users only ,workspace Admin, Member, and Contributor roles bypass OneLake security entirely by platform design. Your workspace role assignments need to be tight before L1 enforcement means anything. The "semantic model as primary enforcement" model was the correct design pattern before OneLake Security GA because the storage layer had no enforcement capability. With GA, the recommended architecture flips: L1 OneLake is primary (enforces across all engines simultaneously), L2 SQL Endpoint is supplementary defence-in-depth, and L3 DAX/OLS covers the Power BI report consumer layer. All three together give you true defence-in-depth , no single layer is optional. Appreciate your help.325Views0likes0CommentsRLS,CLS Security Design and Implementation
Hi Experts, we are about to implement RLS and CLS at three levels such as Onelake Security ,SQL Analytics Endpoint and Semantic Model. I need experts advice what are key considerations ,we need to bear in mind 2. What are the grey areas that might led to pitfall 3. How to ensure RLS and CLS design highly optimal. Your guidance would be highly appreciated. Thank youSolved362Views0likes3CommentsRe: DimUserAccess Optimal Design
Thank you . Please find the details : Column categories 26 Identity 8,scope 6 life cycle flags 4 and Audit and Pipeline 8 Purpose : Dual -RSL and CLS enforcement at semantic model layer plus SOX audit trail, DAX resolves the authenticated user by USERPRINCIPALNAME Cardinality :Low thousands , estimated steady state 1k to 10k rows Many to Many intentional one user can have multiple rows, one for AccessScopeType they hold Semantic Model Mode : Not locked in yet. Thank again and I appreciate your help.228Views0likes1CommentRe: DimUserAccess Optimal Design
Thank you ,happy to share the details: Column categories (26 total) Identity (8): UserAccessKey, AccessGrantType, AccessSubjectType, UserPrincipalName, GroupObjectId, EmployeeId, DisplayName, PersonaRole Scope (6): AccessScopeType, PermittedSegmentCode, PermittedBranchRegionCode, PermittedSiteCode, PermittedBusinessUnitCode, SecurityTier Lifecycle flags (4): IsSOXUser, EffectiveFromDate, EffectiveToDate, IsActive Audit / Pipeline (8): ApprovalReference, SourceSystem, CreatedDateTime, CreatedBy, LastModifiedDateTime, LastModifiedBy, RecordHash, BatchId Purpose Dual - RLS + CLS enforcement at the semantic model layer, plus SOX audit trail. DAX resolves the authenticated user via USERPRINCIPALNAME(), looks up active entitlements at query time, filters rows by scope (RLS) and columns by SecurityTier (CLS via OLS). Not a reporting dimension - lives in a restricted security workspace, never exposed to consumers. Cardinality Low thousands - driven by active users × avg scope rows per user (typically 1–3). Estimated steady state 1k–10k rows. Well within CALCULATETABLE + IN safe range; TREATAS/INTERSECT documented as the upgrade path if that threshold is crossed. Many-to-many - yes, intentional One user can have multiple rows - one per AccessScopeType they hold (e.g. SEGMENT + BRANCH_REGION simultaneously). Each DAX pattern independently filters its scope type from _activeEntitlements and the union governs total visibility. A normalised bridge table approach (DimUserAccess + UserAccessScope) is flagged as the Phase 1 option if scope combinations grow complex. Semantic model mode Not locked in yet - DAX patterns are mode-agnostic. F32 dedicated capacity so Direct Lake is the target. Main thing to validate: Direct Lake → DirectQuery fallback behaviour under RLS, which needs explicit testing before go-live. I appreciate your help alot.234Views0likes0CommentsDimUserAccess Optimal Design
Hi Experts, I have designed the DimUserAccess dimension which consists of 26 columns, it could be fair to assume that it is wide table. Now, I have design quandry such as leave it as a single table or is it better to split it into two tables (DimUserAccess + UserAccessScope bridge table) and normalize. Please help me what approach would be in this scenerio. what are the pros and cons of each approach. Any other key considerations I should be mindful of. Thank a lot in advance.Solved286Views0likes4Comments
Data Privacy
Microsoft Fabric Community and Privacy
To learn more about how we manage your data, please review the Microsoft Fabric Community Data Privacy guide.