Custom Role in Azure Synapse

Question

Can i create a Custom role or edit existing role in Azure Synapse, where

1. i can provide only SELECT query access using Built-in serverless Pool and
2. Pipelines access should be restricted

Ideally i'm looking for a role who can only read SQL & Lake data, query it using different technologies (SQL, Spark) and should not have access to anything else

Answer 1

You can actually create the External Table on the required using the Database Scoped Credential and first GRANT REFERENCES and then SELECT permission to the External Table for SQL user. Follow the below steps:

CREATE DATABASE SCOPED CREDENTIAL SampleIdentity
WITH IDENTITY = 'Managed Identity'
GO

CREATE EXTERNAL DATA SOURCE [DataLakeStorage] WITH (LOCATION = N'https://theorders.dfs.core.windows.net/', CREDENTIAL =  SampleIdentity)
GO

Caller must have one of the following permissions to execute OPENROWSET function:

• One of the permissions to execute OPENROWSET:
  • ADMINISTER BULK OPERATIONS enables login to execute OPENROWSET function.
  • ADMINISTER DATABASE BULK OPERATIONS enables database scoped user to execute OPENROWSET function.
• REFERENCES DATABASE SCOPED CREDENTIAL to the credential that is referenced in EXTERNAL DATA SOURCE.

GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[SampleIdentity] TO [SQLUser];
GO

CREATE EXTERNAL TABLE [dbo].[DimProductexternal]
( ProductKey int, ProductLabel nvarchar, ProductName nvarchar )
WITH
(
LOCATION='/DimProduct/year=*/month=*' ,
DATA_SOURCE = AzureDataLakeStore ,
FILE_FORMAT = TextFileFormat
) ;

You can now Grant SELECT permission to the user for external table.

GRANT SELECT ON [dbo].[DimProductexternal] TO [SQLUser]
GO

To restrict the access to the resource in Synapse, you can assign ROLE BASED ACEESS CONTROL (RBAC)

To restrict run/cancel pipelines access in Synapse workspace you can assign Synapse Monitoring Operator role using the RBAC in synapse. Refer Synapse RBAC roles and the actions they permit for more details.