MySQL的账户管理

目录

1 密码策略

1.1 查看数据库当前密码策略：

1.2 查看密码设置策略

1.3 密码强度检查等级解释（validate_password.policy）

2 新建登录账户

3 账户授权

3.1 赋权原则

3.2 常见的用户权限

3.3 查看权限

3.4 赋权语法

4 实例

4.1 示例1：创建test1账户，赋权

4.2 示例2：创建test2账户，设置密码，赋权select和create权限，仅通过10.100.0.0/16网段登录

4.3 回收权限（revoke）

4.4 示例4：回收所有权限

4.5 删除账户

4.6 示例5：删除所有账户

---

1 密码策略

从 MySQL 8.0 开始，默认身份验证插件从 mysql_native_password 更改 caching_sha2_password

MySQL 5.7 的默认密码插件一直以来都是 mysql_native_password 。拥有密码验证快的优点，无需在网络 中发送实际密码，并且不需要加密的连接。然而，mysql_native_password 依赖于 SHA1 算法，但 NIST （美国国家标准与技术研究院）已建议停止使用 SHA1 算法，因为 SHA1 和其他哈希算法（例如 MD5 ）已被证明非常容易破解

1.1 查看数据库当前密码策略：

mysql> SHOW VARIABLES LIKE '%password%';
+-------------------------------------------------+-----------------+
| Variable_name                                   | Value           |
+-------------------------------------------------+-----------------+
| caching_sha2_password_auto_generate_rsa_keys    | ON              |
| caching_sha2_password_digest_rounds             | 5000            |
| caching_sha2_password_private_key_path          | private_key.pem |
| caching_sha2_password_public_key_path           | public_key.pem  |
| default_password_lifetime                       | 0               |
| disconnect_on_expired_password                  | ON              |
| generated_random_password_length                | 20              |
| password_history                                | 0               |
| password_require_current                        | OFF             |
| password_reuse_interval                         | 0               |
| report_password                                 |                 |
| sha256_password_auto_generate_rsa_keys          | ON              |
| sha256_password_private_key_path                | private_key.pem |
| sha256_password_proxy_users                     | OFF             |
| sha256_password_public_key_path                 | public_key.pem  |
| validate_password.changed_characters_percentage | 0               |
| validate_password.check_user_name               | ON              |
| validate_password.dictionary_file               |                 |
| validate_password.length                        | 8               |
| validate_password.mixed_case_count              | 1               |
| validate_password.number_count                  | 1               |
| validate_password.policy                        | MEDIUM          |
| validate_password.special_char_count            | 1               |
+-------------------------------------------------+-----------------+
23 rows in set (0.00 sec)

1.2 查看密码设置策略

mysql> SHOW VARIABLES LIKE 'validate_password%';
+-------------------------------------------------+--------+
| Variable_name                                   | Value  |
+-------------------------------------------------+--------+
| validate_password.changed_characters_percentage | 0      |
| validate_password.check_user_name               | ON     |
| validate_password.dictionary_file               |        |
| validate_password.length                        | 8      |
| validate_password.mixed_case_count              | 1      |
| validate_password.number_count                  | 1      |
| validate_password.policy                        | MEDIUM |
| validate_password.special_char_count            | 1      |
+-------------------------------------------------+--------+
8 rows in set (0.00 sec)

1.3 密码强度检查等级解释（validate_password.policy）

等级	检查对象
0 or LOW	检查长度
1 or MEDIUM	检查长度、数字、大小写、特殊字符
2 or STRONG	检查长度、数字、大小写、特殊字符、字典文件

登录账户管理

mysql 的账户权限管理原则：先创建账户，在赋予权限

用户信息存放在 mysql 数据库下的 user 表

mysql> select user,host,authentication_string from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+
| user             | host      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost | $A$005$f~C8L%LmIa&J    \ZE5Q3xytrpyLQAWfRGHeZ45ug8IwyhhuHIPh3MoUQgmH0 |
+------------------+-----------+------------------------------------------------------------------------+

2 新建登录账户

格式

create user 'username'@'localhost' identified by 'password';

含义

• username： 创建的用户名
• localhost： 指定该用户在哪个主机上可以登陆，如果是本地用户可用 localhost ，如果想让该用户可以从任意远程主机登陆，可以使用通配符 %
• password： 该用户的登陆密码

示例：

# 注意：密码要符合密码设置规则
mysql> CREATE USER 'test1'@'localhost' IDENTIFIED BY 'Openlab123!';
Query OK, 0 rows affected (0.00 sec)# 查看信息
mysql> SELECT user,host,plugin FROM mysql.user;  
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | caching_sha2_password |
| test1            | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)mysql> exit
Bye[root@Alinolis mysql_rpm]# mysql -utest1 -pOpenlab123!
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 9.0.0 MySQL Community Server - GPLCopyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> SHOW DATABASES;  # 只能看到有限的库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.00 sec)

3 账户授权

3.1 赋权原则

作用：通过账户权限限制普通账户的工作范围及内容，赋权原则如下：

• root账号绝对禁止允许任何IP都可以访问，即登录方式不能用%
• 应用账号和管理账号要分离
• 赋权最小化，即一般只给select权限，最好不要给update、insert等编辑权限
• 密码设置合理
• 定期清理不使用的账户，将其收回权限或删除

3.2 常见的用户权限

权限	权限说明	权限级别
CREATE	创建数据库、表或索引的权限	数据库、表或索引
DROP	删除数据库或表的权限	数据库或表
GRANT OPTION	赋予权限选项	数据库或表
REFERENCES	引用权限	数据库或表
ALTER	更改表的权限	数据表
DELETE	删除表数据的权限	数据表
INDEX	操作索引的权限	数据表
INSERT	添加表数据的权限	数据表
SELECT	查询表数据的权限	数据表
UPDATE	更新表数据的权限	数据表
CREATE VIEW	创建视图的权限	视图
SHOW VIEW	查看视图的权限	视图
ALTER ROUTINE	改存储过程的权限	存储过程
CREATE ROUTINE	创建存储过程的权限	存储过程
EXECUTE	执行存储过程权限	存储过程
FILE	服务器主机文件的访问权限	文件管理
CREATE TEMPORARY TABLES	创建临时表的权限	服务器管理
LOCK TABLES	锁表的权限	服务器管理
CREATE USER	创建用户的权限	服务器管理
RELOAD	执行 flush privileges, refresh, reload 等刷新命令的权限	服务器管理
PROCESS	查看进程的权限	服务器管理
REPLICATION CLIENT	查看从服务器状态的权限	服务器管理
REPLICATION SLAVE	主从复制的权限	服务器管理
SHOW DATABASES	查看数据库的权限	服务器管理
SHUTDOWN	关闭数据库的权限	服务器管理
SUPER	超级权限	服务器管理
ALL [PRIVILEGES]	所有权限	无
USAGE	没有任何权限	无

3.3 查看权限

mysql> exit
Bye
[root@Alinolis mysql_rpm]# mysql -uroot -pOpenlab123!
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 9.0.0 MySQL Community Server - GPLCopyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.# 查看其它账户权限
mysql> SHOW GRANTS FOR 'test1'@'localhost';
+-------------------------------------------+
| Grants for test1@localhost                |
+-------------------------------------------+
| GRANT USAGE ON *.* TO `test1`@`localhost` |
+-------------------------------------------+
1 row in set (0.00 sec)# 上例显示test账户只有usage默认权限即连接登录的权限

 3.4 赋权语法

grant 权限列表 on 数据库名.表名 to '用户名'@'来源地址' [identified by '密码'];

分析

• 权限列表： 用户的操作权限，如 SELECT ， INSERT ， UPDATE ，CREATE 等，如果要授予所有的权限则使用 ALL
• 数据库名： 数据库名，如果 * 代表所有数据库
• 表名：如果 * 代表所有数据表
• localhost： 指定该用户在哪个主机上可以登陆，如果是本地用户可用 localhost ，如果想让该用户
• 可以从任意远程主机登陆，可以使用通配符 %

4 实例

4.1 示例1：创建test1账户，赋权

# 赋予mysql库的查询权限
mysql> GRANT SELECT ON *.* TO 'test1'@'localhost';
Query OK, 0 rows affected (0.00 sec)mysql> SHOW GRANTS FOR 'test1'@'localhost';
+--------------------------------------------+
| Grants for test1@localhost                 |
+--------------------------------------------+
| GRANT SELECT ON *.* TO `test1`@`localhost` |
+--------------------------------------------+
1 row in set (0.00 sec)[root@Alinolis mysql_rpm]# mysql -utest1 -pOpenlab123!mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)mysql> EXIT
Bye

4.2 示例2：创建test2账户，设置密码，赋权select和create权限，仅通过10.100.0.0/16网段登录

mysql> CREATE USER 'test2'@'10.100.%.%' IDENTIFIED BY 'Openlab123!';
Query OK, 0 rows affected (0.01 sec)mysql> GRANT SELECT, CREATE ON *.* TO 'test2'@'10.100.%.%';# 远程登录数据库
mysql -utest2 -pOpenlab123! -h192.168.239.179mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 9.0.0 MySQL Community Server - GPLCopyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> select user();
+---------------------+
| user()              |
+---------------------+
| test2@192.168.239.1 |
+---------------------+
1 row in set (0.00 sec)mysql> SELECT @@HOSTNAME;
+------------+
| @@HOSTNAME |
+------------+
| Alinolis   |
+------------+
1 row in set (0.00 sec)

示例 3 ：创建 test3 账户，设置密码，赋与所有权限，仅通过 % 登录 , 登录后创建 test4 账户并赋权

mysql> SELECT USER();
+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)mysql> CREATE USER 'test4'@'%' IDENTIFIED BY 'Openlab123!';
Query OK, 0 rows affected (0.00 sec)mysql> GRANT ALL ON *.* TO 'test4'@'%';
Query OK, 0 rows affected (0.00 sec)# 查看是否赋权成功
mysql> SHOW GRANTS FOR 'test4'@'%';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for test4@%                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `test4`@`%`                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| GRANT ALLOW_NONEXISTENT_DEFINER,APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_PRIVILEGES,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,OPTIMIZE_LOCAL_TABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_ANY_DEFINER,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,TRANSACTION_GTID_TAG,XA_RECOVER_ADMIN ON *.* TO `test4`@`%` |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

注意：在 LINUX 下的MySQL 的权限是补全的，若赋权不成功则先给root账户增加system_user权限

4.3 回收权限（revoke）

格式：

revoke 权限列表/all on 库名.表名 from '用户名'@'来源地址';# revoke跟grant 的语法差不多，只需要把关键字 “to” 换成 “from” 即可

4.4 示例4：回收所有权限

mysql> SHOW GRANTS FOR 'test4'@'%';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for test4@%                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `test4`@`%`                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| GRANT ALLOW_NONEXISTENT_DEFINER,APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_PRIVILEGES,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,OPTIMIZE_LOCAL_TABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_ANY_DEFINER,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,TRANSACTION_GTID_TAG,XA_RECOVER_ADMIN ON *.* TO `test4`@`%` |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)mysql> REVOKE ALL ON *.* FROM 'test4'@'%';
Query OK, 0 rows affected (0.01 sec)mysql> SHOW GRANTS FOR 'test4'@'%';
+-----------------------------------+
| Grants for test4@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO `test4`@`%` |
+-----------------------------------+
1 row in set (0.00 sec)# 其余所有账户也是使用一样的方法

4.5 删除账户

格式：

DROP USER  '用户名'@'访问主机名';

4.6 示例5：删除所有账户

mysql> SELECT USER,HOST FROM mysql.user;
+------------------+---------------+
| USER             | HOST          |
+------------------+---------------+
| test4            | %             |
| test2            | 10.100.%.%    |
| test2            | 192.168.239.% |
| mysql.infoschema | localhost     |
| mysql.session    | localhost     |
| mysql.sys        | localhost     |
| root             | localhost     |
| test1            | localhost     |
+------------------+---------------+
8 rows in set (0.00 sec)mysql> DROP USER 'test1'@'localhost';
Query OK, 0 rows affected (0.01 sec)mysql> DROP USER 'test2'@'10.100.%.%';
Query OK, 0 rows affected (0.00 sec)mysql> 
mysql> DROP USER 'test2'@'192.168.239.%';
Query OK, 0 rows affected (0.01 sec)mysql> DROP USER 'test4'@'%';
Query OK, 0 rows affected (0.00 sec)mysql> SELECT USER,HOST FROM mysql.user;
+------------------+-----------+
| USER             | HOST      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)