How to Configure MariaDB Read Only User

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up a mariadb read only user by creating an account with select permissions. You then link this account to specific databases or tables. Many teams share one root login across several apps and analysts. Broad permissions let simple mistakes delete rows or alter core tables. A read only role stops accidental writes from breaking production systems. How a mariadb read only user works in practice MariaDB manages permissions through an internal grant system. This system checks both the use

Your coding agents run with your access.

Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.

You set up a mariadb read only user by creating an account with select permissions. You then link this account to specific databases or tables. Many teams share one root login across several apps and analysts. Broad permissions let simple mistakes delete rows or alter core tables. A read only role stops accidental writes from breaking production systems.

How a mariadb read only user works in practice

MariaDB manages permissions through an internal grant system. This system checks both the user name and the host address. When a client logs in, the server verifies the host first. Next, it checks the password or external auth plugin. Once the session starts, MariaDB loads the permissions for that account.

To make an account read only, admins grant only the select right. You can apply this rule to the whole server or one database. You can also limit the rule to single tables or specific columns. Many teams create a shared read role first. Then they assign that role to individual analyst accounts.

This role setup makes updates much easier for the database team. When a new schema goes live, you update the shared role once. Every user with that role gets the new read access right away. If someone needs to inspect view definitions, admins add the show view right. Without write permissions, the server blocks insert, update, and delete statements.

What native read permissions solve and what they miss

Native read permissions stop users from changing or dropping data. This protects your tables from bad scripts and human error. It also keeps reporting tools from locking rows during busy hours. For basic safety, database roles are a solid first step.

Still, native database grants only decide who logs in. They cannot inspect what individual queries do while in flight. A read only account can still run a huge join that crashes the server. It can also pull every customer row in one giant query.

Read-Only Root Filesystem + User Provisioning (SCIM): architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Native select rules do not hide private fields inside a table either. If an analyst can read the users table, they see everything in it. Standard grants cannot mask emails, phone numbers, or card numbers on the fly. Teams often build extra views to hide sensitive columns from staff. Maintaining hundreds of custom views across many databases takes too much time.

Static database accounts also create key management headaches for engineering teams. People store database passwords in plain text files or chat rooms. When someone leaves the team, admins must rotate those shared passwords by hand. You also lack a clear log tying queries to real team identities.

Add wire guardrails and masking beside MySQL

Database grants and TLS setup decide who can reach MySQL. Even so, an open session can run broad updates or read raw personal data from allowed tables. In practice, hoop.dev sits inline in the wire protocol beside the database as a sidecar for your MySQL. On every production query an agent or engineer sends, hoop.dev decides to pass, mask, block, or escalate it for human review.

With hoop.dev MySQL support, guardrails refuse bad queries before they run or hold risky calls for approval, while data masking rewrites matched sensitive values in result rows in memory. Furthermore, hoop.dev holds SOC 2 Type II and generates evidence for GDPR, HIPAA, and PCI DSS programs. Read the docs and source on GitHub.

Frequently asked questions

Can a read only account still lock a MariaDB database?

Yes, a read only user can still run heavy select queries. Long queries can eat up memory and slow down writes for other apps. Admins often set query timeouts and connection limits to reduce this risk.

How do you restrict a user to specific columns in MariaDB?

MariaDB lets you attach select rights to individual column names. You can also create a separate view that excludes private columns. Both methods work, but they take significant effort to maintain at scale.

Do read only users need permissions for temporary tables?

Some reporting apps create temporary tables to sort complex query results. For those tools, admins must grant the create temporary tables right. This permission lets the session build scratch tables without changing real data.

The query runs. The emails stay hidden.