Search code examples
azureazure-synapseopenrowset

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.

Solution

  • 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.