MySQL REVOKE - Revoking User Privileges

The REVOKE command in MySQL is used to remove privileges from users or roles in the database. It is the opposite of the GRANT command and represents a key tool for database security management.

Types of privileges that can be revoked:

1. SELECT, INSERT, UPDATE, DELETE (for data manipulation)
2. CREATE, ALTER, DROP (for structure management)
3. ALL PRIVILEGES (all privileges)

Examples of objects:

1. Specific table: database_name.table_name
2. All tables in database: database_name.*
3. All databases: *.*

Important notes:

1. Administrative privileges are required to use REVOKE
2. Privilege changes take effect immediately
3. After multiple privilege changes, it is recommended to run FLUSH PRIVILEGES
4. Always follow the principle of least privilege

1. First, we create a new user "demo_user" who can only connect from the local computer (localhost). We also set their password to "demo123".

   CREATE USER 'demo_user'@'localhost' IDENTIFIED BY 'demo123';            

2. We grant the user privileges for selecting (SELECT), inserting (INSERT), and updating (UPDATE) data on all tables in the "trading" database.

   GRANT SELECT, INSERT, UPDATE ON trading.* TO 'demo_user'@'localhost';            

3. We check which privileges have been granted to user "demo_user".

   SHOW GRANTS FOR 'demo_user'@'localhost';            

4. We revoke the insert (INSERT) and update (UPDATE) privileges from the user for all tables in the "trading" database.

   REVOKE INSERT, UPDATE ON trading.* FROM 'demo_user'@'localhost';            

5. We check the user "demo_user" privileges again to confirm the successful revocation.

   SHOW GRANTS FOR 'demo_user'@'localhost';            

6. We revoke the last privilege (SELECT) from user "demo_user" on all tables in the "trading" database.

   REVOKE SELECT ON trading.* FROM 'demo_user'@'localhost';            

7. We delete the user "demo_user" as they no longer need access to the database.

   DROP USER 'demo_user'@'localhost';            

8. Finally, we refresh the privileges to ensure all changes take effect in the system.

   FLUSH PRIVILEGES;