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
|
########################################################################
# Test for case:
# - create user A identified via unix_socket as 'B' or unix_socket as 'C';
# - connect as database user A using unix user of B
# Expected result:
# - connection succeed
########################################################################
create user 'DB_USER1' identified via unix_socket as 'OS_USER' or unix_socket as '-Cannot-Match-Any-Legal-Unix-User-Name';
grant select on test.* to 'DB_USER1';
#
# Auth succeed with OS user matches the first authentication string.
# @@external_user is set to OS_USER name.
#
select user(), current_user(), database();
user() current_user() database()
DB_USER1@localhost DB_USER1@% test
select @@external_user;
@@external_user
OS_USER
select host, user, json_value(priv, '$.authentication_string') as authentication_string,
json_value(priv, '$.auth_or[0].authentication_string') as optional_authentication_string from mysql.global_priv where user='DB_USER1';
host user authentication_string optional_authentication_string
% DB_USER1 -Cannot-Match-Any-Legal-Unix-User-Name OS_USER
########################################################################
# Test for case:
# - create user A identified via unix_socket as 'B' or unix_socket as 'C';
# - connect as database user A using unix user of C
# Expected result:
# - connection succeed
########################################################################
create user 'DB_USER2' identified via unix_socket as '-Cannot-Match-Any-Legal-Unix-User-Name' or unix_socket as 'OS_USER';
grant select on test.* to 'DB_USER2';
#
# Auth succeed with OS user matches the optional authentication string.
# @@external_user is set to OS_USER name.
#
select user(), current_user(), database();
user() current_user() database()
DB_USER2@localhost DB_USER2@% test
select @@external_user;
@@external_user
OS_USER
select host, user, json_value(priv, '$.authentication_string') as authentication_string,
json_value(priv, '$.auth_or[0].authentication_string') as optional_authentication_string from mysql.global_priv where user='DB_USER1';
host user authentication_string optional_authentication_string
% DB_USER1 -Cannot-Match-Any-Legal-Unix-User-Name OS_USER
########################################################################
# Test for case:
# - create user A identified via unix_socket as 'B';
# - connect as database user A using unix user of D
# Expected result:
# - connection is refused
########################################################################
create user 'DB_USER3' identified via unix_socket as '-Cannot-Match-Any-Legal-Unix-User-Name';
grant select on test.* to 'DB_USER3';
#
# Auth fail with OS user that does not match the authentication string.
#
#
########################################################################
# Test for case:
# - create user A identified via unix_socket as 'B' or unix_socket as 'C';
# - connect as database user A using unix user of D
# Expected result:
# - connection is refused
########################################################################
create user 'DB_USER4' identified via unix_socket as '-Cannot-Match-Any-Legal-Unix-User-Name-1'
or unix_socket as '-Cannot-Match-Any-Legal-Unix-User-Name-2';
grant select on test.* to 'DB_USER4';
#
# Auth fail with OS user that does not match the authentication string.
#
select host, user, json_value(priv, '$.authentication_string') as authentication_string,
json_value(priv, '$.auth_or[0].authentication_string') as optional_authentication_string from mysql.global_priv where user='DB_USER1';
host user authentication_string optional_authentication_string
% DB_USER1 -Cannot-Match-Any-Legal-Unix-User-Name OS_USER
########################################################################
# Test for case:
# - create user A identified via unix_socket as 'B' or unix_socket as 'C' or unix_socket as 'D' or unix_socket as 'E' or unix_socket as 'F';
# - connect as database user A using unix user of 'D'
# Expected result:
# - connection succeed
########################################################################
create user 'DB_USER5' identified via unix_socket as '-Cannot-Match-Any-Legal-Unix-User-Name-B'
or unix_socket as '-Cannot-Match-Any-Legal-Unix-User-Name-C'
or unix_socket as 'OS_USER'
or unix_socket as '-Cannot-Match-Any-Legal-Unix-User-Name-E'
or unix_socket as '-Cannot-Match-Any-Legal-Unix-User-Name-F';
grant select on test.* to 'DB_USER5';
#
# Auth succeed with OS user matches the first authentication string.
# @@external_user is set to OS_USER name.
#
select user(), current_user(), database();
user() current_user() database()
DB_USER5@localhost DB_USER5@% test
select @@external_user;
@@external_user
OS_USER
select host, user, json_value(priv, '$.authentication_string') as authentication_string,
json_value(priv, '$.auth_or[0].authentication_string') as optional_authentication_string_0,
json_value(priv, '$.auth_or[1].authentication_string') as optional_authentication_string_1,
json_value(priv, '$.auth_or[2].authentication_string') as optional_authentication_string_2,
json_value(priv, '$.auth_or[3].authentication_string') as optional_authentication_string_3
from mysql.global_priv where user='DB_USER5';
host user authentication_string optional_authentication_string_0 optional_authentication_string_1 optional_authentication_string_2 optional_authentication_string_3
% DB_USER5 -Cannot-Match-Any-Legal-Unix-User-Name-F -Cannot-Match-Any-Legal-Unix-User-Name-B -Cannot-Match-Any-Legal-Unix-User-Name-C OS_USER -Cannot-Match-Any-Legal-Unix-User-Name-E
########################################################################
# Test for case:
# - create user A identified via unix_socket as 'B';
# - connect as database user A using unix user of A
# Expected result:
# - connection is rejected
########################################################################
#
# Create DB user different with the OS user name, but using OS user name as the authentication string.
#
create user 'OS_USER' identified via unix_socket as '-Cannot-Match-Any-Legal-Unix-User-Name';
grant select on test.* to 'OS_USER';
#
# Auth fail with OS user that does not match the authentication string.
#
########################################################################
# Removing the test user.
########################################################################
drop user 'DB_USER1';
drop user 'DB_USER2';
drop user 'DB_USER3';
drop user 'DB_USER4';
drop user 'DB_USER5';
drop user 'OS_USER';
FLUSH PRIVILEGES;
########################################################################
# Removing the test file.
########################################################################
|
