数据库数据安全的九个最佳实践

netkiller- 2016-08-24 04:40:20

数据库数据安全的九个最佳实践

本文节选自《Netkiller Architect 手札》
作者：netkiller

博客分类：数据库应用
netkiller mysql oracle security

第 5 章数据库安全

目录
5.1. 保护表
5.2. 保护表字段
5.3. 时间一致性
5.4. 为数据安全而分库
5.5. 内容版本控制,撰改留痕
5.6. 用户/角色认证
5.7. Token 认证
5.8. 数据加密
5.8.1. AES_ENCRYPT / AES_DECRYPT
5.8.2. 加密字段
5.9. 开发加密插件开发
5.1. 保护表

保护表中的数据不被删除，当记录被用户删除时会提示"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
5.2. 保护表字段

通过触发器，使之无法修改某些字段的数据，同时不影响修改其他字段。
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;

5.3. 时间一致性

经常会因为每个服务器的时间不同，导致插入数据有问题，虽然可以采用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;

5.4. 为数据安全而分库

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

5.5. 内容版本控制,撰改留痕

主表
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）增加出发器，任何修改将被拒绝.

...全文