Our VPS is set up in such a way that all databases are accessible from any domain via localhost and the username/password/dbname.
Is this the usual set up, or should there be some way of 'jailing' each user so they cannot access other databases on localhost?
Thanks, Mark
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
-- 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.
You should use finer grained privileges using the MySQL privileges table so that each application (or even component of an application) only has access to the databases or tables that are required for it's function. This will reduce potential problems with privacy violations, reduce damages in the event of a security breach, and even help reveal bugs in your software during testing.
As @simon and @caleb mention, it is common for global permissions to be given but that doesn't make it good practice, I use this 'macro' (which I cobbled together based on another post on serverfault or SO, can't remember which one tho') for privileges:
SET @usr = '\'username\'@\'localhost\'';
SET @pwd = '\'longpassword\'';
SET @db = 'database';
set @query = CONCAT('revoke all privileges on *.* from ', @usr);PREPARE stmt FROM @query;EXECUTE stmt;DEALLOCATE PREPARE stmt;
set @query = CONCAT('drop user ', @usr);PREPARE stmt FROM @query;EXECUTE stmt;DEALLOCATE PREPARE stmt;
set @query = CONCAT('create user ', @usr, ' identified by ', @pwd);PREPARE stmt FROM @query;EXECUTE stmt;DEALLOCATE PREPARE stmt;
set @obj = CONCAT(@db, '.tablename');SET @query = CONCAT('GRANT select ON ', @obj, ' TO ', @usr, ' identified by ', @pwd);PREPARE stmt FROM @query;EXECUTE stmt;DEALLOCATE PREPARE stmt;
removing the 'revoke', 'drop' and 'create' lines and changing the table name and privileges as required. It allows, as @caleb suggests, the creation of fine grained privileges with the minimum of hassle.
