🏭🗒️Microsoft Fabric: Security in Warehouse [Notes]

Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 25-Mar-2024

[Microsoft Fabric] Security in Warehouse

• {def} suite of technologies aimed at safeguarding sensitive information in Fabric [1]
• leverages SQL engine’s security features [1]
• allows for security mechanism at the warehouse level [1]
• ⇐ the warehouse and SQL analytics endpoint items also allow for the defining of native SQL security [4]
• the permissions configured only apply to the queries executed against the respective surfaces [4]
• the access to OneLake data is controlled separately through OneLake data access roles [4]
• {recommendation} to ensure users with SQL specific permissions don't see data they don't have SQL access to, don't include those users in a OneLake data access role [4]
• supports a range of data protection features that enable administrators to shield sensitive data from unauthorized access [1]
• ⇐ across warehouses and SQL analytics endpoints without necessitating changes to applications [1]
• {type} object-level security (OLS)
• permissions governing DML operations [1]
• applies to tables and views
• ⇐ when denied, the user will be prevented from performing the respective operation
• SELECT
• allows users to view the data within the object [1]
• INSERT
• allows users to insert data in the object [1]
• UPDATE
• allows users to update data within the object [1]
• DELETE
• allows users to delete the data within the object [1]
• permissions can be granted, revoked or denied on database objects [1]
•  tables and views
• GRANT
• permission is granted to user or role [1]
• DENY
• permissions is denied to user or role [1]
• REVOKE
• permissions is revoked to user or role [1]
• ALTER
• grants the user the ability to change the definition of the object [1]
• CONTROL
• grants the user all rights to the object [1]
• {principle} least privilege
• users and applications should only be given the permissions needed in order for them to complete the task
• {type} column-level security (CLS)
• allows to restrict column access to sensitive data [1]
• provides granular control over who can access specific pieces of data [1]
• ⇒ enhances the overall security of the data warehouse [1]
• steps
• identify the sensitive columns [1]
• define access roles [1]
• assign roles to users [1]
• implement access control [1]
• restrict access to ta column based on user's role [1]
• {type} row-level security (RLS)
• provides granular control over access to rows in a table based on group membership or execution context [1]
• using WHERE clause filters [1]
• works by associating a function (aka security predicate) with a table [1]
• defined to return true or false based on certain conditions [1]
• ⇐ typically involving the values of one or more columns in the table [1]
• when a user attempts to access data in the table, the security predicate function is invoked [1]
• if the function returns true, the row is accessible to the user; otherwise, the row doesn't show up in the query results [1]
• the predicate can be as simple/complex as required [1]
• the process is transparent to the user and is enforced automatically by SQL Server
• ⇐ ensures consistent application of security rules [1]
• implemented in two main steps:
• filter predicates 
• an inline table-valued function that filters the results based on the predicate defined [1]
• security policy
• invokes an inline table-valued function to protect access to the rows in a table [1]
• because access control is configured and applied at the warehouse level, application changes are minimal - if any [1]
• users can directly have access to the tables and can query their own data [1]
• {recommendation} create a separate schema for predicate functions, and security policies [1]
• {recommendation} avoid type conversions in predicate functions [1]
• {recommendation} to maximize performance, avoid using excessive table joins and recursion in predicate functions [1]
• {type} dynamic data masking (DDM) 
• allows to limits data exposure to nonprivileged users by obscuring sensitive data [1]
• e.g. email addresses 
• {benefit} enhance the security and manageability of the data [1]
• {feature} real-time masking
• when querying sensitive data, DDM applies dynamic masking to it in real time [1]
• the actual data is never exposed to unauthorized users, thus enhancing the security of your data [1]
• straightforward to implement [1]
• doesn’t require complex coding, making it accessible for users of all skill levels [1]
• {benefit} the data in the database isn’t changed when DDM is applied
• ⇒  the actual data remains intact and secure, while nonprivileged users only see a masked version of the data [1]
• {operation} define masking rule
• set up at column level [1]
• offers a suite of features [1]
• comprehensive and partial masking capabilities [1]
• supports several masking types
• help prevent unauthorized viewing of sensitive data [1]
• by enabling administrators to specify how much sensitive data to reveal [1]
• ⇒  minimal effect on the application layer [1]
• applied to query results, so the data in the database isn't changed 
• ⇒  allows many applications to mask sensitive data without modifying existing queries  [1]
• random masking function designed for numeric data [1]
• {risk} unprivileged users with query permissions can infer the actual data since the data isn’t physically obfuscated [1]
• {recommendation} DDM should be used as part of a comprehensive data security strategy [1]
• should include
• the proper management of object-level security with SQL granular permissions [1]
• adherence to the principle of minimal required permissions [1]
• {concept} Dynamic SQL 
• allows T-SQL statements to be generated within a stored procedure or a query itself [1]
• executed via sp_executesql stored procedure
• {risk} SQL injection attacks
• use  QUOTENAME to sanitize inputs [1]
write access to a warehouse or SQL analytics endpoint
• {approach} granted through the Fabric workspace roles
• the role automatically translates to a corresponding role in SQL that grants equivalent write access [4]
• {recommendation} if a user needs write access to all warehouses and endpoints, assign the user to a workspace role [4]
• use the Contributor role unless the user needs to assign other users to workspace roles [4]
• {recommendation} grant direct access through SQL permissions if the user only needs to write to specific warehouses or endpoints [4]
• {approach} grant read access to the SQL engine, and grant custom SQL permissions to write to some or all the data [4]
write access to a warehouse or SQL analytics endpoint
• {approach} grant read access through the ReadData permission, granted as part of the Fabric workspace roles [4]
•  ReadData permission maps the user to a SQL role that gives SELECT permissions on all tables in the warehouse or lakehouse
• helpful if the user needs to see all or most of the data in the lakehouse or warehouse [4]
• any SQL DENY permissions set on a particular lakehouse or warehouse still apply and limit access to tables [4]
• row and column level security can be set on tables to restrict access at a granular level [4]
• {approach} grant read access to the SQL engine, and grant custom SQL permissions to read to some or all the data [4]
• if the user needs access only to a specific lakehouse or warehouse, the share feature provides access to only the shared item [4]
• during the share, users can choose to give only Read permission or Read + ReadData 
• granting Read permission allows the user to connect to the warehouse or SQL analytics endpoint but gives no table access [4]
• granting users the ReadData permissions gives them full read access to all tables in the warehouse or SQL analytics endpoint
• ⇐ additional SQL security can be configured to grant or deny access to specific tables [4]

Previous Post <<||>> Next Post 

References:

[1] Microsoft Learn (2024) Secure a Microsoft Fabric data warehouse [link]

[2] Data Mozart (2025) Lock Up! Understanding Data Access Options in Microsoft Fabric, by Nikola Ilic [link]
[3] Microsoft Learn (2024) Security in Microsoft Fabric [link]
[4] Microsoft Learn (2024) Microsoft Fabric: How to secure a lakehouse for Data Warehousing teams [link]

Resources:

[R1] Microsoft Learn (2025) Fabric: What's new in Microsoft Fabric? [link]
[R2] Microsoft Learn (2025) Fabric: Security for data warehousing in Microsoft Fabric [link]

[R3] Microsoft Learn (2025) Fabric: Share your data and manage permissions [link]

Acronyms:

CLS - Column-Level Security

DDM - Dynamic Data Masking
DML - Data Manipulation Language 
MF - Microsoft Fabric

OLS - Object-Level Security

RLS - Row-Level Security
SQL - Structured Query Language