1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
|
--source include/not_embedded.inc
--enable_connect_log
call mtr.add_suppression("password and an authentication plugin");
--echo #
--echo # Create a user with mysql_native_password plugin.
--echo # The user has no password or auth_string set.
--echo #
create user u1;
GRANT SELECT ON mysql.* to u1 IDENTIFIED VIA mysql_native_password;
select user, host, password, plugin, authentication_string from mysql.user where user = 'u1';
--echo #
--echo # The user's grants should show no password at all.
--echo #
show grants for u1;
--echo #
--echo # Test to see if connecting with no password is succesful.
--echo #
--connect (con1, localhost, u1,,)
show grants;
--disconnect con1
--connection default
--echo #
--echo # Test after flushing privileges.
--echo #
flush privileges;
--connect (con1, localhost, u1,,)
show grants;
--disconnect con1
--connection default
--echo #
--echo # Now add a mysql_native password string in authentication_string.
--echo #
# Password string is SOMETHING
GRANT SELECT ON mysql.* to u1 IDENTIFIED VIA mysql_native_password
USING '*7AFEFD08B6B720E781FB000CAA418F54FA662626';
select user, host, password, plugin, authentication_string from mysql.user where user = 'u1';
--echo #
--echo # Test to see if connecting with password is succesful.
--echo #
--connect (con1, localhost, u1,'SOMETHING',)
show grants;
--disconnect con1
--connection default
--echo #
--echo # Test after flushing privileges.
--echo #
flush privileges;
--connect (con1, localhost, u1,'SOMETHING',)
show grants;
--disconnect con1
--connection default
--echo #
--echo # Now we also set a password for the user.
--echo #
set password for u1 = PASSWORD('SOMETHINGELSE');
select user, host, password, plugin, authentication_string from mysql.user where user = 'u1';
--echo #
--echo # Here we should use the password field, as that primes over
--echo # the authentication_string field.
--echo #
show grants for u1;
--echo #
--echo # Logging in with the user's password should work.
--echo #
--connect (con1, localhost, u1,'SOMETHINGELSE',)
show grants;
--disconnect con1
--connection default
--echo #
--echo # Reload privileges and test logging in again.
--echo #
flush privileges;
show grants for u1;
--echo #
--echo # Here we connect via the user's password again.
--echo #
--connect (con1, localhost, u1,'SOMETHINGELSE',)
show grants;
--disconnect con1
--connection default
--echo #
--echo # Now we remove the authentication plugin password, flush privileges and
--echo # try again.
--echo #
update mysql.user set authentication_string = '' where user='u1';
select user, host, password, plugin, authentication_string from mysql.user where user = 'u1';
flush privileges;
show grants for u1;
--echo #
--echo # Here we connect via the user's password.
--echo #
--connect (con1, localhost, u1,'SOMETHINGELSE',)
select user, host, password, plugin, authentication_string from mysql.user where user = 'u1';
--disconnect con1
--connection default
--echo #
--echo # Try and set a wrong auth_string password, with mysql_native_password.
--echo # Make sure it fails.
--echo #
--error ER_PASSWD_LENGTH
GRANT USAGE ON *.* TO u1 IDENTIFIED VIA mysql_native_password USING 'asd';
--echo #
--echo # Now set a correct password.
--echo #
GRANT SELECT ON mysql.* to u1 IDENTIFIED VIA mysql_native_password
USING '*7AFEFD08B6B720E781FB000CAA418F54FA662626';
show grants for u1;
--echo #
--echo # Test if the user can now use that password instead.
--echo #
--connect (con1, localhost, u1,'SOMETHING',)
show grants;
--disconnect con1
--echo #
--echo # Test if the user can now use that password instead, after flushing privileges;
--echo #
--connection default
flush privileges;
--connect (con1, localhost, u1,'SOMETHING',)
show grants;
--disconnect con1
--connection default
--echo #
--echo # Clear all passwords from the user.
--echo #
GRANT SELECT ON mysql.* to u1 IDENTIFIED VIA mysql_native_password;
select user, host, password, plugin, authentication_string from mysql.user where user = 'u1';
--echo #
--echo # Test no password connect.
--echo #
--connect (con1, localhost, u1,,)
show grants;
--disconnect con1
--connection default
--echo #
--echo # Test no password connect, after flushing privileges.
--echo #
flush privileges;
--connect (con1, localhost, u1,,)
show grants;
--disconnect con1
--connection default
drop user u1;
|
