M
mahes-a
Row-Level Security (RLS) and Column-Level Security (CLS) are methods that simplify the design and coding of security in applications by imposing restrictions on data access. CLS allows for specific users to access only certain columns of a table relevant to their department, protecting sensitive data. RLS, on the other hand, restricts users to accessing only data rows pertinent to their role or department. Both methods locate the access restriction logic in the database tier, applying the restrictions every time data access is attempted from any tier, making the security system more reliable and robust.
In Microsoft Fabric Warehouse and SQL Endpoint
Visit Security for data warehousing to learn more about the security topics for securing the SQL Endpoint of the lakehouse and the Warehouse in Microsoft Fabric.
--grant the warehouse select to the non-workspace user
USE Saleswarehouse;
GRANT SELECT TO [productadminaad_grp];
--Create a Schema
CREATE SCHEMA [Sales]
GO
--Create a table to hold data.
CREATE TABLE [Sales].[SaleDetails](
[SaleID] [int] NOT NULL,
[ProductID] [varchar](100),
[ProductName] [varchar](100) ,
[SalesPersonID] [varchar](100)
)
GO
--populate the table with four rows of data, showing orders for each sales representative.
INSERT INTO Sales.SaleDetails VALUES (1, 'Prd1', 'ProductName1', 'salesusr@contoso.com');
INSERT INTO Sales.SaleDetails VALUES (2, 'Prd2', 'ProductName2', 'salesusr1@contoso.com');
INSERT INTO Sales.SaleDetails VALUES (3, 'Prd3', 'ProductName2', 'salesusr3@contoso.com');
INSERT INTO Sales.SaleDetails VALUES (4, 'Prd4', 'ProductName3', 'salesusr4@contoso.com');
--create schema
CREATE SCHEMA Security;
GO
--create a inline security function
CREATE FUNCTION Security.tvf_finsecuritypredicate(@SalesRep AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS tvf_finsecuritypredicate_result
WHERE @SalesRep = SUSER_SNAME() OR IS_MEMBER('productadminaad_grp')=1;
GO
CREATE SECURITY POLICY SalesFinFilter
ADD FILTER PREDICATE Security.tvf_finsecuritypredicate(SalesPersonID)
ON Sales.SaleDetails
WITH (STATE = ON);
GO
Implementing Column-Level Security (CLS) in Microsoft Fabric SQL Endpoint
The example will use Grant Statement to provide members of the Security Group with access to view only few columns of a table.
GRANT SELECT ON [ProductLakeHouse].[dbo].[customerdetails]([CustomerID],[CustomerName],[PhoneNumber]) TO [CLSAcessAADgrp];
Summary
In conclusion, this guide provides the steps to implement Row-Level Security (RLS) and Column-level security on Microsoft Fabric Warehouse and SQL Endpoint.
Continue reading...
In Microsoft Fabric Warehouse and SQL Endpoint
- Row-Level Security (RLS) is implemented by using the CREATE SECURITY POLICY Transact-SQL statement, and predicates created as inline table-valued functions.
- Column-level security is implemented using the GRANT T-SQL statement. Only Azure Active Directory authentication is supported.
- This example provides how we can grant row level access to two users (sales user and a user who is a member of the Product Admin Microsoft Entra ID (AAD) group) in Fabric.
- We will create and populate a table with four rows.
- We will then create an inline table-valued function and a security policy for the table.
- The example then shows how we grant access so the select statements output rows will be filtered for the using RLS implementation.
- The sales user will be able to view only his sales while the product admin AAD group user will have all access to view all the sales records.
- Create a Microsoft Entra ID security group (Azure Active Directory is now Microsoft Entra ID) for the Product admins and make sure that the Group Type is Security.
- Add users to the Security Group
- Access to the sales user will be provided using the Workspace roles. From Workspace -> Manage access -> Add people or groups and add the user
- For the Security Group which requires primarily read only access, we would assign them to the Viewer role and grant read access through T-SQL.
- Using the share icon
of the warehouse, share the warehouse, ensure no "Additional Permissions" are provided when sharing.
Visit Security for data warehousing to learn more about the security topics for securing the SQL Endpoint of the lakehouse and the Warehouse in Microsoft Fabric.
- Open the warehouse and create a query window with "New SQL query" option. We will use the TSQL Grant option to provide access to the product admin security group.
--grant the warehouse select to the non-workspace user
USE Saleswarehouse;
GRANT SELECT TO [productadminaad_grp];
- Create and populate table.
--Create a Schema
CREATE SCHEMA [Sales]
GO
--Create a table to hold data.
CREATE TABLE [Sales].[SaleDetails](
[SaleID] [int] NOT NULL,
[ProductID] [varchar](100),
[ProductName] [varchar](100) ,
[SalesPersonID] [varchar](100)
)
GO
--populate the table with four rows of data, showing orders for each sales representative.
INSERT INTO Sales.SaleDetails VALUES (1, 'Prd1', 'ProductName1', 'salesusr@contoso.com');
INSERT INTO Sales.SaleDetails VALUES (2, 'Prd2', 'ProductName2', 'salesusr1@contoso.com');
INSERT INTO Sales.SaleDetails VALUES (3, 'Prd3', 'ProductName2', 'salesusr3@contoso.com');
INSERT INTO Sales.SaleDetails VALUES (4, 'Prd4', 'ProductName3', 'salesusr4@contoso.com');
- Create a new schema, and an inline table-valued function. The function returns 1 when a row in the SalesPersonID column is the same as the user executing the query (@SalesRep = SUSER_SNAME()) or if the user executing the query is a member of the productadminaad_grp security group (IS_MEMBER('productadminaad_grp')=1;[FONT=Menlo, Monaco, Consolas, Courier New, monospace])[/FONT] This example of a user-defined, table-valued function is useful to serve as a filter for the security policy created in the next step.
--create schema
CREATE SCHEMA Security;
GO
--create a inline security function
CREATE FUNCTION Security.tvf_finsecuritypredicate(@SalesRep AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS tvf_finsecuritypredicate_result
WHERE @SalesRep = SUSER_SNAME() OR IS_MEMBER('productadminaad_grp')=1;
GO
- Create a security policy adding the function as a filter predicate. The state must be set to ON to enable the policy
CREATE SECURITY POLICY SalesFinFilter
ADD FILTER PREDICATE Security.tvf_finsecuritypredicate(SalesPersonID)
ON Sales.SaleDetails
WITH (STATE = ON);
GO
- Let's test the filtering predicate, by selecting from the Sales.SaleDetailstable as each user.
- Copy the connection string for the warehouse and use Sql Management Studio to login as the sales user and validate the sales user should only see their own sales.
- Login via SSMS as the user who is a member of the Product Admin security group, and the security group user should see all four rows.
Implementing Column-Level Security (CLS) in Microsoft Fabric SQL Endpoint
The example will use Grant Statement to provide members of the Security Group with access to view only few columns of a table.
- Create a Microsoft Entra ID security group (Azure Active Directory is now Microsoft Entra ID) and make sure that the Group Type is Security.
- Add users to the Security Group
- Using the share icon
of the lakehouse, share the lakehouse to the security group, ensure no "Additional Permissions" are provided when sharing.
- Open the SQL endpoint of the lakehouse and open the Query window using "New SQL query" and use the Grant statement to provide access to selected columns of the customer details table.
GRANT SELECT ON [ProductLakeHouse].[dbo].[customerdetails]([CustomerID],[CustomerName],[PhoneNumber]) TO [CLSAcessAADgrp];
- Copy the connection string for the SQL endpoint and use Sql Management Studio to login as the user who is part of the security group created in previous steps and validate the user should be able to view the specified columns of the table.
Summary
In conclusion, this guide provides the steps to implement Row-Level Security (RLS) and Column-level security on Microsoft Fabric Warehouse and SQL Endpoint.
Continue reading...