Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2004
    Posts
    6

    Unanswered: Giving user permission to a table but not the rest of the database

    I'm sorry for such a simple question, but I've been trying for a week now to solve this and haven't been able to find a solution.

    user: guestuser
    database: db1
    table: tb1

    I've been trying to give guest user insert privileges to tb1 but not to the rest of the tables in db1. I've tried:

    grant select on db1.tb1 to guestuser;

    which works... until I reboot my machine. Then it says that user can't select db1. But if I give guestuser select privileges on db1 in the mysql database, then he has access to all tables in db1. Anyone has a solution? Thanks in advance.

  2. #2
    Join Date
    Sep 2003
    Posts
    4
    Quote Originally Posted by Sleepingkirby
    I'm sorry for such a simple question, but I've been trying for a week now to solve this and haven't been able to find a solution.

    user: guestuser
    database: db1
    table: tb1

    I've been trying to give guest user insert privileges to tb1 but not to the rest of the tables in db1. I've tried:

    grant select on db1.tb1 to guestuser;

    which works... until I reboot my machine. Then it says that user can't select db1. But if I give guestuser select privileges on db1 in the mysql database, then he has access to all tables in db1. Anyone has a solution? Thanks in advance.
    ::If you are trying to give INSERT privileges to a user you need to add INSERT to your GRANT.

    try:
    Code:
    GRANT Insert ON db1.tb1 TO 'guestuser'@'%';
    FLUSH PRIVILEGES;
    or:
    Code:
    GRANT Select, Insert ON db1.tb1 TO 'guestuser'@'%';
    FLUSH PRIVILEGES;
    The 'FLUSH PRIVILEGES' at the end will tell the server to reset the privileges table and update with the new information. That might be the problem with it not saving.

    Also, make sure that the user account that your using ( if not the root account ) has the rights to do so.
    Last edited by Danov; 06-07-04 at 10:23.

  3. #3
    Join Date
    Jun 2004
    Posts
    6
    sorry about that, I meant insert. My syntax is correct, the permissions at the mysql db shows it's correct. but...

    But either way, the guestuser is able to select the table and insert into the table until I reboot the computer. After that, mysql gives me a "access denied to db1"

    any other suggestions?

  4. #4
    Join Date
    Sep 2003
    Posts
    4
    What version of MySQL Server are you running ?

    and have you tried the FLUSH PRIVILEGES; after your
    grant statement ?

  5. #5
    Join Date
    Jun 2004
    Posts
    6
    version 4.1.1a

    yes, flush privileges everytime I change permissions. Also, that doesn't matter since my problem happens when my *reboots* which means, the service was restarted.

  6. #6
    Join Date
    Oct 2003
    Posts
    706
    Notice that there are two permissions required here:
    • The right to use table tb1 in database db1.
    • The right to have access to database db1 in the first place!

    You have granted access to the table, but the user isn't getting that far. He doesn't have "the right to access the database in the first place."
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  7. #7
    Join Date
    Jun 2004
    Posts
    6
    Right, but if I give him access to the database;

    grant select on db1.* to guestuser;

    he will have access to "all" the tables in the database and any new tables I make in that database. I can't allow that. Is the only solution to give him access to the whole database then restrict access? That just doesn't make sense since mysql is suppose to be dealing with permission from the ground up basis (assume users with no permissions then start from there). Anyways, that's my frustration. Thanks for the try, sundailvcs. I appreciate it. If you have any more suggestions, please tell me.

  8. #8
    Join Date
    Dec 2003
    Location
    Houston, TX
    Posts
    21
    phpMyAdmin and Webmin will both allow you to easily setup Table permissions for different users very quickly.
    I do not fear computers. I fear the lack of them.
    -Isaac Asimov (1920 - 1992)

  9. #9
    Join Date
    Jun 2004
    Posts
    6
    but both are gui which doesn't change the underlying problem, which is mysql itself.

    BTW, I use phpmyadmin too, same problem.
    Last edited by Sleepingkirby; 06-09-04 at 02:02.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •