[SOLVED] Cannot access MySQL server other than user=root

Harry2o · 04-13-2011, 05:00 AM

Hi,

I've tried every manual there is for creating new MySQL users ... but without success.
I keep getting

Code:

ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)

even though I just created it with all privileges ... WITH a password that I DO remember

.

Fortunately I still have access via the MySQL root user (so I try not to disturb that user, because I have had computers with THAT account not accessible as well - even WITH resetting the password via skip-grant-tables).

Any ideas what's wrong with my grant tables?

EricTRA · 04-13-2011, 05:36 AM

Hello,

Can you post the exact commands you used to create a new user and grant him privileges? Did you get any error or warning when running those commands? Also enter as root in a MySQL console and execute:

Code:

show grants for 'test'@'localhost';

and post the output here.

Kind regards,

Eric

Harry2o · 04-13-2011, 07:09 AM

you got it

Code:

harry@dragonfly:~$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 78687
Server version: 5.1.49-1ubuntu8.1 (Ubuntu)
[...]

mysql> create user 'test'@'%' identified by 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'test'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'test'@'%';
+--------------------------------------------------------------------------------------------------------------------------------+
| Grants for test@%                                                                                                              |
+--------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> Bye
harry@dragonfly:~$ mysql -u test -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)
harry@dragonfly:~$

And yes, I did enter the password correctly

EricTRA · 04-13-2011, 07:19 AM

Quote:

Originally Posted by Harry2o

you got it

And yes, I did enter the password correctly

Hi,

Thanks for the info. I think this is your problem (from MySQL Reference Manual):

Quote:

After connecting to the server as root, you can add new accounts. The following statements use GRANT to set up four new accounts:

mysql> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
-> WITH GRANT OPTION;
mysql> CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
-> WITH GRANT OPTION;
mysql> CREATE USER 'admin'@'localhost';
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
mysql> CREATE USER 'dummy'@'localhost';
The accounts created by these statements have the following properties:

Two of the accounts have a user name of monty and a password of some_pass. Both accounts are superuser accounts with full privileges to do anything. The 'monty'@'localhost' account can be used only when connecting from the local host. The 'monty'@'%' account uses the '%' wildcard for the host part, so it can be used to connect from any host.

It is necessary to have both accounts for monty to be able to connect from anywhere as monty. Without the localhost account, the anonymous-user account for localhost that is created by mysql_install_db would take precedence when monty connects from the local host. As a result, monty would be treated as an anonymous user. The reason for this is that the anonymous-user account has a more specific Host column value than the 'monty'@'%' account and thus comes earlier in the user table sort order. (user table sorting is discussed in Section 5.4.4, “Access Control, Stage 1: Connection Verification”.)

So create a user test@localhost with the same password to take precedence over the anonymous default user for localhost in MySQL and try again. Hope it helps.

Kind regards,

Eric

Harry2o · 04-13-2011, 08:12 AM

Thanks, Eric, I think that was the problem (taking the user "Any" is the named anonymous).

Checking the machines where the creation of a new user was successful and usable didn't have that user "Any". I wonder why some machines (i.e. some installations of MySQL server) available to me do have that(those) user(s) (for different host origins) and others don't ...

However, I think that fixed the problem!

EricTRA · 04-13-2011, 08:36 AM

Hello,

Glad you got a solution and thanks for marking the thread solved. On a side note, the anonymous user gets created normally upon first run of the MySQL server (mysql_install_db) so maybe there's the difference. Check if you have different versions of MySQL server on your servers and check differences between that script on those different servers. Or maybe on the other servers someone executed mysql_secure_installation instead of mysql_install_db which deletes the default test database and anonymous user. That might shed some light. Have fun with Linux.

Kind regards,

Eric