Debian Codegraph @

mysql-test/suite/auth_sec/include/plugin_auth_caching_sha2_password_tests.inc

raw
1
2 # Create users
3
4 CREATE USER arthurdent@localhost IDENTIFIED WITH caching_sha2_password BY 'abcd';
5 CREATE USER marvintheparanoidandroid@localhost IDENTIFIED WITH caching_sha2_password BY 'efghi';
6 CREATE USER zaphodbeeblebrox@localhost IDENTIFIED WITH caching_sha2_password BY 'xyz';
7
8 # Connection using RSA keys - cache entry will be created for arthudent.
9 --exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT --ssl-mode=DISABLED -uarthurdent -pabcd --server-public-key-path=$MYSQL_TEST_DIR/std_data/rsa_public_key.pem -e "SELECT USER(), CURRENT_USER()" 2>&1
10
11 # Now even plaintext connection should work
12 --connect(conn_arthurdent, localhost, arthurdent, abcd,,,,)
13 SELECT USER(), CURRENT_USER();
14
15 # Let's try one more time
16 --connect(conn_arthurdent_2, localhost, arthurdent, abcd,,,,)
17 SELECT USER(), CURRENT_USER();
18
19 # Change the password with SET PASSWORD
20 SET PASSWORD = 'dcba';
21
22 # Old password based scramble shouldn't work
23 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
24 --error ER_ACCESS_DENIED_ERROR
25 --connect(conn_arthurdent_3, localhost, arthurdent, abcd,,,,SSL)
26
27 # Neither should the full authentication - Actual error ER_ACCESS_DENIED_ERROR
28 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
29 --error ER_ACCESS_DENIED_ERROR
30 --connect(conn_arthurdent_3, localhost, arthurdent, abcd,,,,SSL)
31 --error 1
32 --exec $MYSQL -uarthurdent -pabcd --server-public-key-path=$MYSQL_TEST_DIR/std_data/rsa_public_key.pem -e "SELECT USER(), CURRENT_USER()" 2>&1
33
34 # Scrmable based on new password is not yet cached on server side - Actual error CR_AUTH_PLUGIN_ERR
35 --error 1
36 --exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT -uarthurdent -pdcba --ssl-mode=DISABLED -e "SELECT USER(), CURRENT_USER()" 2>&1
37
38 # But if you perform full authentication, then everything should be set
39 --exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT --ssl-mode=DISABLED -uarthurdent -pdcba --server-public-key-path=$MYSQL_TEST_DIR/std_data/rsa_public_key.pem -e "SELECT USER(), CURRENT_USER()" 2>&1
40
41 # Try with plaintext connection, it should work now
42 --exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT --ssl-mode=DISABLED -uarthurdent -pdcba -e "SELECT USER(), CURRENT_USER()" 2>&1
43
44 # Now try password change through ALTER USER
45 --exec $MYSQL -uarthurdent -pdcba -e "ALTER USER arthurdent@localhost IDENTIFIED BY 'acbd';" 2>&1
46
47 # Same story : Old scramble - no, old password - no, new scramble - not yet available, new password - yes, then new scramble -yes
48 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
49 --error ER_ACCESS_DENIED_ERROR
50 --connect(conn_arthurdent_4, localhost, arthurdent, dcba,,,,SSL)
51 --error ER_ACCESS_DENIED_ERROR
52 --connect(conn_arthurdent_3, localhost, arthurdent, dcba,,,,SSL)
53 --error 1
54 --exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT --ssl-mode=DISABLED -uarthurdent -pdcba --server-public-key-path=$MYSQL_TEST_DIR/std_data/rsa_public_key.pem -e "SELECT USER(), CURRENT_USER()" 2>&1
55 --error 1
56 --exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT --ssl-mode=DISABLED -uarthurdent -pacbd -e "SELECT USER(), CURRENT_USER()" 2>&1
57 --exec $MYSQL -uarthurdent --host=127.0.0.1 -P $MASTER_MYPORT --ssl-mode=DISABLED -pacbd --server-public-key-path=$MYSQL_TEST_DIR/std_data/rsa_public_key.pem -e "SELECT USER(), CURRENT_USER()" 2>&1
58 --exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT --ssl-mode=DISABLED -uarthurdent -pacbd -e "SELECT USER(), CURRENT_USER()" 2>&1
59
60 # Same should happen if other changes the password
61 --connection default
62 SET PASSWORD FOR arthurdent@localhost = 'dbca';
63
64 # Same story : Old scramble - no, old password - no, new scramble - not yet available, new password - yes, then new scramble -yes
65 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
66 --error ER_ACCESS_DENIED_ERROR
67 --connect(conn_arthurdent_4, localhost, arthurdent, acbd,,,,)
68 --error ER_ACCESS_DENIED_ERROR
69 --connect(conn_arthurdent_3, localhost, arthurdent, acbd,,,,SSL)
70 --error 1
71 --exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT --ssl-mode=DISABLED -uarthurdent -pacbd --server-public-key-path=$MYSQL_TEST_DIR/std_data/rsa_public_key.pem -e "SELECT USER(), CURRENT_USER()" 2>&1
72 --error 1
73 --exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT --ssl-mode=DISABLED -uarthurdent -pdbca -e "SELECT USER(), CURRENT_USER()" 2>&1
74 --exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT --ssl-mode=DISABLED -uarthurdent -pdbca --server-public-key-path=$MYSQL_TEST_DIR/std_data/rsa_public_key.pem -e "SELECT USER(), CURRENT_USER()" 2>&1
75 --exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT --ssl-mode=DISABLED -uarthurdent -pdbca -e "SELECT USER(), CURRENT_USER()" 2>&1
76
77 --connection default
78 ALTER USER arthurdent@localhost IDENTIFIED BY 'abcd';
79
80 # Same story : Old scramble - no, old password - no, new scramble - not yet available, new password - yes, then new scramble -yes
81 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
82 --error ER_ACCESS_DENIED_ERROR
83 --connect(conn_arthurdent_4, localhost, arthurdent, dbca,,,,)
84 --error ER_ACCESS_DENIED_ERROR
85 --connect(conn_arthurdent_3, localhost, arthurdent, dbca,,,,SSL)
86 --error 1
87 --exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT --ssl-mode=DISABLED -uarthurdent -pdbca --server-public-key-path=$MYSQL_TEST_DIR/std_data/rsa_public_key.pem -e "SELECT USER(), CURRENT_USER()" 2>&1
88 --error 1
89 --exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT --ssl-mode=DISABLED -uarthurdent -pabcd -e "SELECT USER(), CURRENT_USER()" 2>&1
90 --exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT --ssl-mode=DISABLED -uarthurdent -pabcd --server-public-key-path=$MYSQL_TEST_DIR/std_data/rsa_public_key.pem -e "SELECT USER(), CURRENT_USER()" 2>&1
91 --exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT --ssl-mode=DISABLED -uarthurdent -pabcd -e "SELECT USER(), CURRENT_USER()" 2>&1
92
93 # FLUSH PRIVILEGES should clear the sha2 cache as well
94 --connection default
95 FLUSH PRIVILEGES;
96
97 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
98 --error 1
99 --exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT --ssl-mode=DISABLED -uarthurdent -pabcd -e "SELECT USER(), CURRENT_USER()" 2>&1
100 --exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT --ssl-mode=REQUIREd -uarthurdent -pabcd -e "SELECT USER(), CURRENT_USER()" 2>&1
101 --connect(conn_arthurdent_6, localhost, arthurdent, abcd,,,,)
102 SELECT USER(), CURRENT_USER();
103 --exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT --ssl-mode=DISABLED -uarthurdent -pabcd -e "SELECT USER(), CURRENT_USER()" 2>&1
104
105 # caching_sha2_password as client's default authentication
106 --exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT --ssl-mode=DISABLED -uarthurdent -pabcd --default-auth=caching_sha2_password -e "SELECT USER(), CURRENT_USER()" 2>&1
107
108 # Expire password - Should force full authentication
109 --connection default
110 ALTER USER arthurdent@localhost PASSWORD EXPIRE;
111 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
112 --exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT --ssl-mode=REQUIRED -uarthurdent -pabcd --connect-expired-password -e "SET PASSWORD='abcd';" 2>&1
113 --exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT --ssl-mode=REQUIRED -uarthurdent -pabcd -e "SELECT USER(), CURRENT_USER()" 2>&1
114
115 # RENAME USER should clear the sha2 cache as well
116 --connection default
117 RENAME USER arthurdent@localhost TO slartibartfast@localhost;
118
119 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
120 --error 1
121 --exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT --ssl-mode=DISABLED -uslartibartfast -pabcd -e "SELECT USER(), CURRENT_USER()" 2>&1
122 --exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT --ssl-mode=DISABLED -uslartibartfast -pabcd --server-public-key-path=$MYSQL_TEST_DIR/std_data/rsa_public_key.pem -e "SELECT USER(), CURRENT_USER()" 2>&1
123
124 # Rename again
125 --connection default
126 RENAME USER slartibartfast@localhost TO arthurdent@localhost;
127 --exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT --ssl-mode=DISABLED -uarthurdent -pabcd --server-public-key-path=$MYSQL_TEST_DIR/std_data/rsa_public_key.pem -e "SELECT USER(), CURRENT_USER()" 2>&1
128
129 # SSL connection - cache entry will be created for marvintheparanoidandroid
130 --connect(conn_marvintheparanoidandroid,localhost,marvintheparanoidandroid,efghi,,,,SSL)
131 SELECT USER(), CURRENT_USER();
132
133 # Now even plaintext connection should work
134 --connect(conn_marvintheparanoidandroid_2,localhost,marvintheparanoidandroid,efghi,,,,)
135 SELECT USER(), CURRENT_USER();
136
137 # Let's try one more time
138 --connect(conn_marvintheparanoidandroid_3,localhost,marvintheparanoidandroid,efghi,,,,)
139 SELECT USER(), CURRENT_USER();
140
141 # Try change_user
142 change_user marvintheparanoidandroid, efghi;
143 SELECT USER(), CURRENT_USER();
144
145 # Plaintext connection - cache entry will be created for zaphodbeeblebrox
146 --exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT --ssl-mode=DISABLED -uzaphodbeeblebrox -pxyz --get-server-public-key -e "SELECT USER(), CURRENT_USER()" 2>&1
147
148 # Now even plaintext connection should work
149 --connect(conn_zaphodbeeblebrox, localhost, zaphodbeeblebrox, xyz,,,,)
150 SELECT USER(), CURRENT_USER();
151
152 # Let's try one more time
153 --connect(conn_zaphodbeeblebrox_2, localhost, zaphodbeeblebrox, xyz,,,,)
154 SELECT USER(), CURRENT_USER();
155
156 # SHOW CREATE USER
157 --connection default
158 --replace_regex /AS .*$/AS '<non-deterministic-password-hash>'/
159 SHOW CREATE USER arthurdent@localhost;
160 --replace_regex /AS .*$/AS '<non-deterministic-password-hash>'/
161 SHOW CREATE USER marvintheparanoidandroid@localhost;
162 --replace_regex /AS .*$/AS '<non-deterministic-password-hash>'/
163 SHOW CREATE USER zaphodbeeblebrox@localhost;
164
165
166 # Cleanup - Close connections and remove users
167
168 --connection default
169 --disconnect conn_arthurdent
170 --disconnect conn_arthurdent_2
171 --disconnect conn_arthurdent_6
172 --disconnect conn_marvintheparanoidandroid
173 --disconnect conn_marvintheparanoidandroid_2
174 --disconnect conn_marvintheparanoidandroid_3
175 --disconnect conn_zaphodbeeblebrox
176 --disconnect conn_zaphodbeeblebrox_2
177
178 DROP USER arthurdent@localhost;
179 DROP USER marvintheparanoidandroid@localhost;
180 DROP USER zaphodbeeblebrox@localhost;
181