Revoke database-level privileges without the need for "use db_name;" statement

Question

I have a MySQL user with database-level privileges.

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON mydb.* TO 'theuser';

The user can create tables and insert rows into them.

When the user exceeds the limits, for example, the number of allowed rows in his plan, I want to revoke the INSERT privilege. So the user cannot insert new rows into the tables anymore. It's a plan with soft limits.

I know I can execute the following query:

REVOKE INSERT ON mydb.* FROM 'theuser';

But it doesn't affect until the next use mydb.

According to MySQL docs:

A grant table reload affects privileges for each existing client connection as follows:

• Table and column privilege changes take effect with the client's next request.
• Database privilege changes take effect the next time the client executes a USE db_name statement.
• Global privileges and passwords are unaffected for a connected client. These changes take effect only for subsequent connections.

So my problem is how can I force MySQL to reload database-level privileges without the need for "use db_name" statement, just like table-level privileges.

Answer 1

There is really nothing you can do to circumvent this. Why ???

Look at your third bulletpoint:

Global privileges and passwords are unaffected for a connected client. These changes take effect only for subsequent connections.

That bulletpoint is true for a user with SUPER privileges or root@localhost. Such a user cannot change a live connection's grants in full in the middle of an external or separate session. That has to be true even more so for a non-superuser.

By definition, you should be able to revoke a privilege you could grant.

But look at the grants again

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON mydb.* TO 'theuser';

Notice that this user cannot give away its grants. Why not ??? The WITH GRANT OPTION is missing. So, theuser cannot give away its grants to anyone else.

What you are seeking is the opposite: to revoke the grants. In this case, you are revoking from yourself. Unfortunately, there is no such thing as WITH REVOKE OPTION.

Switching to a database with USE mydb signals mysqld to trigger the reload of theuser's grants from all the grant tables (mysql.user, mysql.db, mysql.tables_priv, mysql.columns_priv). Doing this is far easier to implement that creating an option to revoke grants from a live connection (kinda like unsubscribing from a service that sends you email). Otherwise, grants would more frequently reload grants into RAM for mysqld or into a session's internal buffers. This would introduce some significant read traffic from these tables just for establishing grants.

In light of all this, there are only fours things you can do:

• Do the USE mydb (Best Option)
• Code Changes (Godspeed, Spiderman !!!)
  • Disconnect and open a new session
  • Change application to no longer do SELECT if the user is theuser
  • Use Views, then you can just drops views and trap read errors based on missing views