support column privilege

[erjiaqing]

Feature Request

root:

➜  ~ mysql -h 127.0.0.1 -P 4000 -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.7.25-TiDB-v3.0.0-beta-231-g20463d6da-dirty MySQL Community Server (Apache License 2.0)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> create user test_test_1@localhost;
Query OK, 1 row affected (0.008 sec)

MySQL [(none)]> create database test_test;
Query OK, 0 rows affected (0.011 sec)

MySQL [(none)]> 
MySQL [(none)]> create table test_test.t1 (a int, b int);
Query OK, 0 rows affected (0.010 sec)

MySQL [(none)]> create view test_test.v1 (c,d) as select a+1,b+1 from test_test.t1;
Query OK, 0 rows affected (0.006 sec)

MySQL [(none)]> grant select (c) on test_test.v1 to test_test_1@localhost;
Query OK, 0 rows affected (0.008 sec)

MySQL [(none)]> use mysql;

MySQL [mysql]> select * from columns_priv;
+-----------+-----------+-------------+------------+-------------+---------------------+-------------+
| Host      | DB        | User        | Table_name | Column_name | Timestamp           | Column_priv |
+-----------+-----------+-------------+------------+-------------+---------------------+-------------+
| localhost | test_test | test_test_1 | v1         | c           | 2019-03-18 14:31:45 | Select      |
+-----------+-----------+-------------+------------+-------------+---------------------+-------------+
1 row in set (0.002 sec)


test_test_1:

➜  ~ mysql -h 127.0.0.1 -P 4000 -u test_test_1 -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.25-TiDB-v3.0.0-beta-231-g20463d6da-dirty MySQL Community Server (Apache License 2.0)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> use test_test
Database changed
MySQL [test_test]> select c from test_test.v1;
ERROR 1142 (42000): SELECT command denied to user 'localhost'@'test_test_1' for table 'v1'
MySQL [test_test]> 

Describe the feature you'd like:

Supported column privilege control.

Teachability, Documentation, Adoption, Migration Strategy:

https://dev.mysql.com/doc/refman/8.0/en/grant.html#grant-column-privileges

[zz-jason]

@tiancaiamao PTAL

[morgo]

This is documented under compatibility already: https://www.pingcap.com/docs/sql/mysql-compatibility/

Note that column level privileges pre-dated views in MySQL (which are usually a better way of handling this). I don't think this is a high priority feature, but it might be useful for compatibility.

[zz-jason]

The feature request can be rewritten to this simpler one:

Feature Request Description

Supported column privilege as MySQL. Below is a simple example of column privilege.

Create a table t and a user user_a@localhost, grant the user user_a@localhost with select privilege on column a:

drop table if exists t;
create table t(a bigint, b bigint);
create user user_a@localhost;
grant select(a) on t to user_a@localhost;
flush privileges;

As you can see in MySQL, it supports column privilege:

MySQL(root@127.0.0.1:test) > show grants for user_a@localhost;
+----------------------------------------------------------+
| Grants for user_a@localhost                              |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO `user_a`@`localhost`               |
| GRANT SELECT (`a`) ON `test`.`t` TO `user_a`@`localhost` |
+----------------------------------------------------------+
2 rows in set (0.00 sec)

But in TiDB, column privilege is not supported for now:

TiDB(root@127.0.0.1:test) > show grants for user_a@localhost;
+--------------------------------------------+
| Grants for user_a@localhost                |
+--------------------------------------------+
| GRANT USAGE ON *.* TO 'user_a'@'localhost' |
+--------------------------------------------+
1 row in set (0.00 sec)

Refer to: MySQL Column Privileges

Category

Feature, Security

Value

• Value Point: 2

As @morgo mentioned in the above comment, I haven't seen the request from users at present. The biggest value IMO at present is for MySQL compatibility. We can higher the value point in the future if there are some urgent or important use cases of this feature.

Workload Estimation

Workload contains coding, documenting, and testing.
1 Point for 1 Person/Work Day

It's hard to say, maybe 30 man day?