数据库安全·用户/角色认证
以下节选择《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. */ 本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2016-08-25，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读