locked
Control Execute permissions between the databases RRS feed

  • Question

  •  Hi, I have a requirement to have the ability for a login to execute the stored procs on few databases. There is 1 database in specific when that user executes a storedproc it shouldn't be able to update the objects outside that database. Tricky part is that user has execute permission in all the databases. Is that even possible?

     Here is the example: 

    •   I have an sql login\user named sql_execuser, it has read\write\exec priviliges on 3 databases db1,db2, db3.
    •   When sql_execuser runs a stored proc in db1 or db2, it can read\write to\from any objects from all 3 dbs (db1,db2,db3)
    •   But we want to restrict that user when it executes a stored proc in db3, It shouldn't be able to write on other databases (db1,db2)

    Is that even possible without going to object level (granular) permissions? Thank you!

     

    Wednesday, February 28, 2018 11:17 AM

Answers

  • Follow the security principle of least privilege and your problem is solved; grant execution permissions on only those stored procedures the user needs. You'll need to jump through a number of hoops if you grant the user broader permissions than needed and then try to lock things down at a more granular level, such as an explicit DENY (which takes precedence over GRANT).

    It's obviously possible to grant permissions at the database level since the user already has those rights. Assuming the user does not already have execute permissions via privileged role membership like db_owner, execute permissions could be granted at the database level, schema level, or object level:

    USE db1;
    --grant user permissions to execute all procs in db1
    GRANT EXECUTE ON DATABASE::db1 TO sql_execuser;
    --grant user permissions to execute all procs in dbo schema in db1
    GRANT EXECUTE ON SCHEMA::dbo TO sql_execuser;
    --grant user permissions to execute dbo.usp_SomeProc in db1
    GRANT EXECUTE ON dbo.usp_SomeProc TO sql_execuser;

    You mentioned that the user currently has read and write permissions. Do you mean the user can write directly to tables without executing stored procedures? In that case, why bother locking down writes from specific procs? The general philosophy with SQL Server stored procedure permissions is that when you grant execute permissions, you want to grant all of the functionality encapsulated within the module even if the user has no direct permissions on the database underlying objects.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Proposed as answer by Uri DimantMVP Wednesday, February 28, 2018 2:13 PM
    • Marked as answer by SQLmaddy Wednesday, February 28, 2018 9:19 PM
    Wednesday, February 28, 2018 1:26 PM

All replies

  • do want to give permission on single or multiple procedure ?

    GRANT EXECUTE ON <procedurename> to <username>
    GO


    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

    Wednesday, February 28, 2018 11:23 AM
  • Follow the security principle of least privilege and your problem is solved; grant execution permissions on only those stored procedures the user needs. You'll need to jump through a number of hoops if you grant the user broader permissions than needed and then try to lock things down at a more granular level, such as an explicit DENY (which takes precedence over GRANT).

    It's obviously possible to grant permissions at the database level since the user already has those rights. Assuming the user does not already have execute permissions via privileged role membership like db_owner, execute permissions could be granted at the database level, schema level, or object level:

    USE db1;
    --grant user permissions to execute all procs in db1
    GRANT EXECUTE ON DATABASE::db1 TO sql_execuser;
    --grant user permissions to execute all procs in dbo schema in db1
    GRANT EXECUTE ON SCHEMA::dbo TO sql_execuser;
    --grant user permissions to execute dbo.usp_SomeProc in db1
    GRANT EXECUTE ON dbo.usp_SomeProc TO sql_execuser;

    You mentioned that the user currently has read and write permissions. Do you mean the user can write directly to tables without executing stored procedures? In that case, why bother locking down writes from specific procs? The general philosophy with SQL Server stored procedure permissions is that when you grant execute permissions, you want to grant all of the functionality encapsulated within the module even if the user has no direct permissions on the database underlying objects.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Proposed as answer by Uri DimantMVP Wednesday, February 28, 2018 2:13 PM
    • Marked as answer by SQLmaddy Wednesday, February 28, 2018 9:19 PM
    Wednesday, February 28, 2018 1:26 PM
  •   we are trying to prevent giving granular permissions at the object level as we have 100's of new deployments every week, thats a different problem though! But yeah, i agree that will be the only solution if we have to lock down. on that read\write, tl dr is we have a test DB developers trying to test their code and they don't want to accidentally update the production dbs, as they use only stored procs we trying to lock down them. Thank you very much!
    Wednesday, February 28, 2018 4:04 PM