As Caleb mentions, when setting up MySQL, it's best, as with other systems, to operate a system of least-required-privileges - unless the different applications are related, then there is little reason for them to have access to each others' databases. If some are related, then they should be granted specific, limited privileges on the tables they need access to in the other database but not more.
I am assuming that when you say
accessible from any domain via localhost
you mean that you're running a multi-domain Apache installation (or similar), that means that all your applications are running on the same machine, and thus, from MySQL's point of view every user is a
'username'@'localhost' user, so you're restricted to specifying users by username.
It's important to note that because of the way MySQL permissions work, in a situation where you have remote users that
'user1'@'abc.example.com' is actually a completely different user to
'user1'@'localhost' despite sharing a username which does significantly increase your flexibility in setting permissions (or makes things very confusing, depending on your perspective). It's a point in favour of running your database server on a different VM if you're able - not a must-have of course though.
The common, easy to use (and insecure) method for adding database users tends to be something along the lines of:
GRANT ALL ON *.* TO `user1`@`localhost`;
which simply gives that user
GLOBAL privileges to do anything on any database.
This is bad for some obvious, and other not-so-obvious reasons.
*.* includes the
mysql database itself - that lets you do anything you like with other users, settings etc, and should be avoided. The
ALL part of the statement grants all privileges available at the
GLOBAL scope - this means they can do things like
SHOW PROCESSLIST and
KILL <query-id>. It might seem fine ("why would they do that anyway?") but again, as Caleb notes - if the application is compromised, the database user it connects with is the user the attacker "uses" against you.
A simple improvement to the above is to assign privileges using a statement like this, for each database:
GRANT ALL ON `user1_database`.* TO `user1`@`localhost`;
This grants all
DATABASE level privileges to
user1_database - this is important as it excludes all the powerful
GLOBAL privileges listed above, so now the user can, at worst, drop its own database.
A further improvement to this method is to create at least two users per application. One with a
GRANT ALL on the database (for administration), and another to be used by the web application itself that has only the privileges it needs, and only on the objects it needs access to. This is obviously quite a bit of extra work when you make changes, and extra testing, but done well it can significantly reduce the damage possible if the system is compromised.
I often have a standard user, and another with a
_www suffix for a web application.
You can simplify some of this using MySQL's wildcard privileges, and have users that are able to create their own databases (matching a certain pattern) and
-- Create a user that has ALL privileges on databases beginning with 'user1_'
-- This is our application admin user
-- it can create any database that matches the pattern
GRANT ALL ON `user1\_%`.* TO `user1`@`localhost`;
-- Create a "safe" version of this user with just the ability to modify data
-- This is a generic approach and could be applied at object level per-database
GRANT SELECT, INSERT, UPDATE, DELETE ON `user1\_%`.* TO `user1_www`@`localhost`;
The way the admin user is created in the example is a common method in a shared environment - it allows users to create databases, without assigning any
GLOBAL privileges so that they can't see other users' databases.