synapse workspace - blob storage

Question

synapse workspace - blob storage

arkiboys 9,706

Hello,
In synapse workspace, I am using serverless sql pool:
I have created a number of hierarchies in the blob storage
For example:
folder1, folder2, etc.
In addition, I have created a number of SQL Scripts which create views with schemas to read the data from the blob storage folder files...
At present, I have added the Power BI users to the list of the "Storage Blob Data Contributor"
Now the power BI users can see the views I created above.
Question:
How is it possible to limit the power BI users to the views inside a specific schema, say schemaX ?

Thank you

KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-08-13T09:25:57.253+00:00

Hi @arkiboys ,

Thanks for using Microsoft Q&A forum and posting your query.
When you say *Now the power BI users can see the views I created above.* - Do you mean to say that they are able see the views from Synapse Studio -> Data --> Databases --> Serverless --> Schemas ? The reason I would like to clarify is because of the confusion between Storage Blob Data Contributor and your requirement to limit the users to the views of a specific schema.

Also please clarify if you would want those users to be able to see only those views associated to a particular schema or is your ask to restrict those users executing those views associated to particular schema?

Appreciate if you could clarify a bit on this, so that we can better assist.

Thank you
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-08-13T12:55:33.167+00:00
Hi @arkiboys ,

If your ask is to provide users access to only the views in a particular SCHEMA, then have your views in a schema with no other objects and Grant SELECT on the schema.

Syntax:

GRANT permission [ ,...n ] ON SCHEMA :: schema_name TO database_principal [ ,...n ] [ WITH GRANT OPTION ] [ AS granting_principal ]

For more info please refer to this doc: GRANT Schema Permissions (Transact-SQL)

Hope this helps. Please correct if I misunderstood your ask.

Thank you
arkiboys 9,706 Reputation points

2021-08-13T13:49:45.937+00:00

Hi,
The report developers can connect to the view which are inside synapse workspace.
The connect using the "Get Data" button and entering details to connect to the workspace...
The report users do not go into the synapse studio.
Hope this answers your first question

As for your second question:
Would like the report developers to only see/execute views associated to a particular schema.
Hope this helps

Thank you
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-08-17T11:14:54.927+00:00

Hi @arkiboys ,

Synapse Administrators are granted db_owner (DBO) permissions on the serverless SQL pool, 'Built-in'. To grant other users access to serverless SQL pools, Synapse administrators need to run SQL scripts on each serverless pool.

If you would like to grant users access to only a particular schema, the you can Grant SELECT on the schema as mentioned in my previous comment. Ref doc: GRANT Schema Permissions (Transact-SQL)

Please do let us know if you have further query.
arkiboys 9,706 Reputation points

2021-08-17T12:49:07.543+00:00

Hi
KranthiPakala-MSFT,
Thank you for the details, however, I am still unsure of exactly what sql to run and where...
At present, the power bi user has access to the views in Schema1, Schema2 and SchemaX
I would like the user NOT to have access to Schema1 and Schema2 views, whereas to be able to read SchemaX views.
What should the sql be and where to run it in synapse workspace?

Thank you

Accepted answer

0 additional answers

Your answer

KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-08-13T09:25:57.253+00:00

Hi @arkiboys ,

Thanks for using Microsoft Q&A forum and posting your query.
When you say *Now the power BI users can see the views I created above.* - Do you mean to say that they are able see the views from Synapse Studio -> Data --> Databases --> Serverless --> Schemas ? The reason I would like to clarify is because of the confusion between Storage Blob Data Contributor and your requirement to limit the users to the views of a specific schema.

Also please clarify if you would want those users to be able to see only those views associated to a particular schema or is your ask to restrict those users executing those views associated to particular schema?

Appreciate if you could clarify a bit on this, so that we can better assist.

Thank you
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-08-13T12:55:33.167+00:00

Hi @arkiboys ,

If your ask is to provide users access to only the views in a particular SCHEMA, then have your views in a schema with no other objects and Grant SELECT on the schema.

Syntax:

GRANT permission [ ,...n ] ON SCHEMA :: schema_name TO database_principal [ ,...n ] [ WITH GRANT OPTION ] [ AS granting_principal ]

For more info please refer to this doc: GRANT Schema Permissions (Transact-SQL)

Hope this helps. Please correct if I misunderstood your ask.

Thank you
arkiboys 9,706 Reputation points

2021-08-13T13:49:45.937+00:00

Hi,
The report developers can connect to the view which are inside synapse workspace.
The connect using the "Get Data" button and entering details to connect to the workspace...
The report users do not go into the synapse studio.
Hope this answers your first question

As for your second question:
Would like the report developers to only see/execute views associated to a particular schema.
Hope this helps

Thank you
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-08-17T11:14:54.927+00:00

Hi @arkiboys ,

Synapse Administrators are granted db_owner (DBO) permissions on the serverless SQL pool, 'Built-in'. To grant other users access to serverless SQL pools, Synapse administrators need to run SQL scripts on each serverless pool.

If you would like to grant users access to only a particular schema, the you can Grant SELECT on the schema as mentioned in my previous comment. Ref doc: GRANT Schema Permissions (Transact-SQL)

Please do let us know if you have further query.
arkiboys 9,706 Reputation points

2021-08-17T12:49:07.543+00:00

Hi
KranthiPakala-MSFT,
Thank you for the details, however, I am still unsure of exactly what sql to run and where...
At present, the power bi user has access to the views in Schema1, Schema2 and SchemaX
I would like the user NOT to have access to Schema1 and Schema2 views, whereas to be able to read SchemaX views.
What should the sql be and where to run it in synapse workspace?

Thank you

Answer 1

KranthiPakala-MSFT 46,642 Microsoft Employee Moderator

Hi there,

Thanks for getting back and apologies for the delay. You can also DENY permission to user which will accomplish your request that users don’t have access for Schema1 and Schema2.

Sample Query:

DENY ADMINISTER DATABASE BULK OPERATIONS TO [******@contoso.com]  
GO  
GRANT SELECT ON SCHEMA::SchemaX TO [******@contoso.com]  
GO  
GRANT SELECT ON OBJECT::SchemaX.cases TO [******@contoso.com]  
GO  
GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::MySchemaXAccountCredential TO [******@contoso.com]  
GO

You can find more details here: Access and permissions for logical Datawarehouse with Serverless SQL pool

arkiboys 9,706 Reputation points

2021-08-26T14:13:49.95+00:00

Thank you for the details
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-08-30T03:27:07.577+00:00

You are welcome. Please feel free to accept answer or upvote if it is helpful as it can be beneficial for other community members.

Thanks

Share via

synapse workspace - blob storage

0 additional answers

Your answer