Debian Codegraph @

mysql-test/suite/auth_sec/r/key_value_auth.result

raw
1
2
3 =======================================================================
4 Checking the password authentication with @default_plugin plugin
5 =======================================================================
6
7 set @default_plugin= @@global.default_authentication_plugin;
8 Warnings:
9 Warning 1287 '@@default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
10 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc';
11 CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED BY 'abc';
12 select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')=@default_plugin;
13 (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')=@default_plugin
14 1
15 1 Expected
16 select (select plugin from mysql.user where User='Tanjotuser2' and Host='127.0.0.1')=@default_plugin;
17 (select plugin from mysql.user where User='Tanjotuser2' and Host='127.0.0.1')=@default_plugin
18 1
19 1 Expected
20 ERROR 28000: Access denied for user 'Tanjotuser1'@'localhost' (using password: NO)
21 ERROR 28000: Access denied for user 'Tanjotuser2'@'localhost' (using password: NO)
22 ERROR 28000: Access denied for user 'Tanjotuser1'@'localhost' (using password: NO)
23 ERROR 28000: Access denied for user 'Tanjotuser2'@'localhost' (using password: NO)
24 DROP USER 'Tanjotuser1'@'localhost';
25 DROP USER 'Tanjotuser2'@'127.0.0.1';
26
27
28 =======================================================================
29 Checking the password authentication with sha256_password plugin
30 =======================================================================
31
32 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'sha256_password';
33 CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED WITH 'sha256_password';
34 select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='sha256_password';
35 (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='sha256_password'
36 1
37 1 Expected
38 select (select plugin from mysql.user where User='Tanjotuser2' and Host='127.0.0.1')='sha256_password';
39 (select plugin from mysql.user where User='Tanjotuser2' and Host='127.0.0.1')='sha256_password'
40 1
41 1 Expected
42 ERROR 28000: Access denied for user 'Tanjotuser1'@'localhost' (using password: YES)
43 ERROR 28000: Access denied for user 'Tanjotuser2'@'localhost' (using password: YES)
44 SET PASSWORD FOR 'Tanjotuser1'@'localhost'='abc';
45 SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'='abc';
46 ERROR 28000: Access denied for user 'Tanjotuser1'@'localhost' (using password: NO)
47 ERROR 28000: Access denied for user 'Tanjotuser2'@'localhost' (using password: NO)
48 ERROR 28000: Access denied for user 'Tanjotuser1'@'localhost' (using password: YES)
49 ERROR 28000: Access denied for user 'Tanjotuser2'@'localhost' (using password: YES)
50 DROP USER 'Tanjotuser1'@'localhost';
51 DROP USER 'Tanjotuser2'@'127.0.0.1';
52
53
54 ====================================================================================================
55 checking the integrity of long password (more than 40 chars) using @default_plugin plugin
56 ====================================================================================================
57
58 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeaaaaaaaaaannnnnnnnnnaaaaaaaaaahhhhhhhhhh1111111111gggggggggg2222222222';
59 CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED BY 'aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeaaaaaaaaaannnnnnnnnnaaaaaaaaaahhhhhhhhhh1111111111gggggggggg2222222222';
60 ERROR 28000: Access denied for user 'Tanjotuser1'@'localhost' (using password: NO)
61 ERROR 28000: Access denied for user 'Tanjotuser2'@'localhost' (using password: NO)
62 DROP USER 'Tanjotuser1'@'localhost';
63 DROP USER 'Tanjotuser2'@'127.0.0.1';
64
65
66 ====================================================================================================
67 checking the integrity of long password (more than 40 chars) using sha256_password plugin
68 ====================================================================================================
69
70 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'sha256_password';
71 CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED WITH 'sha256_password';
72 SET PASSWORD FOR 'Tanjotuser1'@'localhost'='aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeaaaaaaaaaannnnnnnnnnaaaaaaaaaahhhhhhhhhh1111111111gggggggggg2222222222';
73 SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'='aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeaaaaaaaaaannnnnnnnnnaaaaaaaaaahhhhhhhhhh1111111111gggggggggg2222222222';
74 ERROR 28000: Access denied for user 'Tanjotuser1'@'localhost' (using password: NO)
75 ERROR 28000: Access denied for user 'Tanjotuser2'@'localhost' (using password: NO)
76 DROP USER 'Tanjotuser1'@'localhost';
77 DROP USER 'Tanjotuser2'@'127.0.0.1';
78
79
80 ====================================================================================================
81 checking the integrity of NULL password using @default_plugin plugin
82 ====================================================================================================
83
84 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY '';
85 CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED BY '';
86 ERROR 28000: Access denied for user 'Tanjotuser1'@'localhost' (using password: YES)
87 ERROR 28000: Access denied for user 'Tanjotuser2'@'localhost' (using password: YES)
88 ERROR 28000: Access denied for user 'Tanjotuser1'@'localhost' (using password: YES)
89 ERROR 28000: Access denied for user 'Tanjotuser2'@'localhost' (using password: YES)
90 ERROR 28000: Access denied for user 'Tanjotuser1'@'localhost' (using password: YES)
91 ERROR 28000: Access denied for user 'Tanjotuser2'@'localhost' (using password: YES)
92 DROP USER 'Tanjotuser1'@'localhost';
93 DROP USER 'Tanjotuser2'@'127.0.0.1';
94
95
96 ====================================================================================================
97 checking the integrity of NULL password using sha256_password plugin
98 ====================================================================================================
99
100 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'sha256_password';
101 CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED WITH 'sha256_password';
102 SET PASSWORD FOR 'Tanjotuser1'@'localhost'='';
103 SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'='';
104 ERROR 28000: Access denied for user 'Tanjotuser1'@'localhost' (using password: YES)
105 ERROR 28000: Access denied for user 'Tanjotuser2'@'localhost' (using password: YES)
106 ERROR 28000: Access denied for user 'Tanjotuser1'@'localhost' (using password: YES)
107 ERROR 28000: Access denied for user 'Tanjotuser2'@'localhost' (using password: YES)
108 DROP USER 'Tanjotuser1'@'localhost';
109 DROP USER 'Tanjotuser2'@'127.0.0.1';
110
111
112 =================================================================================================
113 checking the combination of Client default_auth=sha256_password and server default_auth=native
114 =================================================================================================
115
116 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'sha256_password';
117 CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED WITH 'sha256_password';
118 SET PASSWORD FOR 'Tanjotuser1'@'localhost'='abc';
119 SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'='abc';
120 GRANT ALL ON *.* TO 'Tanjotuser1'@'localhost';
121 GRANT ALL ON *.* TO 'Tanjotuser2'@'127.0.0.1';
122 **** Client default_auth=sha_256_password and server default auth=native
123 user() current_user()
124 Tanjotuser1@localhost Tanjotuser1@localhost
125 **** Client default_auth=native and server default auth=native
126 user() current_user()
127 Tanjotuser2@localhost [email protected]
128 **** Client default_auth=sha_256_password + public key on file.
129 user() current_user()
130 Tanjotuser1@localhost Tanjotuser1@localhost
131 DROP USER 'Tanjotuser1'@'localhost';
132 DROP USER 'Tanjotuser2'@'127.0.0.1';
133
134
135 =================================================================================================
136 Starting the server with the default authentication mysql_native_password
137 =================================================================================================
138
139 # Restart server with default-authentication-plugin=mysql_native_password;
140 Creating a user with mysql_native_password plugin
141 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc';
142 Grant all on *.* to 'Tanjotuser1'@'localhost';
143 Creating a user with SHA256_password plugin
144 CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
145 set password for 'Tanjotuser3'@'localhost' ='abc';
146 Grant all on *.* to 'Tanjotuser3'@'localhost';
147 select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='mysql_native_password';
148 (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='mysql_native_password'
149 1
150 1 Expected
151 select (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password';
152 (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password'
153 1
154 1 Expected
155 DROP USER 'Tanjotuser1'@'localhost';
156 DROP USER 'Tanjotuser3'@'localhost';
157
158
159 =================================================================================================
160 Starting the server with the default authentication sha256_password
161 =================================================================================================
162
163 # Restart server with default-authentication-plugin=sha256_password;
164 Creating a user with mysql_native_password plugin
165 CREATE USER 'Tanjotuser2'@'localhost' IDENTIFIED WITH 'mysql_native_password';
166 set password for 'Tanjotuser2'@'localhost' ='abc';
167 Grant all on *.* to 'Tanjotuser2'@'localhost';
168 Creating a user with SHA256_password plugin
169 CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED by 'abc';
170 Grant all on *.* to 'Tanjotuser3'@'localhost';
171 select (select plugin from mysql.user where User='Tanjotuser2' and Host='localhost')='mysql_native_password';
172 (select plugin from mysql.user where User='Tanjotuser2' and Host='localhost')='mysql_native_password'
173 1
174 1 Expected
175 select (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password';
176 (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password'
177 1
178 1 Expected
179 DROP USER 'Tanjotuser2'@'localhost';
180 DROP USER 'Tanjotuser3'@'localhost';
181 # restart: