MySQL权限控制

对 MySQL 的权限的控制可以大致分为三个层面，即连接层面、数据库层面以及数据表层面，具体解释如下：

1. 连接层面，即连接上允不允许（是否能登录到 MySQL 服务器）。
2. 数据库层面上，即允许用户操作哪些数据库。
3. 数据表层面上，允许用户操作哪些表，并可以定义对表的操作权限：比如 insert，create，update 等。如果还觉得不够精细，MySQL 还可以精确到对某表某列控制操作权限。

MySQL权限认证

MySQL 中存在 4 个控制权限的表，权限表存放在 mysql 数据库里，由 mysql_install_db 脚本初始化。这些权限表分别 user，db，table_priv，columns_priv 和 host，它们的作用如下：

数据表	描述
user	记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
db	记录各个帐号在各个数据库上的操作权限。
table_priv	记录数据表级的操作权限。
columns_priv	记录数据列级的操作权限。
host	配合 db 权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受 GRANT 和 REVOKE 语句的影响。

mysql 权限表的验证过程为：

1. 先从 user 表中的 Host,User,Password 这 3 个字段中判断连接的 ip、用户名、密码是否存在，存在则通过验证。
2. 通过身份认证后，进行权限分配，按照 user，db，tables_priv，columns_priv 的顺序进行验证。即先检查全局权限表 user，如果 user 中对应的权限为 Y，则此用户对所有数据库的权限都为 Y，将不再检查 db, tables_priv,columns_priv；如果为 N，则到 db 表中检查此用户对应的具体数据库，并得到 db 中为 Y 的权限；如果 db 中为 N，则检查 tables_priv 中此数据库对应的具体表，取得表中的权限 Y，以此类推。

MySQL用户认证

MySQL 的用户认证形式是: 用户名+主机。比如 test@127.0.0.1 和 test@192.168.10.10 是不一样的用户，当然 test@127.0.0.1 和 test@localhost 其实也是不一样的用户。MySQL 中的权限分配都是分配到用户+主机的实体上。

MySQL 的主机信息可以是本地(localhost)，某个 IP，某个 IP 段，以及任何地方等，即用户的地址可以限制到某个具体的 IP，或者某个 IP 范围，或者任意地方。MySQL 用户分为普通用户和 root 用户。root 用户是超级管理员，拥有所有权限，普通用户只拥有被授予的各种权限。

MySQL访问控制流程

MySQL 访问控制分为两个阶段：

1. 用户连接检查阶段。
2. 执行 SQL 语句时检查阶段。

用户连接时的检查

1. 当用户连接时，MySQL 服务器首先从 user 表里匹配 host, user, password，匹配不到则拒绝该连接。
2. 接着检查 user 表的 max_connections 和 max_user_connections，如果超过上限则拒绝连接。
3. 检查 user 表的 SSL 安全连接，如果有配置SSL，则需确认用户提供的证书是否合法只有上面 3 个检查都通过后，服务器才建立连接，连接建立后，当用户执行SQL语句时，需要做 SQL 语句执行检查。

执行SQL语句时的检查

1. 从 user 表里检查 max_questions 和 max_updates，如果超过上限则拒绝执行 SQL 下面几步是进行权限检查。
2. 首先检查 user 表，看是否具有相应的全局性权限，如果有，则执行，没有则继续下一步检查。
3. 接着到 db 表，看是否具有数据库级别的权限，如果有，则执行，没有则继续下一步检查。
4. 最后到 tables_priv, columns_priv, procs_priv 表里查看是否具有相应对象的权限从以上的过程我们可以知道，MySQL 检查权限是一个比较复杂的过程，所以为了提高性能，MySQL 的启动时就会把这 5 张权限表加载到内存。

MySQL权限详解

全局层级

全局权限适用于一个给定 MySQL Server 中的所有数据库，这些权限存储在 mysql.user 表中：

# *.* 表示数据库库的所有库和表，对应权限存储在mysql.user表中
GRANT ALL ON *.* TO 'user'@'host';

数据库层级

数据库权限适用于一个给定数据库中的所有目标，这些权限存储在 mysql.db 表中：

# mydb.* 表示mysql数据库下的所有表，对应权限存储在mysql.db表中
GRANT ALL ON mydb.* TO 'user'@'host';

表层级

表权限适用于一个给定表中的所有列，这些权限存储在 mysql.tables_priv 表中：

# mydb.mytable 表示mysql数据库下的mytable表，对应权限存储在mysql.tables_priv表
GRANT ALL ON mydb.mytable TO 'user'@'host';

列层级

列权限使用于一个给定表中的单一列，这些权限存储在 mysql.columns_priv 表中：

# mydb.mytable 表示mysql数据库下的mytable表， col1, col2,  col3表示mytable表中的列名
GRANT ALL (col1， col2， col3)  ON mydb.mytable TO 'user'@'host'; 

子程序层级

CREATE ROUTINE、ALTER ROUTINE、EXECUTE 和 GRANT 权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且，除了 CREATE ROUTINE 外，这些权限可以被授予子程序层级，并存储在 mysql.procs_priv 表中：

# mydb.mytable 表示mysql数据库下的mytable表，PROCEDUR表示存储过程
GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'user'@'host';

MySQL权限操作

账户权限信息被存储在 MySQL 数据库的几张权限表中，在 MySQL 启动时，服务器将这些数据库表中权限信息的内容读入内存。其中 GRANT 和 REVOKE 语句所涉及的常用权限大致如下这些：CREATE、DROP、SELECT、INSERT、UPDATE、DELETE、INDEX、ALTER、CREATE、ROUTINE、FILE 等，还有一个特殊的 proxy 权限，是用来赋予某个用户具有给他人赋予权限的权限。

grant 所有权限：

mysql> grant all privileges on *.* to 'USERNAME'@'HOST';
mysql> flush privileges;

grant super 权限在*.*上(super 权限可以对全局变量更改)：

mysql> grant super on *.* to 'USERNAME'@'HOST';
mysql> flush privileges;

grant 某个库下所有表的所有权限：

mysql> grant all privileges on DB_NAME.* to 'USERNAME'@'HOST';
mysql>  flush privileges;

grant 某个库下所有表的 select 权限：

mysql> grant select on DB_NAME.* to 'USERNAME'@'HOST';
mysql> flush privileges;

grant 某个库下某个表的 insert 权限：

mysql> grant insert on  DB_NAME.TABLE_NAME to 'USERNAME'@'HOST';
mysql> flush privileges;

grant 某个库下某个表的 update 权限：

mysql> grant update on DB_NAME.TABLE_NAME to 'USERNAME'@'HOST';
mysql> flush privileges;

grant 某个库下某个表的某个字段 update 权限：

mysql> grant update(COLUMN_NAME) on DB_NAME.TABLE_NAME to 'USERNAME'@'HOST';
mysql> flush privileges;

通过 GRANT 语句中的 USAGE 权限，可以创建账户而不授予任何权限：

mysql> grant usage on *.* to 'USERNAME'@'HOST';
mysql> flush privileges;

grant 创建、修改、删除 MySQL 数据表结构权限：

mysql> grant create on testdb.* to developer@'192.168.0.%';
mysql> grant alter on testdb.* to developer@'192.168.0.%';
mysql> grant drop on testdb.* to developer@'192.168.0.%';
mysql> flush privileges;

grant 操作 MySQL 外键权限：

mysql> grant references on testdb.* to developer@'192.168.0.%';
mysql> flush privileges;

grant 操作 MySQL 临时表权限：

mysql> grant references on testdb.* to developer@'192.168.0.%';
mysql> flush privileges;

grant 操作 MySQL 索引权限：

mysql> grant index on testdb.* to developer@'192.168.0.%';
mysql> flush privileges;

grant 操作 MySQL 视图、查看视图源代码权限：

mysql> grant create view on testdb.* to developer@'192.168.0.%';
mysql> grant show view on testdb.* to developer@'192.168.0.%';
mysql> flush privileges;

grant 操作 MySQL 存储过程、存储函数权限：

mysql> grant create routine on testdb.* to developer@'192.168.0.%';
mysql> grant alter routine on testdb.* to developer@'192.168.0.%';
mysql> grant execute on testdb.* to developer@'192.168.0.%';
mysql> flush privileges;

查看用户的权限：

mysql> show grants for 'USERNAME'@'HOST';

移除用户权限：

# 移除tom用户对于db.xsb的权限;
Mysql> revoke all on db.xsb from 'tom'@'localhost';
# 刷新授权表;
Mysql> flush privileges;

权限管理经验

用户管理经验

1. 尽量使用 create user, grant 等语句，而不要直接修改权限表。

   虽然 create user, grant 等语句底层也是修改权限表，和直接修改权限表的效果是一样的，但是，对于非高手来说，采用封装好的语句肯定不会出错，而如果直接修改权限表，难免会漏掉某些表。而且，修改完权限表之后，还需要执行 flush privileges 重新加载到内存，否则不会生效。

2. 把匿名用户删除掉

   匿名用户没有密码，不但不安全，还会产生一些莫名其妙的问题，强烈建议删除。

权限管理经验

1. 只授予能满足需要的最小权限，防止用户干坏事。比如用户只是需要查询，那就只给 select 权限就可以了，不要给用户赋予 update、insert 或者 delete 权限。
2. 创建用户的时候限制用户的登录主机，一般是限制成指定 IP 或者内网 IP 段。
3. 初始化数据库的时候删除没有密码的用户。安装完数据库的时候会自动创建一些用户，这些用户默认没有密码。
4. 为每个用户设置满足密码复杂度的密码。
5. 定期清理不需要的用户，回收权限或者删除用户。