Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2002
    Posts
    2

    Unanswered: creating users in sql 2000

    i recently noticed that the programmer has been adding the users of our applications to the actual users under our database. When i inquired as to why, he said it was the only way they could access the data. I also noticed that the users all have been set to owners. I spoke with another friend and he said as far as he knew there wasn't any real reason why the application users would have to be set up in the actual database but just in a table/field, but that they really don't need to be owners. This app is designed to have thousands and thousands of users, and it seem like this is going to be difficult to manage.
    Any thoughts?

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    There seems to many ways you can implement security, Windows Authentication, SQL Server mode, Application Roles, individual user accounts, single application accounts.

    To shorten the response I will just say how we implemented security. Like you we have about 2000 users that access the database via one of many applications. We elected to create a single user account for each application (example: Payroll - INetPayroll). Each enduser would enter there logon information into a logon screen (.ASP) and be validated against a security table that listed every user and there password. Once validated the application would then connect to the database using it's own SQL Server account (INetPayroll). This way we limited the number of user accounts that had to be maintained in the database, security would only be granted to a few user accounts, we implemented GROUPs and added users (INetPayroll) to the GROUPs, there by only needing to grant permission to the GROUP only. We couldn't use NT authentication because we deal with NOVELL users and all applications are via Intranet and connections are made with the IIS user account.

    By making every user the owner you have opened up a can of worms. This is the lazy mans way of doing it. If a user has MS Access on their desktop or MS Query, they could connect to the database directly and access to database. As owners they can now modify any data directly, drop create objects, not good.

    In our implementation user don't really have a SQL Server account so they can not access the database directly.

    You may want to look at Application Roles.
    MCDBA

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    and as a follow on to achorozy, I NEVER allow programmers direct access to production dbs & tables. If they can't get to what they want via an application & stored procedures they are out of luck. That always has been a tuff argument to make to managment and there will always be an exception but this is one area I won't back down on. Security can never be taken to seriously.
    Paul Young
    (Knowledge is power! Get some!)

  4. #4
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    I don't want to side track here, but Paul brings up a good point. In our environment all data access, manipulation is done through stored procedures and not direct SQL statements. Were I work only the DBAs create the stored procedures and database objects, the developers write the VB/ASP code, design the screens, etc.

    I wish I could be a little survey here and find out how many people that subscribe to this forum are in fact DBAs at there site or have a DBA at there site. Are any of you out there developers/programmers thrown into the DBA role? Do you want to take on the responsiblities of a DBA or are you just doing enough to get by, to get the system up and running?

    Just some thought I have.
    MCDBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •