MySQL列权限授予及注意事项

对于权限的授予，库和表级别的都比较常见，但具体到表的某个列的访问权限怎么授予呢？创建视图？是否也可以按照独立的列授予insert|delete|update的权限呢？
其实，MySQL对列的授权，是有专门的语法实现的，例如授予test库priv_test表的id列的select权限给sam用户，可以用以下语法实现：
grant select(id) on test.priv_test to sam@‘localhost’;

mysql> select * from priv_test;
+------+------+
| id   | id2  |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

mysql> grant select(id) on test.priv_test to sam@'localhost';
Query OK, 0 rows affected (0.15 sec)

mysql> show grants for sam@'localhost';
+----------------------------------------------------------------+
| Grants for sam@localhost                                       |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `sam`@`localhost`                        |
| GRANT SELECT (`id`) ON `test`.`priv_test` TO `sam`@`localhost` |
+----------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> exit
Bye
[root@linux-base ~]# mysql -usam -psam123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 64
Server version: 8.0.12 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

mysql> use test;
Database changed
mysql> desc priv_test;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> select * from priv_test;
ERROR 1142 (42000): SELECT command denied to user 'sam'@'localhost' for table 'priv_test'
mysql> select id from priv_test; 
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

既然可以为单独的列授予select权限，那么DML操作呢？例如insert能不能用以下语法对单独的列授权呢？
grant insert(id) on test.priv_test to sam@‘localhost’;

mysql> show grants for sam@'localhost';
+----------------------------------------------------------------+
| Grants for sam@localhost                                       |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `sam`@`localhost`                        |
| GRANT SELECT (`id`) ON `test`.`priv_test` TO `sam`@`localhost` |
+----------------------------------------------------------------+
2 rows in set (0.01 sec)

mysql> grant insert(id) on test.priv_test to sam@'localhost';
Query OK, 0 rows affected (0.15 sec)

mysql> show grants for sam@'localhost';
+-------------------------------------------------------------------------------+
| Grants for sam@localhost                                                      |
+-------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `sam`@`localhost`                                       |
| GRANT SELECT (`id`), INSERT (`id`) ON `test`.`priv_test` TO `sam`@`localhost` |
+-------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> exit
Bye
[root@linux-base ~]# mysql -usam -psam123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 70
Server version: 8.0.12 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> use test;
Database changed
mysql> insert into priv_test(id) values (1);
Query OK, 1 row affected (0.14 sec)

mysql> insert into priv_test(id2) values (1); 
ERROR 1143 (42000): INSERT command denied to user 'sam'@'localhost' for column 'id2' in table 'priv_test'
mysql> select id from priv_test;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select * from priv_test;
ERROR 1142 (42000): SELECT command denied to user 'sam'@'localhost' for table 'priv_test'

确实可以，不错不错。那么，是否会存在一种情况：对一个列有insert的权限，但没有select的权限？那么插入数据之后，还能不能看由自己插入的东西呢？

mysql> select * from priv_test;
+------+------+
| id   | id2  |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.03 sec)

mysql> revoke SELECT (`id`) ON `test`.`priv_test` from `sam`@`localhost`;
Query OK, 0 rows affected (0.10 sec)

mysql> show grants for sam@'localhost';
+----------------------------------------------------------------+
| Grants for sam@localhost                                       |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `sam`@`localhost`                        |
| GRANT INSERT (`id`) ON `test`.`priv_test` TO `sam`@`localhost` |
+----------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> exit
Bye
[root@linux-base ~]# mysql -usam -psam123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 72
Server version: 8.0.12 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> use test;
Database changed
mysql> insert into priv_test(id) values (2);
Query OK, 1 row affected (0.14 sec)

mysql> select id from priv_test;
ERROR 1142 (42000): SELECT command denied to user 'sam'@'localhost' for table 'priv_test'

可以看到，有insert没select，就会出现能插入，但无法查看数据。在权限授予上需要注意。
另外即使删除了表，用户拥有的权限也不会自动回收的，这也是一个安全隐患的地方。

mysql> show grants for sam@'localhost';
+----------------------------------------------------------------+
| Grants for sam@localhost                                       |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `sam`@`localhost`                        |
| GRANT INSERT (`id`) ON `test`.`priv_test` TO `sam`@`localhost` |
+----------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> drop table test.priv_test;
Query OK, 0 rows affected (0.14 sec)

mysql> desc test.priv_test;
ERROR 1146 (42S02): Table 'test.priv_test' doesn't exist
mysql> show grants for sam@'localhost';
+----------------------------------------------------------------+
| Grants for sam@localhost                                       |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `sam`@`localhost`                        |
| GRANT INSERT (`id`) ON `test`.`priv_test` TO `sam`@`localhost` |
+----------------------------------------------------------------+
2 rows in set (0.00 sec)