Granting access to collections of objects within a workspace.

Question

Goal: Achieve visibility and access control over pipelines, scripts, and data to the respective departments only in Azure Synapse Analytics workspace.

Additional details:

Data: Stored in Azure Synapse dedicated SQL pool.

Description:

I have data tables, pipelines, and scripts stored in Azure Synapse's dedicated SQL pool on which It is required to apply departmental access control. On the site, it is mentioned:

"In Synapse RBAC, the top-level scope is a workspace. Assigning a role with workspace scope grants permissions to all applicable objects in the workspace.

Current supported scopes within a workspace are:

• Apache Spark pool
• Integration runtime
• linked service
• credential

Access to code artifacts is granted with workspace scope. Granting access to collections of artifacts within a workspace will be supported in a later release."

Can you please specify the dates for later release and what functionalities are going to be there?

Also, Please suggest any alternate method to achieve this access control at the object level in the synapse workspace.

Answer 1

Hi @Apurva Tiwari
Welcome to Microsoft Q&A platform and thanks for posting your question here.

In Azure Synapse Analytics, Role-Based Access Control is used to manage access to resources within a workspace. The current RBAC implementation allows for assigning roles with workspace scope, which grants permissions across the workspace, affecting all applicable objects within it.

Microsoft has not provided specific dates for the release of functionalities for granting access to collections of artifacts within a workspace. To stay informed about upcoming features and releases, it is recommended to follow the Azure Synapse Analytics updates page and the Microsoft Azure updates page.

To achieve object-level access control in your Synapse workspace, you can utilize object-level security features. Every object in a dedicated SQL pool has associated permissions that can be granted to principals (users or groups). This allows you to secure individual tables, views, stored procedures, and functions by granting permissions like SELECT to user accounts or database roles. Additionally, you can combine these permissions with other access control mechanisms such as row-level security, column-level security, and dynamic data masking to further refine access controls.

To achieve departmental access control, you can follow these steps:

• Group users by department and assign them to security groups within your organization’s Active Directory.
• Use Azure roles to control who can create and manage resources like SQL pools, Apache Spark pools, and Integration runtimes, and access ADLS Gen2 storage.
• Control access to published code artifacts, use of compute resources, and integration runtimes by assigning Synapse roles.
• Use SQL permissions to control administrative and data plane access to SQL pools.
• If you’re using Git for source control, set permissions to control who can access code artifacts.

By following the mentioned steps, you can create a basic access control system that suits the roles and personas in your organization. This system will ensure the required visibility and access control over pipelines, scripts, and data for the respective departments.

Reference:
https://learn.microsoft.com/en-us/azure/synapse-analytics/whats-new
https://learn.microsoft.com/en-us/azure/synapse-analytics/security/how-to-set-up-access-control
https://learn.microsoft.com/en-us/azure/synapse-analytics/guidance/security-white-paper-access-control

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 2

Azure Synapse Analytics currently supports role-based access control at the workspace level. This means that assigning roles at the workspace scope grants permissions to all applicable objects within that workspace. While this provides comprehensive access management, it lacks finer granularity, such as object-level access control within the workspace.

Azure Synapse Analytics is expected to introduce object-level access control within the workspace later in 2024. This update will enable more granular permissions, allowing specific departments to have access to pipelines, scripts, and data tables, enhancing security and governance within the organization.

In the current absence of built-in object-level access control, you can manage access to data in dedicated SQL pools using SQL permissions, which involves running SQL scripts to grant or restrict access at the database, schema, or table level. For example:

CREATE LOGIN [******@domain.com] FROM EXTERNAL PROVIDER;

CREATE USER alias FOR LOGIN [******@domain.com];

ALTER ROLE db_datareader ADD MEMBER alias;

ALTER ROLE db_datawriter ADD MEMBER alias;

Or, you can use Synapse RBAC roles to manage who can create, update, delete, or execute various types of artifacts and resources within the Synapse workspace. While this does not allow for object-level permissions, it can control broader access to components like SQL pools, Apache Spark pools, and integration runtimes.