如何仅将 MySQL 权限授予特定行 [英] how to grant MySQL privileges only to a specific row
问题描述
想象有一张学生桌
student(id,name,city)
我想创建一个用户 A 并仅授予更新 id=10 的记录的权限.
Imagine there is a student table
student(id,name,city)
I want to create a user A and grant permission only to update record where id=10.
创建用户A； GRANT UPDATE ON student TO A WHERE student.id=10;
我试过了，但它不起作用.
I tried this and it does not work.
推荐答案
不是单行，而是包含单行的视图，该视图将依次更新实际的真实表.
No not a single row but a view that contains a single row which will, in turn, will update the actual real table.
这可以通过每个学生的特定表视图来完成(是的，这将是一个混乱的数据库结构).仅在仅选择/更新的情况下授予此用户对视图的访问权限，并且主键将不可更新.主表会在视图更新时自行更新.
This can be done via specific table view per student (yes it will be a messy DB structure). Grant access to the view for this user only alow select/updates only and the primary key will be non-updateable. The main table will update itself when the view is updated.
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
这篇关于如何仅将 MySQL 权限授予特定行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
