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

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,215