mysql针对表的字段级别的权限管理和授权

本文介绍mysql针对表的字段级别的权限管理和授权，并举例进行详细说明。

针对Mike账号 db1库下面的t1表的 id，name字段授予select权限，age字段授予update权限
授权格式 select(要授权的字段，要授权的字段) 用户括号 括起来  、update()

mysql> grant select(id,name),update(age) on db1.t1 to 'mike'@'localhost';
Query OK, 0 rows affected (0.11 sec)

授权的记录操作过程：

mysql> select * from mysql.columns_priv;
+-----------+-----+------+------------+-------------+---------------------+-------------+
| Host      | Db  | User | Table_name | Column_name | Timestamp           | Column_priv |
+-----------+-----+------+------------+-------------+---------------------+-------------+
| localhost | db1 | mike | t1         | id          | 0000-00-00 00:00:00 | Select      |
| localhost | db1 | mike | t1         | name        | 0000-00-00 00:00:00 | Select      |
| localhost | db1 | mike | t1         | age         | 0000-00-00 00:00:00 | Update      |
+-----------+-----+------+------------+-------------+---------------------+-------------+
3 rows in set (0.00 sec)

验证：

ysql> exit
Bye

[root@mysql ~]# mysql -umike -p123
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 10
Server version: 5.6.36 Source distribution

Copyright (c) 2000, 2017, 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 db1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)

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


mysql> select id,name from t1;
+------+------+
| id   | name |
+------+------+
|    1 | mike |
|    2 | alex |
|    3 | NULL |
|    4 | NULL |
+------+------+
4 rows in set (0.00 sec)

*代表所有字段

只能查看t1表中的 id，name字段
不能查age字段 但可以用update age字段

mysql> select age from t1;
ERROR 1143 (42000): SELECT command denied to user 'mike'@'localhost' for column 'age' in table 't1'