How to create a read-only user in MariaDB

Before connecting your MariaDB database to any external tool, create a dedicated read-only user. This limits the blast radius if credentials are ever leaked and prevents accidental writes from queries that go wrong.

MariaDB is a fork of MySQL and shares almost identical CREATE USER / GRANT syntax, so if you’ve set up a read-only user in MySQL before, this will look familiar.

Step 1: Create the user

CREATE USER 'byesql_reader'@'%' IDENTIFIED BY 'choose-a-strong-password';

The '%' wildcard allows connections from any host. If your tool always connects from a specific IP range (e.g. a fixed egress IP), restrict it:

CREATE USER 'byesql_reader'@'203.0.113.0/24' IDENTIFIED BY 'choose-a-strong-password';

MariaDB (10.4+) defaults new users to the mysql_native_password or ed25519 plugin depending on version and configuration. Either works fine for a password-authenticated read-only user, and no extra configuration is needed for this use case.

Step 2: Grant SELECT on the database

GRANT SELECT ON your_database_name.* TO 'byesql_reader'@'%';

This grants SELECT on all tables in your_database_name, now and in the future. Like MySQL, MariaDB applies ON database.* grants to tables created after the grant was given, so no additional default privilege configuration is needed.

Step 3: Apply the privileges

FLUSH PRIVILEGES;

This is technically not required when using GRANT (MariaDB reloads grant tables automatically), but it is a common convention to run it explicitly for clarity.

Grant on a specific database only

The your_database_name.* syntax scopes access to a single database. The user cannot access other databases on the same server.

To confirm:

SHOW GRANTS FOR 'byesql_reader'@'%';

You should see something like:

GRANT SELECT ON `your_database_name`.* TO `byesql_reader`@`%`

Limiting access to specific tables

If you need to hide certain tables, for example a payment_methods table or a user_credentials table, grant SELECT per table instead of using .*:

GRANT SELECT ON your_database_name.orders TO 'byesql_reader'@'%';
GRANT SELECT ON your_database_name.products TO 'byesql_reader'@'%';
GRANT SELECT ON your_database_name.customers TO 'byesql_reader'@'%';
FLUSH PRIVILEGES;

The user will see only those three tables and nothing else.

Verify the setup

Connect as the read-only user and test:

-- As byesql_reader:
SELECT * FROM orders LIMIT 1;             -- should succeed
INSERT INTO orders (status) VALUES ('x'); -- should fail: INSERT command denied

Revoking access

REVOKE ALL PRIVILEGES ON your_database_name.* FROM 'byesql_reader'@'%';
DROP USER 'byesql_reader'@'%';
FLUSH PRIVILEGES;

A note on defense in depth

A read-only MariaDB user is your database-level safeguard. ByeSQL adds a second layer through SAFE mode: all write operations (INSERT, UPDATE, DELETE, DROP, TRUNCATE, ALTER) are blocked at the application level before any SQL is sent to your database.

Both controls together mean that even if one layer has a gap, the other catches it. Use both.