Managing MySQL User Accounts

Below are some useful commands to manage users in MySQL:

List all Users

Run the following query to get a list of the users:

select host, user from mysql.user;

Add User

To create a new account:

CREATE USER 'adm'@'localhost' IDENTIFIED BY 'yourpasswordhere';

Here the ‘adm’ user is created, which can only connect from localhost.

In MySQL a user is always associated with a host in this way. Multiple entries for a single username @ different hosts are treated as separate accounts, which can have different passwords and privileges. Where the host definition part of a user account overlaps, the most specific account is used (eg. @’localhost’ takes precedence over @’%’).

You should create users with the most specific host definition possible:

CREATE USER 'adm'@'localhost' IDENTIFIED BY ‘yourpasswordhere';

CREATE USER 'adm'@'192.168.0.%' IDENTIFIED BY ‘yourpasswordhere';

The % is a wildcard that will match any numeric address in the 192.168.0 class C network.

Where possible, you should avoid creating an account with access from any network address:

CREATE USER 'adm'@'%' IDENTIFIED BY ‘yourpasswordhere';

Here an ‘adm’ user is created that can connect from anywhere, using the ‘%’ wildcard as host.

Drop User

To remove or drop a user account:

DROP USER 'adm'@'192.168.0.1';

From MySQL Server 5.0.2 onwards this will also remove any privileges held by that account, and will not remove any data objects created by that account.

Granting permissions to MySQL accounts

This gives all privileges at the database level (except for GRANT OPTION).

GRANT ALL PRIVILEGES ON mydb.* TO 'adm'@'localhost'
FLUSH PRIVILEGES;

You can also grant privileges more granularly:

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON mydb.* TO 'adm'@'localhost';

Non-administrative MySQL users should never be granted access to the mysql system database, or given system level privileges. Most importantly no user other than root should have access to the users table in the mysql database. In some cases mysql database privileges may be granted to MySQL accounts used for monitoring and replication, but then only as required.

To grant a user with admin rights you will need to use the with WITH GRANT OPTION as per the example below:

GRANT ALL ON *.* TO 'adm'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

To show permissions belonging to a user:

SHOW GRANTS FOR 'adm'@'localhost';

Password Changes

To change a password for another user account:

SET PASSWORD FOR 'adm'@'localhost' = PASSWORD('yourpasswordhere');

To change your own password:

SET PASSWORD = PASSWORD('yourpasswordhere');

Delete User

To delete a user, use the following command:

DROP USER 'adm'@'localhost';

Revoking permissions to MySQL accounts

The syntax for revoking privileges is similar to granting privileges:

REVOKE ALL PRIVILEGES ON *.* FROM 'adm'@'127.0.0.1';