Azure Synapse Workspace - Role and Permission to see SQL views and execute views

Jaimin Kansara 0 Reputation points
2023-01-19T22:47:52.4933333+00:00

Hi,
I have data lake storage account which has raw JSON files. I created Synapse Workspace and added SQL serverless database which has 2 different views on top of these JSON files. I as admin can query this views and able to see results in synapse workspace. now I want to add external users in Synapse workspace who are going to use this views and also execute queries to get data set. they will be using the result for further analysis in their domain.
Question is:

  1. which Synapse Role supports only list views and execute query ? FYI: I tried, Synapse User, Synapse Credential User and Synapse Compute Operator role but end user is not able to see SQL database created under Synapse workspace. so far Synapse Admin and Synapse SQL admin works. but in that case user can delete/modify views.
  2. Is synapse workspace is ever built to use like this? do you suggest to build additional interface on top of the views for end users and manage access and permission over there?

Let me know your thoughts..
Regards, Jaimin Kansara

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,566 questions
{count} votes

1 answer

Sort by: Most helpful
  1. BhargavaGunnam-MSFT 28,271 Reputation points Microsoft Employee
    2023-02-01T00:35:32.67+00:00

    Hello @Jaimin Kansara,

    Thank you for the clarification. Since you are looking to restrict access to users on the serverless DB, please follow the below.

    A serverless SQL pool in Synapse Analytics workspace can read the content of files stored in Azure Data Lake storage. You need to configure permissions on storage to enable a user who executes a SQL query to read the files.

    There are three methods for enabling the access to the files

    RBAC: enables you to assign a role to some Azure AD user in the tenant where your storage is placed.

    A reader must have Storage Blob Data Reader, Storage Blob Data Contributor, or Storage Blob Data Owner RBAC role on storage account.

    A user who writes data in the Azure storage must have Storage Blob Data Contributor or Storage Blob Data Owner role.

    Note that Storage Owner role does not imply that a user is also Storage Data Owner.

    Access Control Lists (ACL) enable you to define a fine grained Read(R), Write(W), and Execute(X) permissions on the files and directories in Azure storage. ACL can be assigned to Azure AD users. If readers want to read a file on a path in Azure Storage, they must have Execute(X) ACL on every folder in the file path, and Read(R) ACL on the file. Learn more how to set ACL permissions in storage layer.

    Shared access signature (SAS) enables a reader to access the files on the Azure Data Lake storage using the time-limited token. The reader doesn’t even need to be authenticated as Azure AD user. SAS token contains the permissions granted to the reader as well as the period when the token is valid. SAS token is good choice for time-constrained access to any user that doesn't even need to be in the same Azure AD tenant. SAS token can be defined on the storage account or on specific directories. Learn more about granting limited access to Azure Storage resources using shared access signatures.

    As an alternative, you can make your files publicly available by allowing anonymous access. This approach should NOT be used if you have non-public data.

    This is documented here: [https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-storage-files-storage-access-control?tabs=user-identity

    For the Dedicated SQL pools, you can restrict the users access via Synapse roles.

    Synapse Administrator: for users who need complete control over a workspace

    Synapse Contributor for users who need to develop, debug, and publish code to a service.

    Synapse Compute Operator for users who need to manage and monitor Apache Spark pools and Integration runtimes.

    I hope this helps. Please let me know if you have any further questions.

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

    For your case, you need to configure permissions on the gen2 level to enable a user to execute a SQL query to read the files.

    I hope this helps. Please let me know if you have any further questions here.

    0 comments No comments