Managing MySQL user accounts

Adding MySQL accounts
To create a new account:

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

Here the ‘adm’ user is created, which can only connect from localhost. The password is encrypted by MySQL using the PASSWORD() function.

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 ‘';
CREATE USER 'adm'@'192.168.0.%' IDENTIFIED BY ‘';

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

To create an account with access to a specific network address and subnet mask:

CREATE USER 'adm'@'10.46.197.0/255.255.255.128' IDENTIFIED BY ‘';

You should avoid creating an account with access from any network address:

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

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

Removing MySQL accounts
To remove a MySQL Server user account:

DROP USER 'adm'@'192.168.0.%';

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

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

This gives all privileges at the database level except for GRANT. 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 show permissions belonging to a user:

SHOW GRANTS FOR 'adm'@'localhost';

To change a password for another user account with the root user:

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

To change your own password:

SET PASSWORD = PASSWORD('');

To remove an account (note that this does not terminate any existing sessions, and does not alter any object ownership):

DROP USER adm;

Revoking permissions to MySQL accounts

The syntax for revoking privileges is similar to granting privileges:

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