Re: Automatic reply: access denied to non-root@localhost null-string user in USER_PRIVILEGES
Hello Round, On 12/27/2012 5:34 AM, Round Square wrote: On 12/26/2012 01:25 PM, Igor Shevtsov wrote: You mysql.user table might be corrupted. If you have access to it as a root user, try check table mysql.user, and repair table mysql.user if table corruption was detected. Alternatively, shut down mysql server, cd /var/lib/mysql/mysql (to your $datadir/mysql directory) and run mysqlcheck -r mysql user I deleted the row with the empty user from mysql.user, then restarted the daemon, and all seems back to normal now. One lingering question is: why did mysql allow this to happen? Could this be considered a bug? After all, an inadvertent and seemingly harmless insertion leads to authentication failure for all users. Are there any other known similar gotchas? The fix for this one appears so trivial as to perhaps NOT call for a restore-from-backup. But there could be other similar glitches that might call for that? Thanks! No. Your tables were not corrupted. There is no need to restore from backup. In fact, it may have been an inappropriate restore that created this situation in the first place. What you had was either an intentional or unintentional failure in security. The system was performing appropriately for the accounts that you had configured. On 26/12/12 18:00, Round Square wrote: ... Poking around in puzzlement and comparing the current, broken state with the functioning state (from backup) I discovered that in the broken version there is this extra line in the information_schema.USER_PRIVILEGES table: | ''@'localhost' | NULL | USAGE | NO | (Note the null-string user prepended to @localhost) Again: the functional, non-broken state does NOT have this entry. Thus, my current theory is that this line is the culprit. Prior to the failure I had a surge of experimental installations, installing third-party software that created mysql tables, and can't clearly retrace everything I did, at this point, to pinpoint the installation that may have caused it. Be that as it may... (1) Is my theory correct? (2) If that line should not be there... (a) How do I remove it, properly? I don't have debian-sys-maint privileges to delete the line. (Or do I?) (b) Are there other tables, besides USER_PRIVILEGES, that would need to be updated/purged Yes. Your theory is correct. Why it had such an effect on your other logins is covered in the free, searchable, and publicly-available user manual (the only kind we have): http://dev.mysql.com/doc/refman/5.5/en/connection-access.html Removing a user account (a MySQL login) does not require debian-sys-maint privileges as this is not a Linux-level account. You need an appropriately-privileged MySQL account to do this maintenance. As you most likely discovered, we cannot directly modify the tables of the INFORMATION_SCHEMA database. Those tables are constructed dynamically (on demand) using information permanently stored in other places. In order to remove that account, you need to issue a DROP USER command or you need to be able to edit the `mysql`.`user` table. http://dev.mysql.com/doc/refman/5.5/en/drop-user.html http://dev.mysql.com/doc/refman/5.5/en/grant-table-structure.html -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Automatic reply: access denied to non-root@localhost null-string user in USER_PRIVILEGES
On 12/26/2012 01:25 PM, Igor Shevtsov wrote: You mysql.user table might be corrupted. If you have access to it as a root user, try check table mysql.user, and repair table mysql.user if table corruption was detected. Alternatively, shut down mysql server, cd /var/lib/mysql/mysql (to your $datadir/mysql directory) and run mysqlcheck -r mysql user I deleted the row with the empty user from mysql.user, then restarted the daemon, and all seems back to normal now. One lingering question is: why did mysql allow this to happen? Could this be considered a bug? After all, an inadvertent and seemingly harmless insertion leads to authentication failure for all users. Are there any other known similar gotchas? The fix for this one appears so trivial as to perhaps NOT call for a restore-from-backup. But there could be other similar glitches that might call for that? Thanks! On 26/12/12 18:00, Round Square wrote: Hi all: Suddenly, after a long, functioning run of the mysql server, all the non-root accounts went bad, with: Access denied for user 'non_root_user'@'localhost' (using password: YES) Authenticating with non_root_u...@server.ip.address still works ( the bind-address in my.cnf is mapped to server.ip.address ) Poking around in puzzlement and comparing the current, broken state with the functioning state (from backup) I discovered that in the broken version there is this extra line in the information_schema.USER_PRIVILEGES table: | ''@'localhost' | NULL | USAGE | NO | (Note the null-string user prepended to @localhost) Again: the functional, non-broken state does NOT have this entry. Thus, my current theory is that this line is the culprit. Prior to the failure I had a surge of experimental installations, installing third-party software that created mysql tables, and can't clearly retrace everything I did, at this point, to pinpoint the installation that may have caused it. Be that as it may... (1) Is my theory correct? (2) If that line should not be there... (a) How do I remove it, properly? I don't have debian-sys-maint privileges to delete the line. (Or do I?) (b) Are there other tables, besides USER_PRIVILEGES, that would need to be updated/purged My version: mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) using readline 6.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
access denied to non-root@localhost null-string user in USER_PRIVILEGES
Hi all: Suddenly, after a long, functioning run of the mysql server, all the non-root accounts went bad, with: Access denied for user 'non_root_user'@'localhost' (using password: YES) Authenticating with non_root_u...@server.ip.address still works ( the bind-address in my.cnf is mapped to server.ip.address ) Poking around in puzzlement and comparing the current, broken state with the functioning state (from backup) I discovered that in the broken version there is this extra line in the information_schema.USER_PRIVILEGES table: | ''@'localhost' | NULL | USAGE | NO | (Note the null-string user prepended to @localhost) Again: the functional, non-broken state does NOT have this entry. Thus, my current theory is that this line is the culprit. Prior to the failure I had a surge of experimental installations, installing third-party software that created mysql tables, and can't clearly retrace everything I did, at this point, to pinpoint the installation that may have caused it. Be that as it may... (1) Is my theory correct? (2) If that line should not be there... (a) How do I remove it, properly? I don't have debian-sys-maint privileges to delete the line. (Or do I?) (b) Are there other tables, besides USER_PRIVILEGES, that would need to be updated/purged My version: mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) using readline 6.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: access denied to non-root@localhost null-string user in USER_PRIVILEGES
You mysql.user table might be corrupted. If you have access to it as a root user, try check table mysql.user, and repair table mysql.user if table corruption was detected. Alternatively, shut down mysql server, cd /var/lib/mysql/mysql (to your $datadir/mysql directory) and run mysqlcheck -r mysql user On 26/12/12 18:00, Round Square wrote: Hi all: Suddenly, after a long, functioning run of the mysql server, all the non-root accounts went bad, with: Access denied for user 'non_root_user'@'localhost' (using password: YES) Authenticating with non_root_u...@server.ip.address still works ( the bind-address in my.cnf is mapped to server.ip.address ) Poking around in puzzlement and comparing the current, broken state with the functioning state (from backup) I discovered that in the broken version there is this extra line in the information_schema.USER_PRIVILEGES table: | ''@'localhost' | NULL | USAGE | NO | (Note the null-string user prepended to @localhost) Again: the functional, non-broken state does NOT have this entry. Thus, my current theory is that this line is the culprit. Prior to the failure I had a surge of experimental installations, installing third-party software that created mysql tables, and can't clearly retrace everything I did, at this point, to pinpoint the installation that may have caused it. Be that as it may... (1) Is my theory correct? (2) If that line should not be there... (a) How do I remove it, properly? I don't have debian-sys-maint privileges to delete the line. (Or do I?) (b) Are there other tables, besides USER_PRIVILEGES, that would need to be updated/purged My version: mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) using readline 6.1
