How do the REVOKE and GRANT commands work?

The GRANT and REVOKE commands in SQL are used to manage user access rights to database objects.

They allow granting or revoking specific privileges to or from users or roles, ensuring security and control over who can perform what actions in the database.

GRANT

The GRANT command provides users or roles with specific privileges on database objects.

Syntax:

MySQL 8.1
GRANT privileges ON object TO user [WITH GRANT OPTION];

• privileges: the actions allowed (e.g., SELECT, INSERT, UPDATE, DELETE, ALL PRIVILEGES).
• object: the database, table, view, procedure, etc.
• user: the username or role to which privileges are granted.
• WITH GRANT OPTION (optional): allows the recipient of the privileges to grant them to others.

Example:
To grant user user1 the privilege to select data from the employees table:

MySQL 8.1
GRANT SELECT ON employees TO user1;

REVOKE

The REVOKE command removes previously granted privileges from users or roles.

Syntax:

MySQL 8.1
REVOKE privileges ON object FROM user;

Example:
To revoke the SELECT privilege from user user1 on the employees

MySQL 8.1
REVOKE SELECT ON employees FROM user1;