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
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
|
=======================================================================
Checking the password authentication with @default_plugin plugin
=======================================================================
set @default_plugin= @@global.default_authentication_plugin;
Warnings:
Warning 1287 '@@default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc';
CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED BY 'abc';
select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')=@default_plugin;
(select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')=@default_plugin
1
1 Expected
select (select plugin from mysql.user where User='Tanjotuser2' and Host='127.0.0.1')=@default_plugin;
(select plugin from mysql.user where User='Tanjotuser2' and Host='127.0.0.1')=@default_plugin
1
1 Expected
ERROR 28000: Access denied for user 'Tanjotuser1'@'localhost' (using password: NO)
ERROR 28000: Access denied for user 'Tanjotuser2'@'localhost' (using password: NO)
ERROR 28000: Access denied for user 'Tanjotuser1'@'localhost' (using password: NO)
ERROR 28000: Access denied for user 'Tanjotuser2'@'localhost' (using password: NO)
DROP USER 'Tanjotuser1'@'localhost';
DROP USER 'Tanjotuser2'@'127.0.0.1';
=======================================================================
Checking the password authentication with sha256_password plugin
=======================================================================
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'sha256_password';
CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED WITH 'sha256_password';
select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='sha256_password';
(select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='sha256_password'
1
1 Expected
select (select plugin from mysql.user where User='Tanjotuser2' and Host='127.0.0.1')='sha256_password';
(select plugin from mysql.user where User='Tanjotuser2' and Host='127.0.0.1')='sha256_password'
1
1 Expected
ERROR 28000: Access denied for user 'Tanjotuser1'@'localhost' (using password: YES)
ERROR 28000: Access denied for user 'Tanjotuser2'@'localhost' (using password: YES)
SET PASSWORD FOR 'Tanjotuser1'@'localhost'='abc';
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'='abc';
ERROR 28000: Access denied for user 'Tanjotuser1'@'localhost' (using password: NO)
ERROR 28000: Access denied for user 'Tanjotuser2'@'localhost' (using password: NO)
ERROR 28000: Access denied for user 'Tanjotuser1'@'localhost' (using password: YES)
ERROR 28000: Access denied for user 'Tanjotuser2'@'localhost' (using password: YES)
DROP USER 'Tanjotuser1'@'localhost';
DROP USER 'Tanjotuser2'@'127.0.0.1';
====================================================================================================
checking the integrity of long password (more than 40 chars) using @default_plugin plugin
====================================================================================================
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeaaaaaaaaaannnnnnnnnnaaaaaaaaaahhhhhhhhhh1111111111gggggggggg2222222222';
CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED BY 'aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeaaaaaaaaaannnnnnnnnnaaaaaaaaaahhhhhhhhhh1111111111gggggggggg2222222222';
ERROR 28000: Access denied for user 'Tanjotuser1'@'localhost' (using password: NO)
ERROR 28000: Access denied for user 'Tanjotuser2'@'localhost' (using password: NO)
DROP USER 'Tanjotuser1'@'localhost';
DROP USER 'Tanjotuser2'@'127.0.0.1';
====================================================================================================
checking the integrity of long password (more than 40 chars) using sha256_password plugin
====================================================================================================
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'sha256_password';
CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED WITH 'sha256_password';
SET PASSWORD FOR 'Tanjotuser1'@'localhost'='aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeaaaaaaaaaannnnnnnnnnaaaaaaaaaahhhhhhhhhh1111111111gggggggggg2222222222';
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'='aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeaaaaaaaaaannnnnnnnnnaaaaaaaaaahhhhhhhhhh1111111111gggggggggg2222222222';
ERROR 28000: Access denied for user 'Tanjotuser1'@'localhost' (using password: NO)
ERROR 28000: Access denied for user 'Tanjotuser2'@'localhost' (using password: NO)
DROP USER 'Tanjotuser1'@'localhost';
DROP USER 'Tanjotuser2'@'127.0.0.1';
====================================================================================================
checking the integrity of NULL password using @default_plugin plugin
====================================================================================================
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY '';
CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED BY '';
ERROR 28000: Access denied for user 'Tanjotuser1'@'localhost' (using password: YES)
ERROR 28000: Access denied for user 'Tanjotuser2'@'localhost' (using password: YES)
ERROR 28000: Access denied for user 'Tanjotuser1'@'localhost' (using password: YES)
ERROR 28000: Access denied for user 'Tanjotuser2'@'localhost' (using password: YES)
ERROR 28000: Access denied for user 'Tanjotuser1'@'localhost' (using password: YES)
ERROR 28000: Access denied for user 'Tanjotuser2'@'localhost' (using password: YES)
DROP USER 'Tanjotuser1'@'localhost';
DROP USER 'Tanjotuser2'@'127.0.0.1';
====================================================================================================
checking the integrity of NULL password using sha256_password plugin
====================================================================================================
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'sha256_password';
CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED WITH 'sha256_password';
SET PASSWORD FOR 'Tanjotuser1'@'localhost'='';
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'='';
ERROR 28000: Access denied for user 'Tanjotuser1'@'localhost' (using password: YES)
ERROR 28000: Access denied for user 'Tanjotuser2'@'localhost' (using password: YES)
ERROR 28000: Access denied for user 'Tanjotuser1'@'localhost' (using password: YES)
ERROR 28000: Access denied for user 'Tanjotuser2'@'localhost' (using password: YES)
DROP USER 'Tanjotuser1'@'localhost';
DROP USER 'Tanjotuser2'@'127.0.0.1';
=================================================================================================
checking the combination of Client default_auth=sha256_password and server default_auth=native
=================================================================================================
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'sha256_password';
CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED WITH 'sha256_password';
SET PASSWORD FOR 'Tanjotuser1'@'localhost'='abc';
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'='abc';
GRANT ALL ON *.* TO 'Tanjotuser1'@'localhost';
GRANT ALL ON *.* TO 'Tanjotuser2'@'127.0.0.1';
**** Client default_auth=sha_256_password and server default auth=native
user() current_user()
Tanjotuser1@localhost Tanjotuser1@localhost
**** Client default_auth=native and server default auth=native
user() current_user()
Tanjotuser2@localhost Tanjotuser2@127.0.0.1
**** Client default_auth=sha_256_password + public key on file.
user() current_user()
Tanjotuser1@localhost Tanjotuser1@localhost
DROP USER 'Tanjotuser1'@'localhost';
DROP USER 'Tanjotuser2'@'127.0.0.1';
=================================================================================================
Starting the server with the default authentication mysql_native_password
=================================================================================================
# Restart server with default-authentication-plugin=mysql_native_password;
Creating a user with mysql_native_password plugin
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc';
Grant all on *.* to 'Tanjotuser1'@'localhost';
Creating a user with SHA256_password plugin
CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
set password for 'Tanjotuser3'@'localhost' ='abc';
Grant all on *.* to 'Tanjotuser3'@'localhost';
select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='mysql_native_password';
(select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='mysql_native_password'
1
1 Expected
select (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password';
(select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password'
1
1 Expected
DROP USER 'Tanjotuser1'@'localhost';
DROP USER 'Tanjotuser3'@'localhost';
=================================================================================================
Starting the server with the default authentication sha256_password
=================================================================================================
# Restart server with default-authentication-plugin=sha256_password;
Creating a user with mysql_native_password plugin
CREATE USER 'Tanjotuser2'@'localhost' IDENTIFIED WITH 'mysql_native_password';
set password for 'Tanjotuser2'@'localhost' ='abc';
Grant all on *.* to 'Tanjotuser2'@'localhost';
Creating a user with SHA256_password plugin
CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED by 'abc';
Grant all on *.* to 'Tanjotuser3'@'localhost';
select (select plugin from mysql.user where User='Tanjotuser2' and Host='localhost')='mysql_native_password';
(select plugin from mysql.user where User='Tanjotuser2' and Host='localhost')='mysql_native_password'
1
1 Expected
select (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password';
(select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password'
1
1 Expected
DROP USER 'Tanjotuser2'@'localhost';
DROP USER 'Tanjotuser3'@'localhost';
# restart:
|
