MariaDB User Management Guide: How to Create Users, Grant Privileges, and Fix Common Errors

Introduction

1.1 Do You Want to Learn How to Create Users in MariaDB?

Have you ever wanted to create a new user in MariaDB and configure appropriate privileges?
For example, you may have encountered the following situations:

• You executed CREATE USER but cannot log in
• You granted privileges using GRANT, but they were not applied
• You are unsure how to configure proper permissions in a production environment
• You want to safely delete unnecessary users

If you are facing these issues, this article will help you fully understand the entire process from creating users to managing privileges and deleting users in MariaDB.

1.2 What You Will Learn in This Article

This article provides a detailed explanation of the following aspects of MariaDB user management:

• How to create and delete users in MariaDB
• How to grant and manage privileges (best practices for proper permission settings)
• Common errors and how to resolve them
• Best operational practices for development and production environments

All topics are explained with beginner-friendly SQL examples, allowing you to learn and practice at the same time.

1.3 Target Audience

This article is intended for:

• Beginners: Those learning MariaDB user management for the first time
• Engineers: Those who want to configure proper permissions in development environments
• Database Administrators: Those aiming to strengthen security in production environments

Even if you are new to MariaDB, this guide provides practical information you can confidently apply in real-world operations.

Creating Users in MariaDB

2.1 Basic User Creation Command

In MariaDB, you create a new user using the CREATE USER command.
You can specify a username and password using the following syntax:

CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';

For example, to create a user named yamada on localhost with the password password123:

CREATE USER 'yamada'@'localhost' IDENTIFIED BY 'password123';

After executing this command, the user yamada will be created and allowed to log in from localhost only (login permitted only on the server itself).

2.2 User Creation Examples by Use Case

When creating users in MariaDB, you can specify where connections are allowed from using the hostname field.
It is important to configure settings appropriately based on your use case.

Using '%' allows connections from all hosts, but
this poses significant security risks and is not recommended for production environments.
In production, it is best to specify IP addresses or configure access through a VPN.

2.3 Important Notes When Creating Users

There are several important considerations when creating users.

(1) Check Existing Users

Before creating a user, it is important to confirm that the same user does not already exist.
You can list currently registered users with the following command:

SELECT user, host FROM mysql.user;

Example output:

+---------+-----------+
| user    | host      |
+---------+-----------+
| root    | localhost |
| yamada  | localhost |
+---------+-----------+

If a user with the same username@hostname already exists, executing CREATE USER will result in an error.

(2) What to Do If the User Already Exists

In MariaDB, you cannot overwrite an existing user by recreating it.
If the same user already exists, use one of the following approaches:

• Change the existing user’s password

ALTER USER 'yamada'@'localhost' IDENTIFIED BY 'newpassword123';

• Delete the unnecessary user and create a new one

DROP USER 'yamada'@'localhost';
CREATE USER 'yamada'@'localhost' IDENTIFIED BY 'password123';

Managing Privileges in MariaDB

3.1 Grant Privileges

Basic Syntax

In MariaDB, you can grant privileges to a user using the GRANT command.

GRANT privilege ON database_name.table_name TO 'username'@'hostname';

For example, to grant all privileges on test_db to the user yamada:

GRANT ALL PRIVILEGES ON test_db.* TO 'yamada'@'localhost';

Meaning of this command:

• ALL PRIVILEGES: Grants all privileges
• test_db.*: Applies to all tables in the test_db database
• 'yamada'@'localhost': Applies to user yamada logging in from localhost

3.2 Commonly Used Privileges

In MariaDB, you can grant privileges such as the following:

If you only want to allow specific operations, it is recommended to grant privileges individually rather than granting everything.

3.3 Privilege Settings by Use Case

It is important to assign the right privileges for each situation.

In production environments, avoid granting ALL PRIVILEGES too easily.

3.4 Check Granted Privileges

To check granted privileges, use the SHOW GRANTS command.

SHOW GRANTS FOR 'username'@'hostname';

For example, to check privileges for user yamada:

SHOW GRANTS FOR 'yamada'@'localhost';

Example output:

+-----------------------------------------------------------------------------------+
| Grants for yamada@localhost                                                       |
+-----------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE ON test_db.* TO 'yamada'@'localhost'                |
+-----------------------------------------------------------------------------------+

This shows the privileges currently granted to the user.

3.5 Revoke Privileges (Remove Permissions)

To revoke specific privileges from a user, use the REVOKE command.

Basic Syntax

REVOKE privilege ON database_name.table_name FROM 'username'@'hostname';

For example, to revoke the UPDATE privilege from user yamada:

REVOKE UPDATE ON test_db.* FROM 'yamada'@'localhost';

Revoke All Privileges

REVOKE ALL PRIVILEGES ON *.* FROM 'yamada'@'localhost';

3.6 What to Do When Privilege Changes Don’t Take Effect

In MariaDB, privilege changes may not be applied immediately. If that happens, run the following command:

FLUSH PRIVILEGES;

Running this command forces MariaDB to reload privilege tables so your changes take effect immediately.

Deleting Users in MariaDB

4.1 Delete a User

To delete a MariaDB user, use the DROP USER command.

Basic Syntax

DROP USER 'username'@'hostname';

For example, to delete the user yamada from localhost:

DROP USER 'yamada'@'localhost';

After executing this command, the user yamada will be completely removed from the MariaDB system.

4.2 What to Check Before Deleting a User

Before deleting a user, there are some important checks you should perform.
Be careful not to delete necessary users by mistake.

(1) Check Whether the Target User Exists

In MariaDB, attempting to delete a non-existent user will cause an error.
Run the following command to confirm the target user exists:

SELECT user, host FROM mysql.user;

Example output:

+---------+-----------+
| user    | host      |
+---------+-----------+
| root    | localhost |
| yamada  | localhost |
| admin   | 192.168.1.100 |
+---------+-----------+

If the user yamada appears in this list, the user can be deleted.

(2) Check Whether the User Is Currently Connected

In MariaDB, if the user you are deleting currently has an active session,
the deletion may not take effect immediately for that session.
Check whether the user is active using the following command:

SELECT user, host FROM information_schema.processlist;

Example output:

+---------+-----------+
| user    | host      |
+---------+-----------+
| yamada  | localhost |
| root    | localhost |
+---------+-----------+

In this example, the user yamada is currently active, so you should terminate the session before deletion.

(3) Force Terminate a Session

You can force terminate a specific session using the KILL command.

1. First, check the process ID (the ID column):

SELECT id, user, host FROM information_schema.processlist;

Example output:

+----+---------+-----------+
| id | user    | host      |
+----+---------+-----------+
| 10 | yamada  | localhost |
| 11 | root    | localhost |
+----+---------+-----------+

2. If yamada has process ID 10, terminate it with:

KILL 10;

4.3 Post-Deletion Cleanup

After deleting a user, residual privilege information may remain in the database.
In such cases, run FLUSH PRIVILEGES to refresh the privilege tables.

FLUSH PRIVILEGES;

Executing this ensures that the deleted user’s information is fully removed from the database system.

4.4 Important Notes When Deleting Users

Keep the following points in mind when deleting users:

1. Deleted users cannot be restored

• Once you execute DROP USER, the deleted user cannot be recovered.
• If deleted by mistake, you must recreate the user using CREATE USER.

1. Privileges May Need to Be Transferred

• If the deleted user was responsible for critical database operations,
  you must transfer the necessary privileges to another appropriate user beforehand.

1. Be Careful in Production Environments

• In production environments, sudden deletion may cause system downtime or errors.
• It is strongly recommended to analyze the impact in advance and create a backup.

Common Errors and Solutions (FAQ)

When managing users in MariaDB, you may encounter various errors.
This section explains the most common errors, their causes, and how to resolve them.

5.1 Beginner FAQ

Q1: What is the difference between MariaDB and MySQL user management?

MariaDB and MySQL generally use the same syntax, but there are some feature differences.

MariaDB supports CREATE ROLE, enabling role-based user management.

Q2: What is the difference between localhost and %?

In MariaDB user management, specifying the hostname is critical.

• localhost: Allows connections only from the local machine
• %: Allows connections from any host (security risk)
• 192.168.1.100: Allows connections only from a specific IP address
• 192.168.1.%: Allows connections only from a specific subnet (192.168.1.*)

5.2 Troubleshooting

Q3: How to Fix “ERROR 1396 (HY000): Operation CREATE USER failed”

Cause: The specified user already exists, so a new user cannot be created.

Solution: Check whether the user exists and delete or modify it if necessary.

1. Check for existing users

SELECT user, host FROM mysql.user WHERE user='yamada';

2. Delete if unnecessary

DROP USER 'yamada'@'localhost';

3. Change password instead

ALTER USER 'yamada'@'localhost' IDENTIFIED BY 'newpassword123';

Q4: Privileges Granted with GRANT Are Not Applied

Cause: The MariaDB privilege cache has not been refreshed.

Solution: Execute FLUSH PRIVILEGES to reload privileges.

FLUSH PRIVILEGES;

Q5: Cannot Connect Remotely Even When Using %

Cause: Remote connections may be restricted in the MariaDB configuration file.

Solution:

1. Edit the configuration file /etc/mysql/my.cnf (or /etc/mysql/mariadb.conf.d/50-server.cnf) and change the value of bind-address to 0.0.0.0.

bind-address = 0.0.0.0

2. Restart MariaDB

sudo systemctl restart mariadb

3. Use % in the GRANT command to allow connections

GRANT ALL PRIVILEGES ON test_db.* TO 'remote_user'@'%' IDENTIFIED BY 'password';

Q6: Getting “Access denied for user ‘user’@’host'” Error

Cause: The user does not have appropriate privileges, or the password is incorrect.

Solution:

1. Check current privileges

SHOW GRANTS FOR 'user'@'host';

2. Grant appropriate privileges

GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'host';

3. Verify you are logging in with the correct password

mysql -u user -p

Conclusion

MariaDB user management is essential for improving database security and operational efficiency.
In this article, we covered user creation, privilege management, user deletion, and common errors with their solutions in detail.

6.1 Recap

① Creating Users in MariaDB

• Create new users using the CREATE USER command
• Restrict access using @'hostname'
• Always check existing users before creating new ones

② Managing Privileges in MariaDB

• Use GRANT to assign the minimum necessary privileges
• Use SHOW GRANTS to verify current privileges
• Use REVOKE to remove unnecessary privileges
• Apply changes with FLUSH PRIVILEGES

③ Deleting Users in MariaDB

• Delete users using the DROP USER command
• Check for active sessions before deletion
• Clear privilege cache using FLUSH PRIVILEGES

④ Common Errors and Solutions

• ERROR 1396 (HY000) → Check for existing users and delete if necessary
• Privilege changes not applied → Run FLUSH PRIVILEGES
• Access denied for user → Verify privileges and password
• Cannot connect remotely with % → Adjust bind-address configuration

6.2 Best Practices for MariaDB User Management

• Grant only the minimum required privileges
• Avoid casually granting ALL PRIVILEGES
• Restrict production users to SELECT, INSERT, UPDATE, DELETE where possible
• Always verify privilege changes
• Use SHOW GRANTS to confirm configurations are correct
• Analyze impact before deleting users
• Be cautious in production and create backups
• Enforce strong security practices
• Apply IP restrictions for remote connections
• Set a strong password for the root user
• Regularly remove unnecessary users and privileges

6.3 Final Thoughts

Through this guide, you have learned everything from the basics to advanced techniques of MariaDB user management.
Use this knowledge to achieve secure and efficient database administration.

We will continue to share helpful MariaDB-related insights, so stay tuned! 🚀