MySQL数据库安全策略

工作时配置数据库的安全策略写一下攻略以备不时之需

一、 数据库口令
二、 数据库登录失败处理功能
三、 数据库账号分权
四、 数据库审计策略
五、 数据备份

一、数据库口令

设置为强制90天更改,并设置复杂度,密码要求大小写数字特殊字符,长度在10位以上,运维需要定期修改密码。

1.数据库设置定时修改密码5.6.6版本

MySQL版本5.6.6版本起,添加了password_expired功能,它允许设置用户的过期时间。
MySQL 5.7.4版开始,用户的密码过期时间这个特性得以改进,可以通过一个全局变量default_password_lifetime来设置密码过期的策略,MySQL会从启动时开始计算时间,此全局变量可以设置一个全局的自动密码过期策略。

//5.6.6可以设置为默认密码过期时间为90天
mysql> SET GLOBAL default_password_lifetime = 90;

##查看系统配置
mysql> 	

##让用户使用默认的密码过期全局策略:
mysql> ALTER USER 'username'@'localhost' PASSWORD EXPIRE DEFAULT;

##还可以为每个具体的用户账户单独设置特定的值,它会自动覆盖密码过期的全局策略。
要注意ALTER USER语句的INTERVAL的单位是“天”。5.7.4
mysql> ALTER USER 'username'@'localhost' PASSWORD EXPIRE INTERVAL 30 DAY;

##禁用密码过期:
mysql> ALTER USER 'username'@'localhost' PASSWORD EXPIRE NEVER;

通过上述语句设置MySQL账户到期,password_expired选择被设置为“Y”,此时这个账户还可以登录到MySQL服务器,但是此时不能运行任何查询操作,显示如下错误提示:

mysql> SHOW DATABASES;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
Keep in mind that this does not affect any current connections the account has 	open.

2.重新设置新密码

错误提示是提示用户设置新的密码,设置了新密码后,该用户的所有操作(根据用户自身的权限)会被允许执行:

##重新设置密码:
mysql> set password = password('新密码');

注:修改密码验证策略

set global validate_password_policy=0; //不验证格式
set global validate_password_length=4; //长度为4

3.利用crontab实现MySql定时任务实现密码自动到期(任意版本)

##新建名为mysql_pd_time的文件
$: vi mysql_pd_time

#写入以下内容
/usr/local/mysql/bin/mysql -u root -p 123456 dbname -e "truncate table test.student"

mysql工具的绝对路径
-u 用户名
-p密码 dbname:数据库名
-e “要执行的mysql语句”

#将用户test密码改为过期
/usr/local/mysql/bin/mysql -uroot -p123456 mysql -e "update mysql.user set password_expired='Y' where User='test'"
#修改/etc/crontab文件
#在文件中添加如下语句,这里每月15号执行一次,每次执行时间于在0点0分,

  0  0 15 * *  root  /data/cronclear/mysql_pd_time

#删除功能
#修改/etc/crontab文件,去掉添加的内容,删除上述创建的文件。
#注:*/,,hp linux不支持这种写法

#查询数据库用户是否锁定过期
select User,Password,password_expired from mysql.user;

mysql工具的绝对路径查找,如下 查找安装路径: whereis mysql 查找mysql bin工具
假设安装路径/usr/local/mysql cd /usr/local/mysql find . -name mysql

二、数据库登录失败处理功能

启用登录失败处理功能,可采取结束会话、限制非法登录次数等,启用登录连接超时及自动退出功能。

1.使用插件实现
CONNECTION_CONTROL
CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS

##安装插件:在mysql里
mysql> install plugin CONNECTION_CONTROL soname 'connection_control.so';
mysql> install plugin CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS soname 'connection_control.so';

##查询是否安装成功
mysql> show variables like '%connection_control%';

//设置失败次数为5次
mysql> SET GLOBAL connection_control_failed_connections_threshold = 5;

##失败五次后将不会出现输入密码框 一段时间后才可以继续输入

2.插件参数说明

connection_control_failed_connections_threshold :
失败登陆次数达到此值后触发延迟。值域:[0, INT_MAX32(2147483647)],0表示关闭此功能。默认值为3。

connection_control_max_connection_delay :
登陆发生延迟时,延迟的最大时间;此值必须大于等于connection_control_min_connection_delay值域:[1,INT_MAX32(2147483647)]。默认值:INT_MAX32。单位:毫秒。

connection_control_min_connection_delay :
登陆发生延迟时,延迟的最小时间,此值必须小于等于connection_control_max_connection_delay。值域:[1000,
INT_MAX32(2147483647)]。默认值:1000。单位:毫秒。

三、数据库账号分权
服务器账号分权处理,禁用或删除默认账户。

1.创建用户

#创建新用户
mysql> CREATE USER 'username'@'host' IDENTIFIED BY 'password';

说明:
username - 你将创建的用户名
host - 指定该用户在哪个主机上可以登陆如果是本地用户可用localhost, 如果想让该用户可以从任意远程主机登陆,可以使用通配符%
password - 该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器。

#例:
#指定IP:192.168.43.144的Sroxi用户登录
create user 'Srox'@'192.168.43.144' identified by '123';


# 指定IP:192.162.43.开头的Sroxi用户登录
create user 'Sroxi'@'192.118.1.%' identified by '123';

# 指定任何IP的Sroxi用户登录
create user 'Sroxi'@'%' identified by '123';

2.MySQL 用户授权

MySQL 中存在4个控制权限的表:

1.user表
2.db表
3.tables_priv表
4.columns_priv表

MySQL 权限表的验证过程为:
先从user表中的Host,User,Password这3个字段中判断连接的ip、用户名、密码是否存在,存在则通过验证。
通过身份认证后,进行权限分配,按照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实例级别
数据库级别的权限: 作用于某个指定的数据库上或者所有的数据库上
数据库对象级别的权限:作用于指定的数据库对象上(表、视图等)或者所有的数据库对象上
权限存储在mysql库的user, db, tables_priv, columns_priv, and procs_priv这几个系统表中,待MySQL实例启动后就加载到内存中
用户权限管理主要有以下作用:

  1. 可以限制用户访问哪些库、哪些表
  2. 可以限制用户对哪些表执行SELECT、CREATE、DELETE、DELETE、ALTER等操作
  3. 可以限制用户登录的IP或域名
  4. 可以限制用户自己的权限是否可以授权给别的用户
#例:
mysql> grant all privileges on *.* to 'yangxin'@'%' identified by 'yangxin123456' with grant option;
all privileges:
#表示将所有权限授予给用户。也可指定具体的权限,如:SELECT、CREATE、DROP等。

on:
表示这些权限对哪些数据库和表生效,格式:数据库名.表名,这里写“*”表示所有数据库,所有表。如果我要指定将权限应用到test库的user表中,可以这么写:test.user
to:
将权限授予哪个用户。格式:”用户名”@”登录IP或域名”。%表示没有限制,在任何主机都可以登录。比如:”yangxin”@”192.168.0.%”,表示yangxin这个用户只能在192.168.0IP段登录
identified by: 指定用户的登录密码 with grant option: 表示允许用户将自己的权限授权给其它用户

授予user用户在所有数据库上的所有权限。

mysql> GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' IDENTIFIED BY '123456';
#授权格式
grant 权限 on 数据库.表名 to 账户@主机名            对特定数据库中的特定表授权
grant 权限 on 数据库.* to 账户@主机名              对特定数据库中的所有表给与授权
grant 权限1,权限2,权限3 on *.* to 账户@主机名      对所有库中的所有表给与多个授权
grant all privileges on *.* to 账户@主机名      对所有库和所有表授权所有权限

#指定该用户只能执行 select和update命令:
mysql> GRANT SELECT, UPDATE ON dbname.* TO 'user'@'localhost' IDENTIFIED BY '123456';

#撤销刚才的授权 EVOKE:删除权限
mysql> REVOKE ALL PRIVILEGES ON *.* FROM 'user'@'localhost';

#调整权限后刷新权限
mysql> FLUSH PRIVILEGES;

#检测数据库中的用户
mysql> select user,host from mysql.user;

#检测用户权限
mysql> show grants for user@host;

3.MySQL 权限详解

All/All Privileges权限代表全局或者全数据库对象级别的所有权限
Alter权限代表允许修改表结构的权限,但必须要求有create和insert权限配合。如果是rename表名,则要求有alter和drop原表,
create和insert新表的权限 Alter routine权限代表允许修改或者删除存储过程、函数的权限
Create权限代表允许创建新的数据库和表的权限 Create routine权限代表允许创建存储过程、函数的权限 Create
tablespace权限代表允许创建、修改、删除表空间和日志组的权限 Create temporary
tables权限代表允许创建临时表的权限 Create user权限代表允许创建、修改、删除、重命名user的权限 Create
view权限代表允许创建视图的权限 Delete权限代表允许删除行数据的权限
Drop权限代表允许删除数据库、表、视图的权限,包括truncate table命令
Event权限代表允许查询,创建,修改,删除MySQL事件 Execute权限代表允许执行存储过程和函数的权限
File权限代表允许在MySQL可以访问的目录进行读写磁盘文件操作,可使用的命令包括load data infile,select …into outfile,load file()函数
Grant option权限代表是否允许此用户授权或者收回给其他用户你给予的权限,重新付给管理员的时候需要加上这个权限
Index权限代表是否允许创建和删除索引 Insert权限代表是否允许在表里插入数据,同时在执行analyze table,optimize
table,repair table语句的时候也需要insert权限
Lock权限代表允许对拥有select权限的表进行锁定,以防止其他链接对此表的读或写
Process权限代表允许查看MySQL中的进程信息,比如执行show processlist, mysqladminprocesslist, show engine等命令
Reference权限是在5.7.6版本之后引入,代表是否允许创建外键
Reload权限代表允许执行flush命令,指明重新加载权限表到系统内存中,refresh命令代表关闭和重新开启日志文件并刷新所有的表
Replication client权限代表允许执行show master status,show slave status,showbinary logs命令
Replication slave权限代表允许slave主机通过此用户连接master以便建立主从复制关系
Select权限代表允许从表中查看数据,某些不查询表数据的select执行则不需要此权限,如Select 1+1, Select PI()+2;而且select权限在执行update/delete语句中含有where条件的情况下也是需要的
Show databases权限代表通过执行show databases命令查看所有的数据库名 Show view权限代表通过执行show create view命令查看视图创建的语句
Shutdown权限代表允许关闭数据库实例,执行语句包括mysqladmin shutdown
Super权限代表允许执行一系列数据库管理命令,包括kill强制关闭某个连接命令, change master to创建复制关系命令,以及create/alter/drop server等命令
Trigger权限代表允许创建,删除,执行,显示触发器的权限 Update权限代表允许修改表中的数据的权限
Usage权限是创建一个用户之后的默认权限,其本身代表连接登录权限

四、数据库审计策略

结合数据库性能,开启相应审计策略,如关闭select的审计,需进行异地备份审计日志。

1.通过init-connect创建审计库表 配合bin log

bin log二进制日志:录所有更改数据的语句,可用于数据复制。
缺点:只对有低级权限的用户的操作有记录,权限高的则没有 。优点:日志信息比较小,对性能影响小

#创建审计用的库表
mysql> create database db_monitor;
mysql> use db_monitor;
mysql> CREATE TABLE accesslog
-> ( thread_id int(11) DEFAULT NULL,  #进程id
->  log_time datetime default null,  #登录时间
->  localname varchar(50) DEFAULT NULL, #登录名称,带详细ip
->  matchname varchar(50) DEFAULT NULL, #登录用户
->  key idx_log_time(log_time));

#配置init-connect参数
mysql> set global init_connect='insert into
-> db_monitor.accesslog(thread_id,log_time,localname,matchname)
-> values(connection_id(),now(),user(),current_user())';
mysql>flush privileges;

#授予普通用户对accesslog表的insert权限(在root用户下)
mysql> create user demon@'%';
mysql> grant insert on db_monitor.accesslog to demon@'%'; #无密码验证可以用
mysql> grant insert on db_monitor.accesslog to usertext@'%' identified by '$.Mysql123';

grant命令 设置指定用户管理指定库 以及 用户权限 具体详情请看账号分权
#调整权限后刷新权限
mysql> FLUSH PRIVILEGES;

2.通过MariaDB审计插件版本

Mysql版本为5.6.51

MariaDB审计插件版本 :1.4.0

mariadb-10.1.23-linux-x86_64.tar.gz 安装介绍论坛:
MySQL安装审计audit

#查看插件配置文件路径
mysql->  show variables like 'plugin_dir';

#将server_audit.so 复制到mysql/plugin里
#1.记得查看文件权限是否为可执行 2.如果下载为tar包server_audit.so在lib/plugin下

#执行安装
mysql->  INSTALL PLUGIN server_audit SONAME 'server_audit.so'
mysql> show plugins ;

MySQL5.7.18以上版本有自带审计
MariaDB_5.x.x:兼容MySQL5.x.x的,接口几乎一致,只限于社区版
MariaDB_10.x.x:10.x.x使用新技术,接口会与mysql逐渐区别开来。
若没有的下载mariadb-5.5.56-linux-x86_64.tar.gz 跟上一步一样

#查询审计配置情况
show global variables like 'log_timestamps';
show global variables like '%general%'

#永久开启审计

vim /etc/my.cnf
#在 /etc/my.cnf 中添加下述配置
[mysqld]
general_log = on                                 // on为开启;off为关闭
general_log_file = /var/log/generalLog.log       // 审计信息存储位置
log_timestamps = SYSTEM                          // 设置日志文件的输出时间为地方时间

#临时开启审计
在mysql中执行以下命令
set global general_log = on;
set global general_log_file = /var/log/generalLog.log
set global log_timestamps = SYSTEM;

3.审计配置

审计配置即修改参数信息即可,需要注意的是最好将其放入MySQL的参数文件(my.cnf),如果仅在数据库层面做修改,数据库重启后参数将失效。
官网配置说明:点击链接跳转

mysql> show variables like '%audit%' ;
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| server_audit_events           |                       |
| server_audit_excl_users       |                       |
| server_audit_file_path        | server_audit.log      |
| server_audit_file_rotate_now  | OFF                   |
| server_audit_file_rotate_size | 1000000               |
| server_audit_file_rotations   | 9                     |
| server_audit_incl_users       |                       |
| server_audit_loc_info         |                       |
| server_audit_logging          | OFF                   |
| server_audit_mode             | 1                     |
| server_audit_output_type      | file                  |
| server_audit_query_log_limit  | 1024                  |
| server_audit_syslog_facility  | LOG_USER              |
| server_audit_syslog_ident     | mysql-server_auditing |
| server_audit_syslog_info      |                       |
| server_audit_syslog_priority  | LOG_INFO              |
+-------------------------------+-----------------------+
server_audit_output_type:指定日志输出类型,可为SYSLOG或FILE
 Commandline: --server-audit-output-type=value
 Scope(作用范围): Global
 Dynamic(值是否动态): Yes
 Data Type: enum(枚举类型)
 Default Value: file
 Valid Values: SYSLOG or FILEserver_audit_logging:启动或关闭审计
 Commandline: --server-audit-logging[={0|1}]
 Scope: Global
 Dynamic: Yes
 Data Type: boolean
 Default Value: OFFserver_audit_events:指定记录事件的类型,可以用逗号分隔的多个值(connect,query,table),如果开启了查询缓存(query cache),查询直接从查询缓存返回数据,将没有table记录
 Commandline: --server-audit-events=value
 Scope: Global
 Dynamic: Yes
 Data Type: string
 Default Value: Empty string
 Valid Values: CONNECT, QUERY and TABLE (QUERY_DDL, QUERY_DML added in 1.2.0 and QUERY_DCL added in 1.3.0, QUERY_DML_NO_SELECT added in 1.4)
 server_audit_logging:启动或关闭审计
 server_audit_file_path:如server_audit_output_type为FILE,使用该变量设置存储日志的文件,可以指定目录,默认存放在数据目录的server_audit.log文件中
 Commandline: --server-audit-file-path=value
 Scope: Global
 Dynamic: Yes
 Data Type: string
 Default Value: server_audit.logserver_audit_file_rotate_size:限制日志文件的大小
 Commandline: --server-audit-rotate-size=#
 Scope: Global
 Dynamic: Yes
 Data Type: numeric
 Default Value: 1000000server_audit_file_rotations:指定日志文件的数量,如果为0日志将从不轮转
 Commandline: --server-audit-rotations=#
 Scope: Global
 Dynamic: Yes
 Data Type: numeric
 Default Value: 9
 Range: 0 to 999server_audit_file_rotate_now:强制日志文件轮转
 Commandline: --server-audit-rotate-now[={0|1}]
 Scope: Global
 Dynamic: Yes
 Data Type: boolean
 Default Value: OFFserver_audit_incl_users:指定哪些用户的活动将记录,connect将不受此变量影响,该变量比server_audit_excl_users优先级高
 Commandline: --server-audit-incl-users=value
 Scope: Global
 Dynamic: Yes
 Data Type: string
 Default Value: Empty stringserver_audit_syslog_facility:默认为LOG_USER,指定facility,其实这里的意思当为SYSLOG文件变量时,给记录定义一个“设施”记录到这个syslog中,可以用这个facility参数来过滤log。
 Commandline: --server-audit-syslog-facility=value
 Scope: Global
 Dynamic: Yes
 Data Type: enum
 Default Value: LOG_USER
 Valid Values: LOG_USER, LOG_MAIL, LOG_DAEMON, LOG_AUTH, LOG_SYSLOG, LOG_LPR, LOG_NEWS, LOG_UUCP, LOG_CRON, LOG_AUTHPRIV, LOG_FTP, and LOG_LOCAL0–LOG_LOCAL7server_audit_syslog_ident:设置ident,作为每个syslog记录的一部分,更改新的值后,需要重启这个审计功能
 Commandline: --server-audit-syslog-ident=value
 Scope: Global
 Dynamic: Yes
 Data Type: string
 Default Value: mysql-server_auditingserver_audit_syslog_info:指定的info字符串将添加到syslog记录,随时可更改
 Commandline: --server-audit-syslog-info=value
 Scope: Global
 Dynamic: Yes
 Data Type: string
 Default Value: Empty stringserver_audit_syslog_priority:定义记录日志的syslogd priority优先级
 Commandline: --server-audit-syslog-priority=value
 Scope: Global
 Dynamic: Yes
 Data Type: enum
 Default Value: LOG_INFO
 Valid Values:LOG_EMERG, LOG_ALERT, LOG_CRIT, LOG_ERR, LOG_WARNING, LOG_NOTICE, LOG_INFO, LOG_DEBUGserver_audit_excl_users:该列表的用户行为将不记录,connect将不受该设置影响
 Commandline: --server-audit-excl-users=value
 Scope: Global
 Dynamic: Yes
 Data Type: string
 Default Value: Empty stringserver_audit_mode:标识版本,用于开发测试
 Commandline: --server-audit-mode[=#]

可用shell脚本的方式,根据自己数据库审计活动配置相关参数到/etc/mysql目录my.cnf配置文件中,但是必须重启mysql服务s

ervice mysql restart
 例如:
 server_audit_logging=on
 server_audit_events=‘CONNECT,QUERY,TABLE’
 server_audit_file_path =/data/
 server_audit_file_rotate_size=2G
 server_audit_file_rotations=30

五、数据备份

至少要有本地备份,有条件进行异地备份,需要异地的服务器或对象存储oss的支持

1.MySQL语句备份数据库

备份的语句mysqldump的基本语法: 网页链接(其他格式.xml等)

备份表结构
mysqldump -u username -p dbname table1 table2...->BackupName.sql;
#备份数据库的所有表结构
mysqldump -u root -p -d dbname > b.sql
#备份多个数据库的所有表结构
mysqldump -u root -p -d --databases db1 db2... > c.sql
#备份所有数据库的表结构
mysqldump -u root -p -d --all-databases > d.sql

备份结构和数据(相当于在备份结构的语法上去掉-d选项)
#备份表结构和数据
mysqldump -u root -p dbname table1 table2 ... > a.sql
#备份数据库的所有表结构和数据
mysqldump -u root -p dbname  > b.sql
#备份多个数据库的表结构和数据
mysqldump -u root -p --databases db1 db2  > c.sql
#备份所有数据库的表结构和数据
mysqldump -u root -p --all-databases > d.sql

-u:
数据库管理员账号 dbname: 要备份数据库的名称;
table1和table2:
参数表示的是需要备份的数据库表的名称,假如为空则表示需要备份整个数据库;
BackupName.sql:
表示的是将数据库备份到指定的这个以后缀名字.sql的文件中,这个文件的前面可以执行一个详细的绝对路径下;默认为当前文件夹下;

#备份表数据 (select ... into outfile)查询导出
select [列名] from table [where 语句] into outfile '目标文件' [options]

目标文件必须是字符串形式
options是设置导出文件的一些格式;
fields terminated by '字符串':多个字段值之间的分隔符,默认是'\t'
fields enclosed by '字符串':用给定的字符来包围字段值,默认不需要
fields optionally enclosed by '字符串':用给定的字符包围文本类型的字段值,默认不需要
fields escaped by '字符串':设置转义字符,默认为"\"
lines starting by '字符串':每条记录的开始字符,默认没有
lines terminated by '字符串':用给定的字符包围文本
#例:
mysql> select * from tb_name
> where 1
> into outfile '/data/tb_out/data.txt'  #导出
> fields terminated by '|' enclosed by '"' #多个字段值之间的分隔符
> optionally enclosed by '@' #用给定的字符包围文本
> lines starting by '^' terminated by '$' ; #每条记录的开始字符
结果:
  ^1|900|@计算机@|90$^2|901|@英语@|80$^3|902|@计算机@|70$^4|903|@中文@|30$
#例2
mysql> select * from tb_name
> where 1 
> into outfile '/data/tb_out/data.txt'
> fields terminated by 'o' 
> terminated by 'p';
结果:
1p900p计算机p90
2p901p英语p80
3p902p计算机p70
4p903p中文p30
#例三
mysql> select * from tb_name
> where 1
> into outfile '/data/tb_out/a.txt' 
> fields enclosed by '[' 
> optionally enclosed by '|';
结果:
1 900 |计算机| 90
2 901 |英语| 80
3 902 |计算机| 70
4 903 |中文| 30

当enclosed by 后面又设置optionally enclosed by时,则前面的enclosed by 无效

2.shell脚本自动备份
在linux中,通常使用BASH脚本对需要执行的内容进行编写,加上定时执行命令crontab实现日志自动化生成。
以下代码功能就是针对mysql进行备份,配合crontab,实现备份的内容为近一个月(31天)内的每天的mysql数据库记录。

#!/bin/bash
#保存备份个数,备份31天数据
number=31
#备份保存路径
backup_dir=/mysql/mysqlbackup
#日期
dd=`date +%Y-%m-%d-%H-%M-%S`
#备份工具
tool=mysqldump
#用户名
username=root
#密码
password=123456
#将要备份的数据库
database_name=db_name

#如果文件夹不存在则创建
if [ ! -d $backup_dir ]; 
then     
    mkdir -p $backup_dir; 
fi

#简单写法  mysqldump -u root -p123456 users > /root/mysqlbackup/users-$filename.sql
$tool -u $username -p$password $database_name > $backup_dir/$database_name-$dd.sql

#写创建备份日志
echo "create $backup_dir/$database_name-$dd.dupm" >> $backup_dir/log.txt

#找出需要删除的备份
delfile=`ls -l -crt  $backup_dir/*.sql | awk '{print $9 }' | head -1`

#判断现在的备份数量是否大于$number
count=`ls -l -crt  $backup_dir/*.sql | awk '{print $9 }' | wc -l`

if [ $count -gt $number ]
then
  #删除最早生成的备份,只保留number数量的备份
  rm $delfile
  #写删除文件日志
  echo "delete $delfile" >> $backup_dir/log.txt
fi