Debian Codegraph @

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

raw
1 include/save_binlog_position.inc
2 CALL mtr.add_suppression('Following users were specified in ALTER USER IF EXISTS');
3 # -----------------------------------------------------------------------
4 # Begin : Tests for ALTER USER
5 CREATE USER userX, userY, userZ;
6 include/save_binlog_position.inc
7 SELECT user, authentication_string FROM mysql.user;
8 user authentication_string
9 userX
10 userY
11 userZ
12 mysql.infoschema $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED
13 mysql.session $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED
14 mysql.sys $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED
15 root
16 # Case 1 : Modify multiple users
17 ALTER USER userX IDENTIFIED BY 'abcd',
18 userY IDENTIFIED BY 'efgh',
19 userZ IDENTIFIED BY 'ijkl';
20 # This event sequence pattern MUST be present in binlog: !Q(ALTER USER.*userX.*userY.*userZ.*)
21 include/assert_binlog_events.inc
22 include/save_binlog_position.inc
23 mysql: [Warning] Using a password on the command line interface can be insecure.
24 CURRENT_USER()
25 userX@%
26 mysql: [Warning] Using a password on the command line interface can be insecure.
27 CURRENT_USER()
28 userY@%
29 mysql: [Warning] Using a password on the command line interface can be insecure.
30 CURRENT_USER()
31 userZ@%
32 # Case 2 : Try to modify property of an non-existing user
33 ALTER USER userW IDENTIFIED BY 'haha',
34 userX IDENTIFIED BY '',
35 userV IDENTIFIED BY 'haha';
36 ERROR HY000: Operation ALTER USER failed for 'userW'@'%','userV'@'%'
37 # This event sequence pattern MUST NOT be present in binlog: !Q(ALTER USER.*userW.*userX.*userV.*)
38 include/assert_binlog_events.inc
39 mysql: [Warning] Using a password on the command line interface can be insecure.
40 CURRENT_USER()
41 userX@%
42 mysql: [Warning] Using a password on the command line interface can be insecure.
43 CURRENT_USER()
44 userY@%
45 mysql: [Warning] Using a password on the command line interface can be insecure.
46 CURRENT_USER()
47 userZ@%
48 # Case 3 : Remove one of the entries from mysql.user but don't flush privileges.
49 DELETE FROM mysql.user WHERE user LIKE 'userX';
50 include/save_binlog_position.inc
51 ALTER USER userY IDENTIFIED BY '',
52 userX IDENTIFIED BY 'haha';
53 ERROR HY000: Operation ALTER USER failed for 'userX'@'%'
54 # Check binlog : Must not have any entry
55 # This event sequence pattern MUST NOT be present in binlog: !Q(ALTER USER.*userY.*userX.*)
56 include/assert_binlog_events.inc
57 FLUSH PRIVILEGES;
58 include/save_binlog_position.inc
59 mysql: [Warning] Using a password on the command line interface can be insecure.
60 CURRENT_USER()
61 userY@%
62 mysql: [Warning] Using a password on the command line interface can be insecure.
63 CURRENT_USER()
64 userZ@%
65 # Case 4 : Try to expire own password in a failing DDL. We update
66 # current session's state as well. However, since DDL is going fail,
67 # session's state must not be altered and user should be able to
68 # execute other statements without any need to change password.
69 ALTER USER root@localhost, userW PASSWORD EXPIRE;
70 ERROR HY000: Operation ALTER USER failed for 'userW'@'%'
71 # Must succeed
72 SELECT user FROM mysql.user;
73 user
74 userY
75 userZ
76 mysql.infoschema
77 mysql.session
78 mysql.sys
79 root
80 # This event sequence pattern MUST NOT be present in binlog: !Q(ALTER USER.*root.*userW.*)
81 include/assert_binlog_events.inc
82 DROP USER userY, userZ;
83 include/save_binlog_position.inc
84 # End : Tests for ALTER USER
85 # -----------------------------------------------------------------------
86 # Begin : Tests for ALTER USER IF EXISTS
87 CALL mtr.add_suppression("Following users were specified in ALTER USER IF NOT EXISTS but they do not exist.");
88 CREATE USER userX, userY;
89 include/save_binlog_position.inc
90 # Case 1 : Must throw warning for userW and userZ
91 ALTER USER IF EXISTS userW IDENTIFIED BY 'abcd',
92 userX IDENTIFIED BY 'efgh',
93 userY IDENTIFIED BY 'ijkl',
94 userZ IDENTIFIED BY 'mnop';
95 Warnings:
96 Note 3162 Authorization ID 'userW'@'%' does not exist.
97 Note 3162 Authorization ID 'userZ'@'%' does not exist.
98 # This event sequence pattern MUST be present in binlog: !Q(ALTER USER.*userW.*userX.*userY.*userZ.*)
99 include/assert_binlog_events.inc
100 include/save_binlog_position.inc
101 mysql: [Warning] Using a password on the command line interface can be insecure.
102 CURRENT_USER()
103 userX@%
104 mysql: [Warning] Using a password on the command line interface can be insecure.
105 CURRENT_USER()
106 userY@%
107 # Case 2 : Remove one of the user from mysql.user table
108 DELETE FROM mysql.user WHERE user LIKE 'userX';
109 include/save_binlog_position.inc
110 # Must throw warning for userX
111 ALTER USER IF EXISTS userX IDENTIFIED BY 'abcd',
112 userY IDENTIFIED BY '';
113 Warnings:
114 Note 3162 Authorization ID 'userX'@'%' does not exist.
115 # This event sequence pattern MUST be present in binlog: !Q(ALTER USER.*userX.*userY.*)
116 include/assert_binlog_events.inc
117 FLUSH PRIVILEGES;
118 include/save_binlog_position.inc
119 # Case 3 : Current session's password must be expired
120 GRANT ALL ON *.* TO userY;
121 ALTER USER IF EXISTS userY, userW PASSWORD EXPIRE;
122 SELECT user FROM mysql.user;
123 ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
124 include/save_binlog_position.inc
125 # Must throw warning
126 ALTER USER IF EXISTS userY IDENTIFIED BY 'abcd', userW IDENTIFIED BY 'haha';
127 Warnings:
128 Note 3162 Authorization ID 'userW'@'%' does not exist.
129 SELECT user FROM mysql.user;
130 user
131 userY
132 mysql.infoschema
133 mysql.session
134 mysql.sys
135 root
136 # This event sequence pattern MUST be present in binlog: !Q(ALTER USER.*userY.*userW.*)
137 include/assert_binlog_events.inc
138 DROP USER userY;
139 include/save_binlog_position.inc
140 # End : Tests for ALTER USER IF EXISTS
141 # -----------------------------------------------------------------------