知乎專欄 | 多維度架構 | 微信號 netkiller-ebook | QQ群:128659835 請註明“讀者” |
本小節我們實現一個功能,當用戶插入,修改或者刪除數據時,判斷該操作是否具備應有的權限。如果權限不符合就拒絶操作同時提示用戶。
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. */