Restrict use of Multiple Users for the Same Login

From: Grant Case (hangtime79_at_DONThSoPtAmMail.com)
Date: 08/06/04 

Date: Thu, 5 Aug 2004 23:38:27 -0500

This is more a design question then anything else but I have yet to come up with a good solution so I am posting here.

Currently, I am rearchitecting the database schema for the security for both our Intranet and Internet. I am fairly comfortable with the features that will be supported by the schema. Some of these features include password expiration and prompt for change, retry limt, Domain-Group-User privilege sets, time-based restrictions, full audting, trigger based status changes, and a whole bevy of other good stuff. (For Joe: No Flags Joe all foreign keyed Status tables for each table within the database where a status could be changed :) ).

Which brings me to my question - A problem we have consistently has been users (600+) sharing their user id's and passwords with each other when logging into our both Intranet and Internet web sites. This is a very large security risk and has created numerous problems even though the users have been warned about this behavior. I would like to somehow be able to restrict the use of a login (a record in our SecurityUser table) multiple times within the "session" of a user's login to database. The problem is that the connection is not persistent because each time a web page is loaded it opens and closes a connection to the database Thus two users logging in at approximately the same time may never be caught because the opens and closes happen so rapidly.

Some of the solutions we came up with and ultimately disgarded are:

Use cookies to uniquely identify the client's web browser and thus the login to the database.
Reason: Our company policy restricts the use of cookies so this is out.

Using the SET CONTEXT_INFO store the login of the user and compare it to the list of users each time logged into the database.
Reason: Still falls prey to the same problem of connections constantly being opened and closed by the web pages.

Use ASP.NET Session State Management in SQL Server and somehow hold the user's login name in the session and then compare it anytime a new user tried to validate their credentials against the database. Reason: This would probably be the ideal solution because the session is persistent in the database, however, the fields Microsoft has decided to hold the state information in are varbinary and image fields so I'm not even sure if we can interogate them in T-SQL.

Add a datetime field to the SecurityUser table and update it each time a user has logged into the database and have a job come along and NULL it out after session timeout limit has been reached.
Reason: If a user's browser somehow died there would be no way for the user to immediately get back into the system and no way to uniquely identify the client's machine.

Hopefully this gives someone enough information to go on to suggest a solution or propose a solution they already have going in their production environment. Thank you for your help in advance.

Grant

Relevant Pages

  • Users in database have no associated login
    ... some reason, the users in our database don't have associated logins (as seen ... databases the users of our database have the sa login associated with them. ... name is blank for the dbo user... ...
    (microsoft.public.sqlserver.server)
  • Users in database have no associated login
    ... some reason, the users in our database don't have associated logins (as seen ... databases the users of our database have the sa login associated with them. ... name is blank for the dbo user... ...
    (microsoft.public.sqlserver.security)
  • Awaiting Command
    ... I have 10 severs that login to my database and after an sp_who2 I noticed all of them have Awaiting Command. ... Is this normal or reason why our front end app is not functioning? ...
    (microsoft.public.sqlserver.programming)
  • Re: PDO: Switch database user without reopening connection
    ... At the bare minimum there will be a login user who only has ... modifications to the database as well (editors get update permission, ... As database connections are expensive to ... a certain visitor in the Session, and use that value to start the right ...
    (comp.lang.php)
  • Re: PDO: Switch database user without reopening connection
    ... At the bare minimum there will be a login user who only has ... the postgres user they are logged in as to one that can make ... modifications to the database as well (editors get update permission, ... As database connections are expensive to ...
    (comp.lang.php)