-
Notifications
You must be signed in to change notification settings - Fork 1.6k
/
binlog_grant.result
128 lines (128 loc) · 4.4 KB
/
binlog_grant.result
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
connection default;
reset master;
set @saved_binlog_format = @@global.binlog_format;
create user mysqltest_1@localhost;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO `mysqltest_1`@`localhost`
connect plain,localhost,mysqltest_1,,test;
connect root,localhost,root,,test;
**** Variable SQL_LOG_BIN ****
connection root;
[root]
set session sql_log_bin = 1;
connection plain;
[plain]
set session sql_log_bin = 1;
ERROR 42000: Access denied; you need (at least one of) the SUPER, BINLOG ADMIN privilege(s) for this operation
**** Variable BINLOG_FORMAT ****
connection root;
[root]
set global binlog_format = row;
set session binlog_format = row;
connection plain;
[plain]
set global binlog_format = row;
ERROR 42000: Access denied; you need (at least one of) the SUPER, BINLOG ADMIN privilege(s) for this operation
set session binlog_format = row;
ERROR 42000: Access denied; you need (at least one of) the SUPER, BINLOG ADMIN privilege(s) for this operation
**** Clean up ****
disconnect plain;
disconnect root;
connection default;
set global binlog_format = @saved_binlog_format;
drop user mysqltest_1@localhost;
CREATE USER 'mysqltest_1'@'localhost';
GRANT REPLICATION CLIENT ON *.* TO 'mysqltest_1'@'localhost';
connect rpl,localhost,mysqltest_1,,;
connection rpl;
SHOW MASTER LOGS;
SHOW BINARY LOGS;
SHOW BINLOG STATUS;
disconnect rpl;
connection default;
DROP USER 'mysqltest_1'@'localhost';
#
# Start of 10.5 test
#
#
# MDEV-21743 Split up SUPER privilege to smaller privileges
#
# Test that REPLICATION CLIENT is an alias for BINLOG MONITOR
CREATE USER user1@localhost;
GRANT REPLICATION CLIENT ON *.* TO user1@localhost;
SHOW GRANTS FOR user1@localhost;
Grants for user1@localhost
GRANT BINLOG MONITOR ON *.* TO `user1`@`localhost`
REVOKE REPLICATION CLIENT ON *.* FROM user1@localhost;
SHOW GRANTS FOR user1@localhost;
Grants for user1@localhost
GRANT USAGE ON *.* TO `user1`@`localhost`
DROP USER user1@localhost;
# Test if SHOW BINARY LOGS and SHOW BINGLOG STATUS are not allowed without REPLICATION CLIENT or SUPER
CREATE USER user1@localhost;
GRANT ALL PRIVILEGES ON *.* TO user1@localhost;
REVOKE REPLICATION CLIENT, SUPER ON *.* FROM user1@localhost;
connect user1,localhost,user1,,;
connection user1;
SHOW MASTER LOGS;
ERROR 42000: Access denied; you need (at least one of) the SUPER, BINLOG MONITOR privilege(s) for this operation
SHOW BINARY LOGS;
ERROR 42000: Access denied; you need (at least one of) the SUPER, BINLOG MONITOR privilege(s) for this operation
SHOW BINLOG STATUS;
ERROR 42000: Access denied; you need (at least one of) the SUPER, BINLOG MONITOR privilege(s) for this operation
disconnect user1;
connection default;
DROP USER user1@localhost;
# Test if PURGE BINARY LOGS is not allowed without BINLOG ADMIN or SUPER
CREATE USER user1@localhost;
GRANT ALL PRIVILEGES ON *.* TO user1@localhost;
REVOKE BINLOG ADMIN, SUPER ON *.* FROM user1@localhost;
connect user1,localhost,user1,,;
connection user1;
PURGE BINARY LOGS BEFORE '2001-01-01 00:00:00';
ERROR 42000: Access denied; you need (at least one of) the SUPER, BINLOG ADMIN privilege(s) for this operation
disconnect user1;
connection default;
DROP USER user1@localhost;
# Test if PURGE BINLOG is allowed with BINLOG ADMIN
CREATE USER user1@localhost;
GRANT BINLOG ADMIN ON *.* TO user1@localhost;
connect user1,localhost,user1,,;
connection user1;
PURGE BINARY LOGS BEFORE '2001-01-01 00:00:00';
disconnect user1;
connection default;
DROP USER user1@localhost;
# Test if PURGE BINLOG is allowed with SUPER
CREATE USER user1@localhost;
GRANT SUPER ON *.* TO user1@localhost;
connect user1,localhost,user1,,;
connection user1;
PURGE BINARY LOGS BEFORE '2001-01-01 00:00:00';
disconnect user1;
connection default;
DROP USER user1@localhost;
# Test if SHOW BINLOG EVENTS is not allowed without BINLOG MONITOR
CREATE USER user1@localhost;
GRANT ALL PRIVILEGES ON *.* TO user1@localhost;
REVOKE BINLOG MONITOR ON *.* FROM user1@localhost;
connect user1,localhost,user1,,;
connection user1;
SHOW BINLOG EVENTS;
ERROR 42000: Access denied; you need (at least one of) the BINLOG MONITOR privilege(s) for this operation
disconnect user1;
connection default;
DROP USER user1@localhost;
# Test if SHOW BINLOG EVENTS is allowed with BINLOG MONITOR
CREATE USER user1@localhost;
GRANT BINLOG MONITOR ON *.* TO user1@localhost;
connect user1,localhost,user1,,;
connection user1;
SHOW BINLOG EVENTS;
disconnect user1;
connection default;
DROP USER user1@localhost;
#
# End of 10.5 test
#