mysql实现一些安全和版本控制的思路

Veröffentlicht am von
http://netkiller.github.io/journal/mysql.security.html 来源
目录

1. 什么是防删除，防撰改

禁止数据删除，数据一旦增加不允许数据被任何人删除

禁止数据修改，数据一旦建立不允许对数据做修改操作

2. 为什么要做防删除，防撰改限制

很多时候我们的数据是只增加，不会删除数据。有些敏感子段一旦数据家里是不允许再修改的，例如银行账户表中的资金子段。

另一个原因是我们防止误操作

3. 何时做防删除，防撰改限制

我认为在数据库设计时就应该考虑倒这些问题，如果发现数据被删除或者被撰改，亡羊补牢也不晚，我们不能允许再次发生。

你可以取消用户的 DELETE 权限，使之只能做查询操作，但修改（UPDATE）呢？你就无能为力！如果取消UPDATE程序将不能正常运行。

4. 在哪里做防删除，防撰改限制

程序设计之初你就应该想到这些问题，如果没有考虑倒，你只能修改现有逻辑。通常的做法是所有表增加一个删除状态子段，删除操作即是更新状态。这种方式也有弊端就是垃圾数据会不停地膨胀。

5. 谁去做防删除，防撰改限制

我认为可以分为两种人，一种是DBA,一种是开发者。这里主要将数据库部分。

6. 怎样实现防删除，防撰改限制

6.1. 限制删除

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

对account表中的记录做删除操作，数据库抛出异常 Permission denied

6.2. 限制修改

禁止所有修改操作

DELIMITER $$
CREATE DEFINER=`dba`@`192.168.%` TRIGGER `logging_before_update` BEFORE UPDATE ON `logging` FOR EACH ROW BEGIN
	SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Permission denied', MYSQL_ERRNO = 1001;
END

限制部分子段修改，其他子段扔允许修改

CREATE DEFINER=`dba`@`192.168.%` TRIGGER `members_before_update` BEFORE UPDATE ON `members` FOR EACH ROW BEGIN
	SET NEW.`id` = OLD.id;
	SET NEW.`name` = OLD.name;
	SET NEW.`chinese_name` = OLD.chinese_name;
	SET NEW.`english_name` = OLD.english_name;
	SET NEW.`sex` = OLD.sex;
	SET NEW.`address` = OLD.address;
	SET NEW.`zipcode` = OLD.zipcode;
	SET NEW.`country_code` = OLD.country_code;
	SET NEW.`mobile` = OLD.mobile;
	SET NEW.`email` = OLD.email;
	SET NEW.`qq` = OLD.qq;
	SET NEW.`question` = OLD.question;
	SET NEW.`answer` = OLD.answer;
	SET NEW.`ctime` = OLD.ctime;
END

在数据库修改前我们覆盖掉修改的数据，使之更新后数据保持不变。

6.3. 为数据安全而分库

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

我们创建三个数据库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@localhost                                                         |
+--------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'backend'@'localhost'                                          |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `cms`.* TO 'backend'@'localhost'             |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `frontend`.* TO 'backend'@'localhost'        |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `backend`.* TO 'backend'@'localhost' |
+--------------------------------------------------------------------------------------+
4 rows in set (0.04 sec)

frontend 是前台权限，主要是用户用户中心，用户注册，登录，用户信息资料编辑，查看新闻等等

mysql> SHOW GRANTS FOR 'frontend'@'localhost';
+------------------------------------------------------------------------+
| Grants for frontend@localhost                                          |
+------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'frontend'@'localhost'                           |
| GRANT SELECT, INSERT, UPDATE ON `frontend`.* TO 'frontend'@'localhost' |
| GRANT SELECT ON `cms`.`news` TO 'frontend'@'localhost'                 |
+------------------------------------------------------------------------+
3 rows in set (0.00 sec)

cms 用户是网站内容管理，主要负责内容更新，但登陆CMS后台需要`backend`.`Employees`表用户认证，所以他需要读取权限，但不允许修改其中的数据。

mysql> SHOW GRANTS FOR 'cms'@'localhost';
+----------------------------------------------------------------------+
| Grants for cms@localhost                                             |
+----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'cms'@'localhost'                              |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `cms`.* TO 'cms'@'localhost' |
| GRANT SELECT ON `backend`.`Employees` TO 'cms'@'localhost'           |
+----------------------------------------------------------------------+
3 rows in set (0.00 sec)

cms与backend 通常我们会限制IP地址来源，安全相对好控制。

frontend 主要对外提供服务，我们假设一旦被骇客入侵，所波及的范围被限制在frontend权限下，至少`backend`.`Employees`不会被撰改，CMS内容也得到了保护。

想100%解决数据的安全是非常空难的，但我们至少保护了一部份数据的安全。使其安全不会进一步扩散影响。

7. 怎样实现数据修改留痕

数据记录每一次修改我们都需要保留之前的数据，这样可以随时调出历史数据，用户审计等等。

7.1. 版本控制

主表

CREATE TABLE `article` (
	`article_id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
	`cat_id` SMALLINT(5) NOT NULL DEFAULT '0',
	`title` VARCHAR(150) NOT NULL DEFAULT '',
	`content` LONGTEXT NOT NULL,
	`author` VARCHAR(30) NOT NULL DEFAULT '',
	`keywords` VARCHAR(255) NOT NULL DEFAULT '',
	PRIMARY KEY (`article_id`),
	INDEX `cat_id` (`cat_id`)
)
ENGINE=MyISAM
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=1

本版控制表，用于记录每次变动

CREATE TABLE `article_history` (
	`id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
	`article_id` MEDIUMINT(8) UNSIGNED NOT NULL,
	`cat_id` SMALLINT(5) NOT NULL DEFAULT '0',
	`title` VARCHAR(150) NOT NULL DEFAULT '',
	`content` LONGTEXT NOT NULL,
	`author` VARCHAR(30) NOT NULL DEFAULT '',
	`keywords` VARCHAR(255) NOT NULL DEFAULT '',
	PRIMARY KEY (`id`),
	INDEX `article_id` (`article_id`)
)
ENGINE=MyISAM
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=1

版本控制触发器

DROP TRIGGER article_history;

DELIMITER //
CREATE TRIGGER article_history BEFORE update ON article FOR EACH ROW
BEGIN
	INSERT INTO article_history SELECT * FROM article WHERE article_id = OLD.article_id;
END; //
DELIMITER;

任何数据的变化都会复制一份到历史表，我们可以随时比较两个版本数据的变化，我还为此开发了一个类似diff的工具，可以逐行比较，通过色彩变化现实数据的不同。

7.2. 一张表实现历史日志记录

我有一个表，里面只有固定行数的行记录，这些数据就是配置参数，我们将配置文件保存在数据库中，因为需要做负载均衡而不能使用文件配置文件。

有这样一个需求，这个记录每次修改都要保存历史记录，用于审计等等。我是这样设计该表的

CREATE TABLE `config_fee` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`level` INT(11) NULL DEFAULT NULL COMMENT '层级',
	`type` ENUM('Deposit','Withdrawing') NOT NULL DEFAULT 'Withdrawing' COMMENT '类型，存款，取款',
	`min_fee` FLOAT(10,2) NOT NULL COMMENT '最低手续费',
	`max_fee` FLOAT(10,2) NOT NULL COMMENT '最高手续费',
	`ratio` FLOAT(10,2) NOT NULL COMMENT '手续费比例',
	`operator` VARCHAR(10) NOT NULL COMMENT '操作者',
	`status` ENUM('Current','Trash') NOT NULL DEFAULT 'Current',
	`ctime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`mtime` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`)
)
COMMENT='手续费管理'
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

数据记录的形态

mysql> select type,operator,status,ctime,mtime from config_mtf_fee;
+---------+----------+---------+---------------------+---------------------+
| type    | operator | status  | ctime               | mtime               |
+---------+----------+---------+---------------------+---------------------+
| Deposit | jam      | Trash   | 2014-07-20 11:10:17 | 2014-07-20 11:10:57 |
| Deposit | lucy     | Trash   | 2014-08-24 11:10:17 | 2014-08-24 11:10:57 |
| Deposit | lily     | Trash   | 2014-08-25 11:10:17 | 2014-08-25 11:10:57 |
| Deposit | kitty    | Trash   | 2014-08-27 11:10:17 | 2014-08-27 11:10:57 |
| Deposit | neo      | Current | 2014-08-28 11:10:54 | 2014-08-28 11:10:59 |
+---------+----------+---------+---------------------+---------------------+
2 rows in set (0.00 sec)

如上图所示，状态 Current 是当前记录，而Trash是废弃的历史记录。

每次修改数据，首先将Current改为Trash，然后插入一条新数据状态为Current，我们只会使用最后一条状态为current的数据。

我们使用更新触发器控制除了status,mtime意外的字段修改

CREATE DEFINER=`root`@`%` TRIGGER `config_fee_before_update` BEFORE UPDATE ON `config_fee` FOR EACH ROW BEGIN
	SET NEW.`id` = OLD.id;
	SET NEW.`level` = OLD.level;
	SET NEW.`type` = OLD.type;
	SET NEW.`min_amount` = OLD.min_amount;
	SET NEW.`min_fee` = OLD.min_fee;
	SET NEW.`max_fee` = OLD.max_fee;
	SET NEW.`ratio` = OLD.ratio;
	SET NEW.`operator` = OLD.operator;
	SET NEW.`ctime` = OLD.ctime;
END;

限制删除的触发器

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