2

I have a server with a large number of mysql databases. Recently some of the schemas have stopped displaying when logged in as Root.

root user - until now always used

If I use the login for that specific database then I can see it.

direct user

The 'owner' for this database is root but others that are also not visible to root are owned by others.

It seems to be databases that we have accessed and worked on recently that vanish but there are now more than 5 that aren't visible. It's not a local cache and anyone also using the root login has the same issue.

TomFirth
  • 81
  • 7
  • just down vote without comment? any advice would be dandy. – TomFirth Apr 20 '15 at 08:10
  • (DISCLAIMER: Not a phpmyadmin person) Please run the following query : `SELECT USER() AttemptedToConnectAsUser,CURRENT_USER() AllowedToConnectAsUser;` What is the output ??? – RolandoMySQLDBA May 07 '15 at 16:22
  • AttemptedToConnectAsUser AllowedToConnectAsUser cpses_********@localhost cpses_********@localhost I'm quite inexperienced with phpmyadmin, i'm assuming this is randomly generated but should this display as 'root@localhost'? – TomFirth May 08 '15 at 08:51
  • It is not randomly generated at all. See my post http://superuser.com/questions/266758/how-do-i-see-which-user-i-am-logged-in-as-in-mysql/271516#271516 for more details. – RolandoMySQLDBA May 09 '15 at 19:50
  • In your config file, do you have values for the options $cfg['Servers'][$i]['hide_db'] or $cfg['Servers'][$i]['only_db'] ? – smokes2345 May 11 '15 at 21:41
  • @Rolando So i'm able to log in as root but i'm not actually logging in as root? I can't understand how to resolve this from the related question. – TomFirth May 12 '15 at 08:25
  • @smokes2345 I don't maintain the server and so can't check straight away, however these databases are only read once a day by root access and although I can't confirm, it's unlikely that any configs would have been changed by us and no major server updates happened that week. – TomFirth May 12 '15 at 08:25

1 Answers1

1

If your CURRENT_USER() does not say root@localhost, then you do not have root privileges and not being allowed to connect as such.

To see what actual privileges you do have after getting connected, run this

SHOW GRANTS;

When you connect to a specific database, you should be able to see

  • the tables of that database
  • metadata in the INFORMATION_SCHEMA for just that database

Let's say, for example, you connected to buyspace_systdb

The output from SHOW GRANTS; would most likely appear something like this

GRANT USAGE ON *.* ...
GRANT ALL PRIVILEGES ON buyspace_systdb.* TO ...

The user root@localhost would work if you login from within that local server. If you are logging in remotely, root@localhost would not allow USER() to authenticate as CURRENT_USER(). The proof of this is in the first display in your question. Note that you can only see information_schema. It will virtually empty with the exception of information_schema.schemata which would have 1 or 2 entries. If root@'%' existed in mysql.user and had the same privileges as root@localhost, then it would be allowed to see everything like root@localhost.

MySQL has an authentication scheme that has a downward spiral effect. If the user you try to connect with does not exist, it will try usernames that are more vague ( allowing for wildcards or limited privileges ). See my DBA StackExchange post MySQL error: Access denied for user 'a'@'localhost' (using password: YES) for further details on this.

Bottom Line: You cannot connect as root@localhost from a remote location

Community
  • 1
RolandoMySQLDBA
  • 3,065
  • 1
  • 20
  • 25