How to list the users in a MySQL/MariaDB database and Manage Permissions

User management involves granting database access, setting user permissions, and monitoring activities. This process, traditionally handled via command-line utilities, includes creating, modifying, and deleting accounts, and configuring user privileges. Although command-line remains relevant, the advent of graphical user interface (GUI) tools has simplified user management.

dbForge Studio for MySQL, a highly functional GUI-based Integrated Development Environment (IDE), significantly improves database user management compared to command-line tools due to a more intuitive and user-friendly approach. The user management is provided by Security Manager of the Studio for MySQL. We are going to utilize this feature to illustrate the task performance and compare the usage of the standard SQL commands vs. applying the GUI-based solution.

To begin, let's explore the SQL commands essential for routine tasks like observing database users and their privileges, and modifying those privileges.

To see a list of all database users, we query the mysql.user table, which holds the necessary details. The command for this task is:

SELECT User, Host 
FROM user;

The output delivers the list of database users and their hosts.

MySQL & MariaDB have a built-in function allowing you to view the username and the host used to log in. The command is:

SELECT user();

The output provides the information about the current user who is running that query.

If you possess admin rights, you can observe the list of currently logged-in database users in MySQL or MariaDB by querying the information_schema.processlist system table.

SELECT user, host, db, command 
FROM 
    information_schema.processlist;

The query output provides the details, including the users currently connected, their ongoing actions, the hosts they are accessing from, the databases they use, the commands they are executing, and more.

The mysql.user table stores the user information, including the details about the password expiration states and account lock status. You can query this table as shown below to retrieve the necessary piece of data:

SELECT 
    User, 
    Host, 
    Account_locked, 
    password_expired 
FROM 
    mysql.user;

View the query output. Y indicates that the account is locked/password has expired, while N signifies that the user account is not locked or that the password has not expired.

User privileges in MySQL and MariaDB allow the database administrators to manage and control access to the MySQL database server. These privileges define the range of actions a user can undertake, such as reading or modifying data or managing other users.

To show the current user's privileges, a standard command can be used:

SHOW GRANTS FOR user_account; 

Let us find out the privileges assigned to the user maria:

SHOW GRANTS FOR maria;

The output displays the privileges of that user account.

The MySQL/MariaDB database administrator can modify these privileges. This includes granting access to entire databases or specific objects and adding or revoking privileges. Next, we'll explore how these adjustments can be made.

When privileges are not set, a newly created user account can connect to the MySQL instance but cannot access any data or perform any actions. Typically, database administrators specify the user's privileges when creating the account. This can involve manually defining specific privileges for each database or particular database objects or applying a set of privileges all at once through a user role.

To allow the user some access permissions for the MySQL/MariaDB database, we use the GRANT statement. The command syntax is as follows:

GRANT privilege 
ON privilege_level 
TO account_name;

The privilege_level parameter can apply to an entire database or a specific table. When granting access to the entire database, you should specify it as db.*, thereby covering all tables within that database.

GRANT SELECT, UPDATE, INSERT, DELETE ON sakila.* TO jane; 

In this demonstration, we're using dbForge Studio for MySQL to showcase the execution of the command. As you can observe in the Security Manager's user management module, the permissions have been successfully configured.

Note: As user privileges are fundamental in ensuring database security and maintaining data integrity, it is vital to grant them judiciously. The guiding principle should be to allocate only the necessary rights for a user to perform their tasks, and nothing beyond that.

User privileges can be granted or revoked. If a user no longer needs specific permissions, you can remove them using the REVOKE statement. While the command syntax is generally similar to the GRANT statement discussed earlier, there are some differences:

REVOKE privilege 
ON privilege_level 
FROM account_name;

To revoke previously granted permissions from the user named jane, you can use the following command:

REVOKE SELECT, UPDATE, INSERT, DELETE ON sakila.* FROM jane;

If the user does not have such privileges, the command will not affect the account in any way.

We recommend watching the video tutorial that explains how to manage user privileges in dbForge Studio for MySQL. This tutorial covers the process of granting and revoking privileges, either individually or through user roles, utilizing the GUI capabilities of the Security Manager feature.

GUI tools offer significant advantages in adding, configuring database user accounts, and managing user privileges as they are more intuitive and user-friendly. They provide a visual representation of user account settings, simplifying the understanding and control of configurations. Additionally, GUI tools offer immediate visual feedback, aiding users in comprehending the impact of their actions.

Using tools like dbForge Studio for MySQL eliminates the need to memorize specific commands, allowing users to replace complex SQL statement chains with just a few clicks when creating and configuring database users. In this guide, we will delve into the process of adding a new database user using the Security Manager section of dbForge Studio.

Open the Security Manager by choosing it from the Database menu:

You will see the area with already present and configured user accounts. To add a new database user, click Create User.

• Start by completing the user details in the following fields:
• Name
• Host
• Password
• Confirm Password
• Optionally, you can establish limits for server resources.
• Proceed by assigning the required privileges to the user, which can be done on the Global Privileges and Object Privileges tabs. The process of granting or revoking privileges is straightforward; simply select the privileges you wish to grant or remove.
• Finally, click Save to confirm adding a new user.

Note: In dbForge Studio for MySQL, you have the option to create a new user based on an existing one. To do this, simply follow these steps:

• Right-click on the user you want to duplicate in the Security Manager and select Duplicate Object from the menu.
• All of the user's data, including their privileges and limitations, will be automatically copied to the editor. Edit any necessary information, such as the username and password.
• Once you've made the required changes, click Save to confirm the creation of the new user.

In MySQL and MariDB, the DROP USER statement is employed for deleting one or more database user accounts along with all associated privileges. Note that dropping a user won't impact any databases or objects created by those users.

To remove a user, use the following command:

DROP USER user_account;

Typically, the user account name includes the host name. If the host name is omitted, it is assumed to be % (meaning any host).

In dbForge Studio for MySQL, you can delete any user visually:

• Open the Security Manager and right-click the account you want to delete
• Select Delete from the menu
• Also, you can select the user from the list and click the Delete button above

dbForge Studio for MySQL is the ultimate solution for database administration that makes it easy to create, edit, and delete user accounts, grant or revoke privileges at both global and object levels, and take full control of database access. In practice, it means that performing these tasks through the graphical interface, rather than typing out code, significantly enhances the speed and simplicity of user management.