How to Manage RBAC in MariaDB

How do I set up role-based access control (RBAC) in MariaDB?

MariaDB RBAC lets you bundle privileges into roles and assign them to users for centralized, least-privilege access control.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What is RBAC in MariaDB?

RBAC (Role-Based Access Control) uses roles—named privilege bundles—to simplify permission management.Instead of granting SELECT on Customers to every analyst, grant it once to a role and give users that role.

How do I create roles?

Run CREATE ROLE for each logical job function, e.g., analysts, support, or app services.

Example

CREATE ROLE analyst, support_agent;

How do I grant privileges to roles?

Use GRANT to attach table-level or database-level privileges to a role.

Example

GRANT SELECT ON ecommerce.Customers TO analyst;GRANT SELECT, UPDATE(stock) ON ecommerce.Products TO support_agent;

How do I assign roles to users?

Grant roles to users, then pick one or more as their default.

Example

GRANT analyst TO 'jane'@'%';SET DEFAULT ROLE analyst TO 'jane'@'%';

How do I activate a role for a session?

Inside a connection, call SET ROLE.Activate one, many, or NONE.

Example

SET ROLE analyst;

How do I revoke privileges or roles?

Use REVOKE to detach permissions or remove role membership.

Example

REVOKE SELECT ON ecommerce.Customers FROM analyst;REVOKE analyst FROM 'jane'@'%';

Best practices for MariaDB RBAC

1) Design roles around business tasks, not people. 2) Grant least privilege—only needed columns. 3) Use DEFAULT ROLE so users activate correct permissions automatically.4) Audit regularly with SHOW GRANTS.

Next steps and tools

Automate role creation in migration scripts, and manage grants visually with Galaxy’s SQL editor to keep RBAC changes version-controlled and reviewable.

Why How to Manage RBAC in MariaDB is important

How to Manage RBAC in MariaDB Example Usage


-- Create a role that can read Customers and Orders and insert new OrdersItems
CREATE ROLE order_processor;
GRANT SELECT ON ecommerce.Customers TO order_processor;
GRANT SELECT, INSERT ON ecommerce.Orders TO order_processor;
GRANT INSERT ON ecommerce.OrderItems TO order_processor;

-- Assign to API user and make it default
GRANT order_processor TO 'api_user'@'10.%';
SET DEFAULT ROLE order_processor TO 'api_user'@'10.%';

How to Manage RBAC in MariaDB Syntax


CREATE ROLE [IF NOT EXISTS] role1 [, role2 ...];

GRANT { privilege [, privilege...] | ALL } 
      ON [database.]table 
      TO role_name;

GRANT role_name [, role_name...] TO 'user'@'host';

SET DEFAULT ROLE {role_name | NONE | ALL} TO 'user'@'host';

SET ROLE {role_name | NONE | ALL};

REVOKE { privilege [, privilege...] | ALL } 
       ON [database.]table 
       FROM role_name;

REVOKE role_name [, role_name...] FROM 'user'@'host';

-- Ecommerce-focused illustration
CREATE ROLE order_viewer;
GRANT SELECT ON ecommerce.Orders TO order_viewer;
GRANT order_viewer TO 'service_app'@'%';

Common Mistakes

Granting privileges directly to each user. Why wrong: ballooning administrative work and inconsistent permissions. Fix: create roles, grant privileges once, then assign roles to users.
Forgetting to set a default role. Why wrong: users start with no privileges and queries fail. Fix: <code>SET DEFAULT ROLE role_name TO 'user'@'host';</code>.