REVOKE column privilege fails after the column is dropped or renamed
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
Please answer these questions before submitting your issue. Thanks!
After a column-level privilege is granted, `SHOW GRANTS` still reports the old column name if the column is later dropped or renamed. That part matches MySQL.
However, TiDB then rejects `REVOKE` of that stale column privilege with `ERROR 1054 (42S22): Unknown column ...`, so the grant cannot be removed via SQL. MySQL 8.0.28 successfully revokes it.
### 1. Minimal reproduce step (Required)
```sql
CREATE DATABASE column_privilege_repro;
CREATE TABLE column_privilege_repro.hoge (id INT, old_column INT);
CREATE USER 'column_privilege_repro'@'%';
GRANT SELECT(id, old_column) ON column_privilege_repro.hoge TO 'column_privilege_repro'@'%';
ALTER TABLE column_privilege_repro.hoge DROP COLUMN old_column;
SHOW GRANTS FOR 'column_privilege_repro'@'%';
REVOKE SELECT(id, old_column) ON column_privilege_repro.hoge FROM 'column_privilege_repro'@'%';
```
The same failure also happens with rename:
```sql
ALTER TABLE column_privilege_repro.hoge RENAME COLUMN old_column TO new_column;
REVOKE SELECT(id, old_column) ON column_privilege_repro.hoge FROM 'column_privilege_repro'@'%';
```
### 2. What did you expect to see? (Required)
`SHOW GRANTS` may still show the stale column name (MySQL-compatible). `REVOKE` of that stale column privilege should succeed, as in MySQL 8.0.28.
```
mysql> SHOW GRANTS FOR 'column_privilege_repro'@'%';
+-----------------------------------------------------------------------------------------------------+
| Grants for column_privilege_repro@% |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'column_privilege_repro'@'%' |
| GRANT SELECT(`id`, `old_column`) ON `column_privilege_repro`.`hoge` TO 'column_privilege_repro'@'%' |
+-----------------------------------------------------------------------------------------------------+
mysql> REVOKE SELECT(id, old_column) ON column_privilege_repro.hoge FROM 'column_privilege_repro'@'%';
Query OK, 0 rows affected
```
TiDB already allows `REVOKE` on a non-existent **table** (see #28533). The same should apply to a non-existent **column** when the privilege row still exists in `mysql.columns_priv`.
### 3. What did you see instead (Required)
```
mysql> SHOW GRANTS FOR 'column_privilege_repro'@'%';
+-----------------------------------------------------------------------------------------------------+
| Grants for column_privilege_repro@% |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'column_privilege_repro'@'%' |
| GRANT SELECT(`id`, `old_column`) ON `column_privilege_repro`.`hoge` TO 'column_privilege_repro'@'%' |
+-----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> REVOKE SELECT(id, old_column) ON column_privilege_repro.hoge FROM 'column_privilege_repro'@'%';
ERROR 1054 (42S22): Unknown column 'old_column' in 'hoge'
```
Root cause: `revokeColumnPriv` requires the column to still exist in the current table schema before deleting the privilege row:
https://github.com/pingcap/tidb/blob/master/pkg/executor/revoke.go
```go
if tbl != nil {
if table.FindCol(tbl.Cols(), c.Name.L) == nil {
return infoschema.ErrColumnNotExists.GenWithStackByArgs(c.Name.L, tbl.Meta().Name.L)
}
}
```
`GRANT` should keep this existence check. `REVOKE` should not: column names in `mysql.columns_priv` are stored as strings and are not updated by `DROP COLUMN` / `RENAME COLUMN` (same as MySQL).
The reported failure does not show up on master because
- The 8.5 column-privilege GRANT/REVOKE rewrite is [#61678](https://github.com/pingcap/tidb/pull/61678). It was merged into release-8.5-20250606-v8.5.2, not master.
- The cherry-pick to master ([#67928](https://github.com/pingcap/tidb/pull/67928)) is still OPEN. Even if it is merged, it still contains the column-existence check, so it would not fix this bug.
Related: #67060 (another column-privilege `REVOKE` inconsistency).
### 4. What is your TiDB version? (Required)
- TiDB v8.5.6
Contributor guide
Research direction
Start in pkg/executor/revoke.go at revokeColumnPriv and inspect how the column existence check affects stale entries in mysql.columns_priv. Reproduce the DROP COLUMN and RENAME COLUMN SQL cases, then verify that REVOKE succeeds for the stale privilege while existing GRANT validation remains unchanged.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, mysql, sql
- Domain
- authorization, databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Active
- Clarity
- Clearly specified
- Newbie friendliness
- 72/100