Manage user permissions in Azure Synapse serverless SQL pools
To secure data, Azure Storage implements an access control model that supports both Azure role-based access control (Azure RBAC) and Portable Operating System Interface for Unix (POSIX) like access control lists (ACLs).
You can associate a security principal with an access level for files and directories. These associations are captured in an access control list (ACL). Each file and directory in your storage account has an access control list. When a security principal attempts an operation on a file or directory, An ACL check determines whether that security principal (user, group, service principal, or managed identity) has the correct permission level to perform the operation.
There are two kinds of access control lists:
Controls access to an object. Files and directories both have access ACLs.
Are templates of ACLs associated with a directory that determine the access ACLs for any child items that are created under that directory. Files do not have default ACLs.
Both access ACLs and default ACLs have the same structure.
The permissions on a container object are Read, Write, and Execute, and they can be used on files and directories as shown in the following table:
Levels of permissions
|Read (R)||Can read the contents of a file||Requires Read and Execute to list the contents of the directory|
|Write (W)||Can write or append to a file||Requires Write and Execute to create child items in a directory|
|Execute (X)||Does not mean anything in the context of Data Lake Storage Gen2||Required to traverse the child items of a directory|
Guidelines in setting up ACLs
Always use Azure Active Directory security groups as the assigned principal in an ACL entry. Resist the opportunity to directly assign individual users or service principals. Using this structure will allow you to add and remove users or service principals without the need to reapply ACLs to an entire directory structure. Instead, you can just add or remove users and service principals from the appropriate Azure AD security group.
There are many ways to set up groups. For example, imagine that you have a directory named /LogData which holds log data that is generated by your server. Azure Data Factory (ADF) ingests data into that folder. Specific users from the service engineering team will upload logs and manage other users of this folder, and various Databricks clusters will analyze logs from that folder.
To enable these activities, you could create a LogsWriter group and a LogsReader group. Then, you could assign permissions as follows:
- Add the LogsWriter group to the ACL of the /LogData directory with rwx permissions.
- Add the LogsReader group to the ACL of the /LogData directory with r-x permissions.
- Add the service principal object or Managed Service Identity (MSI) for ADF to the LogsWriters group.
- Add users in the service engineering team to the LogsWriter group.
- Add the service principal object or MSI for Databricks to the LogsReader group.
If a user in the service engineering team leaves the company, you could just remove them from the LogsWriter group. If you did not add that user to a group, but instead, you added a dedicated ACL entry for that user, you would have to remove that ACL entry from the /LogData directory. You would also have to remove the entry from all subdirectories and files in the entire directory hierarchy of the /LogData directory.
Roles necessary for serverless SQL pool users
For users which need read only access you should assign role named Storage Blob Data Reader.
For users which need read/write access you should assign role named Storage Blob Data Contributor. Read/Write access is needed if user should have access to create external table as select (CETAS).
If user has a role Owner or Contributor, that role is not enough. Azure Data Lake Storage gen 2 has super-roles which should be assigned.
Database level permission
To provide more granular access to the user, you should use Transact-SQL syntax to create logins and users.
To grant access to a user to a single serverless SQL pool database, follow the steps in this example:
use master CREATE LOGIN [firstname.lastname@example.org] FROM EXTERNAL PROVIDER;
use yourdb -- Use your DB name CREATE USER alias FROM LOGIN [email@example.com];
Add USER to members of the specified role
use yourdb -- Use your DB name alter role db_datareader Add member alias -- Type USER name from step 2 -- You can use any Database Role which exists -- (examples: db_owner, db_datareader, db_datawriter) -- Replace alias with alias of the user you would like to give access and domain with the company domain you are using.
Server level permission
To grant full access to a user to all serverless SQL pool databases, follow the step in this example:
CREATE LOGIN [firstname.lastname@example.org] FROM EXTERNAL PROVIDER; ALTER SERVER ROLE sysadmin ADD MEMBER [email@example.com];