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

Marjan Selahi 20 Reputation points
2024-05-02T10:09:36.33+00:00

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.

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,442 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 102.1K Reputation points MVP
    2024-05-12T12:12:24.04+00:00

    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
    
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Amira Bedhiafi 16,071 Reputation points
    2024-05-02T21:05:32.2466667+00:00

    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';