Seeking Guidance on Granting 'Read' Access in Azure Synapse Serverless SQL Pool

Question

Seeking Guidance on Granting 'Read' Access in Azure Synapse Serverless SQL Pool

Nicole Inman 1

Hello Azure Community,

I'm currently working on an Azure Synapse Analytics workspace project utilizing a serverless SQL pool. We rely on a SAS token to access our data stored in Azure Data Lake. This data is then presented within a Power Platform canvas app.

Our main objective is to allow an AAD group (or group) 'Read' access to the data within the serverless SQL pool. Despite my capacity as the service owner and numerous attempts at configuring permissions, I find myself unable to accomplish the desired access control. It's critical for us to strictly confine the specific user or group's access to 'Read' only operations to comply with our organization's stringent data security policies.

Despite my efforts, I'm unable to achieve the required configuration. Your insights and recommendations on this matter would be highly appreciated.

Looking forward to your valuable responses.

Best Regards,

Nicole

Vinodh247 2,926 Reputation points

2023-05-25T06:06:07.78+00:00

did you went through this?

https://learn.microsoft.com/en-us/azure/synapse-analytics/security/how-to-set-up-access-control

Vinodh247 2,926 Reputation points

2023-05-25T06:06:07.78+00:00

did you went through this?

https://learn.microsoft.com/en-us/azure/synapse-analytics/security/how-to-set-up-access-control

Answer 1

risolis 8,151

Hello @Nicole Inman

Thank you for posting this case scenario on this community.

I fully understand your concern so I shall try to address you on this one. For instance, you can either delegate permissions from the AAD or PIM (Privilege Identity Management as well.

Please direct yourself to the following links down below:

https://learn.microsoft.com/en-us/azure/data-share/concepts-roles-permissions

https://learn.microsoft.com/en-us/azure/role-based-access-control/built-in-roles#sql-db-contributor

https://learn.microsoft.com/en-us/azure/role-based-access-control/built-in-roles#reader

I hope that might be useful for you and get it done.

Looking forward to hearing from you

Cheers,

Please "Accept the answer" if the information helped you. This will help us and others in the community.

risolis 8,151 Reputation points

2023-05-26T03:25:00.0333333+00:00

Hello @Nicole Inman

I hope you are doing fine.

I just wanted to know if you had any chance to review the previous post made.

If further details are required, please do not hesitate to get back to us.

Regards!
Nicole Inman 1 Reputation point

2023-05-27T00:26:57.9633333+00:00

Thank you for your response and the links provided. They are indeed very useful resources for understanding the roles and permissions in Azure.

I have transitioned from using built-in RBAC roles to creating a custom role on the database level, primarily to fine-tune the permissions granted to different roles.

The challenge I'm currently facing is determining exactly which permissions need to be assigned to the custom role. I have found that assigning the db_owner role to a security group enables users to read data from a view and external table in a Canvas app. Furthermore, granting the server role sysadmin to a guest user allows them to read data from an external table, but not a view.

I've tried to grant the SELECT permission on the database, external table, and view, but it doesn't seem to cover all the necessary permissions.

Do you have any additional advice or resources that could help in refining these custom roles? How can I precisely define the permissions to allow users to read data from views and external tables without overextending the privileges?

I appreciate your input and look forward to your response.

Seeking Guidance on Granting 'Read' Access in Azure Synapse Serverless SQL Pool

1 answer

Activity