Removing grants for databases that no longer exist

Question

We have quite a few MariaDB servers with whom the permissions have gotten a bit messy over the years. I'd like to tidy them up.

I've already written some tools for presenting grants/table information nicely, and this has highlighted that that some users have a LOT of specific grants for databases or tables that no longer exist. On one server there are over 150 databases referenced by users in SHOW GRANTS, but only 12 databases still exist.

How might the users of Stackoverflow deal with housekeeping of this order?

Are there any tools that do basic auditing on Grants, accesses and Databases that will help me sort through this mess?

Ideally, something that spits out a list of mysql REVOKE statements that remove grants for databases that no longer exist, that can then be reviewed by a human before pasting as a query.

I can see a way to anagrammatically sorting through them, but it is likely to take a few hours to write that up. It may still come to that but I'd like to know if this wheel has already been invented.

Answer 1

I would start by verifying that the information in:

SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA

is valid, i.e. is all existing databases, but nothing more. Then you can loop over %PRIVELIGES tables (8 I think it is) and create REVOKE statements from there, example for TABLE_PRIVILEGES:

SELECT 'REVOKE GRANT ON TABLE ' 
      || RTRIM(TABLE_SCHEMA) || '.' || RTRIM(TABLE_NAME)
      || 'FROM USER ' || GRANTEE
FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
WHERE TABLE_SCHEMA NOT IN (SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA)

In the same matter, go through the remaining _PRIVILEGES tables. All from the top of my head and untested, but it should give you a start.

Answer 2

For future folk trying to do this, thanks to Lennart pointing me in the right direction, this is the query I ended up using;

SELECT CONCAT( 'REVOKE ', RTRIM(PRIVILEGE_TYPE) ,' ON ', RTRIM(TABLE_SCHEMA), '.' , RTRIM(TABLE_NAME) , ' FROM ' , GRANTEE , ';' )
FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
WHERE TABLE_SCHEMA NOT IN (SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA)
AND TABLE_NAME NOT IN (SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA) ;