MySQL create users cheat sheet

Show all MySQL user accounts
[code language=”sql”]
SELECT user from mysql.user;
[/code]

Create a super user jack and grant jack all privileges if jack is connecting from localhost without a password.
[code language=”sql”]
CREATE USER ‘jack’@’localhost’ IDENTIFIED BY ”;
GRANT ALL PRIVILEGES ON *.* TO ‘jack’@’localhost’
WITH GRANT OPTION;
[/code]

Create a super user joe and grant joe all privileges identified by ‘password’, he can be connected from any host.
[code language=”sql”]
CREATE USER ‘joe’@’localhost’ IDENTIFIED BY ‘password’;
GRANT ALL PRIVILEGES ON *.* TO ‘joe’@’%’
WITH GRANT OPTION;
[/code]

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.
[code language=”sql”]
CREATE USER ‘raymond’@’%’ IDENTIFIED BY ‘password’;
GRANT SELECT ON *.* TO ‘raymond’@’%’;
FLUSH privileges;
[/code]

Grant update access to the user raymond to the database books.
[code language=”sql”]
GRANT UPDATE ON `books`.* TO ‘raymond’@’%’;
FLUSH privileges;
[/code]

Remove all privileges for the user raymond connecting from the host 123.2.3.4
[code language=”sql”]
REVOKE all privileges on *.* from ‘raymond’@’123.2.3.4’;
FLUSH privileges;
[/code]

Show privileges of the user raymond connecting from any hosts.
[code language=”sql”]
SHOW GRANTS for ‘raymond’@’%’;
[/code]

Remove the user raymond connecting from any hosts.
[code language=”sql”]
DROP USER ‘raymond’@’%’;
[/code]