I have a database and I'm trying to make only my user have access to that database. or 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.
To revoke privileges you need to use REVOKE . GRANT is only for 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 commas. I think the default usernames 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 been granted privileges they can no longer access.
Obviously you need to have the 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 it is necessary that a specific user have the access must give the privileges to him:
GRANT USAGE ON banco.* TO 'usuario'@'%'
After finishing the whole process it may be worth using FLUSH PRIVILEGES to guarantee the new state immediately.
Obviously you can not revoke privileges that the user does not have on that host .