一、 全局权限,作用于整个 MySQL 实例：mysql.user表

mysql> select Host,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv , Drop_priv from mysql.user;
+-------------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+
| Host        | User             | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv |
+-------------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+
| %           | lang             | Y           | Y           | Y           | Y           | N           | N         |
| 192.168.2.% | lang             | Y           | Y           | Y           | Y           | N           | N         |
| localhost   | mysql.infoschema | Y           | N           | N           | N           | N           | N         |
| localhost   | mysql.session    | N           | N           | N           | N           | N           | N         |
| localhost   | mysql.sys        | N           | N           | N           | N           | N           | N         |
| localhost   | root             | Y           | Y           | Y           | Y           | Y           | Y         |
+-------------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+
6 rows in set (0.00 sec)

二、 库级别的权限定义：mysql.db表

某个账号对某个库所拥有的权限，如下第一行结果表示用户’lang’对库’lang_test’所拥有的权限

mysql> select Host, Db,User, Select_priv,Insert_priv,Update_priv,Delete_priv from mysql.db;
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+
| Host      | Db                 | User          | Select_priv | Insert_priv | Update_priv | Delete_priv |
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+
| %         | lang_test          | lang          | Y           | Y           | Y           | Y           |
| localhost | performance_schema | mysql.session | Y           | N           | N           | N           |
| localhost | sys                | mysql.sys     | N           | N           | N           | N           |
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+
3 rows in set (0.00 sec)

三、 更细粒度的权限：mysql.tables_priv（表权限）、mysql.columns_priv（列权限）

如下结果表示用户’lang’对t、t2表所有拥有的表权限（包括列权限）

mysql> select Host, Db, User, Table_name,Table_priv,Column_priv from mysql.tables_priv where User='lang';
+------+-----------+------+------------+----------------------------------------------------------------------------------------------------+---------------+
| Host | Db        | User | Table_name | Table_priv                                                                                         | Column_priv   |
+------+-----------+------+------------+----------------------------------------------------------------------------------------------------+---------------+
| %    | lang_test | lang | t          | Select,Insert,Update,Delete,Create,Drop,Grant,References,Index,Alter,Create View,Show view,Trigger | Select,Insert |
| %    | lang_test | lang | t2         | Select,Insert,Update,Delete,Create,Drop,Grant                                                      |
|
+------+-----------+------+------------+----------------------------------------------------------------------------------------------------+---------------+
2 rows in set (0.00 sec)

查看详细的列权限

mysql> select * from mysql.columns_priv;
+------+-----------+------+------------+-------------+---------------------+---------------+
| Host | Db        | User | Table_name | Column_name | Timestamp           | Column_priv   |
+------+-----------+------+------------+-------------+---------------------+---------------+
| %    | lang_test | lang | t          | c           | 0000-00-00 00:00:00 | Insert        |
| %    | lang_test | lang | t          | id          | 0000-00-00 00:00:00 | Select,Insert |
+------+-----------+------+------------+-------------+---------------------+---------------+
2 rows in set (0.00 sec)

四、 授权操作：

1、 创建相应账号

create user 'username1'@'%' identified by 'passwd';

2、 赋予权限

所有库、表的全部权限
grant all privileges on *.* to 'username1'@'%' with grant option;

某个库某个表的全部权限
grant all privileges on lang_test.t to 'username1'@'%' with grant option;

某个库某个表的读、插入、更新、删除权限
grant select,insert,update,delete on lang_test.t to 'username1'@'%' with grant option;

创建表t
CREATE TABLE `lang_test`.`t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

某个表的某些列的权限
GRANT SELECT(id), INSERT(id,c) ON lang_test.t TO 'lang'@'%' with grant option;

grant的具体权限官网地址：https://dev.mysql.com/doc/refman/5.7/en/grant.html