Creating, Managing, and Deleting MariaDB Users: Beginner’s Guide

Introduction

1.1 Do You Want to Know How to Create a User in MariaDB?

Have you ever wanted to create a new user in MariaDB and set appropriate permissions?
For example, you might have encountered issues in situations like the following.

• CREATE USER executed but unable to log in
• GRANT used to grant permissions, but they don’t apply
• Don’t know how to set appropriate permissions in a production environment
• Want to know how to safely delete unnecessary users

If you’re facing such problems, by reading this article, you will be able to understand all the steps from creating users in MariaDB to permission management and deletion methods.

1.2 What This Article Can Solve

This article provides a detailed explanation of the following topics regarding MariaDB user management.

• Methods for Creating and Deleting Users in MariaDB
• Granting and Managing Permissions (Key Points for Appropriate Permission Settings)
• Common Errors and Their Solutions
• Appropriate Operation Methods for Development and Production Environments

These topics are explained with easy-to-understand SQL samples for beginners, so you can learn and practice at the same time.

1.3 Target Readers

This article is written for the following people.

• Beginners: Those learning MariaDB user management for the first time
• Engineers: Those who want to set appropriate permissions in a development environment
• DB Administrators: Those who want to strengthen security in a production environment

To provide information useful for actual operations, even those handling MariaDB for the first time can approach it with confidence.

Creating Users in MariaDB

2.1 Basic User Creation Command

In MariaDB, use the CREATE USER command to create a new user.
You can specify the username and password with the following syntax.

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

For example, to create a user named yamada on localhost and set the password to password123, it would be as follows.

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

Executing this command creates the user yamada for localhost (login possible only on the server).

2.2 Creation Examples by Use Case

In MariaDB user creation, you can specify where connections are allowed in the hostname part. It is important to make appropriate settings for each use case.

In this way, using '%' allows connections from all hosts, but it is not recommended for production environments due to high security risks.
In production environments, it is best to specify IP addresses or configure connections via VPN.

2.3 Precautions When Creating Users

When creating a user, there are several precautions.

(1) Check for Existing Users

Before creating a user, it is important to check if the same user already exists.
Executing the following command lists the currently registered users.

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) Handling When User Already Exists

In MariaDB, you cannot create a user by overwriting an existing one.
Therefore, if the same user already exists, handle it with one of the following methods.

• Change the password of the existing user

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

• Delete the unnecessary user and then create a new one

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

Managing Permissions in MariaDB

3.1 Granting Permissions

Basic Syntax

In MariaDB, you can grant permissions to users using the GRANT command.

GRANT privileges ON database_name.table_name TO 'username'@'host_name';

For example, to grant all permissions on test_db to the yamada user, describe it as follows.

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

The meaning of this command:

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

3.2 List of Commonly Used Permissions

In MariaDB, you can grant permissions such as the following.

If you want to allow only specific operations, it is recommended to grant permissions individually.

3.3 Permission Settings by Use Case

It is important to set appropriate permissions for each situation.

In production environments, be careful not to grant ALL PRIVILEGES lightly.

3.4 Checking Granted Permissions

To check the granted permissions, use the SHOW GRANTS command.

SHOW GRANTS FOR 'username'@'host_name';

For example, to check the permissions for the yamada user:

SHOW GRANTS FOR 'yamada'@'localhost';

Output example:

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

This way, you can check the currently granted permissions.

3.5 Revoking (Removing) Permissions

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

Basic Syntax

REVOKE privileges ON database_name.table_name FROM 'username'@'host_name';

For example, to remove the UPDATE permission from the yamada user:

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

Revoking All Permissions

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

3.6 Troubleshooting When Permission Changes Are Not Reflected

In MariaDB, permission changes may not be applied immediately. In that case, execute the following command.

FLUSH PRIVILEGES;

By executing this command, the permission settings will be reflected immediately.

Deleting Users in MariaDB

4.1 Deleting a User

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

Basic Syntax

DROP USER 'username'@'hostname';

For example, to delete the user yamada from localhost, execute as follows.

DROP USER 'yamada'@'localhost';

Executing this command will completely remove the yamada user from the MariaDB system.

4.2 Things to Check Before Deleting a User

Before deleting a user, there are several important checks.Be careful not to accidentally delete a necessary user.

(1) Confirm if the User to be Deleted Exists

In MariaDB, attempting to delete a non-existent user will result in an error.
Execute the following command to check if the user to be deleted exists.

SELECT user, host FROM mysql.user;

Output example:

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

If the yamada user is included in this list, it can be deleted.

(2) Confirm if the User to be Deleted is Currently Connected

In MariaDB, if the user you are trying to delete currently has a session,
deletion may result in the impact not being reflected immediately.
Execute the following command to check if the user to be deleted is currently active.

SELECT user, host FROM information_schema.processlist;

Output example:

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

In this case, since the yamada user is currently active, you need to terminate that session before deletion.

(3) Force Terminate the Session

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

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

SELECT id, user, host FROM information_schema.processlist;

Output example:

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

2. If the process ID of yamada is 10, force terminate with the following command:

KILL 10;

4.3 Post-Processing After User Deletion

After deleting a user, unnecessary permission information may remain in the database.
In that case, execute FLUSH PRIVILEGES to update the permission information.

FLUSH PRIVILEGES;

By executing this, the information of the deleted user will be completely removed from the database.

4.4 Precautions When Deleting Users

When deleting a user, please note the following points.

1. Deleted users cannot be restored

• Executing DROP USER means the deleted user cannot be undone.
• If deleted by mistake, you need to recreate it using CREATE USER again.

1. Permission handover may be necessary

• If the deleted user was responsible for important database operations,
  you need to transfer the permissions to an appropriate user in advance.

1. Be cautious when deleting in production environment

• In a production environment, sudden deletion can cause system downtime or errors.
• We recommend analyzing the impact in advance and taking backups.

Common Errors and Solutions (FAQ)

MariaDB user management can lead to various errors.
This section explains the causes of frequent errors and their solutions.

5.1 FAQ for Beginners

Q1: What are the differences in user management between MariaDB and MySQL?

MariaDB and MySQL basically use the same syntax, but there are differences in some features.

In MariaDB, CREATE ROLE is supported, enabling role-based user management.

Q2: What is the difference between localhost and %?

Specifying the host name is important in MariaDB user management.

• localhost: Connections only from the local machine
• %: Allows connections from all hosts (security risk)
• 192.168.1.100: Only specific IP address allowed
• 192.168.1.%: Only specific subnet (192.168.1.*) allowed

5.2 Troubleshooting

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

Cause: The specified user already exists, so it cannot be created.Solution: Check the existing user and delete or modify it as necessary.

1. Check existing users

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

2. Delete if unnecessary

DROP USER 'Yamada'@'localhost';

3. If changing the password

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

Q4: Permissions granted with GRANT are not applied

Cause: MariaDB’s privilege cache has not been updated.Solution: Execute FLUSH PRIVILEGES to update the privileges.

FLUSH PRIVILEGES;

Q5: Cannot connect from external even using %

Cause: Remote connections may be restricted in MariaDB’s 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. Allow connection using the GRANT command with %

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

Q6: “Access denied for user ‘user’@’host’” error occurs

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

1. Check privileges

SHOW GRANTS FOR 'user'@'host';

2. Grant correct privileges

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

3. Confirm login with correct password

mysql -u user -p

Summary

MariaDB user management is crucial for enhancing database security and operational efficiency.
In this article, we detailed user creation, permission management, deletion methods, common errors and their solutions.

6.1 Review of the Article

① MariaDB User Creation

• Create a new user using the CREATE USER command
• You can restrict access by specifying @'hostname'
• When creating a user, it is important to check for existing users

② MariaDB Permission Management

• Grant minimal permissions using the GRANT command
• Check current permissions using the SHOW GRANTS command
• Remove unnecessary permissions using the REVOKE command
• Apply changes with FLUSH PRIVILEGES

③ MariaDB User Deletion

• Delete users with the DROP USER command
• Check currently connected sessions before deletion
• Clear unnecessary permission information with FLUSH PRIVILEGES

④ Common Errors and Solutions

• ERROR 1396 (HY000) → Check for existing users and delete if necessary
• Permission changes with GRANT not reflected → Execute FLUSH PRIVILEGES
• Access denied for user → Check if appropriate permissions are granted
• Cannot connect externally even with % → Change the bind-address setting

6.2 Best Practices for MariaDB User Management

• Grant only the minimum necessary permissions
• Do not grant ALL PRIVILEGES lightly
• In production environments, limit to SELECT, INSERT, UPDATE, DELETE only
• Always verify after permission changes
• Check if settings are as intended using the SHOW GRANTS command
• Check impacts in advance when deleting
• Be cautious with deletions in production and take backups
• Implement thorough security measures
• Implement IP restrictions for remote connections
• Set strong passwords for the root user
• Regularly remove unnecessary users and permissions

6.3 In Conclusion

Through this article, you can learn from the basics to advanced MariaDB user management.
Utilize this knowledge to achieve secure and efficient database management.

We will continue to provide useful information on MariaDB, so please refer to it! 🚀