Row Level Security is a very key requirement for most database or data lake applications. Most of the databases are having natively build row-level security but Synapse serverless SQL pool doesn’t support this inbuilt functionality. In this article, we will see how to implement it.
What is Azure Synapse Analytics SQL pool?
A serverless SQL pool is a query service over the data in your data lake. It enables you to access your data through the following functionalities:
- Familiar TSQL syntax
- Integrated Connectivity via T-SQL Interface
A serverless SQL pool is a distributed data processing system, built for large-scale data and computational functions. As a serverless SQL pool doesn’t hold data there is no storage associated with it.
Interview Ques: Do we have native row-level security available in the serverless SQL pool?
Current problem Scenario
In the current scenario, we have the data of a multi-tenant application which is placed in a Data Lake in files. Each entity of a database is placed as a file in the Azure Data Lake. This file has the column which says defines the row belongs to which tenant. This data is getting accessed by each tenant using a serverless SQL pool. We need to implement row-level security so tenants can see data that only belongs to them.
Implementing Row-Level Security in Azure Synapse Analytics Serverless SQL pools using TSQL
To Implement Row Level Security, we will take two files as shown below with some sample data.
Demo CSV contains the Customer data where TenantID is to identify the id of the tenant for which the particular row belongs.
CustomerName | CustomerDepartment | CustomerID | TenantName | TenantID |
XYZ | Sales | 20004 | Client1 | 1 |
ABC | Sales | 20005 | Client1 | 1 |
RXT | Sales | 20006 | Client2 | 2 |
DBR | Sales | 20007 | Client3 | 3 |
XYZ | Sales | 20008 | Client3 | 3 |
Now we will have one more file which has details related to access to
UserName | ClientID | SUSER_SNAME |
Randheer | 1 | randheer.parmar@xyz.com |
Randheer | 2 | randheer.parmar@xyz.com |
abc | 3 | abc@xyz.com |
abc | 1 | abc@xyz.com |
In the above table, we have a username, ClientId columns with TenantID assigned to that user, and the Suser_Sname column the Service Principal Id of your Azure Synapse login.
These files will be placed in the blob storage as shown in the below screenshot.
Now go to Azure Synapse workspace and create a Serverless SQL Pool and the database in serverless SQL Pool as shown below.
Once you create the SQL Database in serverless SQL Pool you will notice that there is the option of only external tables and external resources. There is no option for other tables which means you cannot create and table with data storage.
Another option we can see is a view. The rest are related to access roles and logins. We will now create the external tables for the files which we placed earlier in the Blob Storage.
Below is the syntax for creating the External Table, in our case, we are creating two tables as we do have two files, we will follow the same structure as those of files.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE EXTERNAL TABLE Demo ( [CustomerName] VARCHAR (100) COLLATE Latin1_General_BIN2, [CustomerDepartment] VARCHAR (100) COLLATE Latin1_General_BIN2, [CustomerID] smallint, [TenantName] VARCHAR (100), TenantID smallint ) WITH ( LOCATION = 'drbtest/DBR_Demo.csv', DATA_SOURCE = ExternalDataSource, FILE_FORMAT = ExternalFileFormat ) |
In the above syntax, there are three major building blocks of an external table. Location, Data Source, and File Format. We need to create an External Data source and External file format before creating the external table.
For this demo, we have used the below script to create External Data Source and External File Format.
|
1 2 3 4 5 6 |
CREATE EXTERNAL DATA SOURCE [ExternalDataSource] WITH ( LOCATION = 'https://******.blob.core.windows.net', CREDENTIAL = SQLCREDENTIALS ) |
)
You must provide the container name in place of ***** in the URL. For Creating the External Data Source, you need to provide CREDENTIALS. These are database-scoped Credentials. Where we have to provide details to connect to Blob Storage.
|
1 2 3 4 5 |
CREATE DATABASE SCOPED CREDENTIAL [SQLCREDENTIALS] WITH IDENTITY='SHARED ACCESS SIGNATURE', SECRET = ‘’ |
We need to provide Secrets from the Blob Storage account where we have created the container to place files. Once database scoped credentials are created, we can create an External Data Source
We need to provide the format of the file with format options where the system can understand the format of the file and process accordingly below is the script that we have used for our demo.
|
1 2 3 4 5 6 7 8 9 |
CREATE EXTERNAL FILE FORMAT ExternalFileFormat WITH ( FORMAT_TYPE = DELIMITEDTEXT, FORMAT_OPTIONS ( FIELD_TERMINATOR = ',', STRING_DELIMITER = '"', FIRST_ROW = 2 ) ); |
Using the same External Data source and file format we have created the UserRoles table also. Below is the script for it.
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE EXTERNAL TABLE UserRoles ( [UserName] VARCHAR (100) COLLATE Latin1_General_BIN2, [ClientID] SMALLINT, [SUSER_SNAME] VARCHAR (100) COLLATE Latin1_General_BIN2 ) WITH ( LOCATION = 'drbtest/UserRoles.csv', DATA_SOURCE = ExternalDataSource, FILE_FORMAT = ExternalFileFormat ) |
After executing the above scripts we have created both the external tables as shown below.
Now to implement Row Level Security we will create a view this view will only be exposed to the end user. Logic to restrict the user to see other data will be implemented in view.
|
1 2 3 4 5 6 |
Create View vw_Demo_RowLevel AS select * from Demo A inner join UserRoles b on a.TenantId=b.[ClientID] where b.[SUSER_SNAME]=SUSER_SNAME() |
In the above code, we have used the system function SUSER_SNAME to restrict user access based on the login. SUSER_NAME always returns the login name for the current security context.
We will get the service principal Id for the user who is accessing the Synapse Serverless SQL pool. We have fixed the access based on UserID.
Now as we have created the view this will be exposed to users who want to retrieve the data from the demo table for their organization.
Restrict access to Underlying tables
Now as we created have created the view, we need to restrict the other objects for end users. The solution will expose views to the end-user and tables need to be restricted for end users.
To implement this, we must provide object-level security for all objects for different users. Below we will see how to create a user and revoke access on the table.
To understand this, we need to provide users with correct access to the synapse workspace. As per the documentation on the Microsoft website below access roles on synapse workspace.
There are three major roles for synapse workspace that can act on the database as specified below:
Synapse Administrator
This Role provides full access to the Synapse workspace. It provides access to SQL Pools both serverless and dedicated, Data Explore, Spark pools, and Runtimes. This role provides the right to create, read, update, and delete access to all artifacts. The role also includes assigning RBAC roles, compute, Link service, and providing the user credentials permissions on a workspace. Other than Synapse administer Azure owners can assign Synapse RBAC roles. Users with Azure permission can create, delete, and manage compute resources.
Synapse SQL Administrator
This Role is related to SQL-based access. Users will have full access to a serverless synapse pool and can create, read, update, and delete published SQL scripts, other artifacts, and lined services.
This rule doesn’t use for granting access. Using this role, we will have owner access to SQL pools and can perform all tasks.
Synapse Contributor
The contributor role provides complete access to spark pool and runtimes. Those users also have access to delete, update, read, and create access to code artifacts. User with this role doesn’t have access to use credentials and pipeline runs. Also, they can’t provide access to other users.
Both Synapse Administrator and SQL administrator provide full control of the SQL database in the synapse workspace. For this example or to implement row-level security we will use the Synapse contributor role.
Below is the screenshot of a few available roles in the Synapse workspace.
Once the user is assigned the synapse contributor role, we need to create a login in the master database for this user as mentioned below.
Use master database
|
1 2 3 |
CREATE LOGIN [abc@xyz.com] FROM EXTERNAL PROVIDER |
Once the login is created, we have to create a user name in the database where we want to provide or revoke permission on the database.
|
1 2 3 |
CREATE USER [abc@xyz.com]FROM LOGIN [abc@xyz.com] |
Once the user is created we need to provide the permissions on the object. In our example, we will revoke permission on tables that we have used to create the views.
|
1 2 3 4 |
DENY SELECT ON OBJECT::dbo.UserRoles TO [abc@xyz.com]; DENY SELECT ON OBJECT::dbo.Demo TO [abc@xyz.com]; |
After executing the revoke permission now, you can only select the view which is filtering data on basis of the user.
Conclusion
We don’t have any inbuilt functionality to implement in row-level security for synapse serverless SQL pool however we can achieve this by using TSQL. This can help to implement the Row Level Security for cloud data lakes not only in azure but also in other public clouds.
Skill summary:
1. SQL Server DB Administration
2. SQL Server DB Model Development
3. SQL Server TSQL or complex query writing
4. MSBI SSIS, SSRS, and SSAS expertise
5. Various other BI tool Expertise
- Understanding the Scalability in Oracle Database - March 9, 2023
- Migrating Oracle Database to Azure SQL Database - February 9, 2023
- Common SQL Interview Questions and Answers - January 6, 2023