MySQL user management cheatsheet.

README.md

MySQL user management cheatsheet

• List all users
• List connected users
• Create user
  • With mysql_native_password identity
  • With auth_socket identity
• Grant user specific permissions to database
• Grant root-like permissions
• List grants for user
• Reference

List all users

SELECT Host,User,plugin,authentication_string
FROM mysql.user

List connected users

SHOW PROCESSLIST

Create user

Note: for HOSTNAME below with local machine users, use localhost value.

With mysql_native_password identity

Traditional username/password user account type:

CREATE USER 'USERNAME'@'HOSTNAME'
IDENTIFIED WITH mysql_native_password BY 'PASSWORD'

With auth_socket identity

Added to MySQL 5.5, Socket Peer-Credential Pluggable Authentication or auth_socket allows authentication for local logins made via a MySQL server's unix socket file under Linux.

After creating a new user:

CREATE USER 'USERNAME'@'localhost'
IDENTIFIED WITH auth_socket

• We can now enter a shell session as USERNAME and login to MySQL via a socket connection.
• MySQL will determine system user connected to the socket and find a matching user with auth_socket identity.
• If a user is found, login is successful otherwise fail.
• The auth_socket plugin refuses login for any alternative connection protocol (such as TCP/IP).

Grant user specific permissions to database

Note: use of backticks around DATABASE.

GRANT SELECT,INSERT,UPDATE,DELETE ON `DATABASE`.* TO 'USERNAME'@'HOSTNAME'

Grant root-like permissions

GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'HOSTNAME' WITH GRANT OPTION

List grants for user

SHOW GRANTS FOR 'USERNAME'@'HOSTNAME'

Reference

• https://dev.mysql.com/doc/refman/5.7/en/create-user.html
• https://dev.mysql.com/doc/refman/5.7/en/grant.html
• https://dev.mysql.com/doc/refman/5.7/en/socket-pluggable-authentication.html