Mysql – Privilege to select only user’s own tuple only in thesql

MySQL

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?

Best Answer

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);
Related Question