MySQL User Permissions: Creating, Granting, Viewing, and Deleting Accounts

1. MySQL User Permissions

MySQL permissions are divided into three categories:

1. MySQL service management privileges – global privileges that manage the MySQL server itself and can only be granted using the *.* syntax.

2. Database‑level privileges – can be granted for all databases or a specific database using *.* or db.* .

3. Object‑level privileges – can be granted for all databases, a specific database, or a specific table using *.* , db.* , or db.tb .

2. Creating, Granting, Viewing, and Deleting Accounts

(1) Create an account with the CREATE USER statement. Example:

CREATE USER `
`@`
` IDENTIFIED BY '
';

Three typical cases:

Local access:

CREATE USER `user1`@`localhost` IDENTIFIED BY '123456';

Access from a specific subnet (e.g., 192.168.210.*):

CREATE USER `user2`@`192.168.210.*` IDENTIFIED BY '123456';

Access from any host:

CREATE USER `user3`@`%` IDENTIFIED BY '123456';

(2) Delete an account:

DROP USER `user1`@`localhost`;

(3) List all MySQL accounts:

SELECT user, host FROM mysql.user;

+------------------+--------------+
| user             | host         |
+------------------+--------------+
| user1            | localhost    |
| root             | %            |
| user3            | %            |
| user2            | 192.168.210.*|
| mysql.infoschema | localhost    |
| mysql.session   | localhost    |
| mysql.sys        | localhost    |
+------------------+--------------+

(4) View the privileges of a specific user:

SHOW GRANTS FOR `user3`@`%` \G

Please scan and follow: