2

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.

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.

2
  • That's a very clever method of approaching this, I would never have thought of that way. Food for thought - thank you. – Simon Avery Jul 12 '19 at 14:57
  • Oh, I see now that you are also interested in tables that no longer exists. You will have to add predicates for that. Something like OR TABLENAME NOT IN (SELECT SCHEMA_NAME FROM ... ). Instead of NOT IN, you may consider using a LEFT JOIN and a predicate for IS NULL. – Lennart Jul 13 '19 at 9:04
0

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) ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.