HiveBrain v1.2.0
Get Started
← Back to all entries
debugsqlMinor

User can drop table from a schema, but cannot create one

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
cannotcancreateuserbutschemadroponefromtable

Problem

In an Azure Synapse dedicated SQL pool I have the following setup:

-- a custom DB role to manage privileges
CREATE ROLE [owner];

-- there is a schema owned by this role
CREATE SCHEMA [myschema] AUTHORIZATION [owner];

-- an Azure AD group to allow its members to log in
CREATE USER [radish] FROM EXTERNAL PROVIDER;

-- the AAD group is a member of the owner role
EXEC sp_addrolemember 'owner', 'radish';

-- privileges are assigned exclusively through custom DB roles
GRANT ALTER, CONTROL on SCHEMA::[myschema] TO [owner];


If I log in, being a member of the radish AAD group, I cannot create a table in myschema:

CREATE TABLE [myschema].[test] (id int);

Msg 6004, Level 14, State 9, Line 1
User does not have permission to perform this action.


Once I get the Synapse Administrator role, this works. (When a member, SESSION_USER is dbo.) This is no surprise, as this role can do basically everything inside the SQL pools of the given workspace.

However, when I remove myself from the Synapse Administrator role, the following happens:

DROP TABLE [myschema].[test];
-- completes successfully, but then:

CREATE TABLE [myschema].[test] (id int);

Msg 6004, Level 14, State 9, Line 1
User does not have permission to perform this action.


Probably my understanding about how the necessary privileges for CREATE TABLE and DROP TABLE relate to one another is totally wrong, but until now I thought it were the same privileges needed for both. Can someone please show me where my thinking is wrong?

So far I see the following possibilities:

  • the owner role doesn't apply to users that are only indirectly member of it (i.e. through membership in the AAD group) - this seems to contradict https://learn.microsoft.com/en-us/azure/synapse-analytics/security/how-to-set-up-access-control#step-8-add-users-to-security-groups



  • the table created when having enough privileges is owned by myself and not the schema owner role. The 'Caution' section contradicts

Solution

As an owner of an object, you can DROP that object. Regardless of whether you have CREATE privileges for such an object.

Schema owner implies CONTROL on the schema which implies ALTER on the schema. CREATE TABLE is a database-level permission, which [owner] doesn't have.

From the CREATE TABLE documentation:

Requires CREATE TABLE permission in the database and ALTER permission on the schema in which the table is being created.

Context

StackExchange Database Administrators Q#317855, answer score: 7

Revisions (0)

No revisions yet.