MacLochlainns Weblog

Having gone through the old DBA training at Oracle, I really appreciated finding a nice diagram of the MySQL Database Catalog. The catalog is found in the information_schema database. If you click on the image file, it’ll take you to the site where the original Visio File is found. You can also download it from there.

What may appear as a downside of the information_schema is that you can’t grant even select privileges to external users. You’d get the following if you tried to do so as the root user.

mysql> GRANT SELECT ON information_schema.* TO myuser;
ERROR 1044 (42000): Access denied FOR USER 'root'@'localhost' TO DATABASE 'information_schema'

There’s a reason for this behavior. You already have select privileges by default because the information_schema is a query only repository and open to all users. Here’s a quick example of accessing them from inside another database, which requires that you reference the tables with the owning database name.

SELECT   t.table_name
,        c.column_name
FROM     information_schema.tables t JOIN information_schema.columns c
ON       t.table_name = c.table_name 
WHERE    t.table_schema = 'SAMPLEDB';

Hope this answers a question or two.