List users in MySQL database

Query below returns list of users in current database.

Query

select host,
       user as username,
       plugin as auth_type,
       authentication_string,
       password_last_changed,
       password_expired,
       account_locked
from mysql.user
order by user;

Columns

  • host - user host limitation (% means all hosts)
  • username - user name
  • auth_type - authentication plugin used on connection.
  • is_superuser - flag if user have superuser privileges
  • authentication_string - hashed password encoded with plugin
  • password_last_changed - date of last password change
  • password_expired - indicate if password is expired
  • account_locked - indicate if account is locked

Rows

  • One row represents one user in the database
  • Scope of rows: all users in the database
  • Ordered by user name

Sample results

sample results

MySQL Workbench

You can vew database users using MySQL Workbench. In the Navigator select Users and Privileges position (1). List of users will appear in new tab (2).

ssms