MariaDB Grants Explained: Every Privilege

MariaDB Grants Explained: Every Privilege and What It Allows

MariaDB controls what a user is allowed to do through privileges, which are assigned with the GRANT statement and removed with REVOKE. Understanding exactly what each privilege permits is essential for keeping your databases secure: the golden rule is to give every user only the privileges they actually need (the principle of least privilege).

How grants work

A privilege in MariaDB can be granted at one of several levels:

Level	Applies to	Example
Global	The whole server	GRANT PROCESS ON *.* TO 'potion'@'localhost';
Database	All objects in one database	GRANT SELECT ON shop.* TO 'potion'@'localhost';
Table	A single table or view	GRANT INSERT ON shop.orders TO 'potion'@'localhost';
Column	Specific columns in a table	GRANT SELECT (name, email) ON shop.customers TO 'potion'@'localhost';
Routine	A stored procedure or function	GRANT EXECUTE ON PROCEDURE shop.report TO 'potion'@'localhost';

To see what a user currently has, run:

SHOW GRANTS FOR 'potion'@'localhost';

Privileges take effect immediately when granted with GRANT. If you edit the privilege tables directly, you must run FLUSH PRIVILEGES for the changes to apply.

All privileges at a glance

Privilege	Category	Levels	What it allows
SELECT	Data	Global, DB, table, column	Read rows from tables and views. Required for any query that reads table data.
INSERT	Data	Global, DB, table, column	Add new rows to a table with INSERT.
UPDATE	Data	Global, DB, table, column	Change existing rows with UPDATE.
DELETE	Data	Global, DB, table	Remove rows from a table with DELETE.
DELETE HISTORY	Data	Global, DB, table	Delete historical rows from system-versioned tables with DELETE HISTORY. (10.3.4+)
CREATE	Structure	Global, DB, table	Create new databases and tables. At table level, only that specific table name.
ALTER	Structure	Global, DB, table	Change table structure with ALTER TABLE — add columns, change types, rename.
DROP	Structure	Global, DB, table	Delete databases, tables, and views entirely. Also needed for TRUNCATE TABLE. Destructive — grant with care.
INDEX	Structure	Global, DB, table	Create and drop indexes with CREATE INDEX / DROP INDEX.
CREATE VIEW	Structure	Global, DB, table	Create views with CREATE VIEW.
SHOW VIEW	Structure	Global, DB, table	See a view's definition with SHOW CREATE VIEW (querying it only needs SELECT).
CREATE TEMPORARY TABLES	Structure	Global, DB	Create session-local temporary tables, invisible to other users.
REFERENCES	Structure	Global, DB, table, column	Create foreign keys that reference the table.
CREATE TABLESPACE	Structure	Global	Reserved for tablespace management; exists for compatibility, little practical use in MariaDB.
CREATE ROUTINE	Routines & events	Global, DB	Create stored procedures and functions.
ALTER ROUTINE	Routines & events	Global, DB, routine	Change or drop stored procedures and functions. Creators get it automatically for their own routines.
EXECUTE	Routines & events	Global, DB, routine	Run stored procedures and functions with CALL or in queries.
TRIGGER	Routines & events	Global, DB, table	Create and drop triggers on a table.
EVENT	Routines & events	Global, DB	Create, alter, and drop scheduled events — MariaDB's built-in task scheduler.
GRANT OPTION	Administration	All levels	Pass one's own privileges on to other users (WITH GRANT OPTION). Never more than the user holds.
CREATE USER	Administration	Global	Manage accounts: CREATE USER, DROP USER, RENAME USER, REVOKE ALL PRIVILEGES.
RELOAD	Administration	Global	Run FLUSH commands (FLUSH PRIVILEGES, FLUSH TABLES, FLUSH LOGS, ...).
PROCESS	Administration	Global	See all users' running queries via SHOW PROCESSLIST. Sensitive — queries may contain data or credentials.
SUPER	Administration	Global	Broad admin power: KILL other connections, SET GLOBAL, connect past max_connections, write despite read_only, control replication. Split into fine-grained privileges from 10.5.2.
SHUTDOWN	Administration	Global	Stop the server with SHUTDOWN or mariadb-admin shutdown.
FILE	Administration	Global	Read/write files on the database server via LOAD DATA INFILE, SELECT ... INTO OUTFILE, LOAD_FILE(). A common attack vector — grant rarely, if ever.
SHOW DATABASES	Administration	Global	See the full database list; without it, users only see databases they have privileges on.
LOCK TABLES	Administration	Global, DB	Explicitly lock tables with LOCK TABLES (also needs SELECT). Used by backup tools like mariadb-dump.
USAGE	Administration	Global	"No privileges" — the user can log in but do nothing else. Every account implicitly has it.
PROXY	Administration	Global	Impersonate another account and take on its privileges. Mainly used with authentication plugins.
REPLICATION CLIENT / BINLOG MONITOR	Replication	Global	Status commands like SHOW MASTER STATUS and SHOW BINARY LOGS. Renamed BINLOG MONITOR in 10.5+ (old name works as alias).
REPLICATION SLAVE	Replication	Global	Read replication events from the primary's binary log — required by the replica's connection account.
REPLICATION MASTER ADMIN	Replication	Global	Administer the primary side: SHOW REPLICA HOSTS, primary-related global variables. (10.5.2+)
REPLICATION SLAVE ADMIN	Replication	Global	Administer the replica side: START/STOP SLAVE, CHANGE MASTER TO, related variables. (10.5.2+)
SLAVE MONITOR	Replication	Global	SHOW SLAVE STATUS and SHOW RELAYLOG EVENTS without full admin rights — useful for monitoring. (10.5.9+)
BINLOG ADMIN	Replication	Global	Administer the binary log: PURGE BINARY LOGS, binlog-related global variables. (10.5.2+)
BINLOG REPLAY	Replication	Global	Replay binary log contents, e.g. apply mariadb-binlog output containing BINLOG statements. (10.5.2+)
CONNECTION ADMIN	SUPER replacements	Global	Kill other users' connections/queries, connect past max_connections, set connection-related global variables. (10.5.2+)
READ_ONLY ADMIN	SUPER replacements	Global	Write to the server even when global read_only is on. Excluded from ALL PRIVILEGES since 10.11. (10.5.2+)
FEDERATED ADMIN	SUPER replacements	Global	Administer FEDERATED/FederatedX servers: CREATE/ALTER/DROP SERVER. (10.5.2+)
SET USER	SUPER replacements	Global	Set the DEFINER of views, routines, triggers, and events to another user — relevant when restoring dumps. (10.5.2+)

ALL PRIVILEGES

GRANT ALL PRIVILEGES gives every privilege available at the chosen level, except GRANT OPTION (and, from MariaDB 10.11, READ_ONLY ADMIN). A typical pattern on hosting platforms is granting a customer full control of their own database:

GRANT ALL PRIVILEGES ON customer_db.* TO 'customer'@'%';

This makes the user all-powerful within that one database while keeping server-wide administration off-limits.

Practical examples

A read-only reporting user:

CREATE USER 'report'@'%' IDENTIFIED BY 'strong-password';
GRANT SELECT ON shop.* TO 'report'@'%';

A typical web-application user:

CREATE USER 'webapp'@'%' IDENTIFIED BY 'strong-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'webapp'@'%';

A backup user:

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'strong-password';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup'@'localhost';

Removing a privilege again:

REVOKE DELETE ON shop.* FROM 'webapp'@'%';

Summary

• Grant the minimum privileges required — start with SELECT, INSERT, UPDATE, DELETE for applications and add more only when needed.
• Treat FILE, SUPER, PROCESS, SHUTDOWN, and GRANT OPTION as administrator-only.
• Use SHOW GRANTS regularly to audit what your users can do.
• On MariaDB 10.5+, prefer the fine-grained admin privileges over SUPER.