PHP MySQL connection

Question

I'm been looking through the Internet for an answer for a long time. How could I control users and connections between php and MySQL. My question is, when we create a mysql database through php, actually we create the user in a user table in mysql. When I connect to the database, all the user we created is using same mysql user account to connect to database. Do I need to create any user in mysql user table (I mean the system table holds users)? So the user we use to login to the website will match the user in sql database user table? Then, each user has his own mysql user account to connect to database. What should I do to handle situation like this? Cheers

Answer 1

I think I understood your question, you are asking about user management on the database side.

You have, per default, a single user in the database, that has (of course) ALL privileges. This user will be named root most of the time (depending of your DBMS, but in the case of MySQL, this is the case).

So you have IMO two choices:

• Create a new user per application that as only the needed privileges like READ, INSERT or UPDATE. If your application requires it, give him DELETE priviliges.
• Use the root for all the applications that access the database.
• Create a user that will have the most common rights, that will be used for ALL applications that need to interact with the database.

Whatever you do, keep in mind that the user can do ANYTHING he has the privilages to, so be carefull with GRANTing too high privilages.

I consider it a best practice (at least this was always the case for me and I think it worked quite well) to have a users on a per-application basis, e.g. when I want an application Blog, I create a user blog that has INSERT, UPDATE, SELECT, [whatever is needed] rights.

One more edit: So to answer your question short: You don't need a MySQL user for each application user.

Answer 2

Each user in your application should not be a user in MySQL. Generally, you should have one MySQL user that your entire application uses.

Answer 3

Better yet, create one user per application. Give that user rights to execute stored procedures and nothing else. Create stored procedures for any type of database activity you allow that user. It gives you another security barrier.

Answer 4

Thank you guys. I apologize for my bad explanations. I just try to figure out how to manage users between web application and actually database users. I got this solved by all your help. Thanks again.