maria-developers team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

Hi, Witold!

On Feb 21, Witold Filipczyk wrote:
> Hi,
> I'm newbie, I have problem with INFORMATION_SCHEMA.
> There is mariadb-10.0 with around 100000 databases. Disks are slow,
> but most users have one database,
> a few have more than one, but it is not a big number. Queries like
> SHOW DATABASES or SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
> bring to knees whole server.
> The idea to resolve it is:
> in find_files(...) instead of the for loop execute query:
> SELECT REPLACE(TABLE_SCHEMA, '\\', '') FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES WHERE PRIVILEGE_TYPE = 'SELECT';
> There are databases with names with _ in them and \_ in GRANTS.

That's a very interesting idea.

Basically, you're saying that if a user has no probal privileges and
database privileges on only one database and no wildcards in the
database name (*), then SELECT * FROM INFORMATION_SCHEMA.SCHEMATA essentially
has an implicit WHERE schema_name="FOO" clause. Yes, that's certainly
doable. It's even not very difficult to do, I'd think.

(*) this can be extended to privileges on few (more than one) databases
and on wildcards in the database names, but let's start from something simple.

> This is theory, but I don't know how to do it. That's mean how to
> freeze the current query, how to run new query, read results, and fill
> files in the find_files function and resume the current query.

Kind of. Privileges are stored in memory in lists and hashes. You'd need
to traverse them to see what databases a user has grants for.

Regards,
Sergei
Chief Architect MariaDB
and security@xxxxxxxxxxx