Examine this statement:

Question

Exhibit

Options

• AInserting COMMIT; SET @m :=: before line 4
• Buser who creates the procedure needing the create and execute privileges
• Cuser who creates the procedure needing the create routine privilege
• Dinserting USE <database >; before line 3
• EInserting DEFINER 'username '@' localhost' clause into the CREATE PROCEDURE statement

• A
  5% (3)
• B
  6% (4)
• C
  18% (12)
• D
  2% (1)
• E
  70% (46)

Explanation

Option E is correct because the DEFINER clause explicitly sets which MySQL user account's privileges are used when the stored procedure executes, resolving security context issues where the procedure may fail due to insufficient access rights at runtime. Without DEFINER, MySQL defaults to the current user, which may not have the necessary access to the underlying objects the procedure interacts with.

Why the distractors are wrong:

• A is incorrect - inserting COMMIT and a variable assignment has no bearing on procedure security or execution context.
• B is incorrect - the relevant privilege for creating a procedure is CREATE ROUTINE, not EXECUTE; EXECUTE is for calling procedures.
• C is partially true but incomplete - CREATE ROUTINE is needed to create the procedure, but it doesn't solve the runtime security context problem that DEFINER addresses.
• D is incorrect - USE <database> selects a default schema but doesn't resolve privilege or execution context issues within the procedure itself.

Memory tip: Think of DEFINER as the procedure's "identity badge" - it tells MySQL whose credentials to check at runtime, separate from who created or calls it. When a procedure behaves like it lacks access, ask "whose identity is it running as?"

No community discussion yet for this question.