MySQL 8.0用户及安全管理
1.1 用户管理
1.1.1 用户组成
'用户名'@'白名单'
例如：
'user1'@'%'
'user1'@'10.0.0.10'
'user1'@'10.0.0.%'
'user1'@'10.0.0.5%'
'user1'@'luffycity.com'
'user1'@'localhost' # 只能数据库本地socket连接
1.1.2 创建用户
命令：
create user '用户名'@'白名单' identified by '密码';
例如：
create user 'user1'@'10.0.0.%' identified by '123';
mysql> create user 'user1'@'10.0.0.%' identified by '123';
mysql> select user,host,authentication_string,plugin from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
| user | host | authentication_string | plugin |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
| user1 | 10.0.0.% | $A$005$ES|uTOM`fQZi0suMWSoXtZpH5I7s58p2.GNIfe.1hIMls1.xzqCHtl1NSSIA | caching_sha2_password |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| mysql.session | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| mysql.sys | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| root | localhost | | caching_sha2_password |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
5 rows in set (0.00 sec)
提示：MySQL8.0之后创建用户时的密码加密方式默认为caching_sha2_password，可能会和老版本应用程序用兼容性问题，
可以在创建用户时，指定密码加密方式，例如：
mysql> create user 'user2'@'10.0.0.%' identified with mysql_native_password by '123';
mysql> select user,host,authentication_string,plugin from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
| user | host | authentication_string | plugin |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
| user1 | 10.0.0.% | $A$005$ES|uTOM`fQZi0suMWSoXtZpH5I7s58p2.GNIfe.1hIMls1.xzqCHtl1NSSIA | caching_sha2_password |
| user2 | 10.0.0.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | mysql_native_password |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| mysql.session | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| mysql.sys | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| root | localhost | | caching_sha2_password |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
6 rows in set (0.00 sec)
1.1.3 删除用户
命令：
drop user '用户名'@'白名单';
例如：
mysql> drop user 'user1'@'10.0.0.%';
1.1.4 修改用户
修改用户使用alter命令，可以修改用户的白名单，密码及加密方式等
命令：
alter user '用户名'@'白名单' identified with 加密方式 by '密码';
例如：
修改前：
mysql> select user,host,authentication_string,plugin from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
| user | host | authentication_string | plugin |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
| user2 | 10.0.0.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | mysql_native_password |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| mysql.session | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| mysql.sys | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| root | localhost | | caching_sha2_password |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
5 rows in set (0.00 sec)
修改：
mysql> alter user 'user2'@'10.0.0.%' identified with caching_sha2_password by 'abc';
修改后：
mysql> select user,host,authentication_string,plugin from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
| user | host | authentication_string | plugin |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
| user2 | 10.0.0.% | $A$005$9K=^DeSH"k,^+ OwlRgp.fsI3j5RHdIpz.jgO53xhsQYsYHTQX/IDsWc6 | caching_sha2_password |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| mysql.session | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| mysql.sys | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| root | localhost | | caching_sha2_password |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
5 rows in set (0.00 sec)
1.1.5 锁定用户
命令：
ALTER USER '用户名'@'白名单' ACCOUNT LOCK;
例如：
mysql> ALTER USER 'user3'@'10.0.0.%' ACCOUNT LOCK;
mysql> select user,host,account_locked from mysql.user where user='user3';
+-------+----------+----------------+
| user | host | account_locked |
+-------+----------+----------------+
| user3 | 10.0.0.% | Y | # Y表示已锁定
+-------+----------+----------------+
1.1.6 解锁用户
命令：
ALTER USER '用户名'@'白名单' ACCOUNT UNLOCK;
例如：
mysql> ALTER USER 'user3'@'10.0.0.%' ACCOUNT UNLOCK;
mysql> select user,host,account_locked from mysql.user where user='user3';
+-------+----------+----------------+
| user | host | account_locked |
+-------+----------+----------------+
| user3 | 10.0.0.% | N | # N表示未锁定
+-------+----------+----------------+
1.1.7 注意
密码加密插件导致的一些坑
1.主从复制，mgr， 不支持新的密码插件加密的用户
2.老的驱动无法连接8.0的数据库，如：MHA，mycat
3.客户端工具不支持sha2的加密方式，如：navicat、sqlyog
解决方法：
方法1：创建用户的时候使用mysql_native_password加密方式
方法2：设置默认的密码加密插件为mysql_native_password加密方式，需要在配置文件中配置。
vi /etc/my.cnf
[mysqld]
default_authentication_plugin=mysql_native_password
1.2 权限管理
1.2.1 权限级别
*.* :全库级别 # 管理员
test.* :单库级别 # 业务使用
test.t1 :单表级别
select(id,name) : 列级别
1.2.2 权限列表
可通过命令查看所有的权限信息：show privileges;
1.2.3 生产中用户类型规范
管理员 : ALL
开发 : Create ,Create routine,Create temporary tables,Create view,Delete ,Event,Execute,Insert ,References,Select,Trigger,Update
监控 : select , replication slave , client supper
备份 : ALL
主从 : replication slave,Replication client
业务 : insert , update , delete ,select
1.2.4 授权
1.2.4.1 管理员用户
创建用户
mysql> create user 'test'@'10.0.0.%'' identified with mysql_native_password by '123';
mysql> create user 'test'@'localhost' identified with mysql_native_password by '123';
授权
mysql> grant all on *.* to 'test'@'10.0.0.%';
mysql> grant all on *.* to 'test'@'localhost';
1.2.4.2 开发用户
创建用户
create user 'dev_user'@'10.0.0.%' identified with mysql_native_password by '123';
授权
grant Create ,Create routine,Create temporary tables,Create view,Delete ,Event,Execute,Insert ,References,Select ,Trigger,Update on dev_db.* to 'dev_user'@'10.0.0.%';
1.2.4.3 主从复制用户
创建用户
create user 'repl'@'10.0.0.%' identified with mysql_native_password by '123';
授权
grant replication slave,Replication client on *.* to 'repl'@'10.0.0.%';
1.2.4.4 备份用户
创建用户
create user 'backup'@'10.0.0.%' identified with mysql_native_password by '123';
授权
grant all on *.* to 'backup'@'10.0.0.%';
1.2.4.5 监控用户
创建用户
create user 'monitor'@'10.0.0.%' identified with mysql_native_password by '123';
授权
grant select , replication slave, Replication client on *.* to 'monitor'@'10.0.0.%';
1.2.4.6 业务用户
创建用户
create user 'product'@'10.0.0.%' identified with mysql_native_password by '123';
授权
grant select,update,insert,delete on product_app.* to 'product'@'10.0.0.%';
1.2.5 查看用户权限
1.查看单个用户的权限
命令：
show grants for '用户'@'白名单';
例如：
mysql> show grants for 'product'@'10.0.0.%';
+---------------------------------------------------------------------------------+
| Grants for product@10.0.0.% |
+---------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `product`@`10.0.0.%` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `product_app`.* TO `product`@`10.0.0.%` |
+---------------------------------------------------------------------------------+
注意：也可用查看mysql.user,mysql.db
1.2.6 权限回收
命令:
revoke 权限列表 on *.* from '用户名'@'白名单';
例如：
revoke insert on product_app.* from 'product'@'10.0.0.%';
1.3 角色管理
1.3.1 创建角色及授权
1.创建角色
create role 角色名;
2.给角色授权
grant 权限列表 on *.* to 角色名;
3.给用户分配角色
grant 角色名 to '用户名'@'白名单';
例如：
mysql> create user 'role_test'@'10.0.0.%' identified with mysql_native_password by '123';
Query OK, 0 rows affected (0.01 sec)
mysql> create role dev;
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to dev;
mysql> grant dev to 'role_test'@'10.0.0.%';
Query OK, 0 rows affected (0.02 sec)
验证：
[root@localhost ~]# mysql -urole_test -p123 -h10.0.0.10
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 32
Server version: 8.0.24 MySQL Community Server - GPL
Copyright (c) 2000, 2021, 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 |
+--------------------+
1 row in set (0.02 sec)
mysql> create database test1;
**ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement**
注意：
role_test用户虽然有所有权限，但是activate_all_roles_on_login是关闭的，所以无法执行相关命令。
解决方法：
激活权限：
set global activate_all_roles_on_login=on;
然后再次验证：
[root@localhost ~]# mysql -urole_test -p123 -h10.0.0.10
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 33
Server version: 8.0.24 MySQL Community Server - GPL
Copyright (c) 2000, 2021, 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> create database test1;
Query OK, 1 row affected (0.02 sec)
1.3.2 查看用户及角色权限
命令：
show grants for 角色名;
例如：
mysql> show grants for dev;
1.3.3 收回用户角3色
命令：
revoke 角色名 from '用户'@'白名单';
例如：
revoke dev from 'role_test'@'10.0.0.%';
1.3.4 删除角色
drop role 角色名;
例如：
mysql> drop role dev;
1.4 小结
1.避免删库跑路，非管理员禁止给drop权限;
2.不要轻易给ALL权限;
3.生产环境密码复度一定要高;
4.删除角色的同时，用户的所属的角色也会回收，即回收用户的权限;
5.角色命令是关闭状态，需要激活："set global activate_all_roles_on_login=on;"
6.查看当前会话中角色处于活动状态:"select current_role();"
