sql server programming:
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 :) ). =20
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. =20
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.
