Configuring sql user permission to INSERT only via stored procedure in Synapse dedicated pool

Question

We currently have a client with External users set up via AAD and each group has access to a certain schema with grant permission to select, insert, delete and update on their dedicated SQL pool. Our client would like to revoke explicit insert, delete and update from permissions but want the users to insert, delete and update using stored procedures (all stored procedures not schema specific)

I have been looking at multiple options suggested through what I have researched:

GRANT SELECT ON SCHEMA::t1 TO [testuserY]
GRANT EXECUTE TO [testuserY];
CREATE TABLE t1.MySampleTable
(
ID INT,
Name NVARCHAR(100),
Age INT
);

CREATE PROCEDURE t1.InsertIntoSampleTable
sql
@ID INT,
@Name NVARCHAR(100),
@Age INT
AS
BEGIN
sql
INSERT INTO t1.MySampleTable (ID, Name, Age)
VALUES (@ID, @Name, @Age);
END;

When I execute the SP as the testuserY, I receive the error 'The INSERT permission was denied on the object' for this user. I read on Microsoft document around EXECUTE AS Clause but I don't think it applies to Synapse dedicated pool.

I also set up a role to apply the permissions instead:

CREATE ROLE exec_sp_role;

GRANT EXECUTE ON OBJECT::t1.InsertIntoSampleTable TO exec_sp_role;

EXEC sp_addrolemember N'exec_sp_role', N'testuserY';

EXEC sp_addrolemember N'db_datareader', N'testuserY';

This also produces the same INSERT permission was denied on the object. If I grant the role INSERT, then the user is able to INSERT into table directly as well as by executing the Stored procedure.

I am quite new to managing permissions for SQL but I have been reading and looking on the web and I am not sure if it is something obvious I am missing or don't know about SQL permissions or if dedicated SQL pool permissions are different to other type of SQL servers/databases.

Any help or advice is much appreciated.

Answer 1

What you have would work fine in regular SQL Server, thanks to something known as ownership chaining. That is, when a module accesses an object that has the same owner as the module, there is no permission check at all. However, it appears that this is not implemented in Synapse Dedicated Pool. I also found an old thread which suggested that this may come in the future: https://learn.microsoft.com/en-us/answers/questions/528220/idea-suggestion-is-it-possible-to-support-ownershi. But that was three years ago...

Unfortunately, I cannot think of a solution that would meet your requirements. (But I should add that I normally do not work with Synapse, but I hang around with the regular SQL Server.)

You could submit a suggestion for this feature on https://feedback.azure.com/d365community/forum/9b9ba8e4-0825-ec11-b6e6-000d3a4f07b8

Here is an example of ownership chaining at work. The below runs without error on Azure SQL Database, but fails with a permission error on Synapse Dedicated Pool:

CREATE TABLE testtable(a int NOT NULL)
go
CREATE PROCEDURE test_sp @a int AS
   INSERT testtable(a) VALUES(@a)
go
CREATE USER nisse WITHOUT LOGIN
GRANT EXECUTE ON test_sp TO nisse
go
EXECUTE AS USER = 'nisse'
go
EXEC test_sp 122
go
REVERT
go
SELECT a FROM testtable
go
DROP PROCEDURE test_sp
go
DROP TABLE testtable
go
DROP USER nisse

Answer 2

Try to use EXECUTE AS OWNER to enable the SP to run with the privileges of the SP owner (typically a higher-privileged user) even if the actual user executing the procedure has no direct permissions on the underlying objects.

CREATE PROCEDURE t1.InsertIntoSampleTable

@ID INT,

@Name NVARCHAR(100),

@Age INT

WITH EXECUTE AS OWNER

AS

BEGIN

    INSERT INTO t1.MySampleTable (ID, Name, Age)

    VALUES (@ID, @Name, @Age);

END;

Then I think you should add a verification for the users who do not have direct INSERT, DELETE, or UPDATE permissions on the tables.

REVOKE INSERT, DELETE, UPDATE ON OBJECT::t1.MySampleTable TO [testuserY];

Then :

GRANT EXECUTE ON OBJECT::t1.InsertIntoSampleTable TO [testuserY];

If you are managing permissions via roles, make sure your role setup is correct and that the role has executed permissions on the stored procedures (this is an extra step).

CREATE ROLE exec_sp_role;

GRANT EXECUTE ON OBJECT::t1.InsertIntoSampleTable TO exec_sp_role;

EXEC sp_addrolemember N'exec_sp_role', N'testuserY';