Hi,
SHOW DATABASES seems somehow slow on ISPs
how about using I_S.SCHEMA_PRIVILEGES
f.e.
SELECT * FROM `SCHEMA_PRIVILEGES` LIMIT 0 , 30
performs much faster on the ISP i could test:
0.08 sec vs. 0.24 seconds
possible this will only work with ISPs where are no anonymous rights
possible a configuration where ISP can use I_S.SCHEMA_PRIVILEGES instead of SHOW DATABASES, or they can switch to SHOW DATABASES like '[user]_%' which performs much faster too
how about
$cfg['DatabaseListQuery'] = 'SHOW DATABASES'; // #user# is replaced by curent user $cfg['DatabaseListQuery'] = "SHOW DATABASES LIKE '#user#%'"; $cfg['DatabaseListQuery'] = 'SELECT DISTINCT TABLE_SCHEMA FROM `SCHEMA_PRIVILEGES`';
Sebastian Mendel a écrit :
Hi,
SHOW DATABASES seems somehow slow on ISPs
how about using I_S.SCHEMA_PRIVILEGES
f.e.
SELECT * FROM `SCHEMA_PRIVILEGES` LIMIT 0 , 30
performs much faster on the ISP i could test:
0.08 sec vs. 0.24 seconds
possible this will only work with ISPs where are no anonymous rights
possible a configuration where ISP can use I_S.SCHEMA_PRIVILEGES instead of SHOW DATABASES, or they can switch to SHOW DATABASES like '[user]_%' which performs much faster too
how about
$cfg['DatabaseListQuery'] = 'SHOW DATABASES'; // #user# is replaced by curent user $cfg['DatabaseListQuery'] = "SHOW DATABASES LIKE '#user#%'"; $cfg['DatabaseListQuery'] = 'SELECT DISTINCT TABLE_SCHEMA FROM `SCHEMA_PRIVILEGES`';
Maybe I don't understand your idea SHOW DATABASES LIKE 'user%'; naming databases with user names is only a convention, this is why SHOW DATABASES is better IMO in general. Promoting this in PMA could confuse a sysadmin into thinking that this is used to get the databases on which this user has access.
For your last idea, I think you meant SELECT DISTINCT TABLE_SCHEMA FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES.
I'll make some tests on servers with a various number of databases.
Of course, picking the best default value for $cfg['DatabaseListQuery'] depends on the results of our tests.
Marc
Marc Delisle schrieb:
Sebastian Mendel a écrit :
Hi,
SHOW DATABASES seems somehow slow on ISPs
how about using I_S.SCHEMA_PRIVILEGES
f.e.
SELECT * FROM `SCHEMA_PRIVILEGES` LIMIT 0 , 30
performs much faster on the ISP i could test:
0.08 sec vs. 0.24 seconds
possible this will only work with ISPs where are no anonymous rights
possible a configuration where ISP can use I_S.SCHEMA_PRIVILEGES instead of SHOW DATABASES, or they can switch to SHOW DATABASES like '[user]_%' which performs much faster too
how about
$cfg['DatabaseListQuery'] = 'SHOW DATABASES'; // #user# is replaced by curent user $cfg['DatabaseListQuery'] = "SHOW DATABASES LIKE '#user#%'"; $cfg['DatabaseListQuery'] = 'SELECT DISTINCT TABLE_SCHEMA FROM `SCHEMA_PRIVILEGES`';
Maybe I don't understand your idea SHOW DATABASES LIKE 'user%'; naming databases with user names is only a convention, this is why SHOW DATABASES is better IMO in general. Promoting this in PMA could confuse a sysadmin into thinking that this is used to get the databases on which this user has access.
1.) they can tell PMA what command to use to fetch all databases for the user
2.) "SHOW DATABASES LIKE 'user%'" is only an example, some ISPs name thier user databases according to the user user: wp123 has dbs user123_1, user123_2 aso.
For your last idea, I think you meant SELECT DISTINCT TABLE_SCHEMA FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES.
of course, but information_schema not INFORMATION_SCHEMA ... ;-)
I'll make some tests on servers with a various number of databases.
Of course, picking the best default value for $cfg['DatabaseListQuery'] depends on the results of our tests.
i tent to use SHOW DATABASES still as default, but give the ISPs an easy way to change this to speed up things
Sebastian Mendel a écrit :
Marc Delisle schrieb:
Sebastian Mendel a écrit :
Hi,
SHOW DATABASES seems somehow slow on ISPs
how about using I_S.SCHEMA_PRIVILEGES
f.e.
SELECT * FROM `SCHEMA_PRIVILEGES` LIMIT 0 , 30
performs much faster on the ISP i could test:
0.08 sec vs. 0.24 seconds
possible this will only work with ISPs where are no anonymous rights
possible a configuration where ISP can use I_S.SCHEMA_PRIVILEGES instead of SHOW DATABASES, or they can switch to SHOW DATABASES like '[user]_%' which performs much faster too
how about
$cfg['DatabaseListQuery'] = 'SHOW DATABASES'; // #user# is replaced by curent user $cfg['DatabaseListQuery'] = "SHOW DATABASES LIKE '#user#%'"; $cfg['DatabaseListQuery'] = 'SELECT DISTINCT TABLE_SCHEMA FROM `SCHEMA_PRIVILEGES`';
Maybe I don't understand your idea SHOW DATABASES LIKE 'user%'; naming databases with user names is only a convention, this is why SHOW DATABASES is better IMO in general. Promoting this in PMA could confuse a sysadmin into thinking that this is used to get the databases on which this user has access.
1.) they can tell PMA what command to use to fetch all databases for the user
2.) "SHOW DATABASES LIKE 'user%'" is only an example, some ISPs name thier user databases according to the user user: wp123 has dbs user123_1, user123_2 aso.
For your last idea, I think you meant SELECT DISTINCT TABLE_SCHEMA FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES.
of course, but information_schema not INFORMATION_SCHEMA ... ;-)
Of course ;)
I'll make some tests on servers with a various number of databases.
Of course, picking the best default value for $cfg['DatabaseListQuery'] depends on the results of our tests.
i tent to use SHOW DATABASES still as default, but give the ISPs an easy way to change this to speed up things
Ok then, please implement this, with this default. It's up to the sysadmin to experiment and pick the best option for his server.
Hmmm, this makes me think: this option should be under $cfg['Servers'][$i] ? Suppose a 5.0.x server reacts differently than a 5.1.x or a 6.0.x one?
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.
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.
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. ;-)
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.
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.
Regards,
Alexander
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
Hi there,
Sebastian Mendel schrieb:
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.)
Hum, so MySQL does not cache this? Do you think, phpMyAdmin could/should cache the listing somehow (e.g. inside php's session container)? But I guess this would scale even worse than SCHEMATA. X-)
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 '...'
So if I logged in as a root-like user, I wouldn't see any databases if we query SCHEMA_PRIVILEGES, would I?
why is this 'dangerous', it is up to the sysadmin,
Because this may appear like a privilege feature, while in fact the user may still see databases that have been hidden in our listing. If the feature is added, a warning about that issue should be added to the docs.
on most ISP installations there is no need for I_S to the user,
Sure, there is. Even if you are only allowed to access a single database, information_schema is useful to advanced users. Maybe we could auto-add it to the listing, if the admin's query hides it?
Hey, and don't tell me that most advanced users probably know how to access a hidden information_schema. ;-)
`test` is disabled from anonymous access, and all databases for the user start with a specific prefix
This is probably the case.
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
All right then. But that has to be documented very well. I'm just afraid of phpMyAdmin being blamed for security holes introduces by misconfiguration of misunderstood features. ;-)
Marc Delisle schrieb:
See the various speed problems about information_schema here: http://bugs.mysql.com/bug.php?id=19588
I see. I didn't know about this issue. Probably because my MySQL servers don't have enough schemata to encounter it.
Even if it's fixed in 5.1.22, we don't plan to have 5.1.22 as a minimum MySQL version, do we?
If it would only affect the 5.1 branch, I would not work around it as that branch is not GA, yet. But as 5.0 is affected, too, I fully agree with the both of you. I surrender. ;-)
Regards,
Alexander
cand. inf. Alexander M. Turek a écrit :
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.
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.
See the various speed problems about information_schema here: http://bugs.mysql.com/bug.php?id=19588
Even if it's fixed in 5.1.22, we don't plan to have 5.1.22 as a minimum MySQL version, do we?
Marc
-
cand. inf. Alexander M. Turek -
Marc Delisle -
Sebastian Mendel