Strategy for storing User Configurations
-
I couldn't find anything on the existing topics and this seemed like the right place to post this...
I am re-thinking a design we have for Logging-on Users in our application. In the past there was an Administrative GUI for managing Users which would update both a "Users" table as well as updating the SQL Server Login associated with that user. When a User logged-on though, the strategy was less than perfect because if both the User Name and Password were not exactly the same in both the Table and the Login, then the logon would fail. Also, I've now been tasked to use Windows NT Logons for authentication. I do NOT have questions on HOW to do this stuff, that's very easy. My question is more or less, what is the best approach to storing users in a database? Keep in mind that we are also storing some configuration information in the User's table in an XML format along with other tables which are related to the Users table (i.e. UserGroups).
My initial stragegy is to ONLY use the User's table and to forget about maintaining the SQL Server Logins. This approach would use one Login (master?) that has access to the DB to validate the User Logon from the User's table. I would also need to add a Column to the User's table to store the Windows NT Logon so that I could lookup the User based on that to retrieve their config settings.
Pros? Cons? Ideas?
-
This was removed by the editor as SPAM
-
I'm slightly confused, why one login? If the Windows logins are used, then use them to auth to the db.
I do like the idea of storing config data in the Users table and adding the NT login as a column. Altneratively you could map the NT logins to db users, but if this changes the names then it gets hard to administer.
-
The reason for the one (master) Login is because I thought that in order to take advantage of SQL Server connection pooling, the Connection Strings all had to be the same. I am starting to see though that the connection pooling is really always on the Clients PC. However, now that we are supporting Terminal Services / Citrix, the connection pooling will be ever more important. Hence the need for one Login, to keep the connection string the same.
-
I've seen that used before - one SQL login that is used to look up the data in a table containing users and similar. It works relatively well except for auditing. You'll need to code your audit logic appropriately to capture the person currently using the connections or you'll find that everything was done by the SQL Login.
-Pete
-
Should have added in some details:
Users table for the NT Login and associated base information for that person - usually things like Phone/extension, First/Last name, Department, Role, etc.
We then branched out to other tables for actual permissions within the app where that was appropriate. Usually one table to indicate groups of permissions and another table to link people into those groups. Some apps went way beyond that as well - storing objects and then linking to groups, then to people. All of that is kind of up to you on how to implement. I'd start with the basic User/Group configuration and branch out from there as needed.
-Pete
-
Ok, so it sounds like our implementation is fine then. There is an internal Business Object of the application that stores the "Active User" so we can use the ActiveUser.ID for the Auditing info. We've also managed to handle all auditing through one table vs. one Audit Table per Table scenario that most DBs use. This enables us to use one Stored Proc as well for auditing.
Ok, thanks for the input!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply