Somethings unclear with Permissions on Azure Synapse SQL Pool…
Permissions in Synapse Serverless SQL are controlled by user account’s database role memberships and object-level permissions. If SQL Authentication is used, the SQL user exists only in serverless SQL pool and permissions which are scoped to the objects in serverless SQL pool.
The Synapse Administrator is by default given the SQL db_owner role for serverless SQL pools in the workspace. Access to SQL pools for other users is controlled by SQL permissions. Assigning SQL permissions requires SQL scripts to be run on each SQL database post-creation.
- Then, with respect to the permissions on Azure Synapse SQL pool. Can Permissions cover allowed operations (SELECT, INS, DEL etc..), allowed objects, columns and rows.?
→ Yes, permissions on Azure Synapse SQL pool can cover allowed operations, allowed objects, columns, and rows. Permissions can be granted using the Azure Synapse SQL Management Studio or the Azure CLI.
- Allowed operations: Permissions can be granted for specific operations, such as SELECT, INSERT, UPDATE, and DELETE. For example, you could grant a user the SELECT permission on a table, but not the INSERT or UPDATE permissions.
- Allowed objects: Permissions can be granted on specific objects, such as tables, views, stored procedures, and functions. For example, you could grant a user the SELECT permission on a table, but not the SELECT permission on a view.
- Allowed columns: Permissions can be granted on specific columns within a table. For example, you could grant a user the SELECT permission on the
CustomerIDcolumn in a table, but not the SELECT permission on theNamecolumn. - Allowed rows: Permissions can be granted on specific rows within a table. For example, you could grant a user the SELECT permission on the rows where the
CustomerIDis equal to 12345, but not the SELECT permission on any other rows.
2. Also, with respect to the permissions on Synapse SQL pool. Will databases have the rules for masking of certain PII and Financial Sensitive data.?
→ Yes, databases on Azure Synapse SQL pool can have rules for masking certain PII and Financial Sensitive data. This can be done using Dynamic Data Masking (DDM). DDM is a feature that allows you to mask sensitive data in a database so that it is not visible to unauthorized users.
To use DDM, you first need to create a masking policy. A masking policy defines how sensitive data will be masked. For example, you could create a masking policy that masks all Social Security Numbers (SSNs) with asterisks (*).
Once you have created a masking policy, you can apply it to one or more tables in your database. When a user queries a table that has a masking policy applied to it, the sensitive data in the table will be masked according to the masking policy.
3. With respect to the permissions on Synapse SQL pool, can we define database roles and link it to the AD groups?
→ Yes, you can define database roles and link them to AD groups in Azure Synapse SQL pool. This is a good way to manage permissions for users and groups in your database.
A database role is a collection of permissions that can be granted to a user or group. When you create a database role, you specify the permissions that the role will have. For example, you could create a database role that has the SELECT, INSERT, UPDATE, and DELETE permissions on the Customers table.
Once you have created a database role, you can link it to an AD group. This means that all users who are members of the AD group will automatically have the permissions of the database role.
Once you have linked a database role to an AD group, all users who are members of the AD group will automatically have the permissions of the database role. This is a convenient way to manage permissions for users and groups in your database. Some of the benefits of using database roles and AD groups to manage permissions in Azure Synapse SQL pool:
- It is easy to manage permissions. You can simply add or remove users from AD groups to change their permissions.
- It is secure. You can control who has access to your database by controlling who is a member of the AD groups that are linked to database roles.
- It is scalable. You can easily add or remove users from AD groups as your needs change.
Reference:
This article teaches you how to control access to a Microsoft Azure Synapse workspace.Access control in Synapse workspace how to — Azure Synapse Analytics | Microsoft Learn
Serverless SQL pool is a query service over the data in your data lake. It enables you to access your data through the following functionalities Serverless SQL pool — Azure Synapse Analytics | Microsoft Learn
