数据库记录安全解决方案(二)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介:

数据库记录安全解决方案

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

MrNeo Chen (netkiller)陈景峰(BG7NYT)


中国广东省深圳市龙华新区民治街道溪山美地
518131
+86 13113668890
+86 755 29812080

版权声明

转载请与作者联系,转载时请务必标明文章原始出处和作者信息及本声明。

文档出处:
http://netkiller.github.io
http://netkiller.sourceforge.net

 

2014-08-19

摘要

数据库记录防删除,放撰改,撰改留痕,灵活性解决方案

2014-08-28 第二版


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			
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
目录
相关文章
|
11月前
|
关系型数据库 MySQL Java
【IDEA】java后台操作mysql数据库驱动常见错误解决方案
【IDEA】java后台操作mysql数据库驱动常见错误解决方案
307 0
|
2月前
|
运维 监控 关系型数据库
AI 时代的 MySQL 数据库运维解决方案
本文探讨了大模型与MySQL数据库运维结合所带来的变革,介绍了构建结构化运维知识库、选择合适的大模型、设计Prompt调用策略、开发MCP Server以及建立监控优化闭环等关键步骤。通过将自然语言处理能力与数据库运维相结合,实现了故障智能诊断、SQL自动优化等功能,显著提升了MySQL运维效率和准确性。
333 18
|
6天前
|
Java 关系型数据库 数据库
怎么保障数据库在凭据变更过程中的安全与稳定?
本文介绍了在Spring应用中实现RDS数据源账密运行时轮转的方案,通过集成KMS与Nacos,实现数据库凭据的加密托管、动态更新与无缝切换,保障应用在凭据变更过程中的安全与稳定。适用于使用Java语言开发的Spring Boot或Spring Cloud应用,支持多种数据库类型,如MySQL、SQL Server、PostgreSQL等。
|
18天前
|
安全 关系型数据库 数据管理
阿里云数据库:构建高性能与安全的数据管理系统
阿里云数据库提供RDS、PolarDB、Tair等核心产品,具备高可用、弹性扩展、安全合规及智能运维等技术优势,广泛应用于电商、游戏、金融等行业,助力企业高效管理数据,提升业务连续性与竞争力。
|
4月前
|
Cloud Native 关系型数据库 分布式数据库
阿里云PolarDB与沃趣科技携手打造一体化数据库解决方案,助推国产数据库生态发展
阿里云瑶池数据库与沃趣科技将继续深化合作,共同推动国产数据库技术的持续创新与广泛应用,为行业生态的繁荣注入更强劲的技术动力。
阿里云PolarDB与沃趣科技携手打造一体化数据库解决方案,助推国产数据库生态发展
|
3月前
|
运维 监控 关系型数据库
AI 时代的 MySQL 数据库运维解决方案
本方案将大模型与MySQL运维深度融合,构建智能诊断、SQL优化与知识更新的自动化系统。通过知识库建设、大模型调用策略、MCP Server开发及监控闭环设计,全面提升数据库运维效率与准确性,实现从人工经验到智能决策的跃迁。
439 26
|
2月前
|
SQL 安全 关系型数据库
数据库安全管理新范式:DBKEEPER一体化数据库权限管控堡垒机解决方案
在数字化时代,数据库安全至关重要。DBKEEPER提供一站式数据库安全访问与权限管控解决方案,支持多种数据库,具备精细化权限管理、数据脱敏、高危操作拦截、全面审计等功能,助力企业实现智能、安全的数据治理,满足金融、医疗、互联网等行业合规需求。选择DBKEEPER,让数据库安全管理更高效!
数据库安全管理新范式:DBKEEPER一体化数据库权限管控堡垒机解决方案
|
6月前
|
关系型数据库 MySQL 数据库连接
docker拉取MySQL后数据库连接失败解决方案
通过以上方法,可以解决Docker中拉取MySQL镜像后数据库连接失败的常见问题。关键步骤包括确保容器正确启动、配置正确的环境变量、合理设置网络和权限,以及检查主机防火墙设置等。通过逐步排查,可以快速定位并解决连接问题,确保MySQL服务的正常使用。
966 82
|
5月前
|
负载均衡 算法 关系型数据库
大数据新视界--大数据大厂之MySQL数据库课程设计:MySQL集群架构负载均衡故障排除与解决方案
本文深入探讨 MySQL 集群架构负载均衡的常见故障及排除方法。涵盖请求分配不均、节点无法响应、负载均衡器故障等现象,介绍多种负载均衡算法及故障排除步骤,包括检查负载均衡器状态、调整算法、诊断修复节点故障等。还阐述了预防措施与确保系统稳定性的方法,如定期监控维护、备份恢复策略、团队协作与知识管理等。为确保 MySQL 数据库系统高可用性提供全面指导。