MySQL 8.0 用户及安全管理

1.用户的组成

 1 查看创建用户命令
 2 mysql> help create user;
 3 Name: 'CREATE USER'
 4 Description:
 5 Syntax:
 6 CREATE USER [IF NOT EXISTS]
 7     user [auth_option] [, user [auth_option]] ...
 8     DEFAULT ROLE role [, role ] ...
 9     [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
10     [WITH resource_option [resource_option] ...]
11     [password_option | lock_option] ...
12     [COMMENT 'comment_string' | ATTRIBUTE 'json_object']
13 
14 user:
15     (see )
16 
17 auth_option: {
18     IDENTIFIED BY 'auth_string'
19   | IDENTIFIED BY RANDOM PASSWORD
20   | IDENTIFIED WITH auth_plugin
21   | IDENTIFIED WITH auth_plugin BY 'auth_string'
22   | IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD
23   | IDENTIFIED WITH auth_plugin AS 'auth_string'
24 }
25 
26 tls_option: {
27    SSL
28  | X509
29  | CIPHER 'cipher'
30  | ISSUER 'issuer'
31  | SUBJECT 'subject'
32 }
33 
34 resource_option: {
35     MAX_QUERIES_PER_HOUR count
36   | MAX_UPDATES_PER_HOUR count
37   | MAX_CONNECTIONS_PER_HOUR count
38   | MAX_USER_CONNECTIONS count
39 }
40 
41 password_option: {
42     PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY]
43   | PASSWORD HISTORY {DEFAULT | N}
44   | PASSWORD REUSE INTERVAL {DEFAULT | N DAY}
45   | PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]
46   | FAILED_LOGIN_ATTEMPTS N
47   | PASSWORD_LOCK_TIME {N | UNBOUNDED}
48 }
49 
50 lock_option: {
51     ACCOUNT LOCK
52   | ACCOUNT UNLOCK
53 }
54 
55 The CREATE USER statement creates new MySQL accounts. It enables
56 authentication, role, SSL/TLS, resource-limit, and password-management
57 properties to be established for new accounts. It also controls whether
58 accounts are initially locked or unlocked.
59 
60 To use CREATE USER, you must have the global CREATE USER privilege, or
61 the INSERT privilege for the mysql system schema. When the read_only
62 system variable is enabled, CREATE USER additionally requires the
63 CONNECTION_ADMIN privilege (or the deprecated SUPER privilege).
64 
65 As of MySQL 8.0.22, CREATE USER fails with an error if any account to
66 be created is named as the DEFINER attribute for any stored object.
67 (That is, the statement fails if creating an account would cause the
68 account to adopt a currently orphaned stored object.) To perform the
69 operation anyway, you must have the SET_USER_ID privilege; in this
70 case, the statement succeeds with a warning rather than failing with an
71 error. Without SET_USER_ID, to perform the user-creation operation,
72 drop the orphan objects, create the account and grant its privileges,
73 and then re-create the dropped objects. For additional information,
74 including how to identify which objects name a given account as the
75 DEFINER attribute, see
76 https://dev.mysql.com/doc/refman/8.0/en/stored-objects-security.html#st
77 ored-objects-security-orphan-objects.
78 
79 CREATE USER either succeeds for all named users or rolls back and has
80 no effect if any error occurs. By default, an error occurs if you try
81 to create a user that already exists. If the IF NOT EXISTS clause is
82 given, the statement produces a warning for each named user that
83 already exists, rather than an error.
84 
85 URL: https://dev.mysql.com/doc/refman/8.0/en/create-user.html
86 
87 举例：
88 #格式：
89  用户名@'白名单'
90 #举例：
91 test@'%'
92 test@'10.0.0.1'
93 test@'10.0.0.%'       24掩码 1-254
94 test@'10.0.0.5%'     50-59
95 test@'localhost'       数据库本地socket

2.创建用户

 1 create user test@'10.0.0.%' identified by '123456abcd';  
 2 Query OK, 0 rows affected (0.01 sec)
 3 
 4 create user oldguo@'10.0.0.%' identified by '123456abcd';
 5 mySQL> select user,host,authentication_string,plugin from mySQL.user;
 6 +------------------+-----------+------------------------------------------------------------------------+-----------------------+
 7 | user             | host      | authentication_string                                                  | plugin                |
 8 +------------------+-----------+------------------------------------------------------------------------+-----------------------+
 9 | mySQL.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password
10 sha2 : 8.0新的特性  默认密码插件
11 native: 兼容老版本  5.6,5.7版本
12 
13 #可以更改密码插件
14 create user test@'10.0.0.%' identified with mySQL_native_password by '123456abcd';
15 mySQL> select user,host,authentication_string,plugin from mySQL.user;
16 +------------------+-----------+------------------------------------------------------------------------+-----------------------+
17 | user             | host      | authentication_string                                                  | plugin                |
18 +------------------+-----------+------------------------------------------------------------------------+-----------------------+
19 | test             | 10.0.0.%  | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257                              | mySQL_native_password

3.创建密码/修改密码

#1，创建密码
mySQL> create user oldguo@'10.0.0.%';
Query OK, 0 rows affected (0.02 sec)
mySQL> create user test@'10.0.0.%' identified by '123456abcd';


#2.修改密码
mySQL> alter user user1@'10.0.0.%' identified by '123456abcd';  只修改密码

mySQL>  alter user oldguo@'10.0.0.%' identified with mySQL_native_password by '123456abcd';   修改密码及密码插件

4.删除用户

#生产谨慎！！！！！！！！！！！！！
drop user test@'10.0.0.%';
mySQL> select user,host from mySQL.user where (user='' or host='' or authentication_string='') and user!='root';
+-------+-----------+
| user | host |
+-------+-----------+
| test1 | |
| | localhost |
+-------+-----------+
2 rows in set (0.01 sec)
mySQL> drop user test1@'';
Query OK, 0 rows affected (0.00 sec)
mySQL> drop user ''@'localhost';
Query OK, 0 rows affected (0.01 sec)

5.修改用户

alter user oldguo@'10.0.0.%' identified with mySQL_native_password by '123456abcd';

如果是caching_sha2_password 密码插件，因为8.0默认密码插件为caching_sha2_password
alter user oldguo@'10.0.0.%' identified by '123456abcd';

6.锁用户

 1 #一般不会删除用户
 2 可以先将不用的用户锁住，如果还是有人使用，可以快速解锁
 3 ALTER USER 'test'@'10.0.0.%' ACCOUNT LOCK;
 4 
 5 #解锁用户
 6 ALTER USER 'test'@'10.0.0.%' ACCOUNT UNLOCK;
 7 
 8 #查看用户是否上锁（其中N表示未锁，Y表示已锁用户,mysql 有3个默认用户是带锁）
 9 mySQL> select user,host,authentication_string,plugin, account_locked  from mySQL.user;
10 +------------------+-----------+------------------------------------------------------------------------+-----------------------+----------------+
11 | user     | host      | authentication_string     | plugin       | account_locked |
12 | test     | 10.0.0.%  | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257| mySQL_native_password | N         
13 | root             | localhost |                                  | caching_sha2_password | N

7.忘记root管理原密码处理方式

 1 1. 关闭数据库
 2 /etc/init.d/mySQLd stop
 3 
 4 2. 安全模式启动数据库并后台启动
 5 [root@localhost data]# mySQLd_safe --skip-grant-tables --skip-networking &
 6 --skip-grant-tables   #不加载用户认证授权表
 7 --skip-networking     #关闭TCP协议，只能本地连接
 8 
 9 3. 登陆数据库
10 mySQL
11 
12 4. 刷新授权表
13 flush privileges;        #因为跳过用户证授权表了，所以需要人为加载授权表
14 
15 5. 修改密码
16 mySQL> alter user root@'localhost' identified with mySQL_native_password by '123456abcd';
17 
18 6. 重启数据库到正常模式
19 [root@localhost data]# /etc/init.d/mySQLd restart