针对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)
验证
mysql> 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'