Home | 簡體中文 | 繁體中文 | 雜文 | 知乎專欄 | Github | OSChina 博客 | 雲社區 | 雲棲社區 | Facebook | Linkedin | 視頻教程 | 打賞(Donations) | About
知乎專欄多維度架構 微信號 netkiller-ebook | QQ群:128659835 請註明“讀者”

9.8. 用戶/角色認證

本小節我們實現一個功能,當用戶插入,修改或者刪除數據時,判斷該操作是否具備應有的權限。如果權限不符合就拒絶操作同時提示用戶。

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