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.

Improve this question
7
  • just down vote without comment? any advice would be dandy.
    – TomFirth
    Apr 20, 2015 at 8:10
  • (DISCLAIMER: Not a phpmyadmin person) Please run the following query : SELECT USER() AttemptedToConnectAsUser,CURRENT_USER() AllowedToConnectAsUser; What is the output ???
    – RolandoMySQLDBA
    May 7, 2015 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 8, 2015 at 8:51
  • It is not randomly generated at all. See my post superuser.com/questions/266758/… for more details.
    – RolandoMySQLDBA
    May 9, 2015 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, 2015 at 21:41

1 Answer 1

Reset to default
1
+50

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

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .