Granting permissions on databases can be a tedious job. Especially assigning restrictive permissions à la "you may read all tables except for the information in database XYZ" was laborious. For such a task, the SELECT rights had to be defined for all databases that the user should be able to read. The unwanted database was simply given no rights. Of course, such GRANT statements can be created dynamically with SQL to keep the effort within limits. With the version 8.0.16 there is a small relief here.
From now on, permissions can be partially "recalled" (revoked) on schema level. For this purpose, the corresponding parameter "partial_revokes" must be activated.
Several schemas (databases) are available on our test database system. Among others the well-known demo database "sakila".
We now create an account (user 'app_ro') that should have read permissions on all databases (including future ones).
This user now has read access to all (also future) databases:
However, we are now revoking this user's access to the "sakila" database.
The database disappears from the "field of view" of this database user 'app_ro'.
Everything under control?
How are these permission structures managed? It should be generally known that the rights are stored in several system tables within the Mysql database. Up to now, only the assigned "GRANTs", i.e., the positively formulated privileges, are located there.
With the version 8.0.16 now also restrictions are administered in the "user" table within the Mysql database. The column "user_attributes" (data type JSON) is used for this purpose.
Of course, with SELECT, you can format the output of JSON data in a slightly more readable way.
Conclusion: Do we need more restrictions?
The clear answer is "yes". The new functionality is helpful. Unfortunately, restrictions can currently only be set at schema level. More interesting for the developer and / or the DBA (Database Administrators) would be restrictions on object level (e.g., tables). But what is not, can still become.
Do you have questions about the (secure) operation of MySQL? Please contact us.