Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation – Advanced Level

Answer: Granting and Verifying Permissions

Explore how to assign column-level UPDATE privileges in MySQL using the GRANT statement. Learn to verify permissions with SHOW GRANTS and understand the importance of restricting access for data integrity. This lesson helps you manage user privileges effectively for advanced SQL interview readiness.

We'll cover the following...

Solution

The solution is given below:

MySQL 8.0
-- Grant column-level UPDATE permission
GRANT UPDATE (Stock, LastRestockDate)
ON OnlineStore.Products
TO 'inventory_manager'@'localhost';
-- Verify the granted permissions
SHOW GRANTS FOR 'inventory_manager'@'localhost';

Explanation

The explanation of the solution code is given below:

  • Lines 2–4: The GRANT statement assigns UPDATE privileges only for the Stock and LastRestockDate columns on the OnlineStore.Products table to the user account 'inventory_manager'@'localhost'. This keeps write access limited to just the columns required for inventory updates.

  • Line 7: SHOW GRANTS returns the privileges currently granted to 'inventory_manager'@'localhost', allowing you to verify that the column-level permissions were applied as intended.

Recall of relevant concepts

We have covered the following concepts in this question:

    ...