1
drop schema if exists mysqltest_1;
2
create schema mysqltest_1;
4
create table t1 (a int) partition by list (a) (partition p1 values in (1), partition p2 values in (2), partition p3 values in (3));
5
insert into t1 values (1),(2);
6
grant usage on *.* to mysqltest_1@localhost;
7
revoke all privileges on *.* from mysqltest_1@localhost;
8
grant select,alter on mysqltest_1.* to mysqltest_1@localhost;
9
show grants for current_user;
10
Grants for mysqltest_1@localhost
11
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
12
GRANT SELECT, ALTER ON `mysqltest_1`.* TO 'mysqltest_1'@'localhost'
13
alter table t1 add b int;
14
alter table t1 drop partition p2;
15
ERROR 42000: DROP command denied to user 'mysqltest_1'@'localhost' for table 't1'
16
grant drop on mysqltest_1.* to mysqltest_1@localhost;
17
alter table t1 drop partition p2;
18
revoke alter on mysqltest_1.* from mysqltest_1@localhost;
19
alter table t1 drop partition p3;
20
ERROR 42000: ALTER command denied to user 'mysqltest_1'@'localhost' for table 't1'
21
revoke select,alter,drop on mysqltest_1.* from mysqltest_1@localhost;
23
create table t1 (s1 int);
24
insert into t1 values (1);
25
grant alter on mysqltest_1.* to mysqltest_1@localhost;
26
alter table t1 partition by list (s1) (partition p1 values in (2));
27
ERROR HY000: Table has no partition for some existing values
28
grant select, alter on mysqltest_1.* to mysqltest_1@localhost;
29
alter table t1 partition by list (s1) (partition p1 values in (2));
30
ERROR HY000: Table has no partition for value 1
32
drop user mysqltest_1@localhost;
33
drop schema mysqltest_1;