locked
Developer Security Permissions RRS feed

  • Question

  • Ok, so, the developers are trying to force me into giving them DBA rights on the database, but I am telling them no. Essentially, they have DDL admin, Data Reader and Data Writer and Execute on their procedures and functions. They are saying they need db_securityadmin rights in order to grant datareader and datawriter and execute permissions on objects when they create them. If I am not mistaken reading and writing form tables should be automatic, but execute permissions would only need to be set. Is there something else I am not thinking about?
    John M. Couch
    Wednesday, September 22, 2010 4:45 PM

Answers

  • Correct.  Of course, Microsoft does not deliver a builtin db_executor role, but you can make one yourself.

    http://blog.gre-sqlserver-solutions.com/2010/04/dbexecutor-role.html

    Also, instead to using the builtin db_datareader & db_datawriter, it is also possible to GRANT SELECT, GRANT UPDATE, etc. on the database.  But I don't know that there is a advantage to doing that in this case.

    However (just to be clear):  Granting db_datawriter (or GRANT UPDATE, INSERT, DELETE to database) means that anyone with those rights can change any data in the system by any tool to which they have access and not strictly through the application.  (I come from a heavily stored procedure oriented shop and we would be very reluctant to use that approach.)

    Other options are to give the application to the database through a proxy account or a application role.  (Although application roles have additional issues.)  However, unless these are implemented through a middle-tier, there is still some exposure to the proxy or role password.

    All the best,
    RLF

    • Marked as answer by John Couch Wednesday, September 22, 2010 6:17 PM
    Wednesday, September 22, 2010 5:55 PM

All replies

  • If all reading and writing is done through stored procedures, there should be no need for users to have direct access to the tables.  Of course, if the application does not always use stored procedures and functions, then they will need direct access to the affected tables.  (But of course EXECUTE rights need to be granted to the procedures, or (2005 and up) to the schema or to the database.  The advantage of the last two options is 'set and forget'.)

    Should they be security admins?  It depends on how you divide up the work in your shop.  In my shop, for critical systems the DBA team cares for the security, but in smaller systems the developer team may well handle that.  

    I also prefer to get deployment scripts which can be run at a controlled time over giving developers db_owner access to a production database.

    RLF

     

    Wednesday, September 22, 2010 5:26 PM
  • So if I obviously granted db_datareader, db_datawriter, db_ddladmin and db_executer rights, and granted db_executer execute permissions at the schema level, and procedure or function created in that schema could be executed by anyone in db_executer, correct? Any new object created would be selectable or updateable via db_datareader/db_datawriter without manually granting, correct?
    John M. Couch
    Wednesday, September 22, 2010 5:35 PM
  • Correct.  Of course, Microsoft does not deliver a builtin db_executor role, but you can make one yourself.

    http://blog.gre-sqlserver-solutions.com/2010/04/dbexecutor-role.html

    Also, instead to using the builtin db_datareader & db_datawriter, it is also possible to GRANT SELECT, GRANT UPDATE, etc. on the database.  But I don't know that there is a advantage to doing that in this case.

    However (just to be clear):  Granting db_datawriter (or GRANT UPDATE, INSERT, DELETE to database) means that anyone with those rights can change any data in the system by any tool to which they have access and not strictly through the application.  (I come from a heavily stored procedure oriented shop and we would be very reluctant to use that approach.)

    Other options are to give the application to the database through a proxy account or a application role.  (Although application roles have additional issues.)  However, unless these are implemented through a middle-tier, there is still some exposure to the proxy or role password.

    All the best,
    RLF

    • Marked as answer by John Couch Wednesday, September 22, 2010 6:17 PM
    Wednesday, September 22, 2010 5:55 PM
  • I knew about the granting db_datawriter/reader thing, but it is nice to be reminded. They essentially had everything in SQL Server 2000 because the old DBAs didn't care. We are migrating to 2008, and I am trying to lock it down. Now they are obviously crying about it, but I have to give a little and take a lot. So we will see. Thanks for the feedback.
    John M. Couch
    Wednesday, September 22, 2010 6:17 PM