Security for data warehousing in Microsoft Fabric

  • Article

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 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 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.

User audit logs

To track user activity in warehouse and SQL analytics endpoint for meeting regulatory compliance and records managements requirements, a set of audit activities are accessible via Microsoft Purview and PowerShell. You can use user audit logs to identify who is taking what action on your Fabric items.

For more information on how to access user audit logs, see Track user activities in Microsoft Fabric and Operations list.

Related content