How to Manage Users in MariaDB

Galaxy Glossary

How do I manage users in MariaDB?

CREATE, ALTER, GRANT and DROP users to control access in MariaDB.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Thank you! Your submission has been received!

Oops! Something went wrong while submitting the form.

Description

What tasks make up user management?

Creating accounts, setting authentication, granting or revoking privileges, altering credentials, and safely removing users together form MariaDB user management. Each step protects business data in tables such as Customers, Orders, and Products.

How do I create a new user?

Run CREATE USER with a user name, optional host, and authentication method. Providing a host avoids duplicate, unmanaged accounts.

CREATE USER 'app_reader'@'%' IDENTIFIED BY 'S3cure!';

How can I grant only the rights an app needs?

Use GRANT to assign the least-privilege set, targeting the exact schema or table.

GRANT SELECT ON ecommerce.Customers TO 'app_reader'@'%';

How do I change a password or authentication plugin?

ALTER USER modifies credentials instantly. Require a strong password policy to prevent weak logins.

ALTER USER 'app_reader'@'%' IDENTIFIED BY 'N3wPw!2024';

How do I remove an obsolete user?

DROP USER deletes the account and any privilege rows in one step.

DROP USER IF EXISTS 'app_reader'@'%';

How can I list current privileges?

Query the INFORMATION_SCHEMA.USER_PRIVILEGES or run SHOW GRANTS to audit access levels regularly.

SHOW GRANTS FOR 'app_reader'@'%';

What are best practices for MariaDB user management?

Apply least privilege, separate service accounts from humans, rotate passwords, and enable audit_log for traceability.

Why should I avoid using `root` in applications?

Using the superuser in code risks accidental data loss and security breaches. Always create scoped accounts.

Why How to Manage Users in MariaDB is important

How to Manage Users in MariaDB Example Usage


-- Create an analyst account limited to read-only access
CREATE USER 'analyst'@'%' IDENTIFIED BY 'Str0ng!';
GRANT SELECT, SHOW VIEW ON ecommerce.* TO 'analyst'@'%';
-- Verify privileges
SHOW GRANTS FOR 'analyst'@'%';

How to Manage Users in MariaDB Syntax


-- Create user
CREATE USER 'user'@'host' IDENTIFIED BY 'password'
    REQUIRE SSL | NONE
    PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY];

-- Grant privileges
GRANT {ALL PRIVILEGES | privilege [, ...]}
    ON {db.* | db.table}
    TO 'user'@'host'
    [WITH GRANT OPTION];

-- Alter user
ALTER USER 'user'@'host'
    IDENTIFIED BY 'new_password'
    [REQUIRE SSL | NONE]
    PASSWORD EXPIRE;

-- Remove user
DROP USER [IF EXISTS] 'user'@'host';

-- Example ecommerce flow
CREATE USER 'reporter'@'%' IDENTIFIED BY 'pw';
GRANT SELECT ON ecommerce.Orders TO 'reporter'@'%';

Common Mistakes

Ignoring the host part. Creating 'user'@'localhost' but connecting from the app as 'user'@'%' fails. Always specify the correct host when running CREATE USER.
Granting ALL PRIVILEGES on *.* to application users. This exposes the entire server. Instead, grant table-level or schema-level rights required by the app.

Frequently Asked Questions (FAQs)

Can I rename a user?

Yes. Use RENAME USER 'old'@'host' TO 'new'@'host'; which keeps existing privileges.

Do I need FLUSH PRIVILEGES after GRANT?

No. GRANT, REVOKE, CREATE USER, and DROP USER auto-reload privilege tables in MariaDB.

How do I force a user to change password at next login?

Add PASSWORD EXPIRE to ALTER USER. The session will prompt for a new password on first connection.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.