How to GRANT Permissions in MariaDB

How do I use the GRANT command to manage user permissions in MariaDB?

GRANT assigns specific privileges to MariaDB users on databases, tables, or columns so they can read, write, or administer data.

Description

What does the GRANT command do in MariaDB?

GRANT adds privileges to a user account, enabling actions like SELECT, INSERT, UPDATE, DELETE, or ALL on chosen objects. It stores the rules in the mysql.user and mysql.db tables, taking effect after FLUSH PRIVILEGES or an automatic refresh.

How do I create a new read-only user for reporting?

Create the user first with CREATE USER, then grant SELECT on required schemas. Use a strong password and limit the host if possible.

CREATE USER 'reporter'@'%' IDENTIFIED BY 'S3cure!'; GRANT SELECT ON ecommerce.* TO 'reporter'@'%';

How can I let a user insert data into specific tables only?

Grant INSERT on individual tables instead of the whole database. Combine with SELECT if the application must read what it writes.

GRANT INSERT, SELECT ON ecommerce.Orders TO 'loader'@'10.%';

How do I grant all privileges on a new database?

Use GRANT ALL PRIVILEGES ON db.* to cover every object, including ones created later. Add WITH GRANT OPTION if the user should delegate rights.

GRANT ALL PRIVILEGES ON analytics.* TO 'dba'@'localhost' WITH GRANT OPTION;

What is the safest way to apply GRANT in production?

Follow the principle of least privilege, grant at the lowest scope, avoid global grants unless necessary, and review rights regularly.

How do I revoke mistakenly granted rights?

Use REVOKE with the same privilege list and scope. Follow with FLUSH PRIVILEGES or reconnect to force re-authentication.

REVOKE INSERT ON ecommerce.Orders FROM 'loader'@'10.%';

Why How to GRANT Permissions in MariaDB is important

How to GRANT Permissions in MariaDB Example Usage


-- Allow the order-processing microservice to read product stock and add orders
CREATE USER 'order_svc'@'%' IDENTIFIED BY 'Sup3r$ecret';
GRANT SELECT ON ecommerce.Products TO 'order_svc'@'%';
GRANT INSERT, SELECT ON ecommerce.Orders TO 'order_svc'@'%';
GRANT INSERT, SELECT ON ecommerce.OrderItems TO 'order_svc'@'%';

How to GRANT Permissions in MariaDB Syntax


GRANT [privilege [, privilege] ...] ON [object_type] priv_level
      TO user_identified [IDENTIFIED BY "password"]
      [REQUIRE ssl_option]
      [WITH GRANT OPTION | MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count | MAX_CONNECTIONS_PER_HOUR count | MAX_USER_CONNECTIONS count];

privilege: SELECT | INSERT | UPDATE | DELETE | ALL PRIVILEGES ...
object_type priv_level:
    *.*                     -- all databases/tables
    db_name.*               -- all tables in one database
    db_name.table_name      -- single table
    db_name.table.col       -- single column

Example (ecommerce schema):
GRANT SELECT, INSERT ON ecommerce.Orders TO 'app'@'%' IDENTIFIED BY 'pw';

Common Mistakes

Granting ALL PRIVILEGES at *.* level for convenience exposes every database to the user. Fix by granting only required privileges on specific schemas or tables.
Forgetting WITH GRANT OPTION when a DBA needs to delegate rights leads to cascading permission issues. Re-run GRANT with WITH GRANT OPTION to resolve.

Frequently Asked Questions (FAQs)

Does GRANT require a service restart?

No. MariaDB updates the privilege tables instantly. The user must reconnect to see new rights.

How do I check current privileges?

Run SHOW GRANTS FOR 'user'@'host'; It returns SQL statements representing existing privileges.

Can I script multiple GRANTs safely?

Yes. Wrap them in a transaction-like block by disabling autocommit and re-enabling it after all GRANTs run. If one fails, manually ROLLBACK unwanted changes.