How To Grant Mysql Privileges Only To A Specific Row

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. CREATE USER A ; GRANT UPDATE ON student

Solution 1:

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.

CREATESCHEMA`example` ;

CREATETABLE`example`.`student` (
      `id`INTNOTNULL,
      `name`VARCHAR(45) NULL,
      `email`VARCHAR(45) NULL,
      PRIMARYKEY (`id`));

INSERTINTO`example`.`student` (`id`, `name`, `email`) VALUES ('1', 'bob', 'bob@bob.com');


USE`example`;
CREATEORREPLACESQLSECURITYDEFINERVIEW`student_1`ASSELECT`student`.`id`AS`id`,
        `student`.`name`AS`name`,
        `student`.`email`AS`email`FROM`student`WHERE
        (`student`.`id` = '1');

CREATEUSER'student_1_user'@'localhost'IDENTIFIEDBY'user_password';

    GRANTSELECT,UPDATEON example.student_1TO student_1_user@localhostIDENTIFIEDBY'user_password';

UPDATE example.student_1SET email='newemail@bob.com'; // note no primary key needed or allowed