GRANT/REVOKE ON non-existent table incorrectly returns error

[morgo]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

DROP DATABASE IF EXISTS d1;
DROP USER IF EXISTS u1;
CREATE DATABASE d1;
USE d1;
CREATE TABLE t1 (a int);
CREATE USER u1;
GRANT ALTER ON d1.t1 TO u1; -- table exists, works
GRANT INSERT, CREATE ON d1.t2 TO u1; -- table doesn't exist; fails in tidb, works in MySQL.

DROP TABLE t1;
REVOKE ALTER ON d1.t1 FROM u1; -- revoke fails when table doesn't exist (serious)

2. What did you expect to see? (Required)

mysql [localhost:8024] {root} (d1) > DROP DATABASE IF EXISTS d1;
Query OK, 1 row affected (0.00 sec)

mysql [localhost:8024] {root} ((none)) > DROP USER IF EXISTS u1;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8024] {root} ((none)) > CREATE DATABASE d1;
Query OK, 1 row affected (0.00 sec)

mysql [localhost:8024] {root} ((none)) > USE d1;
Database changed
mysql [localhost:8024] {root} (d1) > CREATE TABLE t1 (a int);
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8024] {root} (d1) > CREATE USER u1;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost:8024] {root} (d1) > GRANT ALTER ON d1.t1 TO u1; -- table exists, works
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8024] {root} (d1) > GRANT INSERT, CREATE ON d1.t2 TO u1; -- table doesn't exist; fails in tidb, works in MySQL.
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8024] {root} (d1) > DROP TABLE t1;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8024] {root} (d1) > REVOKE ALTER ON d1.t1 FROM u1; 
Query OK, 0 rows affected (0.00 sec)

3. What did you see instead (Required)

mysql> DROP DATABASE IF EXISTS d1;
Query OK, 0 rows affected (0.01 sec)

mysql> DROP USER IF EXISTS u1;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE DATABASE d1;
Query OK, 0 rows affected (0.00 sec)

mysql> USE d1;
Database changed
mysql> CREATE TABLE t1 (a int);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER u1;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALTER ON d1.t1 TO u1; -- table exists, works
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT INSERT, CREATE ON d1.t2 TO u1; -- table doesn't exist; fails in tidb, works in MySQL.
ERROR 1146 (42S02): Table 'd1.t2' doesn't exist

mysql> DROP TABLE t1;
Query OK, 0 rows affected (0.01 sec)

mysql> REVOKE ALTER ON d1.t1 FROM u1; -- serious
ERROR 1146 (42S02): Table 'd1.t1' doesn't exist

4. What is your TiDB version? (Required)

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v5.3.0-alpha-256-gb0b559f23
Edition: Community
Git Commit Hash: b0b559f2389de5e27d0d2e12d2bfae049186b54b
Git Branch: master
UTC Build Time: 2021-10-01 14:52:33
GoVersion: go1.16.7
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

[morgo]

It is reasonable to assume this could cause compatibility issues because a user might grant access to objects before creating them.

[tisonkun]

It is reasonable to assume this could cause compatibility issues because a user might grant access to objects before creating them.

@morgo could you elaborate a bit the semantic of grant on a non-existing table? It seems if the user create d1.t2 later, it is granted to u1? You can just throw a link to MySQL doc if any.

[tisonkun]

Also we have a label type/compatibility. How do you distinguish labeling an issue in type/bug or type/compatibility?

[morgo]

@morgo could you elaborate a bit the semantic of grant on a non-existing table? It seems if the user create d1.t2 later, it is granted to u1? You can just throw a link to MySQL doc if any.

Yes that's right, you can see this from the t1 example. I am not sure if this is explicitly documented in MySQL, but I discovered this by looking at the test suite.

Also we have a label type/compatibility. How do you distinguish labeling an issue in type/bug or type/compatibility?

Compatibility imho means "both examples could be considered correct but TiDB differs from MySQL". Bug means, there is a behavior difference which is considered wrong and could be more serious.

[unconsolable]

/assign

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.