none
Should a call to a sproc that inserts rows fail if user has read-only permissions to data?

    Question

  • Hello,

    Our production data warehouse DBA is having a difficult time getting us developers all the permissions we need.  We have read permission to all the tables, and we can script out any object from the database, but we can't execute a single stored procedure.  If we are granted permission to execute stored procedures that insert rows, does that effectively override our read-only permission on tables?

    This is exactly what I've found doing local testing this morning.  My test user couldn't perform an insert, but they could execute a stored procedure that performs the same insert. 

    I'm wondering if there's some way for us to have EXEC permission on all sprocs, with the assumption that any particular sproc that inserts rows will fail on the insert statement.

    Thanks,
    Eric B.

    Friday, May 10, 2019 4:12 PM

Answers

  • This is exactly what I've found doing local testing this morning.  My test user couldn't perform an insert, but they could execute a stored procedure that performs the same insert. 

    I'm wondering if there's some way for us to have EXEC permission on all sprocs, with the assumption that any particular sproc that inserts rows will fail on the insert statement.

    Yes, there might be.

    This is the scoop. When a stored procedure accesses an object for a regular operation - SELECT, INSERT, UPDATE, DELETE, MERGE, EXEC - and the procedure and the object have the same owner, there is no permission check performed. The idea is that by writing the stored procedure the owner can control access to his objects.

    To achieve would you are looking for, there are two ways to go:

    1) The procedure performs access control. While tedious, there are scenarios where you really need to do this. The access control does not have to be against the SQL Server permissions, but could also be against the applications own permission system.

    2) Make sure that procedure and objects have different owner. In this case, SQL Server will check your permissions against the object, as if you had executed the INSERT statement yourself.

    Whether any of these options are to the liking of your DBA, I cannot say.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, May 11, 2019 8:02 PM
  • ...

    2) Make sure that procedure and objects have different owner. In this case, SQL Server will check your permissions against the object, as if you had executed the INSERT statement yourself.

    ...

    Just to add to Erlands (correct) comment on the ownership being the key factor here:

    The easiest way to break the ownership-chain would be to place all procedures with write-access in their own schema. Like so:

    -- just a principal to be used for ownership of a certain schema
    CREATE USER OwnerForWriteProcedures WITHOUT LOGIN
    	
    GO
    
    CREATE SCHEMA WriteProcs
    	AUTHORIZATION OwnerForWriteProcedures
    GO
    
    -- new CREATE Proc Command with new Schema
    CREATE PROCEDURE WriteProcs.Procx
    As ...
    
    
    -- Or transfer existing Procs to new Schema using the ALTER SCHEMA command
    ALTER SCHEMA WriteProcs
    	TRANSFER CurrentSchema.ProcName
    
    	-- repeat for all existing procs
    

    and just to make sure: This principle of ownership-chaining leading to less permission checks is unique for SQL Server and is usually appreciated as it saves a lot of extra permission managing.

    Another alternative would be to grant permissions to execute only a chosen list of procedures, which needs to be maintained over time. Which is why I recommend object over schemas with different owners.

    To allow to only execute procedures within a certain schema the following command can be used:

    GRANT EXECUTE
    	ON SCHEMA::ReadProcs
    	TO RoleName

    regards

    Andreas


    Andreas Wolter (Blog | Twitter)
    Senior Program Manager SQL Server & Azure Security

    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012

    Monday, May 13, 2019 3:35 PM

All replies

  • Hello,

    Our production data warehouse DBA is having a difficult time getting us developers all the permissions we need.  We have read permission to all the tables, and we can script out any object from the database, but we can't execute a single stored procedure.  If we are granted permission to execute stored procedures that insert rows, does that effectively override our read-only permission on tables?

    This is exactly what I've found doing local testing this morning.  My test user couldn't perform an insert, but they could execute a stored procedure that performs the same insert. 

    I'm wondering if there's some way for us to have EXEC permission on all sprocs, with the assumption that any particular sproc that inserts rows will fail on the insert statement.

    Thanks,
    Eric B.

    Good day,

    >> I'm wondering if there's some way for us to have EXEC permission on all sprocs

    You can use

    GRANT EXECUTE TO [user]

    You can create a role as well and GRANT the EXECUTE to the role

    CREATE ROLE Executor
    GRANT EXECUTE TO Executor

     


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    • Edited by pituachMVP Friday, May 10, 2019 4:38 PM
    Friday, May 10, 2019 4:36 PM
  • Thanks, Ronen.  Will a member of the Executor role be able to execute stored procedures that insert into tables if the Executor role only has read permission to tables?
    Friday, May 10, 2019 4:45 PM
  • Thanks, Ronen.  Will a member of the Executor role be able to execute stored procedures that insert into tables if the Executor role only has read permission to tables?

    if "the Executor role only has read permission" then it can use this role for read by definition of your description. but if the Executor role have in addition a permission to "execute stored procedures that insert into tables" then by the definition of this he will be able to to execute the SP which mean he can INSERT data :-)

    A role can have multiple permissions and the members can use all these permissions if there is no REVOKE permission which override the GRANT.

    By the way, these type of question is it best to simply test in several seconds. Why won't you simply create role, add user. GRANT permission and test :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]




    Friday, May 10, 2019 5:17 PM
  • This is exactly what I've found doing local testing this morning.  My test user couldn't perform an insert, but they could execute a stored procedure that performs the same insert. 

    I'm wondering if there's some way for us to have EXEC permission on all sprocs, with the assumption that any particular sproc that inserts rows will fail on the insert statement.

    Yes, there might be.

    This is the scoop. When a stored procedure accesses an object for a regular operation - SELECT, INSERT, UPDATE, DELETE, MERGE, EXEC - and the procedure and the object have the same owner, there is no permission check performed. The idea is that by writing the stored procedure the owner can control access to his objects.

    To achieve would you are looking for, there are two ways to go:

    1) The procedure performs access control. While tedious, there are scenarios where you really need to do this. The access control does not have to be against the SQL Server permissions, but could also be against the applications own permission system.

    2) Make sure that procedure and objects have different owner. In this case, SQL Server will check your permissions against the object, as if you had executed the INSERT statement yourself.

    Whether any of these options are to the liking of your DBA, I cannot say.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, May 11, 2019 8:02 PM
  • ...

    2) Make sure that procedure and objects have different owner. In this case, SQL Server will check your permissions against the object, as if you had executed the INSERT statement yourself.

    ...

    Just to add to Erlands (correct) comment on the ownership being the key factor here:

    The easiest way to break the ownership-chain would be to place all procedures with write-access in their own schema. Like so:

    -- just a principal to be used for ownership of a certain schema
    CREATE USER OwnerForWriteProcedures WITHOUT LOGIN
    	
    GO
    
    CREATE SCHEMA WriteProcs
    	AUTHORIZATION OwnerForWriteProcedures
    GO
    
    -- new CREATE Proc Command with new Schema
    CREATE PROCEDURE WriteProcs.Procx
    As ...
    
    
    -- Or transfer existing Procs to new Schema using the ALTER SCHEMA command
    ALTER SCHEMA WriteProcs
    	TRANSFER CurrentSchema.ProcName
    
    	-- repeat for all existing procs
    

    and just to make sure: This principle of ownership-chaining leading to less permission checks is unique for SQL Server and is usually appreciated as it saves a lot of extra permission managing.

    Another alternative would be to grant permissions to execute only a chosen list of procedures, which needs to be maintained over time. Which is why I recommend object over schemas with different owners.

    To allow to only execute procedures within a certain schema the following command can be used:

    GRANT EXECUTE
    	ON SCHEMA::ReadProcs
    	TO RoleName

    regards

    Andreas


    Andreas Wolter (Blog | Twitter)
    Senior Program Manager SQL Server & Azure Security

    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012

    Monday, May 13, 2019 3:35 PM