| View previous topic :: View next topic |
| Author |
Message |
corey_s
Apprentice
Joined: 29 Jun 2003
Posts: 264
|
 Posted: Sat Jul 31, 2010 7:25 pm Post subject: [SOLVED] mysql issue |
 |
|
I'm working with an older version of mysql (4.1.14) on an older box.
Recently some scripts on the box started failing with access denied message when connecting to the db.
Here's the problem:
For some reason, even though a password is set for the db user - I cannot log into the db with the '-p' flag to mysql.... it only allows access for this user when connecting _without_ a password. I'm not understanding why this is happening.
mysql -h localhost -u myuser -p
Fails with a:
ERROR 1045: Access denied for user 'myuser'@'localhost' (using password: YES)
.... however:
mysql -h localhost -u myuser
(no -p flag, no password provided)
works just fine.
What's the deal? Any ideas on how to fix this, or where to look for what might be causing this behavior?
Last edited by corey_s on Sun Aug 01, 2010 12:23 am; edited 1 time in total |
|
| Back to top |
|
 |
mokia
n00b
Joined: 01 Feb 2010
Posts: 63
Location: Hungary
|
| Posted: Sat Jul 31, 2010 8:29 pm Post subject: |
|
|
Execute
| Code: |
use mysql
SELECT host,user,password from user WHERE user='myuser' AND host='localhost'
|
Do you have a password hash in the returned line? |
|
| Back to top |
|
|
corey_s
Apprentice
Joined: 29 Jun 2003
Posts: 264
|
| Posted: Sat Jul 31, 2010 8:41 pm Post subject: |
|
|
| mokia wrote: | Execute
| Code: |
use mysql
SELECT host,user,password from user WHERE user='myuser' AND host='localhost'
|
Do you have a password hash in the returned line? |
Thanks a ton for the quick response - very much appreciated!
Host has '%', so the above query was null; here's output without querying on localhost:
| Code: |
mysql> SELECT host,user,password from user WHERE user='myuser';
+------+----------+-------------------------------------------+
| host | user | password |
+------+----------+-------------------------------------------+
| % | myuser | *38ED700...ED |
+------+----------+-------------------------------------------+
|
Is '%' some sort of wildcard?
(I have another machine that has the same value for host, and it's working ok - so I don't _think_ that is the particular issue I'm experiencing) |
|
| Back to top |
|
|
mokia
n00b
Joined: 01 Feb 2010
Posts: 63
Location: Hungary
|
| Posted: Sat Jul 31, 2010 9:14 pm Post subject: root |
|
|
And the hash was 16 or 41-byte long?
| corey_s wrote: |
Is '%' some sort of wildcard?
|
Yes. its means any host.
| Code: |
SHOW GRANTS FOR 'myuser'@'localhost';
SHOW GRANTS FOR 'myuser'@'%';
|
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'localhost' IDENTIFIED BY PASSWORD '*mypasshash' WITH GRANT OPTION
Last edited by mokia on Sat Jul 31, 2010 9:26 pm; edited 1 time in total |
|
| Back to top |
|
|
corey_s
Apprentice
Joined: 29 Jun 2003
Posts: 264
|
| Posted: Sat Jul 31, 2010 9:25 pm Post subject: |
|
|
| mokia wrote: | | And the hash was 16 or 41-byte long? |
It _was_ 16, originally - then I changed it with:
update user set password=PASSWORD("mypasswd") where User='myuser';
After which it was 41 bytes.
So I recently changed it back (again) to try it out; and things still aren't working.
I know at this point it sounds like things are all over the place, but it's not that bad:
#1 - I saw that there was an issue with our scripts
#2 - realized the issue was do to a sudden authentication issue (I haven't touched the mysql server in months, so this is suspicious)
#3 - found that I could login to the mysql server as user 'myuser' _without_ a password
#4 - figured maybe the myuser password had for some reason been modified or something, so I issued:
update user set password=PASSWORD("mypasswd") where User='myuser';
#5 - noticed the hash went from 16 bytes to 41 bytes
#6 - restarted mysql service without the --skip-grant-tables option
#7 - tried logging into mysql as myuser (/usr/local/mysql/bin/mysql -h 127.0.0.1 -u myuser -p), but got the auth error
#8 - tried again, without the password (-p) option -- works fine... (why is it allowing me to login _without_ a password, even though a password is set?)
#9 - went ahead and re-set the password again directly, using the original 16-byte hash string that was originally set
#10 - same exact problem: I can login as user 'myuser' _without_ a password, even though a password is set
... totally confused. (I'm more experienced with postgresql - so I'm finding myself at a loss here with this mysql issue)
Thanks! |
|
| Back to top |
|
|
corey_s
Apprentice
Joined: 29 Jun 2003
Posts: 264
|
| Posted: Sat Jul 31, 2010 9:26 pm Post subject: Re: root |
|
|
| mokia wrote: |
| Code: |
SHOW GRANTS FOR 'myuser'@'localhost';
SHOW GRANTS FOR 'myuser'@'%';
|
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'localhost' IDENTIFIED BY PASSWORD '*mypasshash' WITH GRANT OPTION |
Thanks - I'll try this right now! |
|
| Back to top |
|
|
corey_s
Apprentice
Joined: 29 Jun 2003
Posts: 264
|
| Posted: Sat Jul 31, 2010 9:38 pm Post subject: |
|
|
Brilliant - I can't login to mysql as root either.
And when I turn on the --skip-grant-tables switch, I'm unable to issue any GRANT commands:
"ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement" |
|
| Back to top |
|
|
corey_s
Apprentice
Joined: 29 Jun 2003
Posts: 264
|
| Posted: Sat Jul 31, 2010 9:51 pm Post subject: |
|
|
One other detail:
I can login using:
/usr/local/mysql/bin/mysql -h 127.0.0.1 -u myuser -p
... if I just hit enter at the password prompt.
But if I enter a password, I get the auth error. |
|
| Back to top |
|
|
mokia
n00b
Joined: 01 Feb 2010
Posts: 63
Location: Hungary
|
| Posted: Sat Jul 31, 2010 10:13 pm Post subject: |
|
|
| So you cant login as root if you run the server wit grant tables? |
|
| Back to top |
|
|
corey_s
Apprentice
Joined: 29 Jun 2003
Posts: 264
|
| Posted: Sat Jul 31, 2010 10:19 pm Post subject: |
|
|
| mokia wrote: | | So you cant login as root if you run the server wit grant tables? |
Correct (assuming I read that right).
If running mysql without --skip-grant-tables, then I'm unable to login to mysql as root. (it could be that the root password has changed, along with whatever else was changed to tweak with the 'myuser' user)
I'm attempting to contact my client to verify whether he had been friggin' with the riggin', but haven't reached him yet.
What's really vexing - is I can login to mysql as user 'myuser' just fine _without_a_password_ (or using a blank password)... That's no good! I could apply a quick fix, and modify the (perl) scripts to connect w/o a pwd... but no way would I do that. I really don't get why I can login without a password, even though a password is set for the user in question.
Is there some mysql config file, or some other table or something that would cause such behavior? |
|
| Back to top |
|
|
corey_s
Apprentice
Joined: 29 Jun 2003
Posts: 264
|
| Posted: Sat Jul 31, 2010 10:24 pm Post subject: |
|
|
Also:
All the grant privs were enabled for 'myuser':
| Code: |
mysql> select * from user where user = 'myuser';
+------+----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+
| Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections |
+------+----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+
| % | myuser | 3a...12 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 |
+------+----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+
|
I went ahead and turned them all off:
| Code: |
mysql> select * from user where user = 'myuser';
+------+----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+
| Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections |
+------+----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+
| % | myuser | 3a...12 | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 |
+------+----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+
1 row in set (0.00 sec)
|
Then issued a "FLUSH PRIVILEGES;"
Then restarted mysql with grant tables turned back on... but same problem:
User 'myuser' has a password in the user table, but I can only log into mysql with that user if I issue a blank password, or if I connect without the -p option. |
|
| Back to top |
|
|
corey_s
Apprentice
Joined: 29 Jun 2003
Posts: 264
|
| Posted: Sat Jul 31, 2010 10:25 pm Post subject: |
|
|
| By the way - thanks so much for your time and help on this; I really appreciate it! |
|
| Back to top |
|
|
mokia
n00b
Joined: 01 Feb 2010
Posts: 63
Location: Hungary
|
|
| Back to top |
|
|
corey_s
Apprentice
Joined: 29 Jun 2003
Posts: 264
|
| Posted: Sat Jul 31, 2010 11:10 pm Post subject: |
|
|
ok - well, I re-set the root mysql password so that I could login into mysql without the --skip-grant-tables switch.
I then issued the GRANT commands you suggested.
I also tried the mysql_fix_privilege_tables script.
I also reset the 'myuser' password once again and flushed the privileges.
... same issue:
Although I can now log into the mysql db as root with no issue (using the reset password), the 'myuser' issue still exists: I can _only_ log into the 'myuser' db account _without_ a password... freaking crazy.
Thanks man for all your suggestions and advice... guess at this point I'll try the mysql forums/mail-list.
Cheers |
|
| Back to top |
|
|
corey_s
Apprentice
Joined: 29 Jun 2003
Posts: 264
|
| Posted: Sun Aug 01, 2010 12:22 am Post subject: |
|
|
Ok - just to follow and provide closure; got some help on the mysql mailling list:
| Quote: |
after login to mysql issue:
select user();
and
select current_user();
and post output
you will see they dont match.
|
| Quote: |
login as root and remove 'empty' user ,issue:
delete from mysql.user where user='';
flush privileges;
mysql authentication system is logging you on as 'empty' user.
you will be ok after that 
|
... so, recently for whatever reason, someone, somehow created an empty user on this server - which caused mysql to default to loging in as the empty user.
When I removed the empty user, the problem I was experiencing went away.
Cheers |
|
| Back to top |
|
|
|
Networking & Security
