How to Get List of MySQL User Accounts in Linux

The name MySQL needs no introduction. Most database administrators and users are well aware of its robustness, efficiency, and reliability as an effective RDBMS (Relational Database Management System).

The MySQL attribute; reliability, is just a name unless fully exploited and implemented. The reliability of the MySQL security metric is dependent on several administrative aspects. One of them is the management of existing database users and their individual permission levels.

As a database administrator, you want to be able to flexibly preview all registered database users; whether active or inactive and audit their permission privileges so as not to compromise the integrity of your database system.

Prerequisites

• Be comfortable with the Linux command line environment and be a Sudoer user.
• Be running a MySQL or MariaDB server on your Linux operating system.

MySQL’s acquisition by Oracle lets it exist as both Community Edition software and Enterprise Edition software. MariaDB is an open-source fork of MySQL. Whether you are using MariaDB or MySQL, their database commands manual implementation is one and the same.

Install MySQL Database Server in Linux

Depending on your Linux operating system distribution, you can install MySQL or MariaDB from either or the following installation commands:

Install MySQL in Linux

$ sudo apt-get install mysql-server     [On Debian, Ubuntu and Mint]
$ sudo yum install mysql-server         [On RHEL/CentOS/Fedora and Rocky Linux/AlmaLinux]
$ sudo pacman -S mysql-server           [On Arch Linux]
$ sudo zypper install mysql-server      [On OpenSUSE]    

Install MariaDB in Linux

$ sudo apt-get install mariadb-server     [On Debian, Ubuntu and Mint]
$ sudo yum install mariadb-server         [On RHEL/CentOS/Fedora and Rocky Linux/AlmaLinux]
$ sudo pacman -S mariadb-server           [On Arch Linux]
$ sudo zypper install mariadb-server      [On OpenSUSE]    

Secure MySQL Database Server in Linux

Once installed, you need to secure the MySQL server installation by setting the new root password, removing anonymous users, disabling root access, removing the test database, and reloading privilege.

$ sudo mysql_secure_installation

Creating MySQL User Accounts

We need to have several users present in our RDBMS for this article to be effective. The syntax for creating a MySQL user account is as follows:

CREATE USER [IF NOT EXISTS] account_name IDENTIFIED BY 'password';

To create a database user tied to a specific database hostname, the “account_name” portion of the above syntax should be something like:

username@hostname

If you want the database user to freely connect to the MySQL DB server from any remote host/machine, then only use:

username

The implementation of “username” and “hostname” defined by special characters need to be quoted.

'username'@ 'hostname'

Now let us create some DB users:

With the MySQL client tool, access your MySQL server as a root user:

$ sudo mysql -u root -p

Enter the earlier setup root password:

First, let us list the current MySQL user accounts in our database system:

MariaDB [(none)]> select user from mysql.user;    

As you can see, we only have the default root user whose password we set up earlier.

Let us create several DB users, some that can remotely connect (username) to this database server and others that only locally connect (username@hostname).

MariaDB [(none)]> create user LinuxShellTips@localhost identified by 'Sec1pass!';
MariaDB [(none)]> create user LinuxShellTipster identified by 'Min1pass!';
MariaDB [(none)]> create user LinuxShellTips_tutor identified by 'Min1pass!';

Listing MySQL User Accounts

Let us first list all the users present on the database:

MariaDB [(none)]> SELECT user FROM mysql.user;

We might also need to list more DB user-related information like account password expiration status, and linked hostname.

MariaDB [(none)]> SELECT user, host, account_locked, password_expired FROM user;

The users that are not under the host “localhost” can access this database server from anywhere. To get the current DB user, implement the following command:

MariaDB [(none)]> SELECT user();

This is the currently active user. You could alternatively use the command:

MariaDB [(none)]> SELECT current_user();

Quit the MySQL shell and log in as a different user:

MariaDB [(none)]> quit;
$ mysql -u LinuxShellTips -p

Since you can have more than one active database user, to list them, you will need a MySQL command similar to the following:

MariaDB [(none)]>  SELECT user, host, db, command FROM information_schema.processlist;

The key points to note while listing MySQL user account information is the table name (e.g user) that these users are under, and the database name (e.g mysql) that holds that table. See you at the next tutorial.