Grant SELECT and INSERT to non-existent table should report an error

[djshow832]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

This is really confusing in MySQL:
When granting ALTER, or INSERT, CREATE privileges, it works, see #28533
However, when granting INSERT or SELECT, it doesn't work.

mysql> drop table if exists tmpdb.t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant select on tmpdb.t1 to test_user;
Query OK, 0 rows affected (0.18 sec)

mysql> grant insert on tmpdb.t1 to test_user;
Query OK, 0 rows affected (0.18 sec)

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

mysql> drop table if exists tmpdb.t1;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> grant select on tmpdb.t1 to test_user;
ERROR 1146 (42S02): Table 'tmpdb.t1' doesn't exist
mysql> grant insert on tmpdb.t1 to test_user;
ERROR 1146 (42S02): Table 'tmpdb.t1' doesn't exist

3. What did you see instead (Required)

mysql> drop table if exists tmpdb.t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant select on tmpdb.t1 to test_user;
Query OK, 0 rows affected (0.18 sec)

mysql> grant insert on tmpdb.t1 to test_user;
Query OK, 0 rows affected (0.18 sec)

4. What is your TiDB version? (Required)

Release Version: None
Edition: Community
Git Commit Hash: None
Git Branch: None
UTC Build Time: None
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[djshow832]

/cc @morgo @unconsolable It seems we produce another error after fixing one.

[djshow832]

I don't think it's only related to CREATE. See #28533, ALTER also works. @unconsolable

[unconsolable]

I guess in MySQL 8.0.27 it is determined by whether create is introduced, see follows.

mysql> grant select on t1 to u1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
mysql> grant insert on t1 to u1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
mysql> grant update on t1 to u1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
mysql> grant delete on t1 to u1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
mysql> grant create on t1 to u1;
Query OK, 0 rows affected (0.03 sec)
mysql> grant drop on t1 to u1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
mysql> grant references on t1 to u1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
mysql> grant index on t1 to u1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
mysql> grant alter on t1 to u1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
mysql> grant create view on t1 to u1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
mysql> grant show view on t1 to u1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
mysql> grant show view, create on t1 to u1;
Query OK, 0 rows affected (0.02 sec)
mysql> grant create view, create on t1 to u1;
Query OK, 0 rows affected (0.02 sec)
mysql> grant alter, create on t1 to u1;
Query OK, 0 rows affected (0.02 sec)
mysql> grant references, create on t1 to u1;
Query OK, 0 rows affected (0.02 sec)
mysql> grant index, create on t1 to u1;
Query OK, 0 rows affected (0.03 sec)
mysql> grant drop, create on t1 to u1;
Query OK, 0 rows affected (0.01 sec)
mysql> grant insert, select, drop, create on t1 to u1;
Query OK, 0 rows affected (0.02 sec)
mysql> grant insert, select, drop, create, index on t1 to u1;
Query OK, 0 rows affected (0.02 sec)
mysql> grant insert, select, drop, index on t1 to u1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist

For revoke it seems that no such limitation.

It seems reasonable to some degree as a user can have the priv to create a non-existing table with other privs.

[unconsolable]

I don't think it's only related to CREATE. See #28533, ALTER also works. @unconsolable

But I do test on MySQL 8.0.27, I will switch to 5.7 to try out.

[djshow832]

I don't think it's only related to CREATE. See #28533, ALTER also works. @unconsolable

But I do test on MySQL 8.0.27, I will switch to 5.7 to try out.

Sorry, my bad. You are right. @unconsolable

[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.