Quote:
Originally Posted by secretlydead
I currently have a single mysql instance for a couple of websites. Both websites require that the user provide a username.
The thing I just noticed is that two people might choose the same username for both applications. Both of them would have grants to each others' tables, although neither would ever be able to access them, since they can only access through php scripts.
But, I'm now guessing this is not best practice. What does one do? Run multiple instances of mysql for several websites?
|
Are you getting confused with the concept of a MySQL user which has permissions on a database and an
application user which is created inside an application database?
Ok, so I'm assuming you have a single MySQL instance, for each of the website applications you create a database, you then create a MySQL user and permission that MySQL user for the database. Are you then installing an application on each website that access the database you've created for them? I'm guessing that is the case as you say "Both websites require that the user provide a username." Users would then be created in the databases tables and access to the website application controlled from that table.
For example, there's a user Bob and a user Sue who are going to be creating websites that use MySQL databases.
You create a database bob_data and then create a user bob_user, grant bob_user all privs on bob_data.
Do the same for sue_data and sue_user.
Now Bob and Sue install their website applications (let's say it's a forum) and they use their own database, so the tables are the same etc. etc. but they are in separate databases. Sue has no permissions on Bob's database so she can't access his tables and the other way around.
Alan now comes along and registers on Bob's forum and also on Sue's forum.
Alan's user details (and permissions) are held
within each application's database. So at no point does Alan actually have a MySQL username. He only exists in two separate applications.
Now, regarding "best practice", it would depend on the environment and resources.
There would be a school of thought that says that running a fully separate MySQL instance for each database would be the way to go, the advantage is that if one database or user screws the MySQL server badly then the other MySQL processes would be unaffected. However this does mean that there is an additional management overhead in setting up and maintaining this kind of infrastructure. You'd need fully separate instances using separate sockets or ports and you'd have to make your website admins aware of which instance to connect to for their application.
Personally I've never had any problems with using a single MySQL server with multiple databases on it. My own web server runs over 50 databases on a single MySQL instance and I've never had any problems with the actual server, it's always been individual applications. I've something like 20 different WordPress blogs and two vBulletin forums (one that does around 1.4M page displays a month) and there's been no issues with cross-database security.
Hope this helps.