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.