Privileges for TRUNCATE PARTITION and DROP PARTITION are inconsistent #64024
Description
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
-- connect as root
CREATE DATABASE test;
CREATE TABLE test.t1 (
id INT,
year_col INT
)
PARTITION BY RANGE (year_col) (
PARTITION p0 VALUES LESS THAN (1991),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (1999),
PARTITION p3 VALUES LESS THAN (2003),
PARTITION p4 VALUES LESS THAN (2007)
);
INSERT INTO test.t1 VALUES (100, 100);
CREATE USER foo;
GRANT ALTER ON test.t1 TO foo;
-- connect as foo
ALTER TABLE test.t1 TRUNCATE PARTITION p0;
ALTER TABLE test.t1 DROP PARTITION p0;
2. What did you expect to see? (Required)
When the user foo only has ALTER privilege on t1:
MySQL [(none)]> ALTER TABLE test.t1 TRUNCATE PARTITION p0;
ERROR 1142 (42000): DROP command denied to user 'foo'@'%' for table 't1'
MySQL [(none)]> ALTER TABLE test.t1 DROP PARTITION p0;
ERROR 1142 (42000): DROP command denied to user 'foo'@'%' for table 't1'
Since TiDB is compatible with MySQL, and MySQL requires DROP privilege for both TRUNCATE PARTITION and DROP PARTITION, TiDB is expected to require DROP privilege for these two commands as well.
3. What did you see instead (Required)
When the user foo only has ALTER privilege on t1:
MySQL [(none)]> ALTER TABLE test.t1 TRUNCATE PARTITION p0;
Query OK, 0 rows affected (0.021 sec)
MySQL [(none)]> ALTER TABLE test.t1 DROP PARTITION p0;
ERROR 1142 (42000): DROP command denied to user 'foo'@'%' for table 't1'
4. What is your TiDB version? (Required)
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v9.0.0-beta.1
Edition: Community
Git Commit Hash: 7aff918dcbfa6facf2adef9ade9961c40f217421
Git Branch: HEAD
UTC Build Time: 2025-03-24 09:09:55
GoVersion: go1.23.7
Race Enabled: false
Check Table Before Drop: false
Store: unistore |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)