Question 220959 in Azure Question posted in
The official documentation to get you started can be found here.

Azure – Grant a login SELECT on some Synapse Serverless Views, but not ability to run OPENROWSET on *entire* Datalake

For SQL Server based login in Synapse Serverless, as soon as I run these two things:

use master
GO
Create Login JustTest WITH PASSWORD='Whatever'

use somedatabase
Create User JustTest from login JustTest

Now, JustTest can run any OPENROWSET query on the Datalake. Yikes!

For an Azure AD security group based login in Synapse Serverless:

use master
GO
Create login JustADTest FROM EXTERNAL PROVIDER

use somedatabase
Create user JustADTEST from LOGIN JustADTEST

Initially it can’t run any View, even if I do a GRANT SELECT ON. I need to give it access to use OPENROWSET

GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL:: WorkspaceIdentity TO JustADTest

Now it can run the Views I’ve given it access to – as well as OPENROWSET on the entire datalake, same as how the SQLServer based login started.

So how does one do this? It seems like SQLServer logins are completely off limits, and one needs to find a way to give an AD based login limited OPENROWSET usage, but I’m not sure.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    There are probably multiple ways to do this, but this is how we ended up solving:

    Per this article:

    https://www.serverlesssql.com/user-permissions-in-serverless-sql-pools-external-tables-vs-views/#Conclusion

    GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL:: WorkspaceIdentity TO [Your Security Group]

DENY ADMINISTER DATABASE BULK OPERATIONS TO [Your Security Group]

    The first - one needs to grant to the security group, so that - in when it selects an object, it can also read the underlying data - which is in the datalake.

    The second - then makes sure that the security group cannot run OPENROWSET itself to everything. Note: the second one, needs to be run from master. Use Master; didn't work either - had to select master from the top left dropdown of SSMS.

    And the key point - In Synapse Serverless - this only works with External Tables, not Views. If you try that second DENY statement, the user won't be able to select the Views either. Thus, need to use External Tables instead, which will still be available.

    The link above goes into more detail.

    Another way we almost solved for all this was - removing credentials from the External Data Sources, then going into the Datalake itself and removing/adding rights there. This wasn't compatible with some other things we had setup though.


  2. 0

    Grant a login SELECT on some Synapse Serverless Views, but not ability to run OPENROWSET on entire Datalake

    The below command is giving permission to use scoped credential to user not OPENROWSET.

    GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL:: WorkspaceIdentity TO JustADTest

    To use the OPENROWSET the database user must have ADMINISTER BULK OPERATIONS permission.

    GRANT ADMINISTER DATABASE BULK OPERATIONS TO <User>;

    AS per MS DOC,

    • In OPENROWSET with DATA_SOURCE authentication mechanism is defined in database scoped credential assigned to the referenced data source. If DATA_SOURCE references Azure storage that isn’t public, you would need to create database-scoped credential and reference it in DATA SOURCE to allow access to storage files.

    Make sure you create data source According to your requirement with which files should not get fetched from that data source.

    # Create Scoped credential
CREATE DATABASE SCOPED CREDENTIAL mcred
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'SAS Access Token';

# Create external data source
CREATE EXTERNAL DATA SOURCE sampledataset
WITH 
(
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://Storageaccount.blob.core.windows.net/folder1/',
    CREDENTIAL = mcred
);

    The above data source will give access to folder 1 files only.

    To restrict user from selecting all the views you can grant permission to the user on particular view. If user does not have select permission on particular view, he will not be able to use SELECT in view.

    GRANT SELECT ON View1 TO <user>;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search