A little less please: MySQL „partial revokes“

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.

mysql> set persist partial_revokes=on;
Query OK, 0 rows affected (0.00 sec)
 

Several schemas (databases) are available on our test database system. Among others the well-known demo database "sakila".

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
+--------------------+
5 rows in set (0.01 sec)
 

We now create an account (user 'app_ro') that should have read permissions on all databases (including future ones).

mysql> create user 'app_ro'@'%' identified by 'ordix';
Query OK, 0 rows affected (0.01 sec)
mysql> grant select on *.* to 'app_ro'@'%';
Query OK, 0 rows affected (0.01 sec)
 

This user now has read access to all (also future) databases:

root@myshell2:/# mysql -uapp_ro -pordix --execute="show databases"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
+--------------------+
 

However, we are now revoking this user's access to the "sakila" database.

mysql> revoke select on sakila.* from 'app_ro'@'%';
Query OK, 0 rows affected (0.01 sec)

root@myshell2:/# mysql -uapp_ro -pordix --execute="show databases"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
 

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.

mysql> select user, host, user_attributes from user where user =  'app_ro';
+--------+------+----------------------------------------------------------------------+
| user   | host | user_attributes                                                      |
+--------+------+----------------------------------------------------------------------+
| app_ro | %    | {"Restrictions": [{"Database": "sakila", "Privileges": ["SELECT"]}]} |
+--------+------+----------------------------------------------------------------------+
1 row in set (0.00 sec)
 

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.