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

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;ENDCREATE 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;ENDCREATE 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 系列 手札