MYSQL: When to create a new user?

Question

I am writing a web application in perl that will create, edit, update and delete data from a MySQL database. I have written a perl module that will manage the connections (issue database handles ). As new users sign up for the application should each get their own MySQL username and password or is okay to execute their queries with the same (one generic) MySQL username and password?

Answer 1

one generic for the application

Since you normally never ever should connect as root to your application it even don't have the permissions to add mysql-users.

How would you even imagine working with a usertable on your applications side which is for every user different - chicken/egg

Answer 2

One generic, non-admin user is what you'll find in most apps.

Some developers take the approach of creating several users based on level of operation and least privilege, particularly for public facing scripts.

So, for example, if the operation is to delete data, that might be one user. Perhaps there is some very sensitive data in the environment and you'll create a given user for accessing that data and no other user has access to that data.

Each user is given no greater access than is required based on the intent of that user.

Then the given script connects with the appropriate user here.

Of course, you should program against and have defenses for db-related vulnerabilities like SQL-injection, but the thinking with the multiple users is if you had a script that was vulnerable to some exploit, the damage would be limited to the privileges of the mysql user used to connect to your database. It's a bit harder to manage and requires some more planning up front, but it adds to damage control in case of a db related exploit. You'd make this call based on how sensitive and important your data is and how much effort you are willing to put into the planning and design.