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 [firstname.lastname@example.org] FROM EXTERNAL PROVIDER; GO ALTER ROLE db_datareader ADD MEMBER [email@example.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 [firstname.lastname@example.org] FROM EXTERNAL PROVIDER GRANT CONNECT ANY DATABASE TO [email@example.com] GRANT SELECT ALL USER SECURABLES TO [firstname.lastname@example.org]
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