Removing user insert Privilege in MySQL

Revoke Privileges from a User

Write a MySQL query to revoke the INSERT privilege from the user "gaioz_amira" on the SalesDB database.

Solution:

-- This command removes the previously granted INSERT privilege from the user 'gaioz_amira'
-- The revocation applies to all tables in SalesDB for connections from localhost
REVOKE INSERT ON SalesDB.* FROM 'gaioz_amira'@'localhost';

Explanation:

• Purpose of the Query:
• The goal is to remove the ability of "gaioz_amira" to insert new records into the SalesDB database.
• This demonstrates the REVOKE statement for modifying a user's privileges.
• Key Components:
• REVOKE INSERT : Specifies the privilege to be removed.
• ON SalesDB.* : Targets the privilege scope on all tables of the SalesDB database.
• FROM 'gaioz_amira'@'localhost' : Identifies the user whose privilege is being revoked.
• Real-World Application:
• Important when adjusting user permissions to prevent unauthorized data modifications.

Notes:

• Verify the current privileges before revoking to avoid accidental privilege removal.

For more Practice: Solve these Related Problems:

• Write a MySQL query to revoke the UPDATE privilege on the "Employee" table in the "HR" database from "test_user"@'localhost'.
• Write a MySQL query to revoke all privileges on the "TestDB" database from "guest_user"@'%'.
• Write a MySQL query to revoke EXECUTE privileges on stored procedures in the "ProcDB" database from "script_user"@'localhost'.
• Write a MySQL query to revoke INSERT and DELETE privileges from "temp_user"@'localhost' on multiple tables in the "Sales" database.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous MySQL Exercise: Grant Privileges to a User.
Next MySQL Exercise: Delete a User.