revoke and apply privileges on current active sessions

Question

I am revoking some privileges to a user like this:

REVOKE INSERT, UPDATE, CREATE, ALTER ON `name`.* FROM 'user'@'%'

I verify via:

SHOW GRANTS FOR 'user'@'%';

And see that privileges really have removed, but the problem I am now trying to solve is that even after flushing the privileges FLUSH PRIVILEGES;, if the user still logged in, the user can do inserts therefore wondering how could I force the new permissions to users with a current session open.

If the user reconnects the permissions take place, I can not kill the connection since I have only access remotely, any alternatives, suggestions?

Answer 1

Assume that you revoke a privilege when the user is executing some long-time query which needs in this privilege for successfull execution. This will cause unpredictable break and may result into data unconsistence. So privilege altering cannot be applied to existing session. You can reset the user's connection to force him to reconnect, new privileges will be applied in that case. Of course the best way is to do that only when user's connection is idle. – Akina

If you have suitable permissions, you can kill his connection. Local vs remote is not relevant. Do SHOW GRANTS; for yourself. From the commandline mysql tool: SHOW PROCESSLIST; to see what the id is, then kill <<that id>>; – Rick James