The World of MySQL & MariaDB

MariaDB User List: View, Manage Permissions, Delete & FAQ

Basic Operations
目次

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 features, user management plays a very important role. By handling user management properly, you can ensure database security and achieve efficient operation. In this article, we focus on how to view the list of users in MariaDB. Specifically, we’ll cover how to retrieve the user list using SQL commands, the differences and advantages of each command, and also provide detailed explanations of related permission settings and cautions. This guide is aimed at both MariaDB beginners and those already managing deployments, offering useful information. Please read through to the end.

2. How to View the User List in MariaDB

There are several ways to view the user list in MariaDB. Each method has its own characteristics, and by choosing the appropriate one for your use case, you can manage users efficiently. Below, we will explain three representative methods in detail.

2.1 How to Use the mysql.user Table

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

Command Example

You can view the user list by executing the following SQL.
SELECT Host, User FROM mysql.user;

Explanation

  • Host: Represents the host name from which the user can connect.
  • User: The user name registered in MariaDB.

Use Cases

  • When you want to see a list of all users.
  • When you want to identify users that can connect from a specific host.

Notes

  • Querying the mysql.user table requires sufficient privileges (typically root user privileges).
  • Depending on the version, using mysql.user may be discouraged, so consult the official documentation.

2.2 How to Use the SHOW GRANTS Command

Using the SHOW GRANTS command, you can view the privileges granted to a specific user.

Command Example

By specifying as follows, you can display the privileges of a particular user.
SHOW GRANTS FOR 'username'@'hostname';

Explanation

  • You can examine each user’s privileges in detail.
  • Example output:
  GRANT ALL PRIVILEGES ON *.* TO 'user1'@'localhost';

Use Cases

  • When you want to verify that a user’s privilege settings are correct.
  • To identify misconfigured privileges when an issue arises.

Notes

  • If you lack sufficient privileges, you cannot execute this command.

2.3 How to Use information_schema.USER_PRIVILEGES

MariaDB includes a system database called information_schema, and by using its USER_PRIVILEGES table you can list the privilege information for all users.

Command Example

SELECT * FROM information_schema.USER_PRIVILEGES;

Explanation

  • This method lets you view each user’s global privileges.
  • The output includes information such as:
  • GRANTEE: The user name and host name.
  • PRIVILEGE_TYPE: The type of privilege granted.
  • IS_GRANTABLE: Whether the privilege can be granted to others.

Use Cases

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

Notes

  • Some privileges may not be visible in this table.

Summary

By using these methods appropriately, you can effectively manage MariaDB’s user list and privilege information. Each approach has its strengths, and selecting the right one based on your purpose is important.

3. Detailed Management of MariaDB Users

In MariaDB, you can view detailed user information and manage privileges. This section provides a detailed explanation of the operations required for user management.

3.1 Checking User Information

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

Command Example

Executing the following SQL displays the details of the specified user.
SHOW CREATE USER 'username'@'hostname';

Explanation

This command is used to verify a user’s authentication method and other attributes. Example output:
CREATE USER 'user1'@'localhost' IDENTIFIED VIA mysql_native_password USING '***';

Use Cases

  • When you want to check a user’s authentication method.
  • When you need to scrutinize user attributes for issues.

Cautions

  • Executing the command requires appropriate privileges (typically root user privileges).

3.2 Setting and Modifying User Privileges

In MariaDB, you can flexibly set privileges for each user. Privilege management uses the GRANT and REVOKE commands.

Granting Privileges

The following SQL grants specific privileges to the specified 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 can assign operation privileges on specific databases or tables.
  • The REVOKE command can cancel privileges that are no longer needed.

Use Cases

  • When setting specific privileges for a new user.
  • When removing unnecessary privileges to strengthen security.

Cautions

  • Misconfiguring privileges can increase security risks, so handle them carefully.

3.3 Deleting Users

Deleting unnecessary users can improve database security. Use the DROP USER command to remove a user.

Command Example

The following SQL deletes the specified user.
DROP USER 'username'@'hostname';

Explanation

  • When a user is deleted, all privileges granted to that user are also removed.
  • There is no output example; on success, a “Query OK” message is displayed.

Use Cases

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

Cautions

  • If the user being deleted is currently in use, unintended errors may occur.
  • User deletion is irreversible, so verify thoroughly before executing.

Summary

User management in MariaDB is essential for enhancing security and operational efficiency. By properly performing tasks such as reviewing user information, setting or modifying privileges, and deleting unnecessary users, you can achieve safe and efficient database operation.

4. Considerations for MariaDB User Management

To properly manage MariaDB users, you need to pay attention to several key points. This section explains how to address insufficient privileges, security considerations, and version‑specific differences.

4.1 How to Address Insufficient Privileges

Checking the user list and privileges in MariaDB? Actually: Checking the user list and privileges in MariaDB requires sufficient privileges. If operations fail due to insufficient privileges, you can try the following solutions.

Error Example

You may see an error like the following:
ERROR 1045 (28000): Access denied for user 'user1'@'localhost'

Solutions

  1. Verify that you are logged in as the correct user
  • If the required privileges (e.g., SELECT privilege) are missing, you cannot query the mysql.user table.
  • As a remedy, try logging in as the root user.
  1. Log in as the root user
  • Logging in as root allows you to perform most operations: bash mysql -u root -p
  1. Reset privileges
  • Grant the necessary privileges: sql GRANT ALL PRIVILEGES ON *.* TO 'username'@'hostname'; FLUSH PRIVILEGES;

Cautions

  • Be careful not to grant more privileges than needed. Excessive rights pose a security risk.

4.2 Security Considerations

In MariaDB user management, security is the top priority. Below are the points you should watch out for.

Remove Unused Users

  • Leaving unused user accounts unattended can make them targets for attacks.
  • Regularly review users and delete unnecessary accounts:
  DROP USER 'username'@'hostname';

Strong Password Policy

  • When creating a user, set a password that is hard to guess:
  CREATE USER 'user1'@'localhost' IDENTIFIED BY 'StrongPassword123!';

Restrict External Access

  • Limit the hosts that can connect to prevent unnecessary external connections:
  • … actually code block remains unchanged:
      CREATE USER 'user1'@'192.168.0.1' IDENTIFIED BY 'password';

Log Monitoring

  • Regularly monitor MariaDB logs (e.g., general_log and error_log) to check for unauthorized access.

4.3 Differences by Version

Because MariaDB’s behavior varies by version, you need to handle it appropriately for the version you’re using.

Deprecation of the mysql.user Table

  • In newer versions, directly querying the mysql.user table may be deprecated. In such cases, use SHOW GRANTS or the information_schema instead.

Authentication Plugins

  • In MariaDB, the default authentication plugin varies by version. Older versions typically use mysql_native_password, while newer versions recommend the more secure caching_sha2_password.

Actions

  • Check your MariaDB version:
  SELECT VERSION();
  • Refer to the official documentation for your version and review the latest best practices.

Summary

Understanding the pitfalls of MariaDB user management helps you strengthen security and achieve efficient operation. Pay attention to insufficient privileges and version differences, and adopt the latest security measures.

5. FAQ (Frequently Asked Questions and Answers)

We have compiled common questions and answers about MariaDB user management below. This section addresses frequent concerns such as permission settings, error handling, and root user issues.

Q1: How do I grant permissions to only a specific user?

Answer

You can use the GRANT command to assign the necessary privileges to a specific user. Below is an example that grants SELECT and INSERT privileges on a particular database:
GRANT SELECT, INSERT ON database_name.* TO 'username'@'hostname';
FLUSH PRIVILEGES;

Key Points

  • Specifying database_name.* applies the privileges to all tables within that database.
  • Running FLUSH PRIVILEGES; ensures the changes take effect.

Q2: What are the causes and solutions when the user list does not display?

Answer

If you cannot view the user list, the following reasons may apply.
  1. Insufficient privileges
  • If the required privileges (e.g., SELECT) are missing, you cannot query the mysql.user table.
  • As a remedy, log in as the root user and try the operation.
  1. Using the appropriate command
  • In newer MariaDB versions, mysql.user may be deprecated. In that case, use the following command: SELECT User, Host FROM information_schema.USER_PRIVILEGES;
  1. Check MariaDB version
  • Since recommended methods can vary by version, first check your current version: SELECT VERSION();

Q3: How to handle a locked root user?

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
  • While in safe mode, log in to MariaDB and reset the root password with: UPDATE mysql.user SET Password=PASSWORD('new_password') WHERE User='root'; FLUSH PRIVILEGES;
  1. Exit safe mode and restart in normal mode
  • Restart MariaDB to return to normal operation.

Caution

Because authentication is bypassed in safe mode, ensure that no unauthorized parties can access the server.

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

Answer

The following issues can cause errors when deleting a user.
  1. The user to be deleted is currently in use
  • If the user you are trying to delete is connected, the deletion will fail.
  • As a remedy, forcefully terminate the user’s connections: SHOW PROCESSLIST; KILL connection_id;
  1. Dependent objects exist
  • If the user has associated objects (e.g., views or stored procedures), you must drop those first.

Q5: How can I save the output of SHOW GRANTS?

Answer

Saving the output of SHOW GRANTS to a file lets you review the privileges later.
  1. Saving to a file
  • Use the mysql client on the command line and redirect the output: bash mysql -u root -p -e "SHOW GRANTS FOR 'username'@'hostname';" > grants_output.txt
  1. Saving as SQL statements
  • Save the output to a file so you can reuse the statements later.

Summary

The FAQ section addressed common questions about MariaDB user management. Using this information can help you troubleshoot issues and improve operational efficiency.

6. Summary

User management in MariaDB is an essential element for improving security and operational efficiency. In this article, we provided a detailed explanation focusing on how to view the user list, as well as permission settings, cautions, and frequently asked questions with their solutions.

Key Points

  1. How to View the User List
  • By leveraging the mysql.user table, SHOW GRANTS, and information_schema.USER_PRIVILEGES, you can effectively view user information.
  1. Detailed User Management
  • We learned how to view user information using SHOW CREATE USER, manage permissions with GRANT and REVOKE, and remove unnecessary users.
  1. Cautions and Security Measures
  • We covered the basics of security, such as solutions for insufficient privileges, setting strong passwords, and removing unnecessary users.
  • It’s also important to know how to handle differences in specifications across versions.
  1. Frequently Asked Questions (FAQ)
  • We presented concrete measures for common operational issues such as permission settings, error handling, and procedures to recover the root user.

Next Steps

  • Use this article as a reference to check the user list and permissions in your own MariaDB environment.
  • As a security measure, we recommend regularly reviewing user and permission management.