- List all users
- List connected users
- Create user
- Grant user specific permissions to database
- Grant root-like permissions
- List grants for user
- Reference
SELECT Host,User,plugin,authentication_string
FROM mysql.user
SHOW PROCESSLIST
Note: for HOSTNAME
below with local machine users, use localhost
value.
Traditional username/password user account type:
CREATE USER 'USERNAME'@'HOSTNAME'
IDENTIFIED WITH mysql_native_password BY 'PASSWORD'
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).
Note: use of backticks around DATABASE
.
GRANT SELECT,INSERT,UPDATE,DELETE ON `DATABASE`.* TO 'USERNAME'@'HOSTNAME'
GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'HOSTNAME' WITH GRANT OPTION
SHOW GRANTS FOR 'USERNAME'@'HOSTNAME'