Thursday, October 12, 2006

I can't see my databases!

This common complaint uttered fairly frequently on the forums and some similar related issues appears to stem from some basic misunderstandings of the MySQL account structure. The most common culprit is the "anonymous guest" account set up by default coupled with an initial lack of knowledge of how to connect to MySQL. Personally I have no use for such accounts and remove them when I find them. Presumably the intent is to allow anonymous users minimal access to MySQL, where they are limited to using the `test` database which is also set up by default. Compounding this issue is the Command Line Interface start-menu shortcut created by the installer versions of the Windows packages, which connects users as 'root' automatically and oftentimes unbeknownst to them. Then when the user goes to connect via some other means and presents incorrect credentials out of ignorance, then there is a chance that this guest account may be inadvertantly invoked.

So there they are, connected to MySQL, yet nothing is right. All they can see is the `test` database, and for 5.0 or better, the `information_schema` database. This might also happen if, in order to connect from a remote node, an account such as 'root' is creating with the remote node's name, but not granted any actual privileges.

So therefore the first thing to do when this situation occurs is to execute the following statement:
SELECT user(), current_user();
to find out exactly how one is connected, particularly if there is any doubt. Then one could go back to a known method for connecting as 'root' or otherwise correct the credentials.

3 Comments:

Blogger Sheeri said...

One important thing to note is that

SELECT USER();

shows you your current username and host. Another command,

SELECT CURRENT_USER();

shows what you're *authenticated* as.

I just had a person ask me this yesterday -- his commandline connection was

mysql -u user-ppass db

so for him, SELECT USER() would have shown "user-ppass@localhost", and select CURRENT_USER() would have shown "@localhost" -- confirming he was the anonymous user. This helps when you're matching up GRANTs with users.

10:03 AM, October 13, 2006  
Blogger Bob Field said...

Noted, thanks.

10:52 AM, October 13, 2006  
Blogger Grashima said...

Hi Bob This was written in 2006 but it saved my breath in 2015 ! I just couldn't find my databases !!!

12:58 AM, January 01, 2015  

Post a Comment

Links to this post:

Create a Link

<< Home