Home | 簡體中文 | 繁體中文 | 雜文 | 知乎專欄 | Github | OSChina 博客 | 雲社區 | 雲棲社區 | Facebook | Linkedin | 視頻教程 | 打賞(Donations) | About
21.26. 資料庫安全
上一頁 第 21 章 關係型資料庫設計 下一頁
知乎專欄 ｜ 多維度架構 微信號 netkiller-ebook | QQ群：128659835 請註明“讀者”

21.26. 資料庫安全

21.26.1. 資料庫結構版本控制

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

21.26.1.1. 什麼是資料庫結構版本控制

首先說說什麼是資料庫結構，什麼事版本控制。

資料庫結構是指資料庫表結構，資料庫定義語言導出的DDL語句。主要由CREATE TABLE, DROP TABLE等等構成。

再來說說什麼事版本控制，如果你從事開發工作應該會很容易理解，版本控制就是記錄每一次變化，可以隨時查看歷史記錄，並可回撤到指定版本。

21.26.1.2. 為什麼要做資料庫結構本版控制

軟件開發過程中需要常常對資料庫結構作調整，這是無法避免的，甚至很多想起啟動後，需求還不明確，開發人員只能按照所理解需求創建表。需求往往會發生變化，一旦變化，代碼需要修改，表結構也避免不了。 我們常常剛改好資料庫結構，需求部門有發來通知，不用修改了，維持原有設計。甚至是過了幾周再次回撤。

所以我們要將資料庫結構的變化進行版本控制，通常的做法是DBA人工管理，但我覺完全可以自動化的工作，沒有必要浪費人力資源，且自動化不會犯錯更穩定，僅僅需要人工定期查看工作狀態即可。

21.26.1.3. 何時做資料庫結構本版控制

任何時候都可以部署下面的腳本，對現有系統無任何影響。

21.26.1.4. 在哪裡做資料庫結構本版控制

可以在版本控制伺服器上，建議GIT倉庫push到遠程。

21.26.1.5. 誰來負責資料庫結構本版控制

DBA與配置管理員都可以做，通常DBA不接觸版本庫這塊，建議創建一個backup用戶給配置管理員。

21.26.1.6. 怎樣做資料庫結構本版控制

21.26.1.6.1. 安裝腳本

首先下載腳本 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/
21.26.1.6.2. 啟動腳本，停止腳本
# /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
21.26.1.6.3. 查看歷史版本

通過 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 手札》

21.26.2. 保護表

保護表中的數據不被刪除，當記錄被用戶刪除時會提示"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

21.26.3. 保護表欄位

通過觸發器，使之無法修改某些欄位的數據，同時不影響修改其他欄位。

			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;

21.26.4. 時間一致性

經常會因為每個伺服器的時間不同，導致插入數據有問題，雖然可以採用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;

21.26.5. 為數據安全而分庫

我們通常使用一個資料庫開發，該資料庫包含了前後台所有的功能，我建議將前後台等等功能進行分庫然後對應各種平台分配用戶權限，例如

我們創建三個資料庫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)

21.26.6. 內容版本控制,撰改留痕

主表

			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;

進一步優化，我們可以為 history 歷史表增加時間欄位，用於記錄被撰改那一時刻的時間。

		
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 '',
	`ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Created Time',
  	`mtime` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'Modified Time',
	PRIMARY KEY (`id`),
	INDEX `article_id` (`article_id`)
)
ENGINE=MyISAM
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=1

我們還可以為該表（article_history）增加出發器，任何修改將被拒絶.

21.26.7. 資料庫審計表

與上一章節所提到的歷史表不同，歷史表需要經常翻查所以我們需要用到索引。審計表通常是數據歸檔，不允許修改，且基本上很少訪問。

		
CREATE TABLE `order` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '訂單ID',
  `name` varchar(45) NOT NULL COMMENT '訂單名稱',
  `price` float NOT NULL COMMENT '價格',
  `ctime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='訂單表'

基于 order 表創建 order_audit 審計表

		
create table order_audit engine=archive as select * from `order`;

order_audit 表結構如下

		
CREATE TABLE `order_audit` (
  `id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '訂單ID',
  `name` varchar(45) NOT NULL COMMENT '訂單名稱',
  `price` float NOT NULL COMMENT '價格',
  `ctime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間'
) ENGINE=ARCHIVE DEFAULT CHARSET=utf8

創建插入和更新觸發器，用於插入和修改的時候同事寫入一份到歸檔表中。

		
DROP TRIGGER IF EXISTS `test`.`order_AFTER_INSERT`;

DELIMITER $$
USE `test`$$
CREATE DEFINER=`dba`@`%` TRIGGER `test`.`order_AFTER_INSERT` AFTER INSERT ON `order` FOR EACH ROW
BEGIN
	INSERT INTO order_audit SELECT * FROM `order` WHERE id = NEW.id; 
END$$
DELIMITER ;
DROP TRIGGER IF EXISTS `test`.`order_AFTER_UPDATE`;

DELIMITER $$
USE `test`$$
CREATE DEFINER=`dba`@`%` TRIGGER `test`.`order_AFTER_UPDATE` AFTER UPDATE ON `order` FOR EACH ROW
BEGIN
	INSERT INTO order_audit SELECT * FROM `order` WHERE id = NEW.id; 
END$$
DELIMITER ;

21.26.8. 用戶/角色認證

本小節我們實現一個功能，當用戶插入，修改或者刪除數據時，判斷該操作是否具備應有的權限。如果權限不符合就拒絶操作同時提示用戶。

		
CREATE TABLE `staff` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '員工ID',
	`name` VARCHAR(50) NOT NULL COMMENT '員工名字',
	PRIMARY KEY (`id`)
)
COMMENT='員工表'
COLLATE='utf8_general_ci'
ENGINE=InnoDB;


INSERT INTO `staff` (`id`, `name`) VALUES
	(1, 'Neo'),
	(2, 'Luke'),
	(2, 'Jack');

staff 是員工表與下面的staff_has_role配合使用，形成員工與權限一對多關係。

		
CREATE TABLE `staff_has_role` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`staff_id` INT(10) UNSIGNED NOT NULL COMMENT '員工ID',
	`role` ENUM('Create','Update','Delete') NOT NULL COMMENT '角色',
	PRIMARY KEY (`id`),
	INDEX `FK_staff_has_role_staff` (`staff_id`),
	CONSTRAINT `FK_staff_has_role_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

INSERT INTO `staff_has_role` (`id`, `staff_id`, `role`) VALUES
	(1, 1, 'Create'),
	(2, 1, 'Delete'),
	(3, 1, 'Update'),
	(4, 2, 'Delete'),
	(5, 3, 'Create');
	(6, 3, 'Update');

權限表可以進一步優化，角色擁有組功能，實現顆粒度更細的權限控制，有情趣看前面的相關章節。

		
CREATE TABLE `product` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '唯一ID',
	`name` VARCHAR(10) NOT NULL COMMENT '名稱',
	`sn` VARCHAR(10) NOT NULL COMMENT '序列號',
	`price` FLOAT NOT NULL COMMENT '價格',
	`amount` SMALLINT(6) NOT NULL COMMENT '數量',
	`staff_id` INT(10) UNSIGNED NOT NULL COMMENT '員工ID',
	`ctime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
	`mtime` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時間',
	PRIMARY KEY (`id`),
	UNIQUE INDEX `sn` (`sn`),
	INDEX `FK_product_staff` (`staff_id`),
	CONSTRAINT `FK_product_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`id`)
)
COMMENT='產品表'
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

以產品表為例，這裡要實現的是對產品表記錄的權限控制。例如Neo有用插入，修改和刪除權限，Luke的Create與Update權限被吊銷，只能刪除他之前創建的數據。而Jack只有能創建於更新數據。

下面的三個觸發器完成具體的權限控制。同樣你可以進一步優化下面的代碼的權限顆粒度，使之能控制到具體列，甚至具體的記錄。

		
CREATE DEFINER=`root`@`%` TRIGGER `product_before_delete` BEFORE DELETE ON `product` FOR EACH ROW BEGIN
	if not exists(select id from staff where id=OLD.staff_id and role="delete") then
		SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Permission denied', MYSQL_ERRNO = 1001;
	end if;
END

CREATE DEFINER=`root`@`%` TRIGGER `product_before_insert` BEFORE INSERT ON `product` FOR EACH ROW BEGIN
	 if not exists(select id from staff where id=NEW.staff_id and role="create") then
	       SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "The staff's role is not correct or it does not exist.", MYSQL_ERRNO = 1001;
	 end if;
END

CREATE DEFINER=`root`@`%` TRIGGER `product_before_update` BEFORE UPDATE ON `product` FOR EACH ROW BEGIN
	if not exists(select id from staff where id=NEW.staff_id and role="update") then
		SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "The staff's role cannot update data.", MYSQL_ERRNO = 1001;
	end if;
END

Neo 測試如下

		
INSERT INTO `test`.`product` (`name`, `sn`, `price`, `amount`, `staff_id`, `ctime`) VALUES ('Iphone', '678624', '5000', '77', '1', '2010-08-18 15:38:23');
SELECT LAST_INSERT_ID();

UPDATE `test`.`product` SET `name`='HTC', `sn`='5544467', `price`='2000' WHERE  `id`=2;

DELETE FROM `test`.`product` WHERE  `id`=1;

Luke 測試如下：

		
INSERT INTO `test`.`product` (`name`, `sn`, `price`, `amount`, `staff_id`) VALUES ('Nokia', '65722', '800', '55', '2');
/* SQL錯誤（1001）：The staff's role is not correct or it does not exist. */

UPDATE `test`.`product` SET `name`='HTC', `sn`='5544467', `price`='2000', staff_id=2 WHERE  `id`=2;
/* SQL錯誤（1001）：The staff's role cannot update data. */

21.26.9. Token 認證

我們在staff表的基礎上增加 token 欄位

		
CREATE TABLE `staff` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '員工ID',
	`name` VARCHAR(50) NOT NULL COMMENT '員工名字',
	`token` VARCHAR(32) NOT NULL COMMENT 'Token 校驗',
	PRIMARY KEY (`id`)
)
COMMENT='員工表'
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

插入數據的時候增加一些干擾字元串，這裡使用concat(NEW.id,'+',NEW.name,'-')

		
CREATE DEFINER=`root`@`%` TRIGGER `staff_before_insert` BEFORE INSERT ON `staff` FOR EACH ROW BEGIN

if md5(concat(NEW.id,'+',NEW.name,'-')) != NEW.token then
		SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Permission denied', MYSQL_ERRNO = 1001;
	end if;

END

注意表權限可以授權給用戶，觸發器權限不讓普通用戶查看。否則用戶看到 concat(NEW.id,'+',NEW.name,'-') 就沒有意義了。

下面開始測試：

		
INSERT INTO `test`.`staff` (`name`, `token`) VALUES ('John', '678797066');
/* SQL錯誤（1001）：Permission denied */

下面再測試，首先生成一個正確的tokon, 然後使用該token插入數據：

		
-- 通過下面語句生成一個 Token
select md5(concat('5','+','Jam','-')) as token;

-- 使用上面的 Token 插入數據
INSERT INTO `test`.`staff` (`id`, `name`, `token`) VALUES (5, 'Jam', '1b033ce21cbadacabc9f0c38fb58dbb2');

SELECT * FROM `test`.`staff` WHERE `id` = 5;

開發注意事項, Token 生成算法要保密，不要使用下面SQL提交數據

			INSERT INTO `test`.`staff` (`id`, `name`, `token`) VALUES (5, 'Jam', md5(concat('5','+','Jam','-')));

應該分兩步，一是計算Token，二是插入數據。可以將Token計算交給程序而不是SQL，並且封裝在。jar(Java)中或者。so(PHP 擴展中).

21.26.10. 數據加密

資料庫中有很多敏感欄位，不允許隨意查看，例如開發人員，運維人員，甚至DBA資料庫管理員。另外加密主要是防止被黑客脫庫（盜走）

敏感數據加密有很多辦法，可以用資料庫內部加密函數，也可以在外部處理後寫入資料庫。加密算法有很多種，但通常兩類比較常用，一種是通過key加密解密，另一種是通過證書加密解密。

通常程序員負責寫程序，程序交給運維配置，運維將key設置好，運維不能有資料庫權限，DBA只能登陸資料庫，沒有key權限。

21.26.10.1. AES_ENCRYPT / AES_DECRYPT

這裡介紹AES加密與解密簡單用法

			
mysql> select AES_ENCRYPT('helloworld','key');
+---------------------------------+
| AES_ENCRYPT('helloworld','key') |
+---------------------------------+
|                                 |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select AES_DECRYPT(AES_ENCRYPT('helloworld','key'),'key');
+----------------------------------------------------+
| AES_DECRYPT(AES_ENCRYPT('helloworld','key'),'key') |
+----------------------------------------------------+
| helloworld                                         |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql>

21.26.10.2. 加密欄位

加密數據入庫

			
CREATE TABLE `encryption` (
	`mobile` VARBINARY(16) NOT NULL,
	`key` VARCHAR(32) NOT NULL
)
ENGINE=InnoDB;

INSERT INTO encryption(`mobile`,`key`)VALUES( AES_ENCRYPT('13691851789',md5('13691851789')), md5('13691851789')) 
select AES_DECRYPT(mobile,`key`), length(mobile) from encryption;

這裡方便演示將key 寫入了資料庫，實際應用key應該存儲在應用程序配置檔案中。通常能把獲得key的人不應該用資料庫權限。

21.26.11. 開發加密插件開發

資料庫內部提供的摘要函數MD5/SHA/CRC與現有的AES/DES加密函數以及不能滿足我們的需求，所以我們有必要開發外掛插件實現數據加密。

這裡有一個例子，是我早年開發的 https://github.com/netkiller/mysql-safenet-plugin 這個UDF是連結 Safenet設備，實現資料庫加密記錄。

saftnet.h

		
my_bool safenet_encrypt_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
char *safenet_encrypt(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error);
void safenet_encrypt_deinit(UDF_INIT *initid);

my_bool safenet_decrypt_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
char *safenet_decrypt(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error);
void safenet_decrypt_deinit(UDF_INIT *initid);

my_bool safenet_config_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
char *safenet_config(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error);
void safenet_config_deinit(UDF_INIT *initid);

safenet.c

		
/*
Homepage: http://netkiller.github.io/
Author: netkiller<netkiller@msn.com>
*/

#include <mysql.h>
#include <string.h>

#include <stdio.h>
#include <stdlib.h>
#include <curl/curl.h>
#include "safenet.h"

#define SAFENET_URL "http://localhost/safe/interface" 
#define SAFENET_KEY "Web01-key" 

char *safe_url;
char *safe_key;


void get_safenet_env(){
    if (getenv("SAFENET_URL")){
	safe_url = getenv("SAFENET_URL");
    }else{
	safe_url = SAFENET_URL;
    }
    if (getenv("SAFENET_KEY")){
	safe_key = getenv("SAFENET_KEY");
    }else{
	safe_key = SAFENET_KEY;
    }
}

/* CURL FUNCTION BEGIN*/
struct string {
  char *ptr;
  size_t len;
};

void init_string(struct string *s) {
  s->len = 0;
  s->ptr = malloc(s->len+1);
  if (s->ptr == NULL) {
    fprintf(stderr, "malloc() failed\n");
    exit(EXIT_FAILURE);
  }
  s->ptr[0] = '\0';
}

size_t writefunc(void *ptr, size_t size, size_t nmemb, struct string *s)
{
  size_t new_len = s->len + size*nmemb;
  s->ptr = realloc(s->ptr, new_len+1);
  if (s->ptr == NULL) {
    fprintf(stderr, "realloc() failed\n");
    exit(EXIT_FAILURE);
  }
  memcpy(s->ptr+s->len, ptr, size*nmemb);
  s->ptr[new_len] = '\0';
  s->len = new_len;

  return size*nmemb;
}

char * safenet(char *url, char *mode, char *key, char *in )
{ 
    CURL *curl;
    CURLcode res;
    char *fields;
    char *data;

//  curl_global_init(CURL_GLOBAL_ALL);
 
    /* get a curl handle */ 
    curl = curl_easy_init();
    if(curl) {
        struct string s;
        init_string(&s); 
        
        asprintf(&fields, "mode=%s&keyname=%s&input=%s", mode, key, in);    
    
        curl_easy_setopt(curl, CURLOPT_URL, url);
        curl_easy_setopt(curl, CURLOPT_USERAGENT, "safenet/1.0 by netkiller <netkiller@msn.com>");
        curl_easy_setopt(curl, CURLOPT_WRITEFUNCTION, writefunc);
        curl_easy_setopt(curl, CURLOPT_WRITEDATA, &s);
        curl_easy_setopt(curl, CURLOPT_POSTFIELDS, fields);
     
        /* Perform the request, res will get the return code */ 
        res = curl_easy_perform(curl);
        /* Check for errors */ 
        if(res != CURLE_OK)
          fprintf(stderr, "curl_easy_perform() failed: %s\n",
                  curl_easy_strerror(res));
     
        asprintf(&data, "%s", s.ptr);
        //printf("Encrypt: %s\n", data);
    
        free(s.ptr);
        /* always cleanup */ 
        curl_easy_cleanup(curl);
    }
    else{
	strcpy(data,"");
    }

    return data;
  //curl_global_cleanup();
}
/* CURL FUNCTION END*/

/* ------------------------ safenet encrypt ----------------------------- */

my_bool safenet_encrypt_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{

  if (args->arg_count != 1)
  {
    strncpy(message,
            "two arguments must be supplied: safenet_encrypt('<data>').",
            MYSQL_ERRMSG_SIZE);
    return 1;
  }
  get_safenet_env(); 
  args->arg_type[0]= STRING_RESULT;

  return 0;
}

char *safenet_encrypt(UDF_INIT *initid, UDF_ARGS *args,
                __attribute__ ((unused)) char *result,
               unsigned long *length,
                __attribute__ ((unused)) char *is_null,
                __attribute__ ((unused)) char *error)
{

    char *data;
    data = safenet(safe_url, "encrypt", safe_key, args->args[0]);
    *length = strlen(data);
    return ((char *)data);

}

void safenet_encrypt_deinit(UDF_INIT *initid)
{
  return;
}

/* ------------------------ safenet decrypt ----------------------------- */

my_bool safenet_decrypt_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{

  if (args->arg_count != 1)
  {
    strncpy(message,
            "two arguments must be supplied: safenet_decrypt('<data>').",
            MYSQL_ERRMSG_SIZE);
    return 1;
  }

  get_safenet_env();
  args->arg_type[0]= STRING_RESULT;

  return 0;
}

char *safenet_decrypt(UDF_INIT *initid, UDF_ARGS *args,
                __attribute__ ((unused)) char *result,
               unsigned long *length,
                __attribute__ ((unused)) char *is_null,
                __attribute__ ((unused)) char *error)
{

    char *data;
    if(strlen(args->args[0]) != 512){
        data = args->args[0];
    }else{
        data = safenet(safe_url, "decrypt", safe_key, args->args[0]);
    }
    *length = strlen(data);
    return ((char *)data);

}

void safenet_decrypt_deinit(UDF_INIT *initid)
{
  return;
}

/* ------------------------ safenet config ----------------------------- */

my_bool safenet_config_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{

    get_safenet_env();
    return 0;
}

char *safenet_config(UDF_INIT *initid, UDF_ARGS *args,
                __attribute__ ((unused)) char *result,
               unsigned long *length,
                __attribute__ ((unused)) char *is_null,
                __attribute__ ((unused)) char *error)
{

  char *config;
  asprintf(&config, "SAFENET_URL=%s, SAFENET_KEY=%s", safe_url, safe_key);
  *length = strlen(config);
  return ((char *)config);
}

void safenet_config_deinit(UDF_INIT *initid)
{
   return;
}

CMakeLists.txt

		
cmake_minimum_required(VERSION 2.8)
PROJECT(safenet)
ADD_LIBRARY(safenet SHARED safenet.c)
INCLUDE_DIRECTORIES(/usr/include/mysql)
TARGET_LINK_LIBRARIES(safenet curl)
INSTALL(PROGRAMS libsafenet.so DESTINATION /usr/lib64/mysql/plugin/)

Installation Plugin

		
yum install -y libcurl-devel

cd src
cmake .
make 
make install

cat > /etc/sysconfig/mysqld <<EOF
export SAFENET_URL=http://host.localdomain/safe/interface
export SAFENET_KEY=Web01-key
EOF

Create Function

		
create function safenet_encrypt returns string soname 'libsafenet.so';
create function safenet_decrypt returns string soname 'libsafenet.so';
create function safenet_config returns string soname 'libsafenet.so';

Example

		
mysql> select safenet_encrypt('Helloworld!!!');
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| safenet_encrypt('Helloworld!!!')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 994BAB7BC417F0559A09ECE94EDCB695AC1D5705F7ABA9F3562158F5AFAC4720FA9B3E53F30DF65C1726E0F02A93A9CAE7E486349F41AE4F504DC2B49F809C5AF77FEF4DE49D03D8DEC4000B15F2F2A2296500AA6159491E65DEFDFE75FB2E79D31D9BF0CC67932ADA212C34C0B04BF30F222102FAD857F440404C0FE92B8626EA3126B0B5A4FA0B1D09F1CC9EF45EBB6A72123AE82D39F659C717A5AA4F7FB5BDBBC7977C7021F61BBC26B9DB78C9A8657C6BC291CAE5C07F9DF485D71A1E9CC8888793B03BB5AF2DDB57AAEFB6D2EA569226651092414F96BA0880B35B0D8A01A1F7B82C308A2316D07C0FD4E0A298ECB33F4E4EB9F1A1E53760B0BFBE7449 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.58 sec)

mysql> select safenet_decrypt(safenet_encrypt('Helloworld!!!'));
+---------------------------------------------------+
| safenet_decrypt(safenet_encrypt('Helloworld!!!')) |
+---------------------------------------------------+
| Helloworld!!!                                     |
+---------------------------------------------------+
1 row in set (0.31 sec)

mysql> select safenet_config();

Drop Function

		
drop function safenet_encrypt;
drop function safenet_decrypt;
drop function safenet_config;

21.26.12. 數據區塊鏈

背景：例如我們需要一個排行榜，存儲活動的報名順序或者考試成績。我們防止有人作弊或者撰改，包括DBA在內。

任務：1.數據檢查，2.發現撰改，2.風險提示

方案：使用鏈表指針方案,將數據看成一個鏈條，中間任何改動，就如同鏈條被剪斷，改動之處之後的數據全部視為無效。

結果：達到數據後發現是否撰改，提示風險目的

		
CREATE TABLE `top100_list` (
	`id` INT,
	`name` VARBINARY(16) NOT NULL,
	......
	......
	`extend` VARCHAR(32) NULL
)
ENGINE=InnoDB;

演示數據

		
id | extend | ...
1 | 0 | ...
2 | 1 | ...
3 | 2 | ...
4 | 3 | ...		
5 | 4 | ...

extend 始終整合上一條記錄，保證數據是連續的。但這樣還不夠，這樣只能防止數據被刪除，如果其他欄位被修改呢

		
id | extend | ...
1 | NULL | ...
2 | crc32(...) | ...
3 | crc32(...) | ...
4 | crc32(...) | ...		
5 | crc32(...) | ...

我們使用crc算法運算上一條一整行的數據，你還可以使用 salt 技術干擾，這個 salt 只有軟件部署者知道，DBA和開發人員不得而知。

對於一般數據crc32 可能做到性能和安全性平衡，如果安全要求更高可以使用 sha256 等等，甚至採用 RSA 非對稱秘鑰。

21.26.13. 狀態保護

表中有一個 Status 欄位，是一個狀態機，你可以理解為工作流，工作流是有任務流向的，不能隨意修改其狀態。

		
CREATE TABLE `card` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`uuid` VARCHAR(36) NOT NULL COMMENT 'UUID' COLLATE 'utf8mb4_unicode_ci',
	`number` VARCHAR(36) NOT NULL COMMENT '充值卡號碼' COLLATE 'utf8mb4_unicode_ci',
	`price` MEDIUMINT(8) UNSIGNED NOT NULL COMMENT '面值',
	`status` ENUM('New','Activated','Recharged','Discard') NOT NULL DEFAULT 'New' COMMENT '充值卡狀態' COLLATE 'utf8mb4_unicode_ci',
	`ctime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
	`mtime` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時間',
	PRIMARY KEY (`id`),
	UNIQUE INDEX `number` (`number`),
	UNIQUE INDEX `uuid` (`uuid`)
)
COMMENT='充值卡表'
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1;

狀態流向

		
	+----------+    +-----------+    +-----------+    
	| New      | -> | Activated | -> | Recharged |
	+----------+    +-----------+    +-----------+    
		 |                |
		 V                |
	+----------+          |
    | Discard  | <--------+
	+----------+

為此我們創建觸發器保護狀態正確走向。

		
CREATE DEFINER=`root`@`%` TRIGGER `card_before_update` BEFORE UPDATE ON `card` FOR EACH ROW BEGIN
	set new.uuid 		= old.uuid;
	set new.number		= old.number;
	set new.price		= old.price;
	set new.ctime		= old.ctime;
	
	IF old.status = "New" THEN
		IF new.status NOT IN ("Activated","Discard") THEN
			SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Status denied', MYSQL_ERRNO = 1001;
		END IF;
	END IF;
	IF old.status = "Activated" THEN
		IF new.status NOT IN ("Recharged") THEN
			SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Status denied', MYSQL_ERRNO = 1001;
		END IF;
	END IF;
	IF old.status = "Recharged" THEN
		set new.status	= old.status;
	END IF;
END

保護記錄不被刪除

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

這個方案很容易移植到其他場景中，例如購物，發貨，收貨等等

21.26.14. 數據歸檔

MySQL 提供 ARCHIVE 引擎，ARCHIVE歸檔的數據不能夠修改，這個引擎只提供插入操作

		
CREATE TABLE `logging` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`tag` ENUM('unknow','www','user','admin') NOT NULL DEFAULT 'unknow' COMMENT '日誌標籤',
	`time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '產生時間',
	`facility` ENUM('card','payment','sms','blockchain') NOT NULL COMMENT '類別',
	`priority` ENUM('info','warning','error','critical','exception','debug') NOT NULL COMMENT '級別',
	`message` VARCHAR(1024) NOT NULL COMMENT '內容',
	PRIMARY KEY (`id`)
)
COMMENT='日誌表'
COLLATE='utf8_general_ci'
ENGINE=ARCHIVE
AUTO_INCREMENT=1;

上一頁 上一級 下一頁
21.25. Sharding 起始頁 21.27. MySQL 大數據操作注意事項