1# Horst Hunger
2# Created: 2010-10-06
3#
4# Test of the authentification interface. The plugin checks the expected values set
5# by this application and the application checks the values set the the plugin.
6--source include/have_plugin_interface.inc
7--source include/not_embedded.inc
8
9CREATE DATABASE test_user_db;
10
11--echo ========== test 1.1.3.2 ====================================
12--echo === check contens of components of info ====================
13
14CREATE USER qa_test_1_user IDENTIFIED WITH qa_auth_interface AS 'qa_test_1_dest';
15CREATE USER qa_test_1_dest IDENTIFIED BY 'dest_passwd';
16GRANT ALL PRIVILEGES ON test_user_db.* TO qa_test_1_dest identified by 'dest_passwd';
17GRANT PROXY ON qa_test_1_dest TO qa_test_1_user;
18--sorted_result
19SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
20SELECT @@proxy_user;
21SELECT @@external_user;
22
23--echo exec MYSQL -h localhost -P MASTER_MYPORT -u qa_test_1_user --password=qa_test_1_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1
24--exec $MYSQL -h localhost -P $MASTER_MYPORT -u qa_test_1_user --password=qa_test_1_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1
25
26--sorted_result
27SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
28
29DROP USER qa_test_1_user;
30DROP USER qa_test_1_dest;
31
32--echo === Assign values to components of info ====================
33
34CREATE USER qa_test_2_user IDENTIFIED WITH qa_auth_interface AS 'qa_test_2_dest';
35CREATE USER qa_test_2_dest IDENTIFIED BY 'dest_passwd';
36CREATE USER authenticated_as IDENTIFIED BY 'dest_passwd';
37GRANT ALL PRIVILEGES ON test_user_db.* TO qa_test_2_dest identified by 'dest_passwd';
38GRANT PROXY ON qa_test_2_dest TO qa_test_2_user;
39GRANT PROXY ON authenticated_as TO qa_test_2_user;
40--sorted_result
41SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
42SELECT @@proxy_user;
43SELECT @@external_user;
44
45--echo exec MYSQL -h localhost -P MASTER_MYPORT -u qa_test_2_user --password=qa_test_2_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1
46--exec $MYSQL -h localhost -P $MASTER_MYPORT -u qa_test_2_user --password=qa_test_2_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1
47
48--sorted_result
49SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
50
51DROP USER qa_test_2_user;
52DROP USER qa_test_2_dest;
53DROP USER authenticated_as;
54
55--echo === Assign too high values for *length, which should have no effect ====
56
57CREATE USER qa_test_3_user IDENTIFIED WITH qa_auth_interface AS 'qa_test_3_dest';
58CREATE USER qa_test_3_dest IDENTIFIED BY 'dest_passwd';
59GRANT ALL PRIVILEGES ON test_user_db.* TO qa_test_3_dest identified by 'dest_passwd';
60GRANT PROXY ON qa_test_3_dest TO qa_test_3_user;
61
62--echo exec MYSQL -h localhost -P MASTER_MYPORT -u qa_test_3_user --password=qa_test_3_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1
63--exec $MYSQL -h localhost -P $MASTER_MYPORT -u qa_test_3_user --password=qa_test_3_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1
64
65DROP USER qa_test_3_user;
66DROP USER qa_test_3_dest;
67
68--echo === Assign too low values for *length, which should have no effect ====
69
70CREATE USER qa_test_4_user IDENTIFIED WITH qa_auth_interface AS 'qa_test_4_dest';
71CREATE USER qa_test_4_dest IDENTIFIED BY 'dest_passwd';
72GRANT ALL PRIVILEGES ON test_user_db.* TO qa_test_4_dest identified by 'dest_passwd';
73GRANT PROXY ON qa_test_4_dest TO qa_test_4_user;
74
75--echo exec MYSQL -h localhost -P MASTER_MYPORT -u qa_test_4_user --password=qa_test_4_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1
76--exec $MYSQL -h localhost -P $MASTER_MYPORT -u qa_test_4_user --password=qa_test_4_dest  test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1
77
78DROP USER qa_test_4_user;
79DROP USER qa_test_4_dest;
80
81--echo === Assign empty string especially to authenticated_as (in plugin) ====
82
83CREATE USER qa_test_5_user IDENTIFIED WITH qa_auth_interface AS 'qa_test_5_dest';
84CREATE USER qa_test_5_dest IDENTIFIED BY 'dest_passwd';
85CREATE USER ''@'localhost' IDENTIFIED BY 'dest_passwd';
86GRANT ALL PRIVILEGES ON test_user_db.* TO qa_test_5_dest identified by 'dest_passwd';
87GRANT ALL PRIVILEGES ON test_user_db.* TO ''@'localhost' identified by 'dest_passwd';
88GRANT PROXY ON qa_test_5_dest TO qa_test_5_user;
89GRANT PROXY ON qa_test_5_dest TO ''@'localhost';
90
91--sorted_result
92SELECT user,plugin,authentication_string,password FROM mysql.user WHERE user != 'root';
93
94--echo exec MYSQL -h localhost -P MASTER_MYPORT --user=qa_test_5_user  --password=qa_test_5_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1
95--error 1
96--exec $MYSQL -h localhost -P $MASTER_MYPORT --user=qa_test_5_user --password=qa_test_5_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1
97
98DROP USER qa_test_5_user;
99DROP USER qa_test_5_dest;
100DROP USER ''@'localhost';
101
102--echo === Assign 'root' especially to authenticated_as (in plugin) ====
103
104CREATE USER qa_test_6_user IDENTIFIED WITH qa_auth_interface AS 'qa_test_6_dest';
105CREATE USER qa_test_6_dest IDENTIFIED BY 'dest_passwd';
106GRANT ALL PRIVILEGES ON test_user_db.* TO qa_test_6_dest identified by 'dest_passwd';
107GRANT PROXY ON qa_test_6_dest TO qa_test_6_user;
108
109--sorted_result
110SELECT user,plugin,authentication_string,password FROM mysql.user WHERE user != 'root';
111
112--echo exec MYSQL -h localhost -P MASTER_MYPORT --user=qa_test_6_user  --password=qa_test_6_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1
113--error 1
114--exec $MYSQL -h localhost -P $MASTER_MYPORT --user=qa_test_6_user --password=qa_test_6_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1
115
116GRANT PROXY ON qa_test_6_dest TO root IDENTIFIED WITH qa_auth_interface AS 'qa_test_6_dest';
117--sorted_result
118SELECT user,plugin,authentication_string,password FROM mysql.user WHERE user != 'root';
119
120--echo exec MYSQL -h localhost -P MASTER_MYPORT --user=root  --password=qa_test_6_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1
121--error 1
122--exec $MYSQL -h localhost -P $MASTER_MYPORT --user=root --password=qa_test_6_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1
123
124REVOKE PROXY ON qa_test_6_dest FROM root;
125--sorted_result
126SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
127
128--echo exec MYSQL -h localhost -P MASTER_MYPORT --user=root  --password=qa_test_6_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1
129--error 1
130--exec $MYSQL -h localhost -P $MASTER_MYPORT --user=root --password=qa_test_6_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1
131
132DROP USER qa_test_6_user;
133DROP USER qa_test_6_dest;
134DELETE FROM mysql.user WHERE user='root' AND plugin='qa_auth_interface';
135--sorted_result
136SELECT user,plugin,authentication_string,password FROM mysql.user WHERE user != 'root';
137
138
139--echo === Test of the --default_auth option for clients ====
140
141CREATE USER qa_test_11_user IDENTIFIED WITH qa_auth_interface AS 'qa_test_11_dest';
142CREATE USER qa_test_11_dest IDENTIFIED BY 'dest_passwd';
143GRANT ALL PRIVILEGES ON test_user_db.* TO qa_test_11_dest identified by 'dest_passwd';
144GRANT PROXY ON qa_test_11_dest TO qa_test_11_user;
145
146--echo exec MYSQL --default_auth=qa_auth_client -h localhost -P MASTER_MYPORT -u qa_test_11_user --password=qa_test_11_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1
147--error 1
148--exec $MYSQL --default_auth=qa_auth_client -h localhost -P $MASTER_MYPORT -u qa_test_11_user --password=qa_test_11_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1
149
150DROP USER qa_test_11_user, qa_test_11_dest;
151DROP DATABASE test_user_db;
152
153--exit
154