資料庫記錄安全解決方案

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

Mr. Neo Chen (陳景峯), netkiller, BG7NYT

中國廣東省深圳市龍華新區民治街道溪山美地
518131
+86 13113668890

<netkiller@msn.com>

轉載請與作者聯繫，轉載時請務必標明文章原始出處和作者信息及本聲明。

文檔出處:

http://netkiller.github.io

http://netkiller.sourceforge.net

微信掃瞄二維碼進入 Netkiller 微信訂閲號

QQ群：128659835 請註明“讀者”

2014-08-19

摘要

資料庫記錄防刪除，放撰改，撰改留痕，靈活性解決方案

2014-08-28 第二版

1. 什麼是防刪除，防撰改
2. 為什麼要做防刪除，防撰改限制
3. 何時做防刪除，防撰改限制
4. 在哪裡做防刪除，防撰改限制
5. 誰去做防刪除，防撰改限制
6. 怎樣實現防刪除，防撰改限制
7. 怎樣實現數據修改留痕
- 7.1. 版本控制
- 7.2. 一張表實現歷史日誌記錄

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