SQL SECURITY DEFINER turns a stored routine into an authorization boundary
A MySQL stored procedure or function can execute with the privileges of its definer. That is the default behavior. It allows an application to expose a narrow operation without granting the caller direct access to the underlying tables. It also makes the routine definition an authorization decision that needs the same review as a grant.
The execution context
MySQL documents SQL SECURITY DEFINER and SQL SECURITY INVOKER as the two security contexts. DEFINER checks privileges against the account named in the routine's DEFINER clause. INVOKER uses the account that calls the routine.
The invoking user needs EXECUTE on the routine. In definer context, the definer account also needs permission to access the database and the objects used by the routine. CURRENT_USER() inside the routine returns the definer value, which is a useful check during testing.
The default is DEFINER. A developer who creates a routine while connected as an administrative account can therefore leave a procedure running with that account's authority even when the application calls it as a low-privilege user.
Role behavior can change the result
The MySQL stored-object documentation notes that a definer-context routine does not automatically activate every role the definer could use. It uses the account's default roles unless the routine enables other roles. This can cause a procedure to fail after a role is removed from the definer's defaults, even though the account still has the role available.
That behavior cuts both ways. A routine may have less authority than an administrator expects, or a default role may carry more access than the routine needs. Review the definer account, its default roles, and the object grants directly used by the routine. Do not infer the effective set from the account's role membership list alone.
Definer privilege can turn input into escalation
A definer routine is not automatically vulnerable to SQL injection. The risk appears when the routine constructs dynamic SQL, accepts an object or schema name from a caller, or calls another routine with broader permissions. A low-privilege caller may be able to make the definer execute a statement that the caller could not run directly.
Keep dynamic SQL out of definer routines unless the identifiers come from a fixed allowlist. Do not concatenate user-provided text into a statement. Use parameter binding for values and validate object names against an explicit map. Avoid SECURITY DEFINER on routines that call arbitrary helpers with different privilege expectations.
The definer account should be a dedicated account with only the objects the routine needs, not an administrative account used for migrations. If the routine is dropped or recreated, verify the definer after restoration. An import can change the definer or fail when the account does not exist.
Inspect the effective routines
Use an account with the required metadata privileges to list routines and their security characteristics:
SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE,
SECURITY_TYPE, DEFINER, SQL_MODE
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'example';
Then inspect the routine body and the definer's default roles. Test with the application account, not the administrator account. Confirm that the application can execute the intended procedure and that it cannot use the same connection to access tables outside the procedure's purpose.
Include the routine's creation and alteration events in change review. A stored procedure can be replaced without changing the table grants that make it work. Comparing only table privileges will miss the change.
Prefer invoker security when it fits
Use SQL SECURITY INVOKER when the caller should retain its own authorization context. Use DEFINER when the application truly needs a controlled operation that the caller cannot perform directly. Document the reason, the definer account, and the routine's input validation.
Finally, test the error path. A routine that returns a detailed SQL error can disclose schema names or query structure to a low-privilege caller. Return a stable error to the application and keep the detailed diagnostic in a protected log.
The useful question is not whether a routine uses DEFINER. It is whether the routine's fixed authority is narrow, auditable, and impossible for the caller to redirect.
Top comments (0)