MySQL / Reliable applications and analytical queries / Lesson 12

Accounts, roles, and safe query construction

Apply least privilege and bind values without confusing them with SQL identifiers.

About 4 min readContent reviewed 2026-10-23

On this page

Lesson goals

Separate authentication, authorization, and query safety.
Bind values and allowlist identifiers and sort directions.

An account includes its host

MySQL accounts are identified by a user and host combination. Authentication proves that the connecting client may use that account; privileges then decide what the account may do. A successful connection does not imply permission to read every schema. Inspect the actual authenticated account with CURRENT_USER(), because the requested login name and matching server-side account can differ. Keep diagnostic output away from public logs.

Grant only a task-shaped permission set

Use the shop fixture in a disposable administrative lab only. A reporting role needs SELECT on approved relations, not global write or account-management privileges. Account creation and grants change server state, so this lesson provides inspection queries rather than issuing credentials. MySQL 8.4 uses caching_sha2_password by default; mysql_native_password is disabled by default. Update compatible clients instead of assuming an older authentication workaround is appropriate.

sql Read authentication and authorization evidence

SELECT USER() AS requested_identity, CURRENT_USER() AS matched_account;
SHOW GRANTS FOR CURRENT_USER;
SELECT CURRENT_ROLE() AS active_roles;

Values depend on the current connection and are intentionally not fabricated. USER identifies the client-supplied login and host context; CURRENT_USER identifies the account used for privilege checking. Granted roles and active roles are distinct, so inspect activation before concluding that a grant is missing.

Parameters represent values

An attacker-controlled name must be data, never concatenated SQL syntax. Native prepared statements separate the SQL template from bound values; placeholders represent expressions such as an id or status. They do not turn untrusted table names, column names, or keywords into safe SQL structure. Application connectors normally provide their own binding APIs; use their documented placeholders and avoid manual escaping as the primary defense.

sql Bind two values into a fixed template

PREPARE customer_orders FROM
 'SELECT id,total FROM orders WHERE customer_id=? AND status=? ORDER BY id';
SET @customer_id=1, @status='paid';
EXECUTE customer_orders USING @customer_id,@status;
DEALLOCATE PREPARE customer_orders;

Expected output

id | total
101 | 25.00

The result is order 101 with total 25.00. The SQL structure remains fixed when the supplied values change. This SQL-level demonstration uses session variables; a real application should use the connector's native parameter interface and close prepared resources according to its lifecycle.

Allowlist SQL structure

For a user-selectable sort, map external choices such as newest or amount to fixed developer-owned fragments such as placed_at DESC, id DESC or total DESC, id DESC. Reject unknown choices. Binding ORDER BY ? orders by a parameter value rather than dynamically naming a column. Quoting an arbitrary identifier is not an authorization decision; the application must decide which identifiers may be selected.

Protect the surrounding system

Use verified encrypted transport, store secrets outside source control, and avoid printing connection strings or passwords in exceptions. Apply separate accounts for migrations, runtime writes, and reporting. Rotation requires testing new credentials and pooling behavior, not merely changing a variable. MySQL and MariaDB authentication plugins, privilege details, and role behavior differ; verify the target product and connector instead of copying account recipes between them.

Common errors and fixes

ERROR 1045 (28000): Access denied.

Why it happens: The matched user/host, credential, or authentication-plugin requirements do not match the client.
How to fix it: Check endpoint and account matching with the administrator and a compatible client.
Prevent it next time: Separate connection diagnostics from privilege changes; never grant broadly to guess.

A bound sort-column parameter does not sort by that column.

Why it happens: A placeholder represents a value expression, not an identifier.
How to fix it: Map the requested sort to a fixed allowlisted SQL fragment.
Prevent it next time: Keep values parameterized and SQL structure developer-controlled.

ERROR 1142 (42000): command denied.

Why it happens: Authentication succeeded but the active account lacks the requested table operation.
How to fix it: Inspect matched account, grants, and active roles before requesting a narrow correction.
Prevent it next time: Test runtime tasks under the least-privileged runtime account.

Check your understanding

1. Can a placeholder safely replace the table name in SELECT * FROM ??

Show worked solution

No. A table name is part of SQL structure, not a value position. Choose from a fixed mapping of permitted table identifiers, then bind all user-supplied values separately. If the caller should not choose tables at all, expose a fixed application operation instead.

2. A reporter can connect but cannot SELECT from orders. What evidence should be checked?