Create database user or store as table record?

Recently, someone posted a database user question on the Dream In Code forums. The gist of the question was, if you had to maintain a number of users for a web application, and each user needs an ID to log in, what’s the preferred method of doing it?

2 options were given. The first was to create an individual database ID for each user. The second was that all users use the same user ID to log in.

What I had in mind was, the database connection string. If individual database IDs were created, then when each user log in, unique connection strings are used to connect to the database. From what I know, this doesn’t scale very well, since the database throttles at a certain number of connections.

So the individual database ID case was no good. Having all users using the same ID would work. But there’s no personalisation, or you need some work to separate each user. Unless personalisation wasn’t an issue. Then there wouldn’t be any need to log in. I was a bit perplexed with the second option…

So I suggested that user IDs be stored in a database table. A database user ID is created specifically for online user log in’s. Every user has a unique ID to log in, and when logging in, the special database ID is used to form the connection string. Then the ID-password pair is used to verify against a table storing the user IDs.

Passwords would be hashed and stored in the database table. When a user logs in, the given password is hashed and compared with the one stored in the table.

This way, there’s only one unique connection string, and if I understand it correctly, it can be cached and reused, for better responsiveness.

I’ve dealt with both cases. In my work, there’s a department who handles the database. My applications are open only to internal users, and someone decided that user IDs must be controlled at the database side. And so each user has their own database ID. When logging in, the authentication process is handled by the database.

Basically I pass in a user ID and password. If a connection goes through, the user ID and password are valid.

This is sometimes infuriating, because my users are a fluid group. Some existing staff leave, or some new staff joins, and I have to ask the database department to update the list of IDs. The infuriating part is when a user asks me how to use the application and after some questioning, discover that she’s a new staff. Help desk support sometimes require me to question some of my assumptions…

The thing is, even though that department is handling the user IDs, I still have to keep a list of the IDs in a database table. The reason? User access. Certain users have rights to certain pages. The only way to control that is at the application level, not the database level.

On the other hand, I also maintain web applications where the user IDs are held in a database table. Maintaining the user list is as simple as an insert or a delete statement. There’s really only one database connection ID. This allowed me to easily scale the applications in terms of users.

I imagine the huge web sites such as the social media sites store users in a database table. I think creating thousands of database IDs is inefficient. Maybe I’m wrong. What do you think?