TransWikia.com

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

Database Administrators Asked by Ju Ju on December 28, 2021

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?

2 Answers

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.

Answered by Rick James on December 28, 2021

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

Answered by Jas on December 28, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP