MySQL权限管理

MySQL的权限管理重要性等同于服务器数据的重要性, 权限体系如果建立的不到位的话, 也就意味着生产数据处于危险状态。

1. Ubuntu 16.04 下安装MySQL

sudo apt-get update
sudo apt-get upgrade
sudo apt-get install mysql-server mysql-client

2. 修改相关的配置文件

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

bind-address = 0.0.0.0
max_connections = 3000

# binlog相关配置
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M

# 慢查询日志配置
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2

编辑并保存后重启MySQL服务即可。另外需要在系统层面赋予MySQL最大文件描述符的配置:

sudo vim /lib/systemd/system/mysql.service

# 添加
LimitNOFILE=65535
LimitNPROC=65535

3. 管理权限体系的建立

通常MySQL的权限分级与开发团队的组织形式直接相关, 包括初级工程师(只能select部分database), 中级工程师(能够对数据进行增删改查), 高级开发工程师(全部权限), 但是在现代的web开发, 使用ORM框架的条件下, 对应用的账号则必须赋予较高的权限, 那么能够查看代码的开发人员依然能够拿到较高权限。 所以MySQL的权限控制需要和Linux权限控制相结合: 涉及生产服务器MySQL数据库连接代码应只存放于服务器中, 并对其进行高权限控制, 即该文件只能由高级开发工程师进行查看或者修改, 以保证权限的统一。

3.1 创建用户并赋予权限

创建用户使用GRANT命令, 常见参数及说明如下表所示:

GRANT ALL PRIVILEGES ON database.table TO user@address IDENTIFIED BY ‘password’
创建用户命令 赋予什么权限 哪个数据库以及哪张表 用户名和登录地址 密码

创建smart用户, 并赋予其全部数据库的全部权限, 但只能在ip地址为144.35.177.20的机器上进行登录

mysql> GRANT ALL PRIVILEGES ON *.* TO 'smart'@'144.35.177.20' IDENTIFIED BY 'passwd';
mysql> FLUSH PRIVILEGES;

mysql> select * from mysql.user where User="smart" \G;
*************************** 1. row ***************************
                  Host: 144.35.177.20
                  User: smart
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
          ...  中间省略
1 row in set (0.00 sec)

在赋予ALL PRIVILEGES之后该用户除了不能创建用户以外, 用于对数据库的所有控制权限。 这样的账号应该只有运维经理以及高级开发才能拥有。

下面来看一下MySQL具体都有哪些权限:

维度 权限
数据 insert, select, update, delete
create, drop, references, index, alter, lock table, create temporary table
主从复制 replication client, replication slave
服务器 shutdown, processlist, grant, super, create role

该表简单的对权限进行了一个分类, 作用仅是便于记忆而已。 根据MySQL官方文档所给出的权限以及每种权限的作用, 下面进行详细的整理。

  • ALTER: 允许更改表结构, 例如添加索引, 删除字段等。 ALTER TABLE语句需要有CREATE以及INSERT权限。 当我们对一个表进行重命名的时候, 需要有ALTER和DROP以及CREATE, INSERT的权限。
  • ALTER ROUTINE: 使用ALTER存储过程的权限。
  • CREATE: 创建表的权限。
  • CREATE ROLE: 创建角色的权限, 当一个用户拥有了CREATE USER权限时, 该权限随即拥有。
  • CREATE ROUTINE: 允许使用CREATE存储过程。
  • CREATE TEMPORARY TABLES: 允许使用CREATE TEMPORARY TABLE语句来创建临时表, 这张临时表的 DROP TABLE, INSERT, UPDATE等权限随之赋予。
  • CREATE USER: 能够使用 ALTER USER, CREATE ROLE, CREATE USER, DROP ROLE, DROP USER, RENAME USER, 以及REVOKE ALL PRIVILEGES等语句, 属于管理员权限。
  • CREATE VIEW: 允许创建视图。
  • DELETE: 允许从表中删除数据。
  • DROP: 删除整张表以及数据的权限, TRUNCATE TABLE命令需要有该权限才能够执行。
  • DROP ROLE: 允许删除某一个用户, 当用户具有了CREATE USER的权限时, 该权限随之赋予。
  • GRANT OPTION: 允许为用户添加权限。
  • INDEX: 允许添加和删除索引, 当用于具有CREATE的权限时, 该权限随之赋予。
  • INSERT: 向表中插入数据的权限。
  • LOCK TABLES: 锁表的权限。
  • PROCESS: 允许查询当前数据库所运行的后台线程信息, 例如主从复制线程信息。
  • RELOAD: 允许运行flush-xxx相关命令, 包括刷新权限, 日志等, 属于管理员权限。
  • REPLICATION CLIENT: 允许使用SHOW MASTER STATUS, SHOW SLAVE STATUS, SHOW BINARY LOGS等语句, 主要用于主从复制。
  • REPLICATION SLAVE: 允许更新主库的变化。
  • SUPER: 这是一个相当重要的权限, 权限非常大, 并且MySQL在将来的版本中将会移除这个权限, 的确, 该权限比较危险。 SUPER权限能够在运行时修改系统变量, 该更主从复制相关信息, 以及更重要的, 该权限可以在MySQL Session中删除binlog日志文件, 即purge master logs命令。
  • UPDATE: 更新表数据的权限。

可以看到, 比较危险的权限也就是DROP, CREATE USER, GRANT OPTION, RELOAD以及SUPER权限, 其中最危险的就是SUPER权限, 除了管理员以外, 任何账号均不能有SUPER权限

3.1.1 创建mysqldump账户并赋予权限

mysqldump命令的权限讲实话我并不清楚, 查阅网上相关资料之后进行创建并赋予:

create user 'dumper'@'localhost' identified by 'passwd';
grant select on myProjects.* to 'dumper'@'localhost';
grant show view on myProjects.* to 'dumper'@'localhost';
grant lock tables on myProjects.* to 'dumper'@'localhost';
grant trigger on myProjects.* to 'dumper'@'localhost';
// 如果需要在备份时刷新二进制日志, 还需要以下权限
grant reload on myProjects.* to 'dumper'@'localhost';
grant replication slave, replication client on myProjects.* to 'dumper'@'localhost';

那么此时使用该账户进行登录, 进行查看myProjects库, 其余的增删改操作均不能进行。

3.1.2 创建主从复制的账号并赋予权限
grant replication slave, replication client on *.* to repl@'192.168.0.%' dentified by 'passwd';

这里使用%通配符来对该局域网内的所有机器进行权限的赋予。

3.1.3 创建项目账号并赋予权限
grant all privileges on myProjects.* to 'shop'@'192.168.1.6' identified by 'complex-passwd';

3.2 查看某个用户的权限

show grants for 'user'@'address';

3.3 回收用户权限以及删除用户

回收用户所有权限

revoke all on *.* from 'user'@'address';

回收用户部分权限

revoke drop on myProjects.* from 'user'@'address';

删除用户

drop user 'user'@'address';

修改某个用户的密码

update mysql.user set authentication_string=password("passwd") where user="user";

在执行完上面儿的语句之后, 尽量的执行flush privileges;命令刷新一下权限。

4. 关于权限管理的一些杂谈

MySQL并没有提供不给用户授予什么权限的命令, 也就是没有exclude这种语法, 但是我们可以先给用户授予全部的权限, 然后将不必要的权限进行回收。 像drop这种很危险的权限就不要随便给, 如果库里面儿只有逻辑删除的话, delete权限都可以不给, 有需要的时候让管理员进行协助处理。 尽可能的用最小权限做更安全的事情, 毕竟使用binlog进行数据恢复也不可能保证100%成功, 将危险扼杀在摇篮里才是正解。

权限管理对于管理员来讲确实是比较麻烦的一件事情, 很多团队root账号满天跑, 包括笔者在内的团队在初期也是这样的。 在付出了血淋淋的代价之后才开始对权限进行管理, 亡羊补牢为时尚晚, 埋过的雷总会炸的。

smartkeyerror

日拱一卒,功不唐捐