Skip to content
Tutorials Center
My learning

Topics

Lesson 36 of 40 ยท Replication, Backup and Security

Users, Grants and Roles in MariaDB

Create MariaDB users, grant and revoke privileges at global, database, table and column level, use roles, resource limits and password policies, and follow least privilege.

Every connection to MariaDB authenticates as an account, and every statement is checked against that account's privileges. Good account design is the cheapest security measure you have: the web application should not be able to DROP tables, the reporting tool should only read, and nobody should use root for daily work.

This lesson covers creating accounts, granting and revoking privileges at different levels, roles, password handling, and the principle of least privilege. Account management needs a server and the mysql system schema, so the SQL statements are read-only; the stand-ins model privilege checks with plain tables.

Accounts: user and host

A MariaDB account is 'user'@'host'. The host part limits where the user may connect from: 'app'@'10.0.0.%' is a different account from 'app'@'localhost' and from 'app'@'%' (anywhere).

SQL
CREATE USER 'shop_app'@'10.0.0.%' IDENTIFIED BY 'a-long-random-password';
CREATE USER IF NOT EXISTS 'reporter'@'localhost' IDENTIFIED BY '...';
CREATE OR REPLACE USER 'tmp'@'localhost' IDENTIFIED BY '...';      -- MariaDB 10.1.3+

ALTER USER 'shop_app'@'10.0.0.%' IDENTIFIED BY 'new-password';
ALTER USER 'shop_app'@'10.0.0.%' PASSWORD EXPIRE INTERVAL 90 DAY;
RENAME USER 'old'@'localhost' TO 'new'@'localhost';
DROP USER 'tmp'@'localhost';
SELECT user, host, plugin FROM mysql.global_priv;     -- MariaDB 10.4+ (mysql.user is a view)
SHOW GRANTS FOR 'shop_app'@'10.0.0.%';
SHOW GRANTS;                                           -- for the current account
SELECT CURRENT_USER(), USER();

Authentication plugins: mysql_native_password (the long-time default), ed25519 (stronger, MariaDB), unix_socket (local OS user logs in without a password: this is how root works on many Linux installs), pam, gssapi. Passwords are stored hashed, never in clear text. When a connection can match several accounts, MariaDB picks the most specific host, so keep the list of accounts small and clear.

GRANT and REVOKE

A grant has three parts: privileges, the scope (ON ...), and the account.

SQL
GRANT SELECT ON shop.* TO 'reporter'@'localhost';                    -- all tables of one database
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop_app'@'10.0.0.%';
GRANT SELECT (id, name, email) ON shop.customers TO 'support'@'%';    -- column level
GRANT EXECUTE ON PROCEDURE shop.place_order TO 'shop_app'@'10.0.0.%';
GRANT ALL PRIVILEGES ON shop.* TO 'migrator'@'10.0.0.5';              -- schema owner
GRANT SELECT ON *.* TO 'auditor'@'localhost';                         -- global
REVOKE DELETE ON shop.* FROM 'shop_app'@'10.0.0.%';
FLUSH PRIVILEGES;     -- only needed after editing mysql.* tables by hand, not after GRANT

Scope levels from broadest to narrowest: global *.*, database db.*, table db.tbl, column (col), and routine. The privileges that matter most:

Privilege Allows
SELECT, INSERT, UPDATE, DELETE normal data access
CREATE, ALTER, DROP, INDEX schema changes
EXECUTE run stored routines
CREATE VIEW, SHOW VIEW, TRIGGER, EVENT stored objects
REFERENCES create foreign keys to a table
LOCK TABLES, RELOAD, PROCESS backup tools
REPLICATION REPLICA, BINLOG MONITOR replication and monitoring (10.5+ names)
GRANT OPTION pass your privileges on to others
SUPER and the new fine-grained ones (CONNECTION ADMIN, READ_ONLY ADMIN, ...) administration; avoid giving SUPER

ALL PRIVILEGES ON *.* equals root power; treat it that way. Add WITH GRANT OPTION only for people who manage permissions.

Privileges are cumulative, and a REVOKE must match the level of the grant: revoking SELECT ON shop.* does not remove a separate SELECT ON shop.customers grant. Existing sessions keep the privileges they had at login for global-level changes, and pick up database-level changes at the next USE.

Roles

A role is a named bundle of privileges that you grant to users. When a team changes, you change the role once instead of editing dozens of accounts.

SQL
CREATE ROLE read_only;
CREATE ROLE order_clerk;

GRANT SELECT ON shop.* TO read_only;
GRANT SELECT, INSERT, UPDATE ON shop.orders TO order_clerk;
GRANT read_only TO order_clerk;                    -- roles can include roles

GRANT order_clerk TO 'ana'@'%';
SET DEFAULT ROLE order_clerk FOR 'ana'@'%';        -- active at login (MariaDB 10.1.1+)

-- in a session:
SET ROLE order_clerk;
SELECT CURRENT_ROLE();
SHOW GRANTS FOR order_clerk;
DROP ROLE order_clerk;

A role grant does nothing until the role is enabled in the session, either with SET ROLE or as the account's default role (SET DEFAULT ROLE). That detail is the number-one reason "I granted the role but the user still gets access denied". Check information_schema.APPLICABLE_ROLES and ENABLED_ROLES.

Modelling roles in SQL

Privilege checking is a lookup, and role inheritance is a hierarchy walk. The model below stores grants in tables and expands roles recursively, the same way MariaDB resolves what a user can do:

SQL Roles that include roles

Press Run (or Ctrl + Enter) to see the result here.

Ana gets SELECT through order_clerk > read_only; Raj only reads; Lee collects everything through two levels.

Least privilege in practice

Give each purpose its own account and only what it needs:

Account Privileges Why
web application SELECT, INSERT, UPDATE, DELETE on its own database (or EXECUTE on selected procedures) cannot change schema or read other databases
migration tool ALL on the application database, from the deploy host only schema owner, used only during deploys
reporting SELECT on views cannot see raw tables
backup RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR no data writes
replication REPLICATION REPLICA only
monitoring PROCESS, BINLOG MONITOR or SLAVE MONITOR

A second runnable model: a check query for "may this account do this?" over a flat grants table, which is also useful for auditing who has what:

SQL Audit: who can write to the orders table?

Press Run (or Ctrl + Enter) to see the result here.

legacy_dev with ALL ON *.* stands out immediately. In MariaDB, run a similar audit against information_schema.USER_PRIVILEGES, SCHEMA_PRIVILEGES and TABLE_PRIVILEGES.

Resource limits and password policy

SQL
ALTER USER 'shop_app'@'10.0.0.%'
  WITH MAX_USER_CONNECTIONS 50 MAX_QUERIES_PER_HOUR 0 MAX_STATEMENT_TIME 20;   -- per-account limits

INSTALL SONAME 'simple_password_check';          -- password strength plugin
SET GLOBAL simple_password_check_minimal_length = 14;
INSTALL SONAME 'cracklib_password_check';        -- dictionary check

Also: remove anonymous accounts, run mariadb-secure-installation on new servers, drop the test database, bind to the private interface (bind-address), and keep the application password in a secret store, not in the repository. See securing MariaDB for network and encryption settings.

Common mistakes

  • Using root (or GRANT ALL ON *.*) for the application.
  • '%' as host when a fixed address would do.
  • Forgetting SET DEFAULT ROLE, so granted roles never become active.
  • Using GRANT ... IDENTIFIED BY to create users implicitly: it works but hides what happens; use CREATE USER first.
  • Sharing one account across services, which makes auditing and revocation impossible.
  • Granting SUPER where a narrower privilege would do.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which account identifies who may connect from where?

    An account is a user plus host pattern; 'app'@'localhost' and 'app'@'%' are different accounts.
  2. 2. A role is granted to a user but the user still gets 'access denied'. What is the most likely cause?

    Use SET DEFAULT ROLE ... FOR user so the role is active at login, or run SET ROLE in the session.
  3. 3. Which grant follows least privilege for a web app that only runs its own CRUD queries?

    Data access on one database only, no schema changes, no administration.
  4. 4. What does REVOKE SELECT ON shop.* FROM u do to a separate grant of SELECT ON shop.customers?

    A REVOKE must match the level of the original grant.

Summary

  • Accounts are 'user'@'host'; create them with CREATE USER, change with ALTER USER.
  • GRANT/REVOKE work at global, database, table, column and routine level; use roles to manage groups and remember to enable them.
  • Apply least privilege: separate accounts per purpose, specific hosts, no SUPER.
  • Add password policy, resource limits and regular audits of information_schema privilege tables.

Next: Securing MariaDB