Controlling Grant for Lake DB in Synapse Analytics Workspace in Spark SQL

Question

Hi Everyone,

We have a Synapse Analytics Workspace. We have a use case where we want users to execute i.e. only perform 'select' on the lake DB Tables exsisting there. Same goes with Serverless Pool. They should be able to Publish notebook or SQL Scripts although should not be able to drop/delete or create Tables in Lake Databses and SQL Databses

Permission Given:

1. On Primary Storage of Synapse Workspace : Reader and Storage Blob Data Reader
2. On Other Linked Storage where tables resides : Reader and ACL Permission Setup since we wanted to segregate users access on Folder level.
3. On Azure Portal For Synapse Workspace (RBAC) : Reader
4. Inside the Synapse Analytics Workspace: SQL Administrator and Apache Spark Pool Administrator.

Though users have SQL Administrator access in Syanpse Workspace but we have remover their sysadmin access explicitly by altering Server Role.

We are able to setup this access apart from the fact that using drop in Spark SQL user is able to drop table or Lake Databases. Can you please help us to restrict users from Dropping Lake DB Tables and Lake Db Databases using Spark SQL Drop Statement?
We do not see an option to grant or revoke or manage the SQL Transact Access as we have a mechanism in Databricks using Table ACL?Is this by design or I am missing the right way to implement this?

Any help from your end would be appreciated. :)

Thank you in Advance!!

Answer 1

Hello @Agarwal, Shagun [DPYUS NON-J&J] ,

Sorry for the delayed response. Here are the details.

The lake databases and tables are secured at two levels:

• The underlying storage layer by assigning to Azure AD users one of the following:
o Azure role-based access control (Azure RBAC)
o Azure attribute-based access control (Azure ABAC) role
o ACL permissions

• The SQL layer where you can define an Azure AD user and grant SQL permissions to SELECT data from tables referencing the lake data.

For data access at the storage level.

• Grant read (R) and Write (w) permission on files (such as the table's underlying data files).
• Grant execute (X) permission on the folder where the files are stored and on every parent folder up to the root.

For SQL security:

• Administrators: Assign the Synapse SQL Administrator workspace role or sysadmin server-level role inside the serverless SQL pool. This role has full control over all databases. The Synapse Administrator and Synapse SQL Administrator roles also have all permissions on all objects in a serverless SQL pool, by default.
• Workspace readers: Grant the server-level permissions GRANT CONNECT ANY DATABASE and GRANT SELECT ALL USER SECURABLES on serverless SQL pool to a login that will enable the login to access and read any database. This might be a good choice for assigning reader/non-admin access to a user.
• Database readers: Create database users from Azure AD in your lake database and add them to db_datareader role, which will enable them to read data in the lake database.

In your case, create database users from your Azure Active Directory domain and assign them to the db_datareader role. This way you can create database-scoped users who can read data only within your Lake database.

CREATE USER [customuser@contoso.com] FROM EXTERNAL PROVIDER;  
GO  
ALTER ROLE db_datareader  
ADD MEMBER [customuser@contoso.com];  

Create workspace-level data reader

A login with GRANT CONNECT ANY DATABASE and GRANT SELECT ALL USER SECURABLES permissions is able to read all tables using the serverless SQL pool, but not able to create SQL databases or modify the objects in them.

CREATE LOGIN [wsdatareader@contoso.com] FROM EXTERNAL PROVIDER  
GRANT CONNECT ANY DATABASE TO [wsdatareader@contoso.com]  
GRANT SELECT ALL USER SECURABLES TO [wsdatareader@contoso.com]  

This script enables you to create users without admin privileges who can read any table in Lake databases.

Please refer this document for the lake database security.

I hope this helps. Please let me know if you have any further questions.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators