MySQL - Show Users

MySQL: Managing and Auditing Users

MySQL is a multi-user system, meaning it allows many different users to connect and interact with databases simultaneously. Proper user management is a critical aspect of database administration, security, and auditing. This chapter covers how to list existing users and view their permissions.

Listing All User Accounts

All user account information is stored in the user table within the special mysql system database. You can query this table directly using a SELECT statement to see all defined users. As a best practice, you should only select the columns you need.

Security Note: Access to the mysql database should be highly restricted. Typically, only administrative accounts like root can read from these tables.

Syntax for MySQL 8.x

The columns in the mysql.user table have changed in modern MySQL versions. The following query works for MySQL 8.0 and later:

SELECT
    User,
    Host,
    account_locked,
    plugin AS authentication_plugin
FROM mysql.user;

Example Output

The output will list each user and the host they can connect from. The localhost host means the user can only connect from the same machine as the MySQL server.

User	Host	account_locked	authentication_plugin
mysql.infoschema	localhost	Y	caching_sha2_password
mysql.session	localhost	Y	caching_sha2_password
mysql.sys	localhost	Y	caching_sha2_password
root	localhost	N	caching_sha2_password
webapp	%	N	caching_sha2_password

The ’%’ wildcard in the Host column means the user can connect from any host, which should be used with caution.

Viewing a Specific User’s Permissions

Just listing users is not enough; you often need to know what a user is allowed to do. The SHOW GRANTS command is the correct and safest way to view a user’s permissions (privileges).

SHOW GRANTS FOR 'webapp'@'%';

-- Example Output:
-- GRANT SELECT, INSERT, UPDATE ON `production_db`.* TO `webapp`@`%`

Identifying the Current User

MySQL provides functions to identify the current connection’s user. There is a subtle but important difference between them:

• USER(): Returns the user name and host you specified when you connected to the server.
• CURRENT_USER(): Returns the user name and host that you are currently authenticated as. This can be different from USER() in cases involving proxies or stored procedures with a DEFINER clause.

SELECT USER(), CURRENT_USER();

USER()	CURRENT_USER()
root@localhost	root@localhost

Monitoring Active Connections

To see who is currently connected to the server and what they are doing, you can query the processlist table in the information_schema database. This is very useful for real-time monitoring and troubleshooting.

SELECT
    ID,
    USER,
    HOST,
    DB,
    COMMAND,
    TIME,
    STATE,
    INFO AS `QUERY`
FROM information_schema.processlist;

ID	USER	HOST	DB	COMMAND	TIME	STATE	QUERY
5	event_scheduler	localhost	NULL	Daemon	3600	Waiting for next activation	NULL
9	root	localhost	test_db	Query	0	starting	SELECT … FROM information_schema.processlist

Listing Users Using a Client Program

The following examples show how to securely query the mysql.user table from different programming languages. Since these are read-only operations with no user input, the risk of SQL injection is low, but using a consistent, modern coding style is still important.

Node.js (with mysql2)

const mysql = require('mysql2/promise');

async function listUsers() {
  let connection;
  try {
    connection = await mysql.createConnection({ host: 'localhost', user: 'root', password: 'my-secret-pw' });
    const [rows, fields] = await connection.execute("SELECT User, Host FROM mysql.user");
    console.log("MySQL Users:");
    rows.forEach(row => {
      console.log(`- User: ${row.User}, Host: ${row.Host}`);
    });
  } catch (error) {
    console.error('Error listing users:', error);
  } finally {
    if (connection) await connection.end();
  }
}

listUsers();

Python (with mysql-connector-python)

import mysql.connector

def list_users():
    try:
        conn = mysql.connector.connect(user='root', password='my-secret-pw', host='127.0.0.1')
        cursor = conn.cursor(dictionary=True)
        cursor.execute("SELECT User, Host FROM mysql.user")

        print("MySQL Users:")
        for row in cursor.fetchall():
            print(f"- User: {row['User']}, Host: {row['Host']}")

    except mysql.connector.Error as err:
        print(f"Error: {err}")
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()

if __name__ == "__main__":
    list_users()