0

I am creating a web application that will have many users. Each user has its own login and password to access the application. The data of the application will be stored in a database.

As the application will have many users, I would like to know the correct way to make the users connect to database through my application.

Is it better to create many users of the database (accounts) or simply use the master/root to connect to database from the application?


Edit/Note: the application users will not have direct access to database. The database is in the same server of the application and will not be open to external access.

ceklock
  • 6,143
  • 10
  • 56
  • 78
  • 3
    The users should have no access to the database itself other than through your application. To do things otherwise is to court disaster. – Hovercraft Full Of Eels Jun 19 '12 at 02:25
  • They will not have access. The database is in the same server of the application. – ceklock Jun 19 '12 at 02:28
  • @tecnotron the physical location of the servers is not an important matter to answer your question. – Luiggi Mendoza Jun 19 '12 at 02:29
  • Added a note because of what Hovercraft Full Of Eels said. The users will not have direct access to the database. – ceklock Jun 19 '12 at 02:32
  • Your application should connect to the database using the database user credentials. The database should have a table to keep application users credential and have the logic in your application to validate against the user credentials stored in database. – Chandra Jun 19 '12 at 02:35
  • Possible duplicate of [Should application users be database users?](https://stackoverflow.com/questions/341271/should-application-users-be-database-users) – mach128x Jul 29 '19 at 14:18

2 Answers2

3

The standard solution is your application has one master login/password to connect the the database. You then create a table with columns like this:

  • user_id
  • username
  • password_encrypted
  • email_address
  • permissions

Note that passwords bring security issues, and you should either use a package to handle authorization, or read up a lot on the subject. Don't store user passwords in the database without strong encryption.

Bryce
  • 8,313
  • 6
  • 55
  • 73
  • That was what I was thinking: a master login/password to connect to the database. But the database can have many users, some with less permissions. I don't know if it is better to create many users of the database or simply use the master (root) to connect to database and manage the other users of the application by adding them as rows on a big 'users' table... – ceklock Jun 19 '12 at 02:14
  • Your application enforces the permissions. Using database level permissions is generally too cumbersome and hard to scale, it is not generally done that way. – Bryce Jun 19 '12 at 02:18
  • Not every application needs to scale to the same degree. An Intranet app may be served very well by a direct authentication model. – JohnFx Jun 19 '12 at 02:39
  • Even then, having direct DB permissions means there is a route to modify data outside the application. Having the app control the database is more robust, even in small setups. It can be done either way: I recommend that the OP have a single login. – Bryce Jun 26 '12 at 15:29
3

There are pros and cons to each.

Using a shared service account to authenticate to the database has the advantage of being able to more efficiently pool connections to the database. That is, connections can be re-used between users minimizing the sometimes costly operation of opening a new connection, which you will have to do if each user authenticates separately. A definite con is that you have to be extra careful about validating any SQL the user is running since the permissions on the account have to be able to do what the most powerful user of the system needs to be able to do.

Using an account for each user gives you more flexibility in assigning permissions to various users without having to implement your own custom authorization scheme in your application. Also, it makes auditing the system a little easier because you know who is connected when you check the DB connections. Finally, this approach can reduce your vulnerability to SQL injection, in that you can lock down each user's account (preferably using role-bases security in the DB platform) to only be able to do things that user should be allowed to do.

So, for example, if they somehow inject a DELETE FROM UsersTable, you would have locked that down and the injected command would fail even if it got through your validation logic.

There is another consideration if you have users who know how to use database tools (especially MS Access) and have direct access to the database server. If you use the per-user authorization model you may have trouble with savvy users circumventing your application and working directly against the database. If your users are a bunch of programmers you might want to go with a shared account.

Use a shared service account for DB access if your app is very heavily trafficked by a large number of concurrent users who do small transactions.

Use an account/per user scheme when you have fewer users connecting to the system concurrently or you want more security and/or better control over authorization to objects.

JohnFx
  • 34,542
  • 18
  • 104
  • 162
  • I really want to learn how can I create and add different roles/permissions to 1000 or more database users in live production. – Luiggi Mendoza Jun 19 '12 at 02:31
  • It depends on your usage scenario. I have apps with 1000's of users, but rarely more than 50 logged in at one time. Concurrency is more important of a consideration than named users. – JohnFx Jun 19 '12 at 02:33
  • When I read your answer, I understand that a site like amazon.com will have at least 1 user per user login. – Luiggi Mendoza Jun 19 '12 at 02:36
  • Then you didn't understand it. That is exactly the opposite of what I am saying. Are you writing a site that will have as many users and similar transaction types as Amazon? – JohnFx Jun 19 '12 at 02:36