0

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 Dec 3 '15 at 15:55
0

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);
  • 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 Dec 4 '15 at 7:57
0

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, privacy policy and cookie policy

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