Creating user log-ins for a website with SQL Server
-
Forgive me if the answer to this appears obvious, I am new to web programming with SQL Server.
I am building a website that will have many users, they must each create an account and log-in before entering info on the website.
The way I would do this in Access is to create a table of user accounts with names and passwords, then users will log-in and the code will check to see if their password is correct in the database.
Is this the best way to do this with SQL Server? The reason I ask is that I notice that you can actually create many user log-ins in SQL Server, so I was wondering if the industry standard for SQL Server web programming is to actually create a new log-in to the database for each web user. Of course this would mean that the database would have 1,000 seperate log-ins if 1,000 people created accounts.
I would much rather just create one log-in account for all users and just check against the database to see if the username and password entered by the wb user is correct, is this the best way to do this?
-
You can have a lot of logins, so it would not be bad to use the structure that is there.
There are some real benefits to using a login per-user rather than a central login. At the database level, you would know the user that is running an operation. This would allow you to use the user_name() function to determine who is accessing data. This can come in handy for filtering data in views and stored procedures. It can also be useful to see who is currently logged in by the user names in a connection. You will also get the user name associated with transactions and may be able to hunt down someone that has managed to do something you do not want them to.
It does mean you will have some additional management concerns. Your login information for your website will be in the Master database, so you need to keep your data and system database backups in sync. You will also have to have a process on your website with enough permissions to create a login, so you will want to ensure you have properly secured this process so nobody can create a login with better permissions.
-
You can do this either way, or as a mixture.
Using a table of usernames and passwords is certainly the easiest to build and administer, for a simple application. It also gives you the least auditing and control options.
Using SQL logins is more complex to set up (but not horribly complex), but gives you more options for auditing and system control.
If you do decide to go with SQL logins, make sure to set up a group with the correct rights to the necessary tables and such, then add each user to that group.
A hybrid solution would be to store some user data in a table, and use SQL logins to control access for others. Perhaps guests/public users would be in a simple table, and would have read-only access through a generic login, while users with any sort of update rights would have their own SQL logins. That would potentially keep the number of SQL logins down to a more managable size, while allowing users to migrate from guest to real pretty simply. Would be the most complex to set up, but still not that big a deal.
Any way you do it, it shouldn't be too complex to accomplish in SQL Server. "More complex" is a relative thing here.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
garethmann101 (9/8/2008)
Forgive me if the answer to this appears obvious, I am new to web programming with SQL Server.I am building a website that will have many users, they must each create an account and log-in before entering info on the website.
The way I would do this in Access is to create a table of user accounts with names and passwords, then users will log-in and the code will check to see if their password is correct in the database.
Is this the best way to do this with SQL Server? The reason I ask is that I notice that you can actually create many user log-ins in SQL Server, so I was wondering if the industry standard for SQL Server web programming is to actually create a new log-in to the database for each web user. Of course this would mean that the database would have 1,000 seperate log-ins if 1,000 people created accounts.
I would much rather just create one log-in account for all users and just check against the database to see if the username and password entered by the wb user is correct, is this the best way to do this?
I would do it the way you do in Access. This way, you only have to worry about managing the database connections at the application level. Let the application control what users can see what data.
Gary Johnson
Sr Database Engineer -
I would say go the same way as you did with access. And let the rules you put in you application determine if it is a valid user or not. Because if you go the other way then not only are you giving them all access to the database you are also giving them direct access to your SQL instance.
-
Gary Johnson, I see you work on zillow.com?
Good job with that. That is one of my favorite web applications, up there with Google Earth and wikipedia.
Keep up the good work, I would love to see that site go international.
-
What platform are you using to build the website? With ASP.Net 2.0 or later, there are login and membership providers built-in that let you provide access control and member profiles pretty easily. If you need to access the member profile data, you might want to look up the SQL Profile Provider - the built-in provider stores the data in a compact but hard-to-query format.
-Eric
[font="Tahoma"]Eric Flamm, Flamm Consulting[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply