How to provide table level access for tables created from Spark in Synapse Serverless Pool

Question

How to provide table level access for tables created from Spark in Synapse Serverless Pool

Narender Kumar 70

I am creating some table from spark code.

The same tables are available to query from serverless pool.

When users query from serverless pool, I want to provide access to only some users on some particular tables, databases.

How can I provide access to a single user or AD Group to a single table or database on the tables/databases created using spark code?

Smaran Thoomu 32,535 Reputation points Microsoft External Staff Moderator

2023-12-26T11:56:56.8833333+00:00
Hi @Narender Kumar

Welcome to the MS Q&A platform. To provide table-level access to specific users or AD groups for tables created from Spark in Synapse Serverless Pool, you can use Azure Synapse's Role-Based Access Control (RBAC) feature. RBAC allows you to assign specific roles to users or groups, which determine their level of access to Synapse resources.

Here are the steps to provide table-level access to specific users or AD groups for tables created from Spark in Synapse Serverless Pool:

Create a new role in Synapse Studio with the required permissions for the table or database. You can create a custom role or use one of the built-in roles.

Assign the role to the user or AD group that requires access to the table or database. You can do this in the Access control (IAM) tab of the Synapse workspace.

Grant the required permissions to the role. You can do this in the Access control (IAM) tab of the Synapse workspace.

Once the role is assigned, the user or AD group will have access to the table or database based on the permissions granted to the role.

For more information on RBAC in Azure Synapse, you can refer to the following Azure documentation:

Role-based access control in Azure Synapse Analytics

I hope this helps! Let me know if you have any further questions.
Smaran Thoomu 32,535 Reputation points Microsoft External Staff Moderator

2023-12-27T18:34:39.4966667+00:00

@Narender Kumar We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Narender Kumar 70 Reputation points

2023-12-28T08:54:37.6233333+00:00
I went through the mentioned link. It explains that RBAC can be applied to the below scopes.

Apache Spark pool

Integration runtime

linked service

credential

It does not mention any table level access for any RBAC role. So I am still not sure how table level access can be provided to any user/ad group
Smaran Thoomu 32,535 Reputation points Microsoft External Staff Moderator

2023-12-29T07:41:52.2166667+00:00
Hi @Narender Kumar
You are correct that RBAC in Azure Synapse Analytics can be applied to the scopes you mentioned, but it can also be applied to individual tables or views within a database. Once the role is assigned to the user or AD group, they will have access to the table based on the permissions granted to the role.
You can refer below articles for more information:

Understand the roles required to perform common tasks in Azure Synapse

Somethings unclear with Permissions on Azure Synapse SQL
Narender Kumar 70 Reputation points

2023-12-29T07:56:53.4766667+00:00

I went through both the links.

The microsoft link explains about different roles but does not explain how we can apply the roles on a single table.

The medium link tells that this can be applied to table but does not explain how it can be applied.

So I am still not sure how table level access can be provided to any user/ad group.

These links does not mention the steps to follow for this.
Smaran Thoomu 32,535 Reputation points Microsoft External Staff Moderator

2023-12-29T12:31:41.94+00:00
@Narender Kumar I apologize for the confusion. The Serverless SQL Pool doesn't come with a direct way to control access at the table level for tables created by Spark. However, you can manage security effectively by using Workspace-Level Access Control. By granting full access to the Serverless SQL Pool, including tables created by Spark, and creating custom roles with specific permissions for actions like reading or modifying tables, you can achieve the desired security setup.
To assign a role to a user, group, service principal, or managed identity as per below

To provide table-level access to a user or AD group in Azure Synapse Analytics, you can follow these steps:

Navigate to the "SQL script" tab in the Synapse workspace and create a new SQL script.

Grant the necessary permissions to the user or AD group on the table. For example, if you want to provide read access to a specific table, you can grant the "SELECT" permission on that table to the user or AD group. Here is an example SQL script:

USE <database_name>; GRANT SELECT ON <schema_name>.<table_name> TO <user_or_AD_group>;

Replace <database_name>, <schema_name>, <table_name>, <user_or_AD_group> with the appropriate values and then execute the SQL script to grant the necessary permissions.

By following these steps, you can provide table-level access to a user or AD group in Azure Synapse Analytics.

Similar thread: https://stackoverflow.com/questions/77059116/how-to-access-azure-synapse-serverless-pool-or-metadata-via-spark-pool
Anonymous

2024-01-25T16:49:24.5133333+00:00

Hello @Smaran Thoomu
Thank you for the detailed information.

Sadly, the last code you provided is incomplete and we don't have the full SQL code as you can see in the image below.

Could you please write it again?

thank you very much for your time.
Smaran Thoomu 32,535 Reputation points Microsoft External Staff Moderator

2024-01-25T17:02:57.5666667+00:00
Grégoire Mansio I apologize for the confusion. Here is the complete SQL script to grant SELECT permission on a table to a user or AD group in Azure Synapse Analytics:

USE <database_name>; GRANT SELECT ON <schema_name>.<table_name> TO <user_or_AD_group>;

Replace <database_name>, <schema_name>, <table_name>, and <user_or_AD_group> with the appropriate values and then execute the SQL script to grant the necessary permissions.

1 answer

Your answer

Smaran Thoomu 32,535 Reputation points Microsoft External Staff Moderator

2023-12-26T11:56:56.8833333+00:00

Hi @Narender Kumar

Welcome to the MS Q&A platform. To provide table-level access to specific users or AD groups for tables created from Spark in Synapse Serverless Pool, you can use Azure Synapse's Role-Based Access Control (RBAC) feature. RBAC allows you to assign specific roles to users or groups, which determine their level of access to Synapse resources.

Here are the steps to provide table-level access to specific users or AD groups for tables created from Spark in Synapse Serverless Pool:

Create a new role in Synapse Studio with the required permissions for the table or database. You can create a custom role or use one of the built-in roles.

Assign the role to the user or AD group that requires access to the table or database. You can do this in the Access control (IAM) tab of the Synapse workspace.

Grant the required permissions to the role. You can do this in the Access control (IAM) tab of the Synapse workspace.

Once the role is assigned, the user or AD group will have access to the table or database based on the permissions granted to the role.

For more information on RBAC in Azure Synapse, you can refer to the following Azure documentation:

Role-based access control in Azure Synapse Analytics

I hope this helps! Let me know if you have any further questions.
Smaran Thoomu 32,535 Reputation points Microsoft External Staff Moderator

2023-12-27T18:34:39.4966667+00:00

@Narender Kumar We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Narender Kumar 70 Reputation points

2023-12-28T08:54:37.6233333+00:00

I went through the mentioned link. It explains that RBAC can be applied to the below scopes.

Apache Spark pool

Integration runtime

linked service

credential

It does not mention any table level access for any RBAC role. So I am still not sure how table level access can be provided to any user/ad group
Smaran Thoomu 32,535 Reputation points Microsoft External Staff Moderator

2023-12-29T07:41:52.2166667+00:00

Hi @Narender Kumar
You are correct that RBAC in Azure Synapse Analytics can be applied to the scopes you mentioned, but it can also be applied to individual tables or views within a database. Once the role is assigned to the user or AD group, they will have access to the table based on the permissions granted to the role.
You can refer below articles for more information:

Understand the roles required to perform common tasks in Azure Synapse

Somethings unclear with Permissions on Azure Synapse SQL
Narender Kumar 70 Reputation points

2023-12-29T07:56:53.4766667+00:00

I went through both the links.

The microsoft link explains about different roles but does not explain how we can apply the roles on a single table.

The medium link tells that this can be applied to table but does not explain how it can be applied.

So I am still not sure how table level access can be provided to any user/ad group.

These links does not mention the steps to follow for this.
Smaran Thoomu 32,535 Reputation points Microsoft External Staff Moderator

2023-12-29T12:31:41.94+00:00

@Narender Kumar I apologize for the confusion. The Serverless SQL Pool doesn't come with a direct way to control access at the table level for tables created by Spark. However, you can manage security effectively by using Workspace-Level Access Control. By granting full access to the Serverless SQL Pool, including tables created by Spark, and creating custom roles with specific permissions for actions like reading or modifying tables, you can achieve the desired security setup.
To assign a role to a user, group, service principal, or managed identity as per below

To provide table-level access to a user or AD group in Azure Synapse Analytics, you can follow these steps:

Navigate to the "SQL script" tab in the Synapse workspace and create a new SQL script.

Grant the necessary permissions to the user or AD group on the table. For example, if you want to provide read access to a specific table, you can grant the "SELECT" permission on that table to the user or AD group. Here is an example SQL script:

USE <database_name>; GRANT SELECT ON <schema_name>.<table_name> TO <user_or_AD_group>;

Replace <database_name>, <schema_name>, <table_name>, <user_or_AD_group> with the appropriate values and then execute the SQL script to grant the necessary permissions.

By following these steps, you can provide table-level access to a user or AD group in Azure Synapse Analytics.

Similar thread: https://stackoverflow.com/questions/77059116/how-to-access-azure-synapse-serverless-pool-or-metadata-via-spark-pool
Anonymous

2024-01-25T16:49:24.5133333+00:00

Hello @Smaran Thoomu
Thank you for the detailed information.

Sadly, the last code you provided is incomplete and we don't have the full SQL code as you can see in the image below.

Could you please write it again?

thank you very much for your time.
Smaran Thoomu 32,535 Reputation points Microsoft External Staff Moderator

2024-01-25T17:02:57.5666667+00:00

Grégoire Mansio I apologize for the confusion. Here is the complete SQL script to grant SELECT permission on a table to a user or AD group in Azure Synapse Analytics:

USE <database_name>; GRANT SELECT ON <schema_name>.<table_name> TO <user_or_AD_group>;

Replace <database_name>, <schema_name>, <table_name>, and <user_or_AD_group> with the appropriate values and then execute the SQL script to grant the necessary permissions.

Answer 1

Ashish Kumar 5 Microsoft Employee

User can access the tables in serverless SQL through the following query :

GRANT SELECT ON <schema>.<tablename> TO <securitygroup or user>

Share via

How to provide table level access for tables created from Spark in Synapse Serverless Pool

1 answer

Your answer