对 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 权限表的验证过程为:
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 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;
权限管理经验
用户管理经验
尽量使用 create user, grant 等语句,而不要直接修改权限表。
虽然 create user, grant 等语句底层也是修改权限表,和直接修改权限表的效果是一样的,但是,对于非高手来说,采用封装好的语句肯定不会出错,而如果直接修改权限表,难免会漏掉某些表。而且,修改完权限表之后,还需要执行 flush privileges 重新加载到内存,否则不会生效。
把匿名用户删除掉
匿名用户没有密码,不但不安全,还会产生一些莫名其妙的问题,强烈建议删除。
权限管理经验
- 只授予能满足需要的最小权限,防止用户干坏事。比如用户只是需要查询,那就只给 select 权限就可以了,不要给用户赋予 update、insert 或者 delete 权限。
- 创建用户的时候限制用户的登录主机,一般是限制成指定 IP 或者内网 IP 段。
- 初始化数据库的时候删除没有密码的用户。安装完数据库的时候会自动创建一些用户,这些用户默认没有密码。
- 为每个用户设置满足密码复杂度的密码。
- 定期清理不需要的用户,回收权限或者删除用户。