cand. inf. Alexander M. Turek schrieb:
Hi there,
Sebastian Mendel schrieb:
SHOW DATABASES seems somehow slow on ISPs
how about using I_S.SCHEMA_PRIVILEGES
What about information_schema.SCHEMATA? I guess, that's the purpose of that view.
with this feature implemented the sysadmin can choose, but I_S.SCHEMATA is much slower than I_S.SCHEMA_PRIVILEGES, cause privileges are in RAM, but for I_S.SCHEMATA a directory scan is done, and all database informations a collected (default charset, engine, aso.)
possible a configuration where ISP can use I_S.SCHEMA_PRIVILEGES instead of SHOW DATABASES,
Switching from SHOW queries to information_schema is a good idea, imho. Since the next version of phpMyAdmin won't support MySQL 4.x anymore, I don't see, why a config parameter is needed for that switch.
cause I_S.SCHEMA_PRIVILEGES contains only databases with explicit privileges for the current users, not all, and as stated above I_S.SCHEMATA can be much much slower them SHOW DATABASES or SHOW DATABASES LIKE '...'
or they can switch to SHOW DATABASES like '[user]_%' which performs much faster too
This is a little dangerous because it enables the ISPs to hide databases from the user. Your query would hide all other databases from the user, especially information_schema and the test databases that are enabled by default. And of course the database [user] that you may create in server_privileges. ;-)
why is this 'dangerous', it is up to the sysadmin, on most ISP installations there is no need for I_S to the user, `test` is disabled from anonymous access, and all databases for the user start with a specific prefix
We've had a similar discussion when MySQL 4.1 became stable and MySQL's privilege upgrade script was a little buggy, so all users could see the whole database listing, even if they weren't allowed to access most of the listed databases. Though, I don't know if the guys have fixed that, yet.
as this solution is mainly for ISPs and the default will still be SHOw DATABASES and i think ISPs take care what users can see and not this should not be a problem
The problem is, that you create the illusion of a security that is not there (users don't see certain databases, although they may access them via manual queries). So far, we have tried to avoid such situations.
hiding databases from listing is currently possible too