I was logged in as root when I ran this command, so that’s the user shown. If you need to see the current logged in user, you can use the USER() function or the CURRENT_USER function. There are a couple of columns you can check in the user table to see if a user is locked or has their password expired: SELECT In both of these examples, there’s a root user and two built-in mysql users.įind Locked and Password Expired Users in MySQL To find a list of usernames only, or a distinct list of users, use the DISTINCT keyword on the user column. Some usernames are repeated, as they have different host values, and possibly different values for other columns. To show a list of all users on the MySQL database, query the er table: SELECT user, host What Else Can You Find Out About MySQL Users?.Find Locked and Password Expired Users in MySQL.In summary, if you need to list the users in a MySQL database, I hope this has been helpful. So for most cases where you want to show MySQL user accounts you'll probably want to limit your MySQL users' query to a few important columns, something like this: select host, user, password from er | max_user_connections | int(11) unsigned | NO | | 0 | | | max_connections | int(11) unsigned | NO | | 0 | | | max_updates | int(11) unsigned | NO | | 0 | | | max_questions | int(11) unsigned | NO | | 0 | | | Alter_routine_priv | enum('N','Y') | NO | | N | | | Create_routine_priv | enum('N','Y') | NO | | N | | | Create_tmp_table_priv | enum('N','Y') | NO | | N | | | References_priv | enum('N','Y') | NO | | N | | | Field | Type | Null | Key | Default | Extra | On my current server this shows the following 37 columns of MySQL user information, as shown here: You can get a listing of the fields in the er table by running this MySQL query: How to reduce the amount of ‘user’ information shown The next section provides more details and information about this second query. Mysql> select host, user, password from er This makes for a lot of output, so as a practical matter you may want to trim down some of the fields to display, something like this: However, note that this query shows all of the columns from the er table. To show the users in a MySQL database, first log into your MySQL server as an administrative user using the mysql command line client, then run this MySQL query: MySQL FAQ: How do I show/list MySQL users, i.e., the user accounts in a MySQL or MariaDB database? Solution
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |