debugsqlMinor
User can drop table from a schema, but cannot create one
Viewed 0 times
cannotcancreateuserbutschemadroponefromtable
Problem
In an Azure Synapse dedicated SQL pool I have the following setup:
If I log in, being a member of the
Once I get the Synapse Administrator role, this works. (When a member,
However, when I remove myself from the Synapse Administrator role, the following happens:
Probably my understanding about how the necessary privileges for
So far I see the following possibilities:
-- 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
ownerrole 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
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.