Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Location
    Lowlands
    Posts
    53

    Unanswered: user management webapplication

    Hi All,

    I've a question about user-management when developing webapplications:

    Which approach would you people preferre?

    1. Create a table in the database with the following columns: username, password, group, rights

    use this table to add or remove users from the system and take care of the user rights like reading, writing, updating, deleting to the database.

    OR

    2. Using sql server 2000 user management, and create logins for every user. The groups, Read/Write/delete/update rights are managed trough the sqlserver 2000 system.

    thanks in advance
    If Perfection really is an Illusion. Then I want to get as close as possible to that Illusion.

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    I have done it both ways and there are pros and cons to each.

    My experience with using SQL logins in a web environment came about because:

    1. We had no Active Directory and we were not allowed to pass NTLM/Kerberos traffice through the firewall.
    2. Security required us to provide non-repudiation for changes/updates/deletes (ie, we had to know exactly what user was doing what to which object and when).

    Here's what we did:
    1. We created about a dozen custom roles and assigned object permissions to these roles.
    2. We scripted the user creation process and provided a web GUI that "super users" could use to create new user accounts.
    3. Periodically, we'd "audit" user access and send notifications to "stale" accounts. We'd purge these accounts after 90 days of no activity. Accounts could be deleted from the web front end.
    4. For presentation purposes, we also maintained a table with user rights in the application.

    A big benefit to doing it this way was to be able to trace user activity through the application to the database. An obvious drawback is that we were unable to take advantage of performance improvements such as connection pooling (we had approximately 1,000 users and at any given moment in time there might be 100 simultaneous connections; so not a huge app by any means). A big drawback was that it was initially a real headache to set up and we had a painful month or so while trying to "figure things out". Troubleshooting was a pain until we learned to check permissions first.

    The other method is to create a single "application" user and embed that connection information into your app. Assign all the privileges needed to that user (or you can still create custom roles and assign object permissions to the role, but that's kind of a pointless additional step in this model). Then you have to create the user permissions table.

    It's obviously a lot easier to troubleshoot using the second method. It's also less expensive (probably) to develop initially. You will still need some sort of web GUI to dole out permissions; the second method is just less complicated.

    I wish you the best,

    hmscott
    Have you hugged your backup today?

Posting Permissions

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