Cannot login to mysql as user other than root

English Applications
1

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:


> 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:


> 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

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

Thom

3

pixelpusher wrote:
> Oh, and setting the password after the user has been created doesn’t
> work, either.

what happens if you install and set it up using the documentation?

–
palladium

4

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.

5

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.

6

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.

7

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.

8

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?