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'@'xyz.example.com'
or '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
on 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
Example
-- 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.