MySQL create users cheat sheet

Show all MySQL user accounts

SELECT user from mysql.user;

Create a super user jack and grant jack all privileges if jack is connecting from localhost without a password.

CREATE USER 'jack'@'localhost' IDENTIFIED BY '';
GRANT ALL PRIVILEGES ON *.* TO 'jack'@'localhost'
WITH GRANT OPTION;

Create a super user joe and grant joe all privileges identified by ‘password’, he can be connected from any host.

CREATE USER 'joe'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'joe'@'%'
WITH GRANT OPTION;

Create a read only access user raymond identified by ‘password’, he can be connected from any host. In case if there are anonymous user ”@’localhost’ or ”@’127.0.0.1′ in the mysql.user table, better to drop these anonymous users, otherwise the uer ‘raymond’@’%’ might get access denied error.

CREATE USER 'raymond'@'%' IDENTIFIED BY 'password';
GRANT SELECT ON *.* TO 'raymond'@'%';
FLUSH privileges;

Grant update access to the user raymond to the database books.

GRANT UPDATE ON `books`.* TO 'raymond'@'%';
FLUSH privileges;

Remove all privileges for the user raymond connecting from the host 123.2.3.4

REVOKE all privileges on *.* from 'raymond'@'123.2.3.4';
FLUSH privileges;

Show privileges of the user raymond connecting from any hosts.

SHOW GRANTS for 'raymond'@'%';

Remove the user raymond connecting from any hosts.

DROP USER 'raymond'@'%';