SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    What's HTML?
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    1,701
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Creating new MySQL users

    I finally got MySQL installed and running properly on my machine (yes!), thanks for the help guys. But since my box (Apache 1.3/Red Hat 7.1) hosts several domains, I'm guessing it would be more secure to have a different MySQL user account for each domain, especially since each domain will have it's own database and a variety of people will need access to particular databases.

    So, how exactly do I create a new MySQL user account and restrict its access to a given database?
    Ryan Kuhle - A Proud Advisor - Got Questions? Just Ask!
    Get your website started for less than $20! Click Here

  2. #2
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Log into MySQL from the command prompt usuall through ssh or telnet then follow these steps

    mysql>grant all privileges on dbname.* to username@localhost identified by 'password'

    mysql>flush privileges


    And thats it.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  3. #3
    What's HTML?
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    1,701
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What would be a good way to test whether this worked right? From the code you gave me I replaced "dbname", "username", and "password", correct?

    I went ahead and set up phpMyAdmin so I could test it, I edited the correct file but I keep receiving this response:
    Warning: MySQL Connection Failed: Access denied for user: 'idream@localhost' (Using password: YES) in /www/www.idreamportal.com/phpadmin/phpMyAdmin/lib.inc.php on line 255
    Error

    Any ideas?
    Ryan Kuhle - A Proud Advisor - Got Questions? Just Ask!
    Get your website started for less than $20! Click Here

  4. #4
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Did you try logging into mysql from the commandline using the new username and passsword

    $>mysql -u newusername -p


    That will be the fastest way to see if it worked. If it still doesn't work make sure you ran the flush privileges


    And one more thing you did create the new database before you issued the grant statement right?
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  5. #5
    What's HTML?
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    1,701
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, yes I did create the database beforehand. I tried logging into MySQL using the newly created user name but received an Access Denied (ick!). It was an Error 1045 if that means anything. Oh, I did issue the flush privileges when I first set it up.

    Anything else I might be missing? Or should I try to recreate the user?
    Ryan Kuhle - A Proud Advisor - Got Questions? Just Ask!
    Get your website started for less than $20! Click Here

  6. #6
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Look in the user table of the mysql database to see if it added the user. It should have a record for each user, make sure there is one for the one you created. If not try running the grant statement again and flush privileges
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  7. #7
    What's HTML?
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    1,701
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok I just checked and it did in fact create the user, and password. Should I try to recreate the password, maybe I entered it wrong? Also, how would I do this?
    Ryan Kuhle - A Proud Advisor - Got Questions? Just Ask!
    Get your website started for less than $20! Click Here

  8. #8
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    update user set password = password('newpassword') where username = 'username';


    Maybe try reloading mysql get out of the mysql program and enter this on the commandline

    mysqladmin -u username -p reload


    Make sure to use a user who has root privs in MySQL
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  9. #9
    What's HTML?
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    1,701
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You not only do PHP, but you also save lives! Thanks a lot man.

    I think my problem was something as minute as bumping a wrong key when entering the password. Oh, one more thing. In the "user" table, the user account column isn't called "username", but called "User". On my box anyway...
    Ryan Kuhle - A Proud Advisor - Got Questions? Just Ask!
    Get your website started for less than $20! Click Here

  10. #10
    Talk to the /dev/null Theiggsta's Avatar
    Join Date
    Mar 2001
    Location
    Tampa, FL
    Posts
    376
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    GRANT ALL PRIVILEGES ON dbname.* TO user@localhost IDENTIFIED BY 'somepass' WITH GRANT OPTION

    change the info there and if you want to use any host, use user@'%'

    that works
    Aaron "Theiggsta" Kalin
    Pixel Martini
    Ruby and Rails Developer

  11. #11
    What's HTML?
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    1,701
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've been doing this to create new MySQL users and allow them to access a specific database:
    mysql>grant all privileges on dbname.* to username@localhost identified by 'password'
    mysql>flush privileges

    But, the other day I was playing around and I did "show databases;" just for the hell of it. And sure enough, my user had access to ALL the databases, including test and mysql. Should I try doing a REVOKE and take away their privileges for the other databases, or did I do something wrong?
    Ryan Kuhle - A Proud Advisor - Got Questions? Just Ask!
    Get your website started for less than $20! Click Here

  12. #12
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The user had access or could just see the databases. Any valid user can do a show databases; and see all the databases but they should not be able to get into the database. If that happens then you have a problem.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  13. #13
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    FYI, this is all covered in Part 8 (MySQL Administration) of my PHP/MySQL series. Recommended read for anyone wrestling with these issues.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference


Bookmarks

Posting Permissions

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