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?

  • yes. I mean row.
    – Ju Ju
    Commented Dec 3, 2015 at 15:55

2 Answers 2


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

SELECT * FROM person 
 WHERE `name`=LEFT(CURRENT_USER(),INSTR(current_user(), '@')-1);
  • 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?
    – Ju Ju
    Commented Dec 4, 2015 at 7:57

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.