Examine this SQL statement: Which set of privileges will allow Tom to execute this SQL statement?

Question

Options

• AGRANT ALL PRIVILEGES ON world'.city' TO tom'@'%'; GRANT SELECT (code') ON
• BGRANT UPDATE ON world'.* TO tom'@'%';
• CGRANT UPDATE ON world'.city' TO tom'@'%'; GRANT SELECT ON world'.* TO `tom'@'%';
• DGRANT UPDATE ON world'.city' TO tom'@'%'; GRANT SELECT ON world'.`country' TO

• A
  3% (1)
• B
  5% (2)
• C
  13% (5)
• D
  79% (30)

Explanation

Option D is correct because the underlying SQL statement (likely an UPDATE on world.city with a subquery referencing world.country) requires exactly two privileges: UPDATE on the city table to modify rows, and SELECT on the country table to read data from the subquery. Option B fails immediately because it only grants UPDATE on all tables in world but omits SELECT entirely - Tom cannot read from country in the subquery. Option C grants SELECT on world.* (every table in the schema), which is overly permissive and violates the principle of least privilege; MySQL exam questions specifically test whether you grant only what is needed, not more. Option A attempts to use column-level SELECT privilege (only on the code column), which is insufficient if the subquery references additional columns from country, or the grant is simply incomplete as written.

Memory tip: Think "verb + object" - match each operation in the SQL to its exact table. An UPDATE city ... WHERE (SELECT FROM country) needs UPDATE on city + SELECT on country - one grant per table, scoped as narrowly as possible.

No community discussion yet for this question.