数据库安全·用户/角色认证

以下节选择《Netkiller Architect 手札》

地址 http://www.netkiller.cn/architect/

接下来几周的话题是数据库安全。

5.6. 用户/角色认证

本小节我们实现一个功能,当用户插入,修改或者删除数据时,判断该操作是否具备应有的权限。如果权限不符合就拒绝操作同时提示用户。

		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. */		

原文发布于微信公众号 - Netkiller(netkiller-ebook)

原文发表时间:2016-08-25

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据分析

[数据库基础]——编码标准之结构

数据库是由若干表构成,表是由数据行构成,数据行由若干列组成。由于数据库这样的结构,这就要求我们在构成数据库表时,需要注意一些问题。现在提出一些注意的问题。 1....

3155
来自专栏撸码那些事

MySQL——索引优化实战

上篇文章中介绍了索引的基本内容,这篇文章我们继续介绍索引优化实战。在介绍索引优化实战之前,首先要介绍两个与索引相关的重要概念,这两个概念对于索引优化至关重要。

884
来自专栏乐沙弥的世界

使用DBMS_REDEFINITION在线切换普通表到分区表

      随着数据库数据量的不断增长,有些表需要由普通的堆表转换为分区表的模式。有几种不同的方法来对此进行操作,诸如导出表数据,然后创建分区表再导入数据到分区...

902
来自专栏性能与架构

Mysql DISTINCT的实现思路

DISTINCT实际上和GROUP BY操作非常相似,只不过是在GROUP BY之后的每组中只取出一条记录而已 所以,DISTINCT的实现方式和GROUP B...

3517
来自专栏PhpZendo

使用 MySQL Scheduler 和 Event 周期性创建数据表

使用 MySQL Scheduler 和 Event 周期性创建数据表,下面提供的是按月建表计划任务及事件通过 ON SCHEDULE EVERY 1 MINU...

1492
来自专栏数据和云

运维技巧 - 活用临时表隔离冷热数据

编辑手记:Oracle给了我们很多工具,在日常数据库管理中活用这些工具方可发挥最大效能。 作者简介: 张洪涛 富士康 DBA 在数据库监控过程中发现考勤数据...

3575
来自专栏PHP在线

MySQL DELETE语句和TRUNCATE TABLE语句的区别

MySQL DELETE语句和TRUNCATE TABLE语句功能相似,但是二者究竟有何区别呢?下文就将为您分析MySQL DELETE语句和TRUNCATE ...

36314
来自专栏乐沙弥的世界

Oracle 分区索引

    分区索引(或索引分区)主要是针对分区表而言的。随着数据量的不断增长,普通的堆表需要转换到分区表,其索引呢,则对应的转换到分区索引。分区索引的好处是显而易...

1091
来自专栏pangguoming

oracle的行级触发器使用

行级触发器: 当触发器被触发时,要使用被插入、更新或删除的记录中的列值,有时要使用操作前、后列的值. :NEW 修饰符访问操作完成后列的值 :OLD 修饰符访问...

3105
来自专栏奇梦博客

Xiuno BBS 4.0 表结构 XiunoBBS

901

扫码关注云+社区