References privilege should be column-level

[morgo]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

use test;
create user if not exists u1;
create table t1 (a int);
GRANT select,update,insert on t1 to u1;
GRANT select (a), update (a),insert(a), references(a) on t1 to u1; -- fails
GRANT select (a), update (a),insert(a) on t1 to u1; -- works

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

mysql [localhost:8024] {root} (test) > use test;
Database changed
mysql [localhost:8024] {root} (test) > create user if not exists u1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql [localhost:8024] {root} (test) > create table t1 (a int);
ERROR 1050 (42S01): Table 't1' already exists
mysql [localhost:8024] {root} (test) > GRANT select,update,insert on t1 to u1;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8024] {root} (test) > GRANT select (a), update (a),insert(a), references(a) on t1 to u1; -- fails
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8024] {root} (test) > GRANT select (a), update (a),insert(a) on t1 to u1; -- works
Query OK, 0 rows affected (0.01 sec)

3. What did you see instead (Required)

mysql> use test;
Database changed
mysql> create user if not exists u1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table t1 (a int);
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT select,update,insert on t1 to u1;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT select (a), update (a),insert(a), references(a) on t1 to u1; -- fails
ERROR 1221 (HY000): Incorrect usage of COLUMN GRANT and NON-COLUMN PRIVILEGES
mysql> GRANT select (a), update (a),insert(a) on t1 to u1; -- works
Query OK, 0 rows affected (0.01 sec)

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]

This was changed to be schema level in #26703 -- but actually it should be column level.

[unconsolable]

Looks like after adding ReferencesPriv in AllColumnPrivs in parser and changing CreateTablePrivTable and CreateColumnPrivTable in session/bootstrap.go works.

mysql> create user if not exists u1;
Query OK, 0 rows affected (0.02 sec)
mysql> create table t1 (a int);
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT select (a), update (a),insert(a), references(a) on t1 to u1;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from columns_priv;
+------+-------+------+------------+-------------+---------------------+---------------------------------+
| Host | DB    | User | Table_name | Column_name | Timestamp           | Column_priv                     |
+------+-------+------+------------+-------------+---------------------+---------------------------------+
| %    | mysql | u1   | t1         | a           | 2021-10-02 20:40:42 | Select,Insert,Update,References |
+------+-------+------+------------+-------------+---------------------+---------------------------------+
1 row in set (0.00 sec)

As parser in under migration, so the PR for parser should be proposed in pingcap/parser or here?

[morgo]

As parser in under migration, so the PR for parser should be proposed in pingcap/parser or here?

Either way is fine. It is not high priority, so feel free to wait.

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