locked
Grant execute with grant option to a role not working RRS feed

  • Question

  • I want my developers to be able to do their jobs with a bare minimum of permissions. They should be able to see all db-objects, run traces, read/write data, create tables, views and procedures as well as granting others execute permission on the procs they create. I have managed to do all of this, except for the permission to grant execute on procedures to others.

    Below is a case describing what I have done. I have created a role which gets execute with grant option on all procedures in the database. Members of this role is able to execute all procedures, but they are not able to pass the permission on. This should be fine according to BOL.

    Create an empty database named "testdb" to run the code.

    USE [master]
    GO
    CREATE LOGIN [dev] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO
    
    -- Server level permissions
    USE master
    GO
    GRANT VIEW ANY DEFINITION to [dev]
    GO
    GRANT ALTER TRACE to [dev]
    GO
    
    -- Database level permissions
    USE testdb
    GO
    CREATE USER [dev] FOR LOGIN [dev]
    GO
    EXEC sp_addrolemember N'db_datareader', N'dev'
    GO
    EXEC sp_addrolemember N'db_datawriter', N'dev'
    GO
    EXEC sp_addrolemember N'db_ddladmin', N'dev'
    GO
    CREATE ROLE db_executor
    GO
    GRANT EXECUTE TO db_executor with grant option
    GO
    EXEC sp_addrolemember N'db_executor', N'dev'
    GO
    
    -- User to grant
    USE [master]
    GO
    CREATE LOGIN [testuser] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO
    
    USE testdb
    GO
    CREATE USER [testuser] FOR LOGIN [testuser]
    GO
    
    -- Test Queries
    USE testdb
    EXECUTE AS LOGIN = 'dev';
    GO
    
    CREATE PROCEDURE dbo.testproc AS BEGIN
          SELECT 1
    END
    GO
    
    GRANT EXECUTE ON dbo.testproc to [testuser]
    GO
    

    Thursday, November 15, 2012 2:58 PM

Answers

  • You might able to meet your needs by granting CONTOL to the schemas. For example:

    GRANT CONTROL ON SCHEMA::dbo TO db_executor;

    Now the db_executor members can run wild in the schema but not have overall database permissions. (So they can't create new users, or symmetric keys, etc.) You would need to repeat this for all schema's, but I guess you have a limited number of schemas. Or at least the schema's are rarely added. The permissions poster http://go.microsoft.com/fwlink/?LinkId=229142 shows what permissions they would get.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by SQLDawg Tuesday, November 20, 2012 1:39 PM
    Friday, November 16, 2012 4:36 PM
  • I played a little with it, and if you say:

    GRANT EXECUTE TO testdev WITH GRANT OPTION

    testdev can say:

    GRANT EXECUTE TO public

    But not

    GRANT EXECUTE ON some_sp TO public

    The same applies if you grant testdev EXECUTE on SCHEMA::dbo: testdev can propagate that exact permission but not a finer grain.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by SQLDawg Tuesday, November 20, 2012 1:47 PM
    Thursday, November 15, 2012 9:42 PM

All replies

  • Hi ,

    See below link :

    How to allow data update only thru application ...and not thru sql tools

    http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/342ede90-5878-4798-86ec-79e4fe8b91d9/


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Thursday, November 15, 2012 3:15 PM
  • I'm not quite able to see how this relates to my problem?
    Thursday, November 15, 2012 3:28 PM
  • Try

    GRANT EXECUTE ON dbo.testproc TO [testuser] AS db_executor;
    GRANT EXECUTE ON testproc TO db_executor WITH GRANT OPTION;

    I think your problem was that you were mixing up old pre-SQL Server 2005 syntax (GRANT EXECUTE TO db_executor with grant option without mentioning the procedure) with newer (SQL Server 2005+) syntax (GRANT WITH GRANT). That doesn't always work.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Thursday, November 15, 2012 5:00 PM
  • I played a little with it, and if you say:

    GRANT EXECUTE TO testdev WITH GRANT OPTION

    testdev can say:

    GRANT EXECUTE TO public

    But not

    GRANT EXECUTE ON some_sp TO public

    The same applies if you grant testdev EXECUTE on SCHEMA::dbo: testdev can propagate that exact permission but not a finer grain.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by SQLDawg Tuesday, November 20, 2012 1:47 PM
    Thursday, November 15, 2012 9:42 PM
  • Thanks Rick. The thing is that I do not want my devs to be aware of why they are able to perform an action (i.e. I do not want to "bother" them with specifying explicitly which role they need to act as).
    Friday, November 16, 2012 9:48 AM
  • Thank you Erland for providing valuable insight on this. I have two questions:

    1. Is it really not possible to permit a role/group to grant execute on procedures without the use of triggers/other code magic or making them some kind of admin?

    2. If so, is this the intended behaviour or is it a bug?

    Friday, November 16, 2012 9:53 AM
  • Hallo SQLDawq,

    may I ask whether it is a better solution to provide your developers a VM with a DEV-environment where they are sysadmins?
    If they have codings for deployement they have to pass "two levels":

    - UAT (User Acceptance Test)
    If the codes will not damage the system and the customer has confirmed you can go to

    - PROD

    all deployements should be done by a dba and not a developer.

    If i would be in the role of a developer in your company i would stand on full privileges for developments.
    I'm missing IMPERSONATION (as an example) in your privileges.

    From my point of view it is important to check my codiings with less privilges as i have. So basically before I deploy any codings into production i run my codes with a dedicated test user with minimum of privileges.

    I know that will not cover your basic requirements from this thread but maybe it will give you an idea for future installations :)


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)

    Friday, November 16, 2012 1:14 PM
  • Thank you for your input Uwe. With 25 developers and a complex infrastructure it is not feasable using VM's with sysadmin. We run separate dev/test/prod environments, the developers only have access to the dev environment. The set of permissions that I have demonstrated in the first posting is sufficient for the developers to do their job. My main question now is whether the behaviour I demonstrated in my code example is by design or not.
    Friday, November 16, 2012 1:25 PM
  • Well, I use triggers and code magic to make this happen. (Sorry)

    A create a table that outlines what permissions to objects  (using wildcard names) should be granted when an object is created or when a reapply of permissions is run.  I have a DDL trigger that executes another procedure that runs the permissions statements.   (I also have a daily job to clean up changes in the permissions table, such as someone removing rights.)

    In this enviroment, my developers are ddl_admins, but have no rights to set up security, therefore the trigger is essential.

    You can create a signed stored procedure that has the rights to apply permissions.  http://msdn.microsoft.com/en-us/library/bb669102.aspx

    Once you have defined the object to person (or group) relationship, it pretty much maintains itself.

    FWIW,
    RLF

    Friday, November 16, 2012 3:58 PM
  • Thank you Russel, it is nice to see that others have the same issues as myself. I have actually made a (hopefully) temporary solution utilizing the triggers basically the way you describe.

    This brings me back to my question whether this is a bug or not. I have read BOL, but it is not clear to me if the behaviour I have demonstrated is intentional or not.

    Friday, November 16, 2012 4:04 PM
  • You might able to meet your needs by granting CONTOL to the schemas. For example:

    GRANT CONTROL ON SCHEMA::dbo TO db_executor;

    Now the db_executor members can run wild in the schema but not have overall database permissions. (So they can't create new users, or symmetric keys, etc.) You would need to repeat this for all schema's, but I guess you have a limited number of schemas. Or at least the schema's are rarely added. The permissions poster http://go.microsoft.com/fwlink/?LinkId=229142 shows what permissions they would get.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by SQLDawg Tuesday, November 20, 2012 1:39 PM
    Friday, November 16, 2012 4:36 PM
  • The behavious is surely the intended. Books Online is terse, but it does say:

       WITH GRANT OPTION
          Indicates that the principal will also be given the ability to grant
          the specified permission to other principals.

    Note specified, it does not say anyting about implied permissions.

    There is another property with the GRANT OPTION, which makes it less suitable for what you want. You have this developer, he creates procedure like the craze and grants permissions to testusers. One day he quits, and you want to drop him. But you can't because he has granted all these permissions. And you cannot revoke his permissions he has granted, without also revoking the permissions he has granted.

    So what should you do? I share Uwe's sentiment: you are making things overly difficult for yourself. In the environment I work in, everyone knows the sa password and that is what use to create stored procedures etc. There is nothing to protect in the development servers anyway. What matters is the version-control system.

    But if you want to stick to your current idea, write a stored procedure which accepts a stored procedure name and a user name, and which then grants access on the procedure to the given user. Sign that procedure with a certificate, create a user from the certificate and add that user to db_securityadmin. Add checks of input parameters as needed. For more details about this technique, see this article on my web site:
    http://www.sommarskog.se/grantperm.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, November 16, 2012 9:47 PM
  • Thank you Rick, that solved my problem!
    Tuesday, November 20, 2012 1:38 PM
  • Thank you for providing valuable insight on this case Erland. You made me understand WHY my case did not work as I expected. I do not agree on that sharing the SA password is a good idea, but thank you for providing an alternative solution.

    I do believe that implied permissions should work (as it does with e.g. db roles) though. I'm still interested in hearing from somebody on the SQL Server team whether this is the intended behaviour. :-)

    Thank's a lot to everybody helping me on this issue!

    Tuesday, November 20, 2012 1:46 PM
  • I do believe that implied permissions should work (as it does with e.g. db roles) though. I'm still interested in hearing from somebody on the SQL Server team whether this is the intended behaviour. :-)

    I'm confident that it is the indended behaviour. Or maybe more to the point: a concious delimitation. Granting cascaded permissions becomes more difficult if you get EXECUTE on an object from someone who has been granted EXECUTE on database level. Say that A has been granted EXECUTE on database, schema and object level all with GRANT OPTION. A has in his turn granted a user B EXECUTE on an object. Now you need a reference count to know when a REVOKE for A will have an effect for B. You must also track from which permissions B got the permission from A. And originally A only had permission on the object, and only after having granted permission to B he gets permission on database level. As you see, it gets quite messy.

    But by all means, if you think that there is a compelling business reason, submit a suggestion on http://connect.microsoft.com/sqlserver/feedback

    I like to stress that your submission should not only say that you want to work things to work this way, but also what the benefit is.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, November 20, 2012 5:08 PM