title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | ms.custom | |
---|---|---|---|---|---|---|---|---|---|---|
Implement row-level security in Microsoft Fabric data warehousing |
A guide to use row-level security in Fabric Data Warehousing |
WilliamDAssafMSFT |
wiassaf |
stwynant |
04/24/2024 |
fabric |
data-warehouse |
how-to |
|
Applies to: [!INCLUDE fabric-se-and-dw]
Row-level security (RLS) in Fabric Warehouse and SQL analytics endpoint allows you to control access to rows in a database table based on user roles and predicates. For more information, see Row-level security in Fabric data warehousing.
This guide will walk you through the steps to implement row-level security in Microsoft Fabric Warehouse or SQL analytics endpoint.
Before you begin, make sure you have the following:
- A Fabric workspace with an active capacity or trial capacity.
- A Fabric Warehouse or SQL analytics endpoint on a Lakehouse.
- Either the Administrator, Member, or Contributor rights on the workspace, or elevated permissions on the Warehouse or SQL analytics endpoint.
- Log in using an account with elevated access on the Warehouse or SQL analytics endpoint. (Either Admin/Member/Contributor role on the workspace or Control Permissions on the Warehouse or SQL analytics endpoint).
- Open the Fabric workspace and navigate to the Warehouse or SQL analytics endpoint where you want to apply row-level security.
-
Determine the roles and predicates you want to use to control access to data. Roles define who can access data, and predicates define the criteria for access.
-
Create security predicates. Security predicates are conditions that determine which rows a user can access. You can create security predicates as inline table-valued functions. This simple exercise assumes there is a column in your data table,
UserName_column
, that contains the relevant username, populated by the system function USER_NAME().-- Creating schema for Security CREATE SCHEMA Security; GO -- Creating a function for the SalesRep evaluation CREATE FUNCTION Security.tvf_securitypredicate(@UserName AS varchar(50)) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS tvf_securitypredicate_result WHERE @UserName = USER_NAME(); GO -- Using the function to create a Security Policy CREATE SECURITY POLICY YourSecurityPolicy ADD FILTER PREDICATE Security.tvf_securitypredicate(UserName_column) ON sampleschema.sampletable WITH (STATE = ON); GO
-
Replace
YourSecurityPolicy
with your policy name,tvf_securitypredicate
with the name of your predicate function,sampleschema
with the name of your schema andsampletable
with the name of your target table. -
Replace
UserName_column
with a column in your table that contains user names. -
Replace
WHERE @UserName = USER_NAME();
with aWHERE
clause that matches the desired predicate-based security filter. For example, this filters the data where theUserName
column, mapped to the@UserName
parameter, matches the result of the system function USER_NAME(). -
Repeat these steps to create security policies for other tables if needed.
-
Log in to Fabric as a user who is a member of a role with an associated security policy. Use the following query to verify the value that should be matched in the table.
SELECT USER_NAME()
-
Query the database tables to verify that row-level security is working as expected. Users should only see data that satisfies the security predicate defined in their role. For example:
SELECT * FROM sampleschema.sampletable
-
Similar filtered results for the user will be filtered with other applications that use Microsoft Entra authentication for database access.
Regularly monitor and update your row-level security policies as your security requirements evolve. Keep track of role assignments and ensure that users have the appropriate access.