嗨客网搜索
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. 定期清理不需要的用户,回收权限或者删除用户。

赞助商链接:


嗨客网顶部