Privilege to select only user's own tuple only in mysql

Question

I create a table named person which columns such as name, sex, occupation and so on. I want to give each user select grant over that user’s own tuple only. I assume that each users use their own name as their user ID. So I created view as

create view mine as select * from person where name=current_user;

And I give privilege all user

grant select on mine to public;

But it does not work. How to grant privilege to all user in mysql?

And, in the person table, name column store only just name. But current_user replies name@localhost. So it does not match. How to solve that?

• Ju Ju: yes. I mean row.

Answer 1

You may probably make use of LEFT and INSTR built in functions to strip out the host name from the user@host.

CREATE VIEW mine 
AS 
SELECT * FROM person 
 WHERE `name`=LEFT(CURRENT_USER(),INSTR(current_user(), '@')-1);

• Ju Ju: Yes, It works. Thank you. But how to grant the access right to public? Is there a way or just I have to grant 100 persons 100 times?

Answer 2

SQL does not solve all problems. And some of the problems it can solve are rather messy to code. I suggest you implement the solution to this problem in your application.