Manage user permissions in Azure Synapse serverless SQL pools

  • 3 minutes

To secure data, Azure Storage implements an access control model that supports both Azure role-based access control (Azure RBAC) and access control lists (ACLs) like Portable Operating System Interface for Unix (POSIX)

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:

  • Access ACLs

    Controls access to an object. Files and directories both have access ACLs.

  • Default 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

Permission File Directory
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 Microsoft Entra 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 Microsoft Entra 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).

Note

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:

  1. Create LOGIN

    use master
CREATE LOGIN [alias@domain.com] FROM EXTERNAL PROVIDER;

  2. Create USER

    use yourdb -- Use your DB name
CREATE USER alias FROM LOGIN [alias@domain.com];

  3. 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

  1. To grant full access to a user to all serverless SQL pool databases, follow the step in this example:

    CREATE LOGIN [alias@domain.com] FROM EXTERNAL PROVIDER;
ALTER SERVER ROLE sysadmin ADD MEMBER [alias@domain.com];