Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6.16
-
None
-
None
Description
3-node Galera cluster - CentOS 7.9.2009
I used a query left joining information_schema.schema_priveleges to information_schema.schemata to determine and remove grants on non-existent objects:
– Identify users with privileges on non-existent schema
select distinct sp.GRANTEE, sp.TABLE_SCHEMA, sp.PRIVILEGE_TYPE
from information_schema.SCHEMA_PRIVILEGES sp
left join information_schema.SCHEMATA s
on sp.TABLE_SCHEMA = s.SCHEMA_NAME
where s.SCHEMA_NAME is null
order by sp.TABLE_SCHEMA asc;
This query also returned privs granted to a bunch of schema that do exist and contain tables/data. I then adjusted the query just to return the schema names:
select distinct sp.TABLE_SCHEMA
from information_schema.SCHEMA_PRIVILEGES sp
left join information_schema.SCHEMATA s
on sp.TABLE_SCHEMA = s.SCHEMA_NAME
where s.SCHEMA_NAME is null;
On further checking, we're missing 10 schema from the schemata table that exist in the output of "show databases"
I have been unable to replicate this on a standalone database by creating folders in the datadir and I don't have a handy galera cluster that I can break semi-intentionally.
I'm not sure what information might be useful here, the question is: is it possible to somehow force a sync of the schemata table with what's on disk?