MySQL Bugs: #17365: Please provide a way to hide 'information

Bug #17365	Please provide a way to hide 'information_shema' from 'show databases'
Submitted:	14 Feb 2006 8:11	Modified:	15 Dec 2010 12:20
Reporter:	Cyril Bouthors	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	5.0.18	OS:	Linux (Debian GNU/Linux)
Assigned to:		CPU Architecture:	Any

Description:
I've just upgraded from MySQL 4.0 to 5.0 and now all my clients see the database 'information_shema' in their 'show databases' output.

I know it's a kind of virtual database but not a real database and that it contains meta-data not real data, I've read http://dev.mysql.com/doc/refman/5.0/en/information-schema.html but my
clients keep complaining about this "additionnal database" because they feel like they will have to pay for this, they are getting confused to see this table in their PHPMyAdmin web-interface and their
remote clients and they don't even have enough privilege to access it.

I've successfully managed a similar thing when I sat up MySQL to hide every other clients databases in 'show databases': my clients only see one or two databases, their own, not the ~4k databases of all the clients. I wish I could do the same with 'information_shema'.

Is there a way to hide it to my users?

How to repeat:
show databases

Thank you for the feature request. Sorry, but I do not think that we have to implement this feature - you can do it yourself, as you already explained, if it is really needed in your environment. Moreover, it is intended to provide a standard way to query metadata, even for the end users.

There is yet another database with metadata, "mysql". Why nobody complain about its visibility?

Nobody complains about the visibility of the database 'mysql' because it can simply be hidden:

cyb@wide:~$ mysql -uX -pY -hZ -A
(...)
mysql> show databases;
+----------+
| Database |
+----------+
| zobi8225 |
+----------+
1 row in set (5.41 sec)

I need the same hiding feature to be available for the table 'information_shema'.

After my last comment, I'd like to you to remove the "won't fix" status of this bug.

This has to be fixed.

Thanks.

Sorry, but -A option to mysql does not hide anything (it turns off autocompletion of darabase and table names). As for SHOW DATABASES, please, read the manual (http://dev.mysql.com/doc/refman/5.0/en/show-databases.html):

"You see only those databases for which you have some kind of privilege, unless you have the global SHOW DATABASES privilege. You can also get this list using the mysqlshow command.

If the server was started with the --skip-show-database option, you cannot use this statement at all unless you have the SHOW DATABASES privilege."

As INFORMATION_SCHEMA is intended to be visible to any user, there is only one way to "hide" it, as described above.

Valeriy,

I know what the -A option does.

When I said "Nobody complains about the visibility of the database 'mysql' because it can simply be hidden", I meant that you can hide the database 'mysql' to everyone with appropriate privileges tuning. The query clearly shown this because the database 'mysql' does not appear in the output of "show database". It has nothing to do with -A, I just used this option to get a kicker startup.

As far as I know, there's no way to hide 'information_shema' from the 'show databases' query.

Could you please provide a way to hide 'information_shema' from 'show databases' and remove this "won't fix" tag from the BTS?

Many thanks

In fact, mysql database was a bit "wrong way" to store metadata (only some of them, mostly security related). INFORMATION_SCHEMA (as described at http://dev.mysql.com/doc/refman/5.0/en/information-schema.html) was created to give read-only access to all the metadata, as requered by Codd's rules, and as it is done in other RDBMSes.

All users of Oracle, for example, knows that there is SYS schema, with data dictionary in it. Content of some tables may not be visible for most of them, just as with INFORMATION_SCHEMA, but the fact that there is metadata with read-only access is not hidden form users. It is a service - there is not need to hide it, if it is implemented.

I do not think that this feature is needed, it is unreasonable for me, that is why I marked this report as 'Won't fix'. I'll open it back, for you to get more chances for oppinions from others.

Okay, here's another comment. I agree with Valeriy. The information_schema is a part of MySQL's interface. Hiding it would not serve any useful purpose. Moreover, as at some point the non-standard SHOW statement may be deprecated, the information contained in information_schema would be inaccessible to clients then.

I am ok with if user is not able to execute show, let me know how i can disable users seeing information_schema or is there a way using with i can restrict users a to execute any command on information_schem?