How to List and Manage MariaDB Users: Complete Guide to User Accounts and Privileges

1. Introduction

MariaDB is one of the most widely used database management systems today, known for its ease of use and high performance. Among its many features, user management plays a critical role. By properly managing users, you can ensure database security and achieve efficient operations.

This article focuses on how to check the list of users in MariaDB. Specifically, we will explain how to retrieve a list of users using SQL commands, the differences and advantages of each method, as well as related privilege settings and important considerations.

This guide is written to be helpful not only for MariaDB beginners but also for those currently managing production environments. Be sure to read through to the end.

2. How to Check the User List in MariaDB

There are several ways to check the list of users in MariaDB. Each method has its own characteristics, and choosing the appropriate one based on your purpose allows for efficient user management. Below, we explain three representative methods in detail.

2.1 Using the mysql.user Table

The most basic way to check user information in MariaDB is to directly reference the mysql.user table. This table stores all user account information.

Example Command

You can view the list of users by executing the following SQL:

SELECT Host, User FROM mysql.user;

Explanation

• Host: Represents the hostname from which the user is allowed to connect.
• User: The username registered in MariaDB.

Usage Scenarios

• When you want to view all users in a list.
• When you want to check which users can connect from a specific host.

Important Notes

• You need sufficient privileges (typically root privileges) to reference the mysql.user table.
• In some versions, direct use of mysql.user may not be recommended. Always check the official documentation for your version.

2.2 Using the SHOW GRANTS Command

You can use the SHOW GRANTS command to check the privileges granted to a specific user.

Example Command

Specify the user as shown below to display their privileges:

SHOW GRANTS FOR 'username'@'hostname';

Explanation

• You can review detailed privileges for each user.
• Example output:

GRANT ALL PRIVILEGES ON *.* TO 'user1'@'localhost';

Usage Scenarios

• When you want to verify whether a user’s privilege settings are correct.
• When troubleshooting issues caused by incorrect privilege configuration.

Important Notes

• If you lack sufficient privileges, you will not be able to execute this command.

2.3 Using information_schema.USER_PRIVILEGES

MariaDB includes a system database called information_schema. By querying the USER_PRIVILEGES table within it, you can display privilege information for all users.

Example Command

SELECT * FROM information_schema.USER_PRIVILEGES;

Explanation

• This method allows you to review global privileges for each user.
• The output includes information such as:
• GRANTEE: Username and hostname.
• PRIVILEGE_TYPE: Type of granted privilege.
• IS_GRANTABLE: Whether the privilege can be granted to others.

Usage Scenarios

• When you want to review privileges for multiple users at once.
• When auditing global privilege settings.

Important Notes

• Some privileges may not be fully visible in this table.

Summary

By properly utilizing these methods, you can effectively manage MariaDB user accounts and privilege information. Each approach has its strengths, so selecting the right one based on your specific objective is essential.

3. Detailed Management of MariaDB Users

MariaDB allows you to review detailed user information and manage privileges efficiently. In this section, we explain the essential operations required for proper user management.

3.1 Checking User Information

In MariaDB, you can use the SHOW CREATE USER command to check detailed information about a specific user. This command reproduces the statement used when the user was created.

Example Command

Execute the following SQL to display detailed information about the specified user:

SHOW CREATE USER 'username'@'hostname';

Explanation

This command is used to verify the user’s authentication method and other attributes. Example output:

CREATE USER 'user1'@'localhost' IDENTIFIED VIA mysql_native_password USING '***';

Usage Scenarios

• When you want to confirm a user’s authentication method.
• When reviewing user attributes for potential issues.

Important Notes

• Proper privileges are required to execute this command (typically root privileges).

3.2 Setting and Modifying User Privileges

MariaDB allows flexible privilege configuration for each user. You can use the GRANT and REVOKE commands to manage privileges.

Granting Privileges

The following SQL grants specific privileges to a designated user:

GRANT SELECT, INSERT ON database_name.* TO 'username'@'hostname';

Revoking Privileges

To remove unnecessary privileges, use the REVOKE command:

REVOKE INSERT ON database_name.* FROM 'username'@'hostname';

Explanation

• The GRANT command assigns operation privileges for specific databases or tables.
• The REVOKE command removes privileges that are no longer needed.

Usage Scenarios

• When configuring privileges for a new user.
• When removing unnecessary privileges to enhance security.

Important Notes

• Incorrect privilege configuration can increase security risks, so operate carefully.

3.3 Deleting a User

Removing unnecessary users enhances database security. Use the DROP USER command to delete a user.

Example Command

Execute the following SQL to delete a specified user:

DROP USER 'username'@'hostname';

Explanation

• When a user is deleted, all privileges granted to that user are removed simultaneously.
• If successful, MariaDB displays a “Query OK” message.

Usage Scenarios

• When permanently removing an unnecessary user from the database.
• When cleaning up unused accounts for security purposes.

Important Notes

• If the user is currently connected, unintended errors may occur.
• User deletion cannot be undone, so confirm carefully before executing.

Summary

Proper user management in MariaDB is essential for improving both security and operational efficiency. By reviewing user information, configuring and modifying privileges, and deleting unnecessary users, you can maintain a secure and efficient database environment.

4. Key Considerations for MariaDB User Management

To manage MariaDB users properly, you need to pay attention to several important points. This section explains how to handle insufficient privileges, key security considerations, and differences across versions.

4.1 How to Handle Insufficient Privileges

Checking the user list or privileges in MariaDB requires sufficient permissions. If an operation fails due to insufficient privileges, you can try the following approaches.

Example Error

You may see an error like the following:

ERROR 1045 (28000): Access denied for user 'user1'@'localhost'

How to Fix It

1. Confirm you are logged in as the correct user

• If required privileges (for example, the SELECT privilege) are missing, you may not be able to query the mysql.user table.
• As a workaround, try logging in as the root user and repeat the operation.

1. Log in as the root user

• Logging in as root enables most administrative operations:
  bash mysql -u root -p

1. Reconfigure privileges

• Grant the required privileges if needed:
  sql GRANT ALL PRIVILEGES ON *.* TO 'username'@'hostname'; FLUSH PRIVILEGES;

Important Notes

• Avoid granting more privileges than necessary. Excessive privileges increase security risk.

4.2 Security Best Practices

Security should be the top priority in MariaDB user management. Below are key points to keep in mind.

Remove Unused Users

• Leaving unused user accounts can make them potential targets for attackers.
• Regularly review users and remove unnecessary accounts:

  DROP USER 'username'@'hostname';

Use a Strong Password Policy

• When creating users, set passwords that are difficult to guess:

  CREATE USER 'user1'@'localhost' IDENTIFIED BY 'StrongPassword123!';

Restrict External Access

• Limit which hosts can connect to prevent unnecessary external access:

  CREATE USER 'user1'@'192.168.0.1' IDENTIFIED BY 'password';

Monitor Logs

• Regularly review MariaDB logs (for example, general_log and error_log) to detect suspicious access.

4.3 Differences Across MariaDB Versions

MariaDB behavior and recommended practices can vary by version, so you should adjust your approach accordingly.

Deprecation of Direct mysql.user Access

• In newer versions, directly querying the mysql.user table may be discouraged. In that case, use SHOW GRANTS or information_schema instead.

Authentication Plugins

• Depending on the version, the default authentication plugin may differ. Older versions commonly use mysql_native_password, while newer versions may recommend more secure options such as caching_sha2_password.

Recommended Steps

• Check your MariaDB version:

  SELECT VERSION();

• Refer to the official documentation for your version and follow the latest best practices.

Summary

By understanding these key considerations for MariaDB user management, you can strengthen security and improve operational efficiency. Pay attention to privilege issues and version-specific differences, and adopt up-to-date security measures.

5. FAQ (Frequently Asked Questions)

Below are common questions and answers related to MariaDB user management. This section covers privilege configuration, error handling, and root-user issues that administrators often face.

Q1: How can I grant privileges only to a specific user?

Answer

You can use the GRANT command to grant only the necessary privileges to a specific user. Below is an example that grants SELECT and INSERT privileges on a specific database:

GRANT SELECT, INSERT ON database_name.* TO 'username'@'hostname';
FLUSH PRIVILEGES;

Key Points

• By specifying database_name.*, the privileges apply to all tables within that database.
• Run FLUSH PRIVILEGES; to ensure the changes take effect.

Q2: What are the causes and fixes when the user list does not show up?

Answer

If you cannot view the user list, the following causes are common.

1. Insufficient privileges

• If required privileges (for example, the SELECT privilege) are missing, you may not be able to query the mysql.user table.
• As a workaround, try logging in as the root user and repeat the operation.

1. Using the correct command

• In newer MariaDB versions, mysql.user may be discouraged. In that case, use the following command instead:
  SELECT User, Host FROM information_schema.USER_PRIVILEGES;

1. Check your MariaDB version

• Because recommended methods can differ by version, check your current version first:
  SELECT VERSION();

Q3: What should I do if the root user is locked?

Answer

If the root user is locked and you cannot log in, follow these steps.

1. Start MariaDB in safe mode

• Stop the server and restart it in safe mode:
  bash mysqld_safe --skip-grant-tables &

1. Reset the root password

• Log in to MariaDB while in safe mode, then reset the root password using the following command:
  UPDATE mysql.user SET Password=PASSWORD('new_password') WHERE User='root'; FLUSH PRIVILEGES;

1. Exit safe mode and restart normally

• Restart MariaDB and return to normal operations.

Important Notes

Because authentication is bypassed in safe mode, make sure no third party can access the server during this time.

Q4: I got an error when deleting a user. What should I do?

Answer

If you encounter an error when deleting a user, the following issues are common causes.

1. The user you tried to delete is currently in use

• If the user is currently connected, the deletion may fail.
• As a workaround, terminate the user’s connection forcibly:
  SHOW PROCESSLIST; KILL connection_id;

1. Dependent objects exist

• If there are objects associated with the user (for example, views or stored procedures), you may need to remove those first.

Q5: How can I save SHOW GRANTS output?

Answer

You can save the output of SHOW GRANTS to a file for later review.

1. Save to a file

• Use the mysql client in the command line and redirect the output:
  bash mysql -u root -p -e "SHOW GRANTS FOR 'username'@'hostname';" > grants_output.txt

1. Save as SQL statements

• Save the output to a text file so you can reuse it later.

Summary

In this FAQ section, we addressed common questions that frequently come up in MariaDB user management. Use these answers to troubleshoot issues and improve your operational efficiency.