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












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?










share|improve this question
















bumped to the homepage by Community 2 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
















  • yes. I mean row.

    – Ju Ju
    Dec 3 '15 at 15:55
















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?










share|improve this question
















bumped to the homepage by Community 2 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
















  • yes. I mean row.

    – Ju Ju
    Dec 3 '15 at 15:55














0












0








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?










share|improve this 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?







mysql






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Dec 4 '15 at 14:41







Ju Ju

















asked Dec 3 '15 at 14:09









Ju JuJu Ju

4715




4715





bumped to the homepage by Community 2 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.







bumped to the homepage by Community 2 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.















  • yes. I mean row.

    – Ju Ju
    Dec 3 '15 at 15:55



















  • yes. I mean row.

    – Ju Ju
    Dec 3 '15 at 15:55

















yes. I mean row.

– Ju Ju
Dec 3 '15 at 15:55





yes. I mean row.

– Ju Ju
Dec 3 '15 at 15:55










2 Answers
2






active

oldest

votes


















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





share|improve this answer


























  • 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.






share|improve this answer























    Your Answer








    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "182"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: false,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f122795%2fprivilege-to-select-only-users-own-tuple-only-in-mysql%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









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





    share|improve this answer


























    • 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














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





    share|improve this answer


























    • 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












    0








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





    share|improve this 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);






    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited Dec 4 '15 at 4:08









    Erik

    3,99931954




    3,99931954










    answered Dec 4 '15 at 3:39









    JasJas

    1161




    1161













    • 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





















    • 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



















    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







    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.






    share|improve this answer




























      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.






      share|improve this answer


























        0












        0








        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.






        share|improve this answer













        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.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Dec 12 '15 at 6:09









        Rick JamesRick James

        43k22259




        43k22259






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Database Administrators Stack Exchange!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f122795%2fprivilege-to-select-only-users-own-tuple-only-in-mysql%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Liste der Baudenkmale in Friedland (Mecklenburg)

            Single-Malt-Whisky

            Czorneboh