MySQL user administration basics

Last week I spent a frustrating few hours setting up a script to copy a MySQL-backed web application from CVS into a local test environment. In the process I somehow broke the database access settings in a way I didn't understand, and - despite the fact that it was probably quite a trivial error - all my attempts at manually resetting the MySQL user and password failed. Finally I resorted to using phpMyAdmin to sort out the immediate problem, but afterwards I felt I needed to learn more about the basics of MySQL user administration.

First, a bit of background: it's typical for applications such as mine to access the database via a dedicated MySQL user explicitly created for the purpose, and with access rights restricted to only the data that it actually needs. This acts as a security measure to limit the potential damage to the database as a whole if the application is compromised. (Any application that uses MySQL for its database backend should also implement its own independent application-specific user management system, but this is a separate issue not covered here.)

The first point is that MySQL's users are entirely distinct and separate from the users defined on the host system - so they also need to be managed separately. The essential SQL commands for doing this are:

• GRANT: creates a new user and sets its privileges (if the username/hostname pair specified doesn't already exist); modifies the privileges for an existing user (if it does exist):
  
  GRANT privileges ON data TO user IDENTIFIED BY password;
  
  For example:
  
  GRANT SELECT,INSERT,UPDATE,DELETE ON myappdb.* TO 'myappdbuser'@'localhost' IDENTIFIED BY 'quitesecret';
  
  gives the user myappdbuser@localhost a limited set of privileges for all tables in the myappdb database. (To grant all permissions, privileges can be specified as ALL; data can be specified as *.* to grant the rights to all tables in all databases. Also, note that more recent versions of MySQL seem to support an explicit CREATE USER command in addition to GRANT.)


• REVOKE: the opposite of GRANT; removes a user's privileges:
  
  REVOKE privileges ON data FROM user;
  
  For example:
  
  REVOKE INSERT,UPDATE,DELETE ON myappdb.* FROM 'myappdbuser'@'localhost';


• DROP USER: removes a user, for example:
  
  DROP USER 'myappdbuser'@'localhost';


• SET PASSWORD sets or changes the password for a user, for example:
  
  SET PASSWORD FOR 'myappdbuser'@'localhost' = PASSWORD('newsecret');


• To get a list of users as username/host pairs: execute a SELECT query on the user table of MySQL's mysql administrative database (where the user data is stored):
  
  SELECT User,Host FROM mysql.users;

These commmands are actually very straightforward, however based on recent experience two particular points are worth emphasizing:

1. Each user is uniquely identified by a username and host pair (not just by the username) - so to MySQL fred@localhost, fred@example.com and fred@% are all different and distinct users.
2. Each user has its own set of associated privileges, specifying which database operations the user is allowed to perform and on which data - so it's possible for each of fred@localhost, fred@example.com and fred@% to have different privileges.

These are significant when considering connection requests where there is an ambiguity in the user specification (for example, omitting the hostname) which results in more than one potential match in the user table. In this case MySQL uses the most specific match, which might not be the one that was intended. The connection might then be denied (if the intended and actual users have different passwords), or have subsequent problems executing SQL queries (if the two users have different privileges). So I'd also recommend always specifying users explicitly whenever possible with the full username@hostname pair.

Hopefully this overview has given some insight into the basics of MySQL user administration, although obviously there's a lot more than I've outlined here (for example I've skipped over many details are for the GRANT command - see the section Account Management Statements in the MySQL manual for more comprehensive information) - and I'd still recommend using phpMyAdmin or similar for database user management (especially if you don't do it that often). However I hope it's still useful - and I'd welcome any comments or corrections.