3

How many user accounts should be setup for a given application? Where should the separation be?

  • Should it be one login per application?
  • 1 Application per program?
  • 1 for front end and backend?

How do you set up your database accounts?

4 Answers 4

7

I would set up:

  • the stock administration account that comes OOB. Use this for things that no other account can do. Keep it locked up and only haul it out when you don't have a choice.
  • a secondary admin account, one for each administrator. Do all of your day-to-day admin work in this account, but on these admin accounts, omit privileges for the really dangerous stuff (DROP DATABASE, DROP SCHEMA, etc.), use the out-of-box admin account mentioned above for that. (The idea is that forcing you to log in as another account should be a mental indicator that you're about to do something potentially damaging, also that the work-a-day admin accounts can have a built-in "safety switch" in the form of missing privs for actions that could cripple your database)
  • if you are using machine-based connects (connection pooling, proxied connects, etc.) use an account for that. Grant the bare minimum privileges needed for this account to function (to prevent the account from doing enormous harm via DROP DATABASE, etc.)
  • if you have direct connects from end-users, one account for each end-user, UNLESS your end users are a broad catagory and you have no need to separate out privs for that catagory. In that case, a single account for each user group.

2009-07-03 Re-Edit for Clarity.

1
  • Really like the point about End Users ... anyone else hate access?
    – C. Ross
    May 5, 2009 at 11:59
5

I'm going with: it depends. It really depends on how your database will be used. Different scenarios:

  • Internal application connecting on behalf of the user. In this case, we create a service account within the domain and grant it access to the domain. The application uses this one account for all database access.
  • External facing (Internet) application. In this case, we create a SQL Server login because the web servers will be in the DMZ and therefore will not be on the domain. The application uses this one account for all database access.
  • Interal application passes credentials of the user. You see this with SQL Server Reporting Services and other cases where passing the actual credentials are important (audit trail). In this case, you'll likely have different levels of permissions. Each level should have the appropriate Windows domain security group. Windows users should be members of those groups. Those groups should be tied to user-defined database roles within the database. Those database roles should have all the permissions.
  • Direct access to the database. You'll see this more with reporting type of systems. In this case, Windows security groups again with access granted through the use of database roles.
3

You're going to get a variety of answers on this one.

I set up single application (read "per-application") accounts that are used in connection strings. No users (other than my dev group) have direct access to the db's.

I work primarily in a corporate MS environment (.NET, SQL Server, Active Directory). Generally authentication happens via current logged in user context and application specific security tables. Code handles the authentication and then gets the application authorizations from the database. That takes much of the burden of user maintenance off of me and puts it onto our IT department (password policy and resets, account expiration, disables, etc).

1
  • I believe there needs to be at least enough accounts so that if one db-client starts to cause problems you can disable that account without disabling all access to the server
  • Normally I will not use setup an account to access more then a single database.
  • I use a separate account for a web application versus internal client.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .