Revoke privileges

Question

I have a database and I’m trying to make sure that only my user has access to this base. ie no one else can open, search, delete anything. I’m using the following syntax:

GRANT USAGE ON batabase.* TO 'fabricio'@'%'; FLUSH PRIVILEGES;

But nothing has changed all users can still do anything.

Answer 1

To revoke privileges you need to use the REVOKE. GRANT serves only to give privileges. The fact that you give privileges to a user does not automatically revoke anyone’s privilege. This way:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM usuario1, usuario2

You can list all users separated by comma. I think the default user names you know: 'outrousuario'@'%'

For a specific database:

REVOKE ALL PRIVILEGES ON banco.* FROM usuario1, usuario2

Remembering that you only need to remove privileges from users who have them. If they have never received privileges they no longer get access.

Obviously you need to have privilege to do this.

To confirm the privileges configured for each user use:

SHOW GRANTS FOR usuario

To list the privileges of all users:

SHOW GRANTS FOR '%'@'%'

or

SELECT sql_grants FROM common_schema.sql_show_grants

As additional information it is possible to simplify the withdrawal of privileges of all users:

REVOKE ALL PRIVILEGES ON banco.* FROM '%'@'%'

Obviously if you need a specific user to have access you should give the privileges to him:

GRANT USAGE ON banco.* TO 'usuario'@'%'

After finishing the whole process it may be interesting to use FLUSH PRIVILEGES to guarantee the new status immediately.

It is obvious that it is not possible to revoke privileges that the user does not have in that host.

I put in the Github for future reference.