Partial Revokes from Database Objects

Have you ever encountered situations where you want to grant a user access to all databases except a few databases ?  If yes, then this article will interest you.  Starting from MySQL 8.0.16, you can partially revoke database privileges from users, even if they are granted global privileges.

You may want to remind yourself how the privileges work with MySQL here before we get into more detail.

This is the second blog post in the series of how to create multiple accounts without fear.  In the previous blog post we learnt about the SYSTEM_USER dynamic privilege. In the next blog post  we shall put together the pieces of the puzzle to create the multiple users for an app.

Let us understand the partial revokes better through an example use case.

You have an administrator ‘foo_admin’ who assists the development teams, therefore he needs to have read access to almost all of the databases. You have a sensitive database ‘payroll’ among all the databases.  You don’t want to give ‘foo_admin’ even read access to this database. How do you achieve that?

Well, you could do this by granting the database-level SELECT individually on each database the one sensitive database. You could achieve the same effect much more efficiently with MySQL 8.0.16 onward. Let us see how to do that in the following examples.

First, create the user.

mysql> CREATE USER foo_admin;
Query OK, 0 rows affected (0.03 sec)

Grant the global SELECT privilege to the user.

mysql> GRANT SELECT ON *.* TO foo_admin;
 
Query OK, 0 rows affected (0.00 sec)

Revoke the SELECT privilege from the user on the payroll database.

mysql> REVOKE SELECT ON payroll.* FROM foo_admin;
ERROR 1141 (42000): There is no such grant defined for user 'foo_admin' on host '%'

Turn on the system variable ‘partial_revokes’.

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

Try revoke the SELECT privilege from the user on the payroll database again.

mysql> REVOKE SELECT ON payroll.* FROM foo_admin;
Query OK, 0 rows affected (0.01 sec)

That is called a partial revoke because it revokes the global SELECT privilege for a particular database.  The system variable ‘partial_revokes’ must be ON to create partial revokes.

Check what privileges the user has now.

mysql> SHOW GRANTS FOR foo_admin;
+---------------------------------------------------+
| Grants for foo_admin@%                            |
+---------------------------------------------------+
| GRANT SELECT ON *.* TO `foo_admin`@`%`            |
| REVOKE SELECT ON `payroll`.* FROM `foo_admin`@`%` |
+---------------------------------------------------+
2 rows in set (0.00 sec)

Verify that ‘foo_admin’ is able to read from all databases except the restricted database.

mysql> SELECT count(*) FROM quotes.campaign;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
 
mysql> SELECT count(*) FROM payroll.salary;
ERROR 1142 (42000): SELECT command denied to user 'foo_admin'@'localhost' for table 'salary'

Thus, despite having the global SELECT privilege, ‘foo_admin’ could not query in ‘payroll’ database due to partial revoke that was created on the database for that user.

Now, suppose you add a new database ‘payments’.  User ‘foo_admin’ can query in this database since it has global the SELECT privilege and the partial revoke was added for a different database.

You can restrict the access to ‘payments’ database same way for the ‘payroll’ database. Let us see how to do that in the following examples.

Create the database.

mysql> CREATE DATABASE payments;
Query OK, 1 row affected (0.02 sec)

Right now ‘foo_admin’ can query in the new database.

mysql> SELECT count(*) FROM payments.merchant;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

Revoke the SELECT privilege from the user on the payments database.

mysql> REVOKE SELECT ON payments.* FROM foo_admin;
Query OK, 0 rows affected (0.01 sec)

Check what privileges the user has now

mysql> SHOW GRANTS FOR foo_admin;
+---------------------------------------------------+
| Grants for foo_admin@%                            |
+---------------------------------------------------+
| GRANT SELECT ON *.* TO `foo_admin`@`%`            |
| REVOKE SELECT ON `payments`.* FROM `foo_admin`@`%`|
| REVOKE SELECT ON `payroll`.* FROM `foo_admin`@`%` |
+---------------------------------------------------+

Verify that ‘foo_admin’ is able to read from databases except the two restricted databases.

mysql> SELECT count(*) FROM quotes.campaign;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
 
mysql> SELECT count(*) FROM payroll.salary;
ERROR 1142 (42000): SELECT command denied to user 'foo_admin'@'localhost' for table 'salary'
 
mysql> SELECT count(*) FROM payments.merchant;
ERROR 1142 (42000): SELECT command denied to user 'foo_admin'@'localhost' for table 'merchant'

Had the partial revokes been granted through a role then they would become effective after the role is activated. I leave those scenarios for you to try.

Partial Revokes Aggregation

So far, so good. You get the idea how partial revokes work. As we know, a user has the capability to grant the privileges to other users if the former is granted the privileges ‘GRANT OPTION’. We may call the granting user the Grantor. The user to whom the grantor  grants the privileges is the Grantee.

What if Grantor is trying to grant some privileges to Grantee and either one of them or both have partial revokes or DB-level grants? It is interesting to have a look at such scenarios.

Let us understand better from the following use cases. Here, I demonstrate the outcome of GRANT and REVOKE statements executed by Grantor for Grantee. Both of them may already have some grants or partial revokes. For the sake of simplicity, I have only shown the relevant part of the SQL statement which conveys the intention.

Create partial revoke on a database when Grantor and Grantee both have global privilege.

Clear the partial revoke through the DB level grant.

Clear the DB level grant through partial revoke.

Grantor grants global privilege to a grantee who has no privilege. Partial revokes from Grantor also carry to the Grantee.

Grantor has partial revokes and Grantee has global privilege.

• Grantor is not allowed to revoke the privilege from Grantee on the databases on which former has partial revokes.

• Grantor revokes the privilege from Grantee on a database of which former has global access.

• Grantor clears the partial revokes from Grantee by granting the global privilege to the latter.

Aggregate the Global, DB level grants and partial revokes on Grantor as well as Grantee.

Partial Revokes aggregation from roles

A user may be granted multiple roles. Each role may have its own set of grants as well as partial revokes defined. The outcome of the aggregation process becomes even more interesting when roles granted to the user are activated.

Let us peek through an example.

There are four roles, each of which has a set of privileges and partial revoke on a database. All of these four roles are granted to a Grantor who does not have any privilege to begin with. When the Grantor activates all roles, he gets the aggregated privileges (all global privileges) and partial revoke (none in this case).  Grantor now clears the partial revoke from Grantee by granting him the global privilege.

I hope you would have got a fairly good idea about partial revokes on database objects by now. You might be excited to try out your use cases.  You may want to note the following points, though.

Partial revokes feature is controlled through a system variable ‘partial-revokes’ with default as OFF.  You need to change turn ON the value of the system variable in order to use this feature.

You cannot turn OFF the system variable ‘partial-revokes’ as long as there exists at least one partial revoke on a database.

Partial revokes do not support the grants with wildcard characters, hence it treats does not treat the wild card characters ‘_’ and ‘%’ specially.

Thank you for using MySQL!