Removing Grants for Non-Existent Databases in MySQL and MariaDB

mariadbMySQLpermissions

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.

Best Answer

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.

Related Solutions

PostgreSQL – Created User Can Access All Databases Without Grants

At the SQL level, every user can indeed connect to a newly created database, until the following SQL command is issued:

REVOKE connect ON DATABASE database_name FROM PUBLIC;

Once done, each user or role that should be able to connect has to be granted explicitly the connect privilege:

GRANT connect ON DATABASE database_name TO rolename;

Edit: In a multi-tenant scenario, more than just the connect privilege would be removed. For multi-tenancy tips and best practices, you may want to read on the postgresql public wiki: Shared Database Hosting and Managing rights in PostgreSQL.

MySQL – Show GRANTs for All Users

Nothing built-in. You have two options though:

Use common_schema's sql_show_grants view. For example, you can query:
```
SELECT sql_grants FROM common_schema.sql_show_grants;
```
Or you can query for particular users, for example:
```
SELECT sql_grants FROM common_schema.sql_show_grants WHERE user='app';
```
To install common_schema, follow the instructions here.

Disclaimer: I am author of this tool.

Use Percona Toolkit's pt-show-grants, for example:

pt-show-grants --host localhost --user root --ask-pass

In both cases you can ask for the GRANT command or the REVOKE (opposite) command.

The first case requires that you install a schema, the latter requires that you install PERL scripts + dependencies.

Best Answer

Related Solutions

PostgreSQL – Created User Can Access All Databases Without Grants

MySQL – Show GRANTs for All Users

Related Question