Users in database server or database tables

Question

I came across an interesting issue about client server application design.

We have this browser based management application where it has many users using the system. So obvisously within that application we have an user management module within it.

I have always thought having an user table in the database to keep all the login details was good enough.

However, a senior developer said user management should be done in the database server layer if not then is poorly designed. What he meant was, if a user wants to use the application then a user should be created in the user table AND in the database server as a user account as well.

So if I have 50 users using my applications, then I should have 50 database server user logins.

I personally think having just one user account in the database server for this database was enough. Just grant this user with the allowed privileges to operate all the necessary operation need by the application. The users that are interacting with the application should have their user accounts created and managed within the database table as they are more related to the application layer. I don't see and agree there is need to create a database server user account for every user created for the application in the user table.

A single database server user should be enough to handle all the query sent by the application.

Really hope to hear some suggestions / opinions and whether I'm missing something? performance or security issues?

Thank you very much.

Answer 1

There is one point that I can think of in favor of requiring the users have database user accounts. If you go with the approach of having a separate custom user table your application needs to have a connection string baked in in order to connect to the database to authenticate the user. If your application gets hacked, then the hacker has the database credentials to do whatever they want.

However if the user that is logging in has a database account, then your application can just use that user's database credentials. There is no need to store a database connection string in your app.

Answer 2

I don't see any valid points in having user accounts created in the database for application users.

Answer 3

Your "senior developer" is sadly mistaken. I'm not sure how someone who would qualify as "senior" would say that 1 app user==1 db user. As noted previously the application will not scale (in many respects). If the database is something like Oracle is he suggesting that any user of a large web application should be considered a db user as well in terms of licensing costs? What about handling user management?

I don't know what database you're using but a single user that is responsible for accessing the database via the application server is sufficient in most cases. Application users can be stored in the database, in LDAP, etc.

You don't want the maintenance nightmare of 1 app user = 1 db user. The senior person should be ashamed for suggesting such a thing.

Answer 4

No, users are authorized to use the application; the application is authorized to access the database. You don't need both.

You might have different access permissions in the database (e.g., users can't delete or drop tables; admins can do anything). In that case, it's typical to have a user-group-role design where users are assigned to groups, and each group has its own permissions.