Synapse serverless shared database and tables access for non sysadmins

Published Aug 16 2021 01:02 AM 2,356 Views
Microsoft

With Synapse workspaces, you can create tables in the managed spark clusters within the workspace. As a native feature of the product, these tables are synchronously replicated to the managed SQL serverless endpoint within the same workspace.

 

This allows a familiar SQL interface and the ability to manage such SQL-like objects using the familiar SQL server vocabulary.

 

This feature is well documented here and I would encourage you to have a read-through of this capability before reading any further on the shortcomings of this feature/probable solution of the limitation described below.

 

https://docs.microsoft.com/en-us/azure/synapse-analytics/metadata/database

https://docs.microsoft.com/en-us/azure/synapse-analytics/metadata/table

 

Problem statement

Managed and external spark tables are automatically synced with serverless SQL endpoint in the same workspace. The creator of this table can query this table using the synapse spark using their preferred SDK i.e. scala, python, SQL, and C# .NET.

 

Once this table is synced with the serverless endpoint it has been found that the creator of this table (who are not synapse administrator or even synapse SQL administrator) are not able to access this table despite them having access to underlying storage i.e. as a data reader role or even a data contributor role. 

 

If you attempt to query this shared table from SQL serverless (for example - a table named permissiontable created in Synapse Spark) you'll get an error response back - 

 

External table 'permissiontable' is not accessible because content of directory cannot be listed.

 

It has been found that users who are synapse administrators or even synapse SQL administrators with access to underlying storage are able to access the synced tables.

 

This further prohibits collaboration i.e. analytical objects created in the workspace using synapse spark cannot be recycled or reused by other individuals or applications which are not privileged users as a sysadmin i.e. who are not synapse administrators or even synapse SQL administrators on the workspace.

 

Any attempt to create a contained database user fails as the shared and replicated databases are marked as read-only and hence do not permit the creation of additional roles/users in them to govern access.

 

In hindsight, it must be noted that there is no server-level read-only role that would have allowed access to such replicated/shared databases as described above, the database level roles aren’t supported in such shared databases to regular synapse users via any role assignment workflows.

 

Workaround

 

Let’s first talk about few CONTROL SERVER permissions which were introduced with SQL Server 2014 –

 

*CONNECT ANY DATABASE -

Raunak_0-1628865506269.png

CONNECT ANY DATABASE is simple server-level permission that provides access to all current and future databases. It does not grant any permission in any database beyond the ability to connect, but when combined with other permissions,  you can allow very important business security needs to be met with ease.

 

SELECT ALL USER SECURABLES -

Raunak_1-1628865529512.png

 

This permission allows the grantee access to query (read only) the database objects and the tables within.

*This assumes that the user/application is not a sysadmin (i.e. synapse admin or synapse sql admin) on this workspace.

 

You can find more information about these permission grants here - https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-server-permissions-transact-sql?view=sql...

 

You can also use the sys tables i.e. server_permissions to audit list of logins who have been granted explicit roles on the serverless endpoint with such grants.

 

Raunak_2-1628865529524.png

 

Summarizing this post now, these two permissions grant the users/applications can now access to the shared databases/tables which are sync’ed with SQL serverless in Synapse workspace. This is suited where application teams would not want to grant explicit sysadmin grants to query such shared databases/tables.

 

%3CLINGO-SUB%20id%3D%22lingo-sub-2645947%22%20slang%3D%22en-US%22%3ESynapse%20serverless%20shared%20database%20and%20tables%20access%20for%20non%20sysadmins%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2645947%22%20slang%3D%22en-US%22%3E%3CP%3EWith%20Synapse%20workspaces%2C%20you%20can%20create%20tables%20in%20the%20managed%20spark%20clusters%20within%20the%20workspace.%20As%20a%20native%20feature%20of%20the%20product%2C%20these%20tables%20are%20synchronously%20replicated%20to%20the%20managed%20SQL%20serverless%20endpoint%20within%20the%20same%20workspace.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20allows%20a%20familiar%20SQL%20interface%20and%20the%20ability%20to%20manage%20such%20SQL-like%20objects%20using%20the%20familiar%20SQL%20server%20vocabulary.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20feature%20is%20well%20documented%20here%20and%20I%20would%20encourage%20you%20to%20have%20a%20read-through%20of%20this%20capability%20before%20reading%20any%20further%20on%20the%20shortcomings%20of%20this%20feature%2Fprobable%20solution%20of%20the%20limitation%20described%20below.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsynapse-analytics%2Fmetadata%2Fdatabase%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsynapse-analytics%2Fmetadata%2Fdatabase%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsynapse-analytics%2Fmetadata%2Ftable%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsynapse-analytics%2Fmetadata%2Ftable%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%3CU%3EProblem%20statement%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EManaged%20and%20external%20spark%20tables%20are%20automatically%20synced%20with%20serverless%20SQL%20endpoint%20in%20the%20same%20workspace.%20The%20creator%20of%20this%20table%20can%20query%20this%20table%20using%20the%20synapse%20spark%20using%20their%20preferred%20SDK%20i.e.%20scala%2C%20python%2C%20SQL%2C%20and%20C%23%20.NET.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOnce%20this%20table%20is%20synced%20with%20the%20serverless%20endpoint%20it%20has%20been%20found%20that%20the%20creator%20of%20this%20table%20(who%20are%20not%20synapse%20administrator%20or%20even%20synapse%20SQL%20administrator)%20are%20not%20able%20to%20access%20this%20table%20despite%20them%20having%20access%20to%20underlying%20storage%20i.e.%20as%20a%20data%20reader%20role%20or%20even%20a%20data%20contributor%20role.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20attempt%20to%20query%20this%20shared%20table%20from%20SQL%20serverless%20(for%20example%20-%20a%20table%20named%20permissiontable%20created%20in%20Synapse%20Spark)%20you'll%20get%20an%20error%20response%20back%20-%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CEM%3EExternal%20table%20'permissiontable'%20is%20not%20accessible%20because%20content%20of%20directory%20cannot%20be%20listed.%3C%2FEM%3E%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20has%20been%20found%20that%20users%20who%20are%20synapse%20administrators%20or%20even%20synapse%20SQL%20administrators%20with%20access%20to%20underlying%20storage%20are%20able%20to%20access%20the%20synced%20tables.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20further%20prohibits%20collaboration%20i.e.%20analytical%20objects%20created%20in%20the%20workspace%20using%20synapse%20spark%20cannot%20be%20recycled%20or%20reused%20by%20other%20individuals%20or%20applications%20which%20are%20not%20privileged%20users%20as%20a%20sysadmin%20i.e.%20who%20are%20not%20synapse%20administrators%20or%20even%20synapse%20SQL%20administrators%20on%20the%20workspace.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAny%20attempt%20to%20create%20a%20contained%20database%20user%20fails%20as%20the%20shared%20and%20replicated%20databases%20are%20marked%20as%20read-only%20and%20hence%20do%20not%20permit%20the%20creation%20of%20additional%20roles%2Fusers%20in%20them%20to%20govern%20access.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20hindsight%2C%20it%20must%20be%20noted%20that%20there%20is%20no%20server-level%20read-only%20role%20that%20would%20have%20allowed%20access%20to%20such%20replicated%2Fshared%20databases%20as%20described%20above%2C%20the%20database%20level%20roles%20aren%E2%80%99t%20supported%20in%20such%20shared%20databases%20to%20regular%20synapse%20users%20via%20any%20role%20assignment%20workflows.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%3CU%3EWorkaround%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet%E2%80%99s%20first%20talk%20about%20few%20CONTROL%20SERVER%20permissions%20which%20were%20introduced%20with%20SQL%20Server%202014%20%E2%80%93%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E*CONNECT%20ANY%20DATABASE%20-%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Raunak_0-1628865506269.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F303076iEEAE01788C3EFB3E%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Raunak_0-1628865506269.png%22%20alt%3D%22Raunak_0-1628865506269.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ECONNECT%20ANY%20DATABASE%20is%20simple%20server-level%20permission%20that%20provides%20access%20to%20all%20current%20and%20future%20databases.%20It%20does%20not%20grant%20any%20permission%20in%20any%20database%20beyond%20the%20ability%20to%20connect%2C%20but%20when%20combined%20with%20other%20permissions%2C%20%26nbsp%3Byou%20can%20allow%20very%20important%20business%20security%20needs%20to%20be%20met%20with%20ease.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESELECT%20ALL%20USER%20SECURABLES%20-%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Raunak_1-1628865529512.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F303077i927BEDC5C11698CE%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Raunak_1-1628865529512.png%22%20alt%3D%22Raunak_1-1628865529512.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20permission%20allows%20the%20grantee%20access%20to%20query%20(read%20only)%20the%20database%20objects%20and%20the%20tables%20within.%3C%2FP%3E%0A%3CP%3E%3CEM%3E*This%20assumes%20that%20the%20user%2Fapplication%20is%20not%20a%20sysadmin%20(i.e.%20synapse%20admin%20or%20synapse%20sql%20admin)%20on%20this%20workspace.%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20find%20more%20information%20about%20these%20permission%20grants%20here%20-%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Fstatements%2Fgrant-server-permissions-transact-sql%3Fview%3Dsql-server-ver15%23remarks%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Fstatements%2Fgrant-server-permissions-transact-sql%3Fview%3Dsql-server-ver15%23remarks%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20also%20use%20the%20sys%20tables%20i.e.%20server_permissions%20to%20audit%20list%20of%20logins%20who%20have%20been%20granted%20explicit%20roles%20on%20the%20serverless%20endpoint%20with%20such%20grants.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Raunak_2-1628865529524.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F303078i44CC754F44C485DE%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Raunak_2-1628865529524.png%22%20alt%3D%22Raunak_2-1628865529524.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESummarizing%20this%20post%20now%2C%20these%20two%20permissions%20grant%20the%20users%2Fapplications%20can%20now%20access%20to%20the%20shared%20databases%2Ftables%20which%20are%20sync%E2%80%99ed%20with%20SQL%20serverless%20in%20Synapse%20workspace.%20This%20is%20suited%20where%20application%20teams%20would%20not%20want%20to%20grant%20explicit%20sysadmin%20grants%20to%20query%20such%20shared%20databases%2Ftables.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-2645947%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Teaser%20card%20-%20SQL.PNG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F310667i140A6AFF199B0F1B%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Teaser%20card%20-%20SQL.PNG%22%20alt%3D%22Teaser%20card%20-%20SQL.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20article%20talks%20about%20how%20you%20can%20provide%20access%20to%20shared%20tables%20and%20databases%20created%20in%20the%20Synapse%20SQL%20Serverless%20pool.%20All%20such%20tables%20and%20databases%20require%20sysadmin%20i.e.%20SQL%20Administrator%20and%2For%20Synapse%20Administrator%20permissions%20in%20addition%20to%20usual%20file%20system%20permissions%20to%20get%20started%20querying%20such%20tables.%20Given%20such%20elevated%20permissions%20are%20not%20suggested%20for%20a%20regular%20user%2C%20this%20article%20shows%20how%20you%20can%20get%20started%20querying%20such%20tables%20without%20any%20elevated%20permissions.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2645947%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESynapse%20Administration%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESynapse%20Security%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESynapse%20Spark%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESynapse%20SQL%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESynapse%20Studio%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Co-Authors
Version history
Last update:
‎Sep 15 2021 12:18 PM
Updated by: