Blog Post

Azure Synapse Analytics Blog
5 MIN READ

How to implement row-level security in serverless SQL pools

JovanPop's avatar
JovanPop
Icon for Microsoft rankMicrosoft
May 18, 2021
Serverless Synapse SQL pools enable you to read Parquet/CSV files or Cosmos DB collections and return their content as a set of rows. In some scenarios, you would need to ensure that a reader cannot ...
Updated Sep 15, 2021
Version 2.0
Synapse Security
Synapse SQL
FedePi's avatar
FedePi
Copper Contributor
Nov 10, 2021

Thank you for your guide
I tried to follow these steps, but I get the following error when I try to connect to database (using PowerBI) :

(I connected to PowerBI with an AD Azure user that I added as SQL user with the code you suggested)

CREATE USER [...@....com] FROM EXTERNAL PROVIDER
 

I then added the following permission:

GRANT ADMINISTER DATABASE BULK OPERATIONS TO [..@...com]
Then I got this error:

 

Then, I tried to grant read access to this user for the specific parquet file that the view points to, using Synapse interface for data lake, and now it works correctly.

Now, I am worried that in this way the user could bypass the security rule  as you suggest in the conclusion: "You need to ensure that the readers cannot directly query the files or collections in the underlying data source using the OPENROWSET function or the views/external tables. ", however it seems the only option that works. Do you have other options? Or, do you think that the way I did it is correct and the user could not access directly the data with only these permissions?

 

Thank you