如何仅向特定行授予MySQL特权 [英] how to grant MySQL privileges only to a specific row

问题描述

想象有一个学生桌
学生(ID，名称，城市)
我要创建用户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更新到一个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', '[e-mail]');


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='[e-mail]'; // note no primary key needed or allowed

这篇关于如何仅向特定行授予MySQL特权的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！