MySQL: how to grant and revoke privileges in this database

The GRANT function in MySQL is used to grant privileges:

GRANT privilegios
ON base/tabla
TO usuario [IDENTIFIED by ´contraseña´]
[WITH GRANT OPTION];

The WITH GRANT OPTION option allows the user to grant other users the same privileges they have.

The following query would grant all permissions (except WITH GRANT OPTION) to a user on a specified database:

GRANT ALL ON BD.* TO USER@localhost IDENTIFIED BY "PASSWORD";

To grant all permissions, including WITH GRANT OPTION, you must specify the corresponding option:

GRANT ALL ON BD.* TO USER@localhost IDENTIFIED BY "PASSWORD" WITH GRANT OPTION;

Note that this is not advisable and is highly insecure, since only the administrator user should be able to add or remove privileges.

The following would only grant permission to run certain queries on the database. Note that the user would not be able to create new tables or new databases:

GRANT select,insert,update,delete ON BD.* TO USER@localhost IDENTIFIED BY "PASSWORD";

In MySQL +8 it is not possible to use IDENTIFIED with GRANT. Therefore, to create the user and assign permissions, you would use:

CREATE USER 'usuario'@'localhost' IDENTIFIED BY 'PASSWORD';

GRANT ALL PRIVILEGES ON BD.* TO 'usuario'@'localhost';

The function REVOKE is used to revoke privileges from users. Its syntax is very similar to that of GRANT:

REVOKE privilegios ON base/tabla FROM usuario;

This would remove all of the user's permissions.

REVOKE ALL ON BD.* from USER@localhost;