Post reply

Application Architecture Design - DB_OWNER vs Application Role

  • July 28, 2010 at 11:19 am

    #139939

    Hi,

    I am wondering from a secured application archiecture design perspective which is a better role to use for application connection to the database, the use of db_owner or application role and why. Thanks in advance for any advice.

    Regards,

    Jolee

  • July 28, 2010 at 11:55 am

    #1199412

    Do not under any circumstances require DB_Owner rights to a database if you are designing an application. The app account should not need DDL admin to run the system and if it does you are designing it wrong. Plus the app account should not be creating users and assigning security, or taking backups, or other non-data tasks. Using the app role will allow you/force you to give only as much access as needed to run the app and no more. You don't want to give too much access ever to non-sysadmin accounts. In worst case, if a developer got the password to the app account and therefore had DBO then you can't do change control. And suddenly when data changes happen and no one can figure out why, suddenly everyone with the password to the app account is suspect. Better to lock it down tight and control it for safety.

  • July 28, 2010 at 12:21 pm

    #1199437

    Well one part of that is easy to answer. When to use db_owner: never. It's far too high a permission level for an application. Someone with that can do *anything* to the database including drop it.

    The question you should be asking is application role vs database roles (ones that you define and set with appropriate, minimal permissions)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 28, 2010 at 12:58 pm

    #1199465

    Thanks for the advice.

    Sorry, one more query. What if the application has go-live with this architecture setup, use of db_owner role for the application connection to the database? What would be the major concerns if we ask IT/developer to reconfigure this setup using application role instead?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply