1-- source include/have_partition.inc
2# Grant tests not performed with embedded server
3-- source include/not_embedded.inc
4
5--disable_warnings
6drop schema if exists mysqltest_1;
7--enable_warnings
8
9
10#
11# Bug #17139: ALTER TABLE ... DROP PARTITION should require DROP privilege
12#
13
14create schema mysqltest_1;
15use mysqltest_1;
16
17create table t1 (a int) partition by list (a) (partition p1 values in (1), partition p2 values in (2), partition p3 values in (3));
18insert into t1 values (1),(2);
19
20# We don't have DROP USER IF EXISTS. Use this workaround to
21# cleanup possible grants for mysqltest_1 left by previous tests
22# and ensure consistent results of SHOW GRANTS command below.
23--disable_warnings
24set @orig_sql_mode= @@sql_mode;
25set sql_mode= (select replace(@@sql_mode,'NO_AUTO_CREATE_USER',''));
26grant usage on *.* to mysqltest_1@localhost;
27set sql_mode= @orig_sql_mode;
28revoke all privileges on *.* from mysqltest_1@localhost;
29--enable_warnings
30
31grant select,alter on mysqltest_1.* to mysqltest_1@localhost;
32
33connect (conn1,localhost,mysqltest_1,,mysqltest_1);
34show grants for current_user;
35alter table t1 add b int;
36--error ER_TABLEACCESS_DENIED_ERROR
37alter table t1 drop partition p2;
38disconnect conn1;
39
40connection default;
41grant drop on mysqltest_1.* to mysqltest_1@localhost;
42
43connect (conn2,localhost,mysqltest_1,,mysqltest_1);
44alter table t1 drop partition p2;
45disconnect conn2;
46
47connection default;
48revoke alter on mysqltest_1.* from mysqltest_1@localhost;
49
50connect (conn3,localhost,mysqltest_1,,mysqltest_1);
51--error ER_TABLEACCESS_DENIED_ERROR
52alter table t1 drop partition p3;
53disconnect conn3;
54
55connection default;
56
57revoke select,alter,drop on mysqltest_1.* from mysqltest_1@localhost;
58drop table t1;
59
60#
61# Bug #23675 Partitions: possible security breach via alter
62#
63
64create table t1 (s1 int);
65insert into t1 values (1);
66grant alter on mysqltest_1.* to mysqltest_1@localhost;
67connect (conn4,localhost,mysqltest_1,,mysqltest_1);
68connection conn4;
69--error ER_NO_PARTITION_FOR_GIVEN_VALUE
70alter table t1 partition by list (s1) (partition p1 values in (2));
71connection default;
72grant select, alter on mysqltest_1.* to mysqltest_1@localhost;
73disconnect conn4;
74connect (conn5,localhost,mysqltest_1,,mysqltest_1);
75--error ER_NO_PARTITION_FOR_GIVEN_VALUE
76alter table t1 partition by list (s1) (partition p1 values in (2));
77disconnect conn5;
78connection default;
79drop table t1;
80
81drop user mysqltest_1@localhost;
82drop schema mysqltest_1;
83
84--echo End of 5.1 tests
85