Results 1 to 7 of 7

Thread: Cannot login to mysql as user other than root

  1. #1

    Question Cannot login to mysql as user other than root

    I cannot see what the problem is here. I have installed MySQL 5.1.36 via YaST on my openSUSE 11.2 (32-bit) system.

    I can log in as root. I can "create user MY_user identified by 'my_passwd';" and a new user is created. But I cannot log in to the database using that new user name. I keep getting the message:
    Code:
    > mysql -b -u MY_user -p
    Enter password:
    ERROR 1045 (28000): Access denied for user 'MY_user'@'localhost' (using password: YES)
    HOWEVER, logging in without a password works but shouldn't:
    Code:
    > mysql -b -u MY_user
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 137
    Server version: 5.1.36-log SUSE MySQL RPM
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql>
    Can anyone tell me what is going on here? This is driving me nuts!

    Thom

  2. #2

    Default Re: Cannot login to mysql as user other than root

    Oh, and setting the password after the user has been created doesn't work, either.

    Thom

  3. #3
    Join Date
    Jun 2008
    Location
    West Yorkshire, UK
    Posts
    3,454

    Default Re: Cannot login to mysql as user other than root

    After creating a user, you also need to grant them privileges. The lowest is
    grant usage on *.* to 'user'@'host';
    I typically use
    grant select, insert, update on database.* to 'user'@'host' identified by 'password';

    See info mysql for all the options.

  4. #4

    Default Re: Cannot login to mysql as user other than root

    Nope, that still give me Access denied. And doesn't explain why it would let me in using the account but without providing a password.

  5. #5
    Join Date
    Jun 2008
    Location
    West Yorkshire, UK
    Posts
    3,454

    Default Re: Cannot login to mysql as user other than root

    I suggest you look at mysql.user as root. Normally it comes with two anonymous users and root on two different hosts. Remove the anonymous users if they are still there and check the status of any other users you have added.

  6. #6
    Join Date
    Jun 2008
    Location
    Groningen, Netherlands
    Posts
    20,037
    Blog Entries
    14

    Default Re: Cannot login to mysql as user other than root

    To completely open up a database called X for a user Y at 'localhost' with password ZZZZ, do:

    GRANT ALL ON ZZZZ.* TO Y@localhost IDENTIFIED BY 'ZZZZ';
    FLUSH PRIVILIGES;
    quit

    Don't forget the ".*",don't forget the single quotes around the password, no quotes around the username or host. I'd rather not use capitals in the username or databasename.
    ° Appreciate my reply? Click the star and let me know why.

    ° Perfection is not gonna happen. No way.

    https://en.opensuse.org/openSUSE:Board#Members
    http://en.opensuse.org/User:Knurpht
    http://nl.opensuse.org/Gebruiker:Knurpht

  7. #7
    Join Date
    Jan 2009
    Location
    Switzerland
    Posts
    1,529

    Default Re: Cannot login to mysql as user other than root

    In addition to the above you can add 'WITH GRANT OPTION;' to the above query, right at the end. That means the newly created user may issue grants. And don't forget to assign a password to the root user. For browsing the mysql info file I recommend to use 'pinfo mysql' (you may have to install the pinfo package for this).

    Using 'GRANT' has the advantage that 'FLUSH PRIVILEGES;' is not required, but doesn't harm.

    Oh, and setting the password after the user has been created doesn't work, either.
    What did you try to do it?
    Technology is 'stuff that doesn't work yet.' -- Bran Ferren

Tags for this Thread

Posting Permissions

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