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实例启动后就加载到内存中
用户权限管理主要有以下作用:
- 可以限制用户访问哪些库、哪些表
- 可以限制用户对哪些表执行SELECT、CREATE、DELETE、DELETE、ALTER等操作
- 可以限制用户登录的IP或域名
- 可以限制用户自己的权限是否可以授权给别的用户
#例:
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 FILE
server_audit_logging:启动或关闭审计
Commandline: --server-audit-logging[={0|1}]
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: OFF
server_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.log
server_audit_file_rotate_size:限制日志文件的大小
Commandline: --server-audit-rotate-size=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 1000000
server_audit_file_rotations:指定日志文件的数量,如果为0日志将从不轮转
Commandline: --server-audit-rotations=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 9
Range: 0 to 999
server_audit_file_rotate_now:强制日志文件轮转
Commandline: --server-audit-rotate-now[={0|1}]
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: OFF
server_audit_incl_users:指定哪些用户的活动将记录,connect将不受此变量影响,该变量比server_audit_excl_users优先级高
Commandline: --server-audit-incl-users=value
Scope: Global
Dynamic: Yes
Data Type: string
Default Value: Empty string
server_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_LOCAL7
server_audit_syslog_ident:设置ident,作为每个syslog记录的一部分,更改新的值后,需要重启这个审计功能
Commandline: --server-audit-syslog-ident=value
Scope: Global
Dynamic: Yes
Data Type: string
Default Value: mysql-server_auditing
server_audit_syslog_info:指定的info字符串将添加到syslog记录,随时可更改
Commandline: --server-audit-syslog-info=value
Scope: Global
Dynamic: Yes
Data Type: string
Default Value: Empty string
server_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_DEBUG
server_audit_excl_users:该列表的用户行为将不记录,connect将不受该设置影响
Commandline: --server-audit-excl-users=value
Scope: Global
Dynamic: Yes
Data Type: string
Default Value: Empty string
server_audit_mode:标识版本,用于开发测试
Commandline: --server-audit-mode[=#]
可用shell脚本的方式,根据自己数据库审计活动配置相关参数到/etc/mysql目录my.cnf配置文件中,但是必须重启mysql服务service 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