The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Why this command won't grant privilege for user1 to database userdb?

Discussion in 'Database Discussions' started by gundamz, Jun 3, 2007.

  1. gundamz

    gundamz Well-Known Member

    Joined:
    Mar 27, 2002
    Messages:
    245
    Likes Received:
    0
    Trophy Points:
    16
    Hi all,

    I am trying to grant a user 'user1' to use database 'userdb' by running following command line.

    mysql -uroot -pROOTPASS -e "GRANT ALL PRIVILEGES ON userdb . * TO 'user1'@'localhost' WITH GRANT OPTION;"

    When i check back cpanel , the privilege is still not granted.

    any ideas what is wrong?
     
  2. eNetHosts

    eNetHosts Well-Known Member

    Joined:
    Apr 21, 2007
    Messages:
    195
    Likes Received:
    0
    Trophy Points:
    16
    Try this:

    mysql> grant all privileges on DATABASE.* to USER@localhost;
     
  3. gundamz

    gundamz Well-Known Member

    Joined:
    Mar 27, 2002
    Messages:
    245
    Likes Received:
    0
    Trophy Points:
    16
    i want to run one command line to complete the task as i going put this in a shell script.
     
  4. eNetHosts

    eNetHosts Well-Known Member

    Joined:
    Apr 21, 2007
    Messages:
    195
    Likes Received:
    0
    Trophy Points:
    16
  5. gundamz

    gundamz Well-Known Member

    Joined:
    Mar 27, 2002
    Messages:
    245
    Likes Received:
    0
    Trophy Points:
    16
    i just want run a SINGLE COMMAND like i state in my first post.
    did i issue the wrong command?
     
  6. eNetHosts

    eNetHosts Well-Known Member

    Joined:
    Apr 21, 2007
    Messages:
    195
    Likes Received:
    0
    Trophy Points:
    16
    Firstly, you did not say this in your first post. This is what you said & also there's no need for the CAPITALS!

    Secondly it should probably be, if you want to use that command line:-

    -u root -p ROOTPASS -e GRANT ALL PRIVILEGES ON userdb . * TO 'user1'@'localhost';

    and then try, if you really want the grant option:

    -u root -p ROOTPASS -e GRANT ALL PRIVILEGES ON userdb . * TO 'user1'@'localhost' WITH GRANT OPTION;
     
    #6 eNetHosts, Jun 3, 2007
    Last edited: Jun 3, 2007
  7. gundamz

    gundamz Well-Known Member

    Joined:
    Mar 27, 2002
    Messages:
    245
    Likes Received:
    0
    Trophy Points:
    16
    Your suggested
    doesn't run properly. and it should be -pROOTPASS

    it need semi-colon and password after -P(without space)

    mysql -u root -pROOTPASS -e "GRANT ALL PRIVILEGES ON userdb . * TO 'user1'@'localhost';"

    still, there is no priviledge been assigned.
     
  8. eNetHosts

    eNetHosts Well-Known Member

    Joined:
    Apr 21, 2007
    Messages:
    195
    Likes Received:
    0
    Trophy Points:
    16
    Due to it being a Sunday our MySQL guy is out of the office. If nobody helps you to resolve this before he comes in tomorrow then I shall have a word with him about it and see whether he can help.
     
  9. brianoz

    brianoz Well-Known Member

    Joined:
    Mar 13, 2004
    Messages:
    1,146
    Likes Received:
    6
    Trophy Points:
    38
    Location:
    Melbourne, Australia
    cPanel Access Level:
    Root Administrator
    No idea whether this is right, but shouldn't it be userdb.* ie no space?

    This is a fragment of shell code I have that does the same thing, and works:
    Code:
    mysql -u root -p$pass <<!
    create database ${DB};
    grant all privileges on ${DB}.* to ${DB}@localhost
    identified by '$dbpass' with grant option;
    !
     
    #9 brianoz, Jun 3, 2007
    Last edited: Jun 3, 2007
  10. gundamz

    gundamz Well-Known Member

    Joined:
    Mar 27, 2002
    Messages:
    245
    Likes Received:
    0
    Trophy Points:
    16
    The database already exist, so i run this:

    there is still no priviledge been assigned.
     
  11. eNetHosts

    eNetHosts Well-Known Member

    Joined:
    Apr 21, 2007
    Messages:
    195
    Likes Received:
    0
    Trophy Points:
    16
    I'm not a MySQL tech so I'm going to take a guess using what all 3 of us have contributed - how about:

    mysql -u root -pROOTPASS grant all privileges on userdb.* to user1@localhost identified by 'dbpasswd' with grant option;

    without all the !'s and <<'s
     
  12. brianoz

    brianoz Well-Known Member

    Joined:
    Mar 13, 2004
    Messages:
    1,146
    Likes Received:
    6
    Trophy Points:
    38
    Location:
    Melbourne, Australia
    cPanel Access Level:
    Root Administrator
    Guess you're not a shell programmer either! :rolleyes: :p :D
     
  13. eNetHosts

    eNetHosts Well-Known Member

    Joined:
    Apr 21, 2007
    Messages:
    195
    Likes Received:
    0
    Trophy Points:
    16
    haha, you guessed... how? :p

    Me thinks, it's time to avoid the whole MySQL, shell related topics lol

    Been up 18 hours, (double shift) and waiting for the next tech to come in and take over from me.

    I bet I'm not that far out though ;) We'll see
     
  14. brianoz

    brianoz Well-Known Member

    Joined:
    Mar 13, 2004
    Messages:
    1,146
    Likes Received:
    6
    Trophy Points:
    38
    Location:
    Melbourne, Australia
    cPanel Access Level:
    Root Administrator
    Well the << and ! were kind of a critical part of it all! :)

    The << and ! are whats called a "here document". The stuff between the <<! and the ! is fed to the mysql command on stdin which allows you to fit several mysql commands in neatly. Otherwise you end up messing with something like the following, which isn't as neat:
    echo "...." | mysql -uroot .....
     
  15. brianoz

    brianoz Well-Known Member

    Joined:
    Mar 13, 2004
    Messages:
    1,146
    Likes Received:
    6
    Trophy Points:
    38
    Location:
    Melbourne, Australia
    cPanel Access Level:
    Root Administrator
    Hmmmm ... I'd try running the whole thing as I supplied and see if it works, on a differently named database. I'd then try checking to see if the user exists, as that may stop it working. Then check the database exists. See if you can enable verbose SQL errors somehow and check what ensues from that.
     
  16. eNetHosts

    eNetHosts Well-Known Member

    Joined:
    Apr 21, 2007
    Messages:
    195
    Likes Received:
    0
    Trophy Points:
    16
    Oh well, can't blame a guy for trying. Hope you get this sorted ASAP.

    Anyway, my time's up, have a good day!
     
  17. gundamz

    gundamz Well-Known Member

    Joined:
    Mar 27, 2002
    Messages:
    245
    Likes Received:
    0
    Trophy Points:
    16
    Anyone know what went wrong with my command?
     
  18. eNetHosts

    eNetHosts Well-Known Member

    Joined:
    Apr 21, 2007
    Messages:
    195
    Likes Received:
    0
    Trophy Points:
    16
    How about?

    mysql -uroot -e "GRANT ALL PRIVILEGES ON userdb.* TO 'user'@localhost
    -> IDENTIFIED BY 'password' WITH GRANT OPTION;"
     
  19. eNetHosts

    eNetHosts Well-Known Member

    Joined:
    Apr 21, 2007
    Messages:
    195
    Likes Received:
    0
    Trophy Points:
    16
    Found this

    % mysql -u root mysql
    mysql> GRANT ALL PRIVILEGES ON *.* TO user@localhost
    -> IDENTIFIED BY 'xx' WITH GRANT OPTION;

    lurking around in some file so just took one of those guesses at it.
     
  20. gundamz

    gundamz Well-Known Member

    Joined:
    Mar 27, 2002
    Messages:
    245
    Likes Received:
    0
    Trophy Points:
    16
    brianoz already suggested that which doesn't work.
     
Loading...

Share This Page