Randheer Parmar
Roles available in serverless SQL pool. TSQL programming, Synapse Server less Pool. Row Level Security.

Implementing Row-Level Security in Azure Synapse Serverless SQL pools using TSQL

December 5, 2022 by

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.

CSV files containing demo data and user role data is azure storage.  TSQL programming, Synapse Server less Pool. Row Level Security.

Now go to Azure Synapse workspace and create a Serverless SQL Pool and the database in serverless SQL Pool as shown below.

Synapse server less pool with demopool database and component inside database.  TSQL programming, Synapse Server less Pool. Row Level Security.

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.

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.

)

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.

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.

Using the same External Data source and file format we have created the UserRoles table also. Below is the script for it.

After executing the above scripts we have created both the external tables as shown below.

External tables structure with columns details in azure synapse server less sql pool. TSQL programming, Synapse Server less Pool. Row Level Security.

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.

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.

Roles available in serverless SQL pool. TSQL programming, Synapse Server less Pool. Row Level Security.

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

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.

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.

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.

Randheer Parmar
Azure

About Randheer Parmar

I am a Database Architect Having 16 years of Experience in Database Programming. I have expertise in SOL Server Database. I have Expertise in MSBI Stack and various other BI tool like QlikView, Micro Strategy, Tableau etc. 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

708 Views