Security for data warehousing in Microsoft Fabric
Applies to: ✅ SQL analytics endpoint and Warehouse in Microsoft Fabric
This article covers security topics for securing the SQL analytics endpoint of the lakehouse and the Warehouse in Microsoft Fabric.
For information on Microsoft Fabric security, see Security in Microsoft Fabric.
For information on connecting to the SQL analytics endpoint and Warehouse, see Connectivity.
Warehouse access model
Microsoft Fabric permissions and granular SQL permissions work together to govern Warehouse access and the user permissions once connected.
- Warehouse connectivity is dependent on being granted the Microsoft Fabric Read permission, at a minimum, for the Warehouse.
- Microsoft Fabric item permissions enable the ability to provide a user with SQL permissions, without needing to grant those permissions within SQL.
- Microsoft Fabric workspace roles provide Microsoft Fabric permissions for all warehouses within a workspace.
- Granular user permissions can be further managed via T-SQL.
Workspace roles
Workspace roles are used for development team collaboration within a workspace. Role assignment determines the actions available to the user and applies to all items within the workspace.
- For an overview of Microsoft Fabric workspace roles, see Roles in workspaces.
- For instructions on assigning workspace roles, see Give workspace access.
For details on the specific Warehouse capabilities provided through workspace roles, see Workspace roles in Fabric data warehousing.
Item permissions
In contrast to workspace roles, which apply to all items within a workspace, item permissions can be assigned directly to individual Warehouses. The user will receive the assigned permission on that single Warehouse. The primary purpose for these permissions is to enable sharing for downstream consumption of the Warehouse.
For details on the specific permissions provided for warehouses, see Share your warehouse and manage permissions.
Granular security
Workspace roles and item permissions provide an easy way to assign coarse permissions to a user for the entire warehouse. However, in some cases, more granular permissions are needed for a user. To achieve this, standard T-SQL constructs can be used to provide specific permissions to users.
Microsoft Fabric data warehousing supports several data protection technologies that administrators can use to protect sensitive data from unauthorized access. By securing or obfuscating data from unauthorized users or roles, these security features can provide data protection in both a Warehouse and SQL analytics endpoint without application changes.
- Object-level security controls access to specific database objects.
- Column-level security prevents unauthorized viewing of columns in tables.
- Row-level security prevents unauthorized viewing of rows in tables, using familiar
WHERE
clause filter predicates. - Dynamic data masking prevents unauthorized viewing of sensitive data by using masks to prevent access to complete, such as email addresses or numbers.
Object-level security
Object-level security is a security mechanism that controls access to specific database objects, such as tables, views, or procedures, based on user privileges or roles. It ensures that users or roles can only interact with and manipulate the objects they have been granted permissions for, protecting the integrity and confidentiality of the database schema and its associated resources.
For details on the managing granular permissions in SQL, see SQL granular permissions.
Row-level security
Row-level security is a database security feature that restricts access to individual rows or records within a database table based on specified criteria, such as user roles or attributes. It ensures that users can only view or manipulate data that is explicitly authorized for their access, enhancing data privacy and control.
For details on row-level security, see Row-level security in Fabric data warehousing.
Column-level security
Column-level security is a database security measure that limits access to specific columns or fields within a database table, allowing users to see and interact with only the authorized columns while concealing sensitive or restricted information. It offers fine-grained control over data access, safeguarding confidential data within a database.
For details on column-level security, see Column-level security in Fabric data warehousing.
Dynamic data masking
Dynamic data masking helps prevent unauthorized viewing of sensitive data by enabling administrators to specify how much sensitive data to reveal, with minimal effect on the application layer. Dynamic data masking can be configured on designated database fields to hide sensitive data in the result sets of queries. With dynamic data masking, the data in the database isn't changed, so it can be used with existing applications since masking rules are applied to query results. Many applications can mask sensitive data without modifying existing queries.
For details on dynamic data masking, see Dynamic data masking in Fabric data warehousing.
Share a warehouse
Sharing is a convenient way to provide users read access to your Warehouse for downstream consumption. Sharing allows downstream users in your organization to consume a Warehouse using SQL, Spark, or Power BI. You can customize the level of permissions that the shared recipient is granted to provide the appropriate level of access.
For more information on sharing, see How to share your warehouse and manage permissions.
Guidance on user access
When evaluating the permissions to assign to a user, consider the following guidance:
- Only team members who are currently collaborating on the solution should be assigned to Workspace roles (Admin, Member, Contributor), as this provides them access to all Items within the workspace.
- If they primarily require read only access, assign them to the Viewer role and grant read access on specific objects through T-SQL. For more information, see Manage SQL granular permissions.
- If they are higher privileged users, assign them to Admin, Member or Contributor roles. The appropriate role is dependent on the other actions that they will need to perform.
- Other users, who only need access to an individual warehouse or require access to only specific SQL objects, should be given Fabric Item permissions and granted access through SQL to the specific objects.
- You can manage permissions on Microsoft Entra ID (formerly Azure Active Directory) groups, as well, rather than adding each specific member. For more information, see Microsoft Entra authentication as an alternative to SQL authentication in Microsoft Fabric.
Related content
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for