数据库安全解决方案

数据库安全

数据库结构版本控制

http://netkiller.github.io/journal/mysql.struct.html

什么是数据库结构版本控制

首先说说什么是数据库结构，什么是版本控制。

数据库结构是指数据库表结构，数据库定义语言导出的DDL语句。主要由CREATE TABLE, DROP TABLE等等构成。

再来说说什么是版本控制，如果你从事开发工作应该会很容易理解，版本控制就是记录每一次提交的变化，可以随时查看历史记录，并可回撤到指定版本。

为什么要做数据库结构本版控制

软件开发过程中需要常常对数据库结构作调整，这是无法避免的。例如需求还不明确，开发人员只能按照所理解需求创建表。需求往往会发生变化，一旦变化，代码需要修改，表结构也避免不了。 我们常常刚改好数据库结构，需求部门有发来通知，不用修改了，维持原有设计。甚至是过了几周再次回撤。

所以我们要将数据库结构的变化进行版本控制，通常的做法是DBA人工管理，但我觉完全可以自动化的工作，没有必要浪费人力资源，且自动化不会犯错更稳定，仅仅需要人工定期查看工作状态即可。

何时做数据库结构本版控制

任何时候都可以部署下面的脚本，对现有系统无任何影响。

在哪里做数据库结构本版控制

可以在版本控制服务器上，建议GIT仓库push到远程。

谁来负责数据库结构本版控制

DBA与配置管理员都可以做，通常DBA不接触版本库这块，建议创建一个backup用户给配置管理员。

怎样做数据库结构本版控制

安装脚本

首先下载脚本 https://github.com/oscm/devops/blob/master/shell/backup.mysql.struct.sh

wget https://raw.githubusercontent.com/oscm/devops/master/shell/backup.mysql.struct.sh
mv backup.mysql.struct.sh /usr/local/bin
chmod +x /usr/local/bin/backup.mysql.struct
			

创建备份用户

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'SaJePoM6BAPOmOFOd7Xo3e1A52vEPE';
GRANT SELECT, LOCK TABLES  ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'backup'@'localhost';
			

配置脚本

BACKUP_HOST="localhost"			数据库主机
BACKUP_USER="backup"			备份用户
BACKUP_PASS="chen"				备份密码
BACKUP_DBNAME="test aabbcc"		版本控制那些数据库，多个数据库使用空格分隔
BACKUP_DIR=~/backup				数据库结构放在那里
			

初始化仓库

# /usr/local/bin/backup.mysql.struct init
Initialized empty Git repository in /www/database/struct/.git/
			

启动脚本，停止脚本

# /usr/local/bin/backup.mysql.struct
Usage: /usr/local/bin/backup.mysql.struct {init|start|stop|status|restart}
			

开始脚本

# /usr/local/bin/backup.mysql.struct start
			

查看状态

# /usr/local/bin/backup.mysql.struct status
 9644 pts/1    S      0:00 /bin/bash /usr/local/bin/backup.mysql.struct start
			

停止脚本

# /usr/local/bin/backup.mysql.struct status
			

查看历史版本

通过 git log 命令查看历史版本

			
# cd /www/database/struct/

# git status
# On branch master
nothing to commit (working directory clean)

# git log
commit d38fc624c21cad0e2f55f0228bff0c1be981827c
Author: root <root@slave.example.com>
Date:   Wed Dec 17 12:33:55 2014 +0800

    2014-12-17.04:33:55			
			
				

这里仅仅是讲数据库结构版本控制，关于版本控制软件更多细节，延伸阅读 《Netkiller Version 手札》

保护表

保护表中的数据不被删除，当记录被用户删除时会提示"Permission denied" 权限拒绝

		
CREATE DEFINER=`root`@`192.168.%` TRIGGER `member_before_delete` BEFORE DELETE ON `member` FOR EACH ROW BEGIN
	SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Permission denied', MYSQL_ERRNO = 1001;
END		
		
		

保护表字段

通过触发器，使之无法修改某些字段的数据，同时不影响修改其他字段。

		
DROP TRIGGER IF EXISTS `members`;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='';
DELIMITER //
CREATE TRIGGER `members` BEFORE UPDATE ON `members` FOR EACH ROW BEGIN
	set new.name = old.name;
	set new.cellphone = old.cellphone;
	set new.email = old.email;
	set new.password = old.password;
END//
DELIMITER ;
SET SQL_MODE=@OLD_SQL_MODE;
		
		

再举一个例子

		
CREATE TABLE `account` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`user` VARCHAR(50) NOT NULL DEFAULT '0',
	`cash` FLOAT NOT NULL DEFAULT '0',
	PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
		
		

每一次数据变化新增一条数据

		
INSERT INTO `test`.`account` (`user`, `cash`) VALUES ('neo', -10);
INSERT INTO `test`.`account` (`user`, `cash`) VALUES ('neo', -5);
INSERT INTO `test`.`account` (`user`, `cash`) VALUES ('neo', 30);
INSERT INTO `test`.`account` (`user`, `cash`) VALUES ('neo', -20);
		
		

保护用户的余额不被修改

		
DROP TRIGGER IF EXISTS `account`;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='';
DELIMITER //
CREATE TRIGGER `account` BEFORE UPDATE ON `account` FOR EACH ROW BEGIN
	set new.cash = old.cash;
END//
DELIMITER ;
SET SQL_MODE=@OLD_SQL_MODE;
		
		

时间一致性

经常会因为每个服务器的时间不同，导致插入数据有问题，虽然可以采用ntp服务同步时间，但由于各种因素仍然会出问题，怎么解决？我建议以数据库时间为准。

MySQL 5.6 之前的版本

默认值为当前时间

		
CREATE TABLE `tdate` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`ctime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	`mtime` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '修改时间',
	PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
		
		

MySQL不允许一个表拿有两个默认时间。我一无法兼顾修改时间，我们舍弃创建时间，当有数据变化ON UPDATE CURRENT_TIMESTAMP自动修改时间

		
CREATE TABLE `tdate` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`ctime` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',
	`mtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
	PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
		
		

插入创建时间 insert into tdate(ctime) values(CURRENT_TIMESTAMP); 不要采用 insert into tdate(ctime) values('2013-12-02 08:20:06');这种方法，尽量让数据库处理时间。

MySQL 5.6 之后版本，可以实现创建时间为系统默认，修改时间创建的时候默认为空，当修改数据的时候更新时间。

		
CREATE TABLE `tdate` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`ctime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	`mtime` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
	PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
		
		

为数据安全而分库

我们通常使用一个数据库开发，该数据库包含了前后台所有的功能，我建议将前后台等等功能进行分库然后对应各种平台分配用户权限，例如

我们创建三个数据库cms,frontend,backend 同时对应创建三个用户 cms,frontend,backend 三个用户只能分别访问自己的数据库，注意在系统的设计之初你要考虑好这样的划分随之系统需要做相应的调整。

		
CREATE DATABASE `cms` /*!40100 COLLATE 'utf8_general_ci' */;
CREATE DATABASE `frontend` /*!40100 COLLATE 'utf8_general_ci' */;
CREATE DATABASE `backend` /*!40100 COLLATE 'utf8_general_ci' */;
		
		

backend 负责后台，权限最高

		
mysql> SHOW GRANTS FOR 'backend'@'localhost';
+--------------------------------------------------------------------------------------+
| Grants for backend@local