MariaDB / Intermediate: concurrency and reusable SQL / Lesson 11
Accounts, roles and authentication
Design a narrowly scoped reporting identity without weakening administrator authentication. Separate connection verification from object privileges.
On this page
Lesson goals
- Explain user@host matching and plugin authentication.
- Apply role-based least privilege and verify effective grants.
Identity includes the connecting host
A MariaDB account is a user-and-host combination, not just a username. Authentication proves the caller may use an account; authorization decides which statements and objects that account can access. A local socket login and a TCP connection can select different account matches. Never resolve a failed application login by granting global administrative privileges. Identify the intended endpoint, account match and plugin first, then grant only the application operations required by its business role.
Inspect the actual authentication method
On many Unix installations, local administrative access uses unix_socket, tying authentication to operating-system credentials. Distribution packaging and account creation history matter, so do not assume every root account accepts a password or that passwordless socket access is unauthenticated. MariaDB supports pluggable authentication and, from 10.4, alternative plugins on one account. Client connector capabilities also matter. Retain working administrative access while designing a separate service identity; this course never asks you to alter root.
SELECT CURRENT_USER(), USER(), CURRENT_ROLE();
SHOW GRANTS;These inspection statements describe the current session. Results depend on account matching and active roles.
Grant a reporting capability
The following administration SQL is conceptual and requires an already provisioned lab account named harbor_reader at localhost. A role groups the SELECT privilege on a specific schema so the capability can be audited and assigned consistently. SET DEFAULT ROLE controls activation on future sessions; a grant alone does not prove the role is active in the current connection. Authentication provisioning, secret delivery and TLS requirements are separate reviewed steps and are not embedded as reusable passwords in course text.
Make activation visible
After this design is applied by an authorized administrator in a disposable environment, a new reader session should be able to select permitted harbor_lab objects and should fail attempts to mutate them. SHOW GRANTS FOR the account and role records the assignments, while CURRENT_ROLE() in the reader session checks activation. This is an expected privilege contract, not an observed server result. Schema-level SELECT may expose future tables, so a narrower view or table list can be preferable for sensitive reporting.
CREATE ROLE harbor_reporter;
GRANT SELECT ON harbor_lab.* TO harbor_reporter;
GRANT harbor_reporter TO 'harbor_reader'@'localhost';
SET DEFAULT ROLE harbor_reporter FOR 'harbor_reader'@'localhost';
SHOW GRANTS FOR 'harbor_reader'@'localhost';
SHOW GRANTS FOR harbor_reporter;Administrative illustration only; account creation and real credential configuration are deliberately outside this snippet.
Verify and retire privileges deliberately
Separate runtime, migration, backup and monitoring identities because their privilege needs differ. A migration account may need DDL, while an ordinary request handler normally does not. Audit definers on views and routines as well as direct grants; stored objects can introduce another privilege context. Revocation should be rehearsed with active connection behavior and connection pools in mind. Use encrypted transport for remote connections and confirm negotiated protection rather than relying only on a configuration intention.
Common errors and fixes
ERROR 1045 (28000): Access denied for user
- Why it happens
- Credentials, host matching, plugin support or transport requirements do not match.
- How to fix it
- Inspect the intended account and connection method with an authorized administrator.
- Prevent it next time
- Provision service accounts with explicit host, plugin and connector compatibility.
A granted role does not enable SELECT
- Why it happens
- The role may not be active in the session.
- How to fix it
- Inspect CURRENT_ROLE(), role grants and the default-role policy.
- Prevent it next time
- Verify effective privileges through a new representative service connection.
A report account can modify tables
- Why it happens
- Broad direct grants or another role exceeded its intended scope.
- How to fix it
- Inventory all effective grants and revoke excess through a reviewed change.
- Prevent it next time
- Test both allowed reads and denied writes during provisioning.
Check your understanding
1. A service needs only two reporting views. Is GRANT SELECT ON *.* a suitable starting point?
Show worked solution
No. Grant access to the specific approved views or a narrowly defined reporting schema, and review whether their security context requires access to underlying objects. Global SELECT exposes unrelated and future data. Verify the service can read the intended fields and cannot select unrelated tables or issue mutations.
2. Socket login works for an administrator, while a TCP login using the same name fails. Explain a diagnostic sequence.
Show worked solution
Record the connection method and authenticated account from the working session, then inspect the target user@host and its plugin using authorized administration. Socket authentication can depend on OS identity and may not apply to TCP. Check connector plugin and TLS requirements. Do not change root authentication or add wildcard-host grants as an unexamined workaround.
Key takeaways
- Authentication, account matching and grants are separate checks.
- Verify least privilege through actual role activation and denied-operation expectations.