Show mysql users linux

How can I get a list of user accounts using the command line in MySQL?

I’m using the MySQL command-line utility and can navigate through a database. Now I need to see a list of user accounts. How can I do this? I’m using MySQL version 5.4.1.

@Mustapha Why the title change? This answers here are SQL you can run from anywhere, not just a command line. And what does that tag add to the question?

The purpose of my edit was to maintain consistency between the title and the description, but I think you have a good point. Make your edit Mr. @Rup

16 Answers 16

SELECT User FROM mysql.user; 

Which will output a table like this:

As Matthew Scharley points out in the comments on this answer, you can group by the User column if you’d only like to see unique usernames.

I think it may be necessary to group on User too, to only get unique user values, since there’s a seperate row for each user @ host entry.

@barrycarter DELETE FROM mysql.user; better have WHERE user=’someuser’ and host=’somehost’; If you do DELETE FROM mysql.user; , all users are gone. Logins after the next mysql restart or FLUSH PRIVILEGES; eliminate users from memory. Here is an example of one of my posts on doing DELETE FROM mysql.user responsibly : dba.stackexchange.com/questions/4614/…

I find this format the most useful as it includes the host field which is important in MySQL to distinguish between user records.

select User,Host from mysql.user; 

@Packer the host comes into play when you are connecting from a different server. It is possible to grant different access to ‘packer’@’example.com’ and ‘packer’@’google.com’

A user account comprises the username and the host level access.

Therefore, this is the query that gives all user accounts

SELECT CONCAT(QUOTE(user),'@',QUOTE(host)) UserAccount FROM mysql.user; 

This is basically the same as spkane’s answer. What is the benefit of concatenating the user and host columns?

One example: the user@host format is used for setting passwords. Omitting the host from the SET PASSWORD command produces an error. SET PASSWORD FOR wordpressuser = PASSWORD(‘. ‘); produces the error ERROR 1133 (42000): Can’t find any matching row in the user table . Include the host and it works. SET PASSWORD FOR wordpressuser@localhost = PASSWORD(‘. ‘); produces Query OK, 0 rows affected (0.00 sec) .

Best answer, whatever the naysayers may complain about. Only thing i’d change were appending an ORDER BY user to it.

Источник

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:

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

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

Now let us create some DB users:

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

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 ([email protected]).

MariaDB [(none)]> create user [email protected] 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.

Источник

List all users in MySQL database server

This article describes how to list all users in a MySQL database server in MySQL.

It is a common operation for a database administrator or database operation and maintenance personnel to view all users in the current database server.

In the MySQL database server, the mysql database stores some basic information, including all users. You can query all users from the user table in the mysql database.

List All Users

To list all users of the MySQL server, you must be logged in to the MySQL database server as an administrator. In the mysql client, we can do these as follows:

Enter the password for the root account and press Enter :

Use the following SELECT statement to query all users from the user table in the mysql database:

SELECT user, host FROM mysql.user; 

Here’s the output from the MySQL server that I’m connecting to:

+------------------+-----------+ | user | host | +------------------+-----------+ | root | % | | sqliz | % | | test_role1 | % | | test_role2 | % | | test_user1_new | % | | test_user2_new | % | | test_user3_new | % | | testuser | % | | mysql.infoschema | localhost | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +------------------+-----------+

There are many columns in the mysql.user table, which store various information about the user, such as password, password expiration time, whether it is locked or not, and various privileges.

Here, we only output two columns: user and host , where the user column holds the username of the user account, and the host column holds the host (which is usually the hostname or IP address) that the user account is allowed to log in from.

To get more information about the user table, you can output all columns of the user tables:

Here is the complete output:

+--------------------------+-----------------------------------+------+-----+-----------------------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------------+-----------------------------------+------+-----+-----------------------+-------+ | Host | char(255) | NO | PRI | | | | User | char(32) | NO | PRI | | | | Select_priv | enum('N','Y') | NO | | N | | | Insert_priv | enum('N','Y') | NO | | N | | | Update_priv | enum('N','Y') | NO | | N | | | Delete_priv | enum('N','Y') | NO | | N | | | Create_priv | enum('N','Y') | NO | | N | | | Drop_priv | enum('N','Y') | NO | | N | | | Reload_priv | enum('N','Y') | NO | | N | | | Shutdown_priv | enum('N','Y') | NO | | N | | | Process_priv | enum('N','Y') | NO | | N | | | File_priv | enum('N','Y') | NO | | N | | | Grant_priv | enum('N','Y') | NO | | N | | | References_priv | enum('N','Y') | NO | | N | | | Index_priv | enum('N','Y') | NO | | N | | | Alter_priv | enum('N','Y') | NO | | N | | | Show_db_priv | enum('N','Y') | NO | | N | | | Super_priv | enum('N','Y') | NO | | N | | | Create_tmp_table_priv | enum('N','Y') | NO | | N | | | Lock_tables_priv | enum('N','Y') | NO | | N | | | Execute_priv | enum('N','Y') | NO | | N | | | Repl_slave_priv | enum('N','Y') | NO | | N | | | Repl_client_priv | enum('N','Y') | NO | | N | | | Create_view_priv | enum('N','Y') | NO | | N | | | Show_view_priv | enum('N','Y') | NO | | N | | | Create_routine_priv | enum('N','Y') | NO | | N | | | Alter_routine_priv | enum('N','Y') | NO | | N | | | Create_user_priv | enum('N','Y') | NO | | N | | | Event_priv | enum('N','Y') | NO | | N | | | Trigger_priv | enum('N','Y') | NO | | N | | | Create_tablespace_priv | enum('N','Y') | NO | | N | | | ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | | | ssl_cipher | blob | NO | | NULL | | | x509_issuer | blob | NO | | NULL | | | x509_subject | blob | NO | | NULL | | | max_questions | int unsigned | NO | | 0 | | | max_updates | int unsigned | NO | | 0 | | | max_connections | int unsigned | NO | | 0 | | | max_user_connections | int unsigned | NO | | 0 | | | plugin | char(64) | NO | | caching_sha2_password | | | authentication_string | text | YES | | NULL | | | password_expired | enum('N','Y') | NO | | N | | | password_last_changed | timestamp | YES | | NULL | | | password_lifetime | smallint unsigned | YES | | NULL | | | account_locked | enum('N','Y') | NO | | N | | | Create_role_priv | enum('N','Y') | NO | | N | | | Drop_role_priv | enum('N','Y') | NO | | N | | | Password_reuse_history | smallint unsigned | YES | | NULL | | | Password_reuse_time | smallint unsigned | YES | | NULL | | | Password_require_current | enum('N','Y') | YES | | NULL | | | User_attributes | json | YES | | NULL | | +--------------------------+-----------------------------------+------+-----+-----------------------+-------+

Show Current User

To get information about the current user’s credentials, you can use the current_user() function as follows:

Источник