从零开始：MySQL 用户创建与权限配置

1. 查看用户信息

mysql> use mysql;
Database changed
mysql>  select host,user,authentication_string from user;
+-----------+---------------+-------------------------------------------+
| host      | user          | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| localhost | root          | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | shuaijie      | *5D24C4D94238E65A6407DFAB95AA4EA97CA2B199 |
+-----------+---------------+-------------------------------------------+
4 rows in set (0.00 sec)

2. 创建用户

语法：

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

• username 是您要创建的用户名。
• host 是用户可以连接的主机名，可以使用 % 作为通配符，或者使用 localhost 来指定本地连接。
• password 是用户的密码。

案例：

mysql> create user 'shuaijie'@'localhost' identified by '12345678';
Query OK, 0 rows affected (0.00 sec)

mysql>  select user,host,authentication_string from user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *84AAC12F54AB666ECFC2A83C676908C8BBC381B1 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| shuaijie      | localhost | *84AAC12F54AB666ECFC2A83C676908C8BBC381B1 |
+---------------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)

3. 删除用户

语法：

drop user '用户名'@'主机名'

案例：

mysql>  select host,user,authentication_string from user;
+-----------+---------------+-------------------------------------------+
| host      | user          | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| localhost | root          | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | shuaijie      | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+---------------+-------------------------------------------+
4 rows in set (0.00 sec)

mysql> drop user 'shuaijie'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql>  select host,user,authentication_string from user;
+-----------+---------------+-------------------------------------------+
| host      | user          | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| localhost | root          | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+---------------+-------------------------------------------+
3 rows in set (0.00 sec)

4. 修改用户密码

语法：

ALTER USER '用户名'@'localhost' IDENTIFIED BY 'newpassword';

案例：

mysql>  select host,user,authentication_string from user;
+-----------+---------------+-------------------------------------------+
| host      | user          | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| localhost | root          | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | shuaijie      | *5D24C4D94238E65A6407DFAB95AA4EA97CA2B199 |
+-----------+---------------+-------------------------------------------+
4 rows in set (0.00 sec)
mysql> ALTER USER 'shuaijie'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql>  select host,user,authentication_string from user;
+-----------+---------------+-------------------------------------------+
| host      | user          | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| localhost | root          | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | shuaijie      | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+---------------+-------------------------------------------+
4 rows in set (0.00 sec)

5. 授权

语法：

GRANT privileges ON database.table TO 'username'@'host';

• privileges 是您要授予的权限，可以是 ALL PRIVILEGES 或者具体的权限如 SELECT, INSERT, UPDATE 等。
• database 是数据库名。
• table 是数据库中的表名，如果是全局权限可以省略。

案例：

授予用户shuaijie在本地主机上对mysql.user表的所有权限

mysql> GRANT ALL PRIVILEGES ON mysql.user TO 'shuaijie'@'localhost';
Query OK, 0 rows affected (0.00 sec)

授予特定的权限，比如只读权限

mysql> GRANT SELECT ON mysql.user TO 'shuaijie'@'localhost';
Query OK, 0 rows affected (0.00 sec)

在执行GRANT语句之后，您需要执行以下命令使权限更改立即生效：

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

6. 撤销权限

语法：

REVOKE privileges ON database.table FROM 'username'@'host';

案例：

mysql> REVOKE all privileges ON mysql.user FROM 'shuaijie'@'localhost';
Query OK, 0 rows affected (0.00 sec)

7. 查看权限

语法：

SHOW GRANTS FOR 'username'@'host';

案例：

mysql> SHOW GRANTS FOR 'shuaijie'@'localhost';
+----------------------------------------------+
| Grants for shuaijie@localhost                |
+----------------------------------------------+
| GRANT USAGE ON *.* TO 'shuaijie'@'localhost' |
+----------------------------------------------+
1 row in set (0.00 sec)

8. 更改用户权限(更改主机)

语法：

RENAME USER 'olduser'@'oldhost' TO 'newuser'@'newhost';

案例：

mysql> RENAME USER 'shuaijie'@'localhost' TO 'xxx'@'localhost';
Query OK, 0 rows affected (0.00 sec)

9. 查看所有用户

语法：

SELECT User, Host FROM mysql.user;

案例：

mysql> SELECT User, Host FROM mysql.user;
+---------------+-----------+
| User          | Host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
| xxx           | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)

10. 刷新权限

语法：

FLUSH PRIVILEGES;

11. 设置密码有效期

语法：

ALTER USER 'username'@'host' PASSWORD EXPIRE;

12. 设置密码策略

语法：

SET GLOBAL validate_password_policy = MEDIUM;