而是包含 的视图将依次更新实际的实际表。
这可以通过每个学生的特定表视图来完成(是的,这将是一个混乱的数据库结构)。仅在选择/更新之后才授予该用户对该视图的访问权限,并且主键将不可更新。当更新视图时,主表将自动更新。
CREATE SCHEMA `example` ;
CREATE TABLE `example`.`student` (
`id` INT NOT NULL,
`name` VARCHAR(45) NULL,
`email` VARCHAR(45) NULL,
PRIMARY KEY (`id`));
INSERT INTO `example`.`student` (`id`, `name`, `email`) VALUES ('1', 'bob', 'bob@bob.com');
USE `example`;
CREATE
OR REPLACE sql Security DEFINER
VIEW `student_1` AS
SELECT
`student`.`id` AS `id`,
`student`.`name` AS `name`,
`student`.`email` AS `email`
FROM
`student`
WHERE
(`student`.`id` = '1');
CREATE USER 'student_1_user'@'localhost' IDENTIFIED BY 'user_password';
GRANT SELECT,UPDATE ON example.student_1 TO student_1_user@localhost IDENTIFIED BY 'user_password';
UPDATE example.student_1 SET email='newemail@bob.com'; // note no primary key needed or allowed