List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:December 1 2011 1:53pm
Subject:Messed up authentication
View as plain text  
First off, as part of this bit of mayhem I found an answer for the recent (and common)
question how to verify if the server is properly started with skip-name-resolve, as
there's no status variable for that: if you perform a grant on a hostname instead of an
IP, it'll spew a warning at you :-) 

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON `ussr`.* TO 'ussr'@'phpstag1' IDENTIFIED
> BY PASSWORD '*'; 
Query OK, 0 rows affected, 1 warning (0.00 sec) 

mysql> show warnings; 
+---------+------+--------------------------------------------------------------------------------------------------------------+

| Level | Code | Message | 
+---------+------+--------------------------------------------------------------------------------------------------------------+

| Warning | 1285 | MySQL is started in --skip-name-resolve mode; you must restart it
without this switch for this grant to work | 
+---------+------+--------------------------------------------------------------------------------------------------------------+


Useful to know :-) 


Now, my bit of crap for this morning. Note that I've already worked around it, just wanted
to throw it out here in case someone has a clue about possible causes or needs to know
they're not alone. 

The setup is simple: a site on a staging environment that hasn't been used in a few weeks
is suddenly unable to connect to the database. The error message makes it clear that it's
an authentication issue, not a connection issue. The documented grant matches the host,
the host matches the IP in the hosts file. So, I decide to go have a look at what's
effectively granted. 

mysql> show grants for ussr@phpstag1; 
ERROR 1141 (42000): There is no such grant defined for user 'ussr' on host 'phpstag1' 

The fuck? 

mysql> select user, host from user where user = 'ussr' and host = 'phpstag1'; 
+------+----------+ 
| user | host | 
+------+----------+ 
| ussr | phpstag1 | 
+------+----------+ 
1 row in set (0.00 sec) 

mysql> select user, host from db where user = 'ussr' and host = 'phpstag1'; 
+------+----------+ 
| user | host | 
+------+----------+ 
| ussr | phpstag1 | 
+------+----------+ 
1 row in set (0.00 sec) 

So the entries are there, do not have stray spaces or whatnot, but are not found. Flush
hosts, flush privileges, repair extended and even restarting the service are all for
nought. Since it claims it doesn't exist, I try reinserting it, which also yields funny
messages. 

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON `ussr`.* TO 'ussr'@'phpstag1' IDENTIFIED
> BY PASSWORD '*'; 
ERROR 1133 (42000): Can't find any matching row in the user table 

In the end, I deleted the relevant rows from the privilege tables, flushed, regranted and
then it worked as expected. 

Now, the point was really that I had to delete that user anyway as we're converting to
skip-name-resolve (hence the first bit of this mail), but this seemed very strange
indeed... until it hit me. It's a half-bug, probably due to a quirky design decision
somewhere along the road: when running in skip-name-resolve, the "show grants" statement
does not look up hostnames from the privilege tables, but it does inside the privilege
cache. Thus, such grants work - wait, do they? I checked :-) 

mysql> grant all privileges on ussr.* to czar@phpstag1 identified by 'czar'; 
Query OK, 0 rows affected, 1 warning (0.00 sec) 

mysql> show warnings; 
+---------+------+--------------------------------------------------------------------------------------------------------------+

| Level | Code | Message | 
+---------+------+--------------------------------------------------------------------------------------------------------------+

| Warning | 1285 | MySQL is started in --skip-name-resolve mode; you must restart it
without this switch for this grant to work | 
+---------+------+--------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec) 

Then, on host phpstag1: 

14:34:00|meersjo@phpstag1:~ 0 $ mysql -h172.18.65.23 -uczar -pczar 
ERROR 1045 (28000): Access denied for user 'czar'@'172.18.65.22' (using password: YES) 

Good, so that does indeed not work :) 

So, in summary: when in skip-name-resolve mode, grants will spew a useful warning if they
contain hostnames; but "show grants" fails mysteriously. Drop user, interestingly enough,
does work as expected. That one kind of surprised me. 

Not sure if this is a true bug, but it could be handled a bit more gracefully. Anyway, it
may save someone's day sometime. Enjoy. 

Oh, right, this is on 5.0.51a-24+lenny5-log. YMMV. 




-- 
Bier met grenadyn 
Is als mosterd by den wyn 
Sy die't drinkt, is eene kwezel 
Hy die't drinkt, is ras een ezel 

Thread
Messed up authenticationJohan De Meersman1 Dec