mysql showing different number of databases

Question

This is from my terminal in mac.

130-229-0-129-dhcp:~ suyeshamatya$ mysql


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

mysql> quit
Bye
130-229-0-129-dhcp:~ suyeshamatya$ mysql -u root -p
Enter password: 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lportal            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

Can someone please explain why mysql is showing different number of databases when connecting without any username/password and when connecting with root username/password?

UPDATE:

Connected without username/password

mysql> show grants;
+--------------------------------------+
| Grants for @localhost                |
+--------------------------------------+
| GRANT USAGE ON *.* TO ''@'localhost' |
+--------------------------------------+
1 row in set (0.00 sec)

Connected with root username/password

mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '********************' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Sadikhasan · Accepted Answer · 2014-07-09 08:34:45Z

1

Grant USAGE means no privileges for that user according to Manual. So when you login without username and password. It will show default mysql schema. If you want to user using following query:

select User from mysql.user;

It will throw error like

SELECT command denied to user ''@'localhost' for table 'user';

Which means you do not have permission on default database to view users details.

If you login with username and password then all the databases created by that User will show you.

edited Jul 9, 2014 at 8:34

answered Jul 9, 2014 at 8:29

Sadikhasan

18.6k23 gold badges84 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jens · Accepted Answer · 2014-07-09 08:11:03Z

1

I am not sure but I think that the the "unknown user" has no privileges to see the other databases. Please check this in the mysql database.

answered Jul 9, 2014 at 8:11

Jens

69.6k15 gold badges107 silver badges133 bronze badges

Collectives™ on Stack Overflow

mysql showing different number of databases

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related