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
|
--source include/not_embedded.inc
--disable_cursor_protocol
select priv into @root_priv from mysql.global_priv where user='root' and host='localhost';
--enable_cursor_protocol
select * from mysql.user where user = 'root' and host = 'localhost';
--echo # Test syntax
--echo #
--echo # These 2 selects should have no changes from the first one.
alter user CURRENT_USER;
select * from mysql.user where user = 'root' and host = 'localhost';
alter user CURRENT_USER();
select * from mysql.user where user = 'root' and host = 'localhost';
create user foo;
select * from mysql.user where user = 'foo';
alter user foo;
select * from mysql.user where user = 'foo';
--echo #
--echo # Test READ_ONLY privilege works correctly with a read only database.
--echo #
SET @start_read_only = @@global.read_only;
SET GLOBAL read_only=1;
grant create user on *.* to foo;
--echo # Currently no READ_ONLY ADMIN privileges.
connect (a, localhost, foo);
select @@global.read_only;
--error ER_OPTION_PREVENTS_STATEMENT
alter user foo;
--echo # Grant READ_ONLY ADMIN privilege to the user.
connection default;
grant READ_ONLY ADMIN on *.* to foo;
--echo # We now have READ_ONLY ADMIN privilege. We should be able to run alter user.
connect (b, localhost, foo);
alter user foo;
connection default;
SET GLOBAL read_only = @start_read_only;
--echo #
--echo # Test inexistant user.
--echo #
--error ER_CANNOT_USER
alter user boo;
--echo #--warning ER_CANNOT_USER
alter user if exists boo;
--echo #
--echo # Test password related altering.
--echo #
alter user foo identified by 'something';
select * from mysql.user where user = 'foo';
alter user foo identified by 'something2';
select * from mysql.user where user = 'foo';
alter user foo identified by password '*88C89BE093D4ECF72D039F62EBB7477EA1FD4D63';
select * from mysql.user where user = 'foo';
alter user foo identified by password 'invalid';
select * from mysql.user where user = 'foo';
--error ER_CANNOT_USER
alter user foo identified with 'somecoolplugin';
show warnings;
alter user foo identified with 'mysql_old_password';
select * from mysql.user where user = 'foo';
alter user foo identified with 'mysql_old_password' using '0123456789ABCDEF';
select * from mysql.user where user = 'foo';
--echo #
--echo # Test ssl related altering.
--echo #
alter user foo identified by 'something' require SSL;
select * from mysql.user where user = 'foo';
alter user foo identified by 'something' require X509;
select * from mysql.user where user = 'foo';
alter user foo identified by 'something'
require cipher 'text' issuer 'foo_issuer' subject 'foo_subject';
select * from mysql.user where user = 'foo';
--echo #
--echo # Test resource limits altering.
--echo #
alter user foo with MAX_QUERIES_PER_HOUR 10
MAX_UPDATES_PER_HOUR 20
MAX_CONNECTIONS_PER_HOUR 30
MAX_USER_CONNECTIONS 40;
select * from mysql.user where user = 'foo';
drop user foo;
--echo #
--echo # Bug #29882299: ALTER USER ... IDENTIFIED WITH ... BY ... SHOULD BE A PRIVILEGED OPERATION
--echo #
create user foo@localhost;
--connect x,localhost,foo
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
alter user current_user identified with 'something';
--connection default
--disconnect x
drop user foo@localhost;
update mysql.global_priv set priv=@root_priv where user='root' and host='localhost';
|
