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
3
-
1whoa, after reading this twice, i really don't know what is being asked here. Are you referring to a MySQL table of user info or are you talking about actual MySQL user accounts that access the db?bimbom22– bimbom222010-07-02 17:29:24 +00:00Commented Jul 2, 2010 at 17:29
-
Welcome to SO. I'm having trouble following you. Can you try to clarify? Are you talking about a user table in mySQL (for your own application) or mySQL user accounts?Pekka– Pekka2010-07-02 17:29:40 +00:00Commented Jul 2, 2010 at 17:29
-
3I believe he's talking about creating an individual MySQL user (as in a MySQL account with access to specific tables, etc.) for every user of his PHP application. And the answer is, no. You don't need a MySQL user for every application user. Most applications only use 1 or 2 MySQL users.Frank Farmer– Frank Farmer2010-07-02 17:51:38 +00:00Commented Jul 2, 2010 at 17:51
Add a comment
|
3 Answers
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,INSERTorUPDATE. If your application requires it, give himDELETEprivileges. - Use the
rootfor 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 privileges to, so be careful with GRANTing too high privileges.
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.
Comments
Each user in your application should not be a user in MySQL. Generally, you should have one MySQL user that your entire application uses.
Comments
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.
