How to get list of users in MySQL?

How to get list of users in MySQL?


Posted in : Java Posted on : June 1, 2015 at 12:23 PM Comments : [ 0 ]

In this tutorial you will learn How to get list of users in MySQL? We are showing this using the video tutorial.

Video Tutorial: How to get the list of users in a ySQL server?

If you are working with the MySQL database server and you are trying to find the list of all the users in the MySQL database then check the following command which will enable you to get the list of all the users in your MySQL database.

Make user you have logged in as super user usually as root on the MySQL database server. You can run these commands from any MySQL IDE, but for this tutorial I have preferred to use the mysql command line tool.

On the MySQL database server there is a database named 'mysql' and this the database which contains all the information about the user and permissions. So, we need the access of this database to get the information about the database.

Steps to find the list of all the users of the MySQL database server.

Step 1: Login as root using the mysql command line tool. In my test computer I have used the following command:

mysql -uroot -proot

Above common should open the mysql database interface.

Step 2: Now you can connect to the mysql database using the following command:

connect mysql;

Video Tutorial: How to get list of users in MySQL?

You can find the details of all the steps in the above video tutorial.

Step 3: Get the list of all users

Run the following command to get the list of all the users:

SELECT User FROM mysql.user;

Step 4: List data of user table

Following query can be used:

SELECT * FROM mysql.user;

Step 5: Selecting host, username and password from user table

Use the following query:

select host, user, password from mysql.user;

Step 6: View the structure of user table

Following query displays the user table structure:

desc mysql.user;

mysql> desc mysql.user;
+-----------------------+-----------------------------------+------+-----+------
---+-------+
| Field                 | Type                              | Null | Key | Defau
lt | Extra |
+-----------------------+-----------------------------------+------+-----+------
---+-------+
| Host                  | char(60)                          | NO   | PRI |
   |       |
| User                  | char(16)                          | NO   | PRI |
   |       |
| Password              | char(41)                          | NO   |     |
   |       |
| 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
   |       |
| 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(11) unsigned                  | NO   |     | 0
   |       |
| max_updates           | int(11) unsigned                  | NO   |     | 0
   |       |
| max_connections       | int(11) unsigned                  | NO   |     | 0
   |       |
| max_user_connections  | int(11) unsigned                  | NO   |     | 0
   |       |
+-----------------------+-----------------------------------+------+-----+------
---+-------+
39 rows in set (0.01 sec)

mysql>

Step 7: View grants

Following commands can be used for displaying the grants for all user:

//List all grants
show grants;

//Show the grant of a user
show grants for 'root'@'localhost';

In this tutorial you learned to get the list of all the users and grants in a MySQL database.

View All Programming Video Tutorials on Devmanuals.com.

Go to Topic «PreviousHomeNext»

Your Comment:


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 

 
Tutorial Topics