MySQL – Show Users
Table of content
- The MySQL SHOW Users
- Show Current User
- Show Currently Logged in Users
- Show Users Using a Client Program
”;
As you might have already known, MySQL is a multi-user database that allows multiple users to work on it simultaneously. But have you ever wondered who these users might be?
MySQL provides an account to each user that is authenticated with a username and a password. And details of these accounts are stored in the “user” table in the database. This table contains details like username, the host this user is connected from, and other privileges the said user has etc.
The MySQL SHOW Users
MySQL does not provide any direct command to show (list out) all the users. However, the details of these user accounts is stored in the “user” table within the database. Hence, we can use the SELECT statement to list out the contents of this table.
There is no limit for how many users can connect to a MySQL database but the default user is always “root”. And it does not have any password, unless it is set manually.
Syntax
Following is the syntax to show users in a MySQL database −
SELECT * FROM mysql.user;
Example
To see the structure of this “user” table, use the following query with the DESC command −
DESC mysql.user;
Now, in this example, we are listing out all the users in the MySQL database local to a system −
SELECT Host, User, User_attributes, account_locked FROM mysql.user;
Output
The output obtained is as shown below −
Host | User | User_attr | account_locked |
---|---|---|---|
localhost | mysql.infoschema | NULL | Y |
localhost | mysql.session | NULL | Y |
localhost | mysql.sys | NULL | Y |
localhost | root | NULL | N |
The actual user table contains a lot more columns/fields than what is displayed in this chapter. Here, however, only some information is displayed for simplicity.
Note that list of these users are local to a system. Hence, not all systems would give the same output (apart from the default users).
Show Current User
Not only the list of all users, MySQL also has a provision to see the current user. This is done with the help of user() or current_user() functions.
Syntax
Following is the syntax to show the current user −
SELECT user(); or SELECT current_user();
Example
Using the following query, let us display the username of the currently logged in user in MySQL database using the user() function −
SELECT user();
Output
Following is the output obtained −
user() |
---|
root@localhost |
Example
In here, we are using the current_user() function to show the current user −
SELECT current_user();
Output
The output obtained is as follows −
current_user() |
---|
root@localhost |
Show Currently Logged in Users
The difference between current users and currently logged in users is that, current user is the user that is executing the queries; whereas, currently logged in user list includes all the active users that are connected to the MySQL server at the moment.
This information can be extracted from the “information_schema.processlist” table using the SELECT statement.
Example
In the following query, we are retrieving the information of all the currently logged in users −
DESC information_schema.processlist;
Output
Following is the output of the above code −
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
ID | bigint unsigned | NO | |||
USER | varchar(32) | NO | |||
HOST | varchar(261) | NO | |||
DB | varchar(64) | YES | |||
COMMAND | varchar(16) | NO | |||
TIME | int | NO | |||
STATE | varchar(64) | YES | |||
INFO | varchar(65535) | YES |
Example
In here, we are retrieving information of current users, host, database, and command from the information_schema −
SELECT user, host, db, command FROM information_schema.processlist;
Output
After executing the above code, we get the following output −
user | host | db | command |
---|---|---|---|
root | localhost:49958 | customers | Query |
event_scheduler | localhost | NULL | Daemon |
Show Users Using a Client Program
We can also display information about the MySQL users using a client program.
Syntax
Following are the syntaxes to display information regarding MySQL users in various programming languages −
To display info regarding user(s) in a MySQL database using a PHP program, we need to execute the SELECT USER statement using the query() function of the PHP mysqli library as −
$sql = "SELECT USER FROM MYSQL.user"; $mysqli->query($sql);
To display the user information We need to execute the SELECT * FROM statement using the query() function of mysql2 library using JavaScript (NodeJS) program as follows −
sql= "SELECT * FROM mysql.user"; con.query(sql, function (err, result) { if (err) throw err; console.log(result); });
Similarly in Java we can use the JDBC executeQuery() function to execute the SQL query that displays the user info as follows −
String sql = "SELECT USER FROM MYSQL.USER"; statement.executeQuery(sql);
The MySQL Connector/Python provides a function named execute() to execute an SQL query in the MySQL database.To show user info in MySQL database, we need to execute the SELECT USER statement using this function as −
sql = "SELECT user, host FROM mysql.user"; cursorObj.execute(sql);
Example
Following are the programs −
$dbhost = ''localhost''; $dbuser = ''root''; $dbpass = ''password''; $mysqli = new mysqli($dbhost, $dbuser, $dbpass); if($mysqli->connect_errno ) { printf("Connect failed: %s
", $mysqli->connect_error); exit(); } //printf(''Connected successfully.
''); $sql = "SELECT USER FROM MYSQL.user"; if($result = $mysqli->query($sql)){ printf("User found successfully...!"); printf("Users list are: "); while($row = mysqli_fetch_array($result)){ print_r($row); } } if($mysqli->error){ printf("Failed..!" , $mysqli->error); } $mysqli->close();
Output
The output obtained is as follows −
User found successfully...!Users list are: Array ( [0] => Vivek Verma [USER] => Vivek Verma ) Array ( [0] => Revathi [USER] => Revathi ) Array ( [0] => Sarika [USER] => Sarika ) Array ( [0] => mysql.infoschema [USER] => mysql.infoschema ) Array ( [0] => mysql.session [USER] => mysql.session ) Array ( [0] => mysql.sys [USER] => mysql.sys ) Array ( [0] => root [USER] => root )
var mysql = require(''mysql2''); var con = mysql.createConnection({ host: "localhost", user: "root", password: "Nr5a0204@123" }); //Connecting to MySQL con.connect(function (err) { if (err) throw err; console.log("Connected!"); console.log("--------------------------"); //Creating Users sql = "CREATE USER TestUser1@localhost IDENTIFIED BY ''password1'';" con.query(sql); sql = "CREATE USER TestUser2@localhost IDENTIFIED BY ''password2'';" con.query(sql); sql = "CREATE USER TestUser3@localhost IDENTIFIED BY ''password3'';" con.query(sql); sql = "CREATE USER TestUser4@localhost IDENTIFIED BY ''password4'';" con.query(sql); //Listing the users sql = "SELECT USER FROM mysql.user;" con.query(sql, function(err, result){ if (err) throw err console.log("**List of Users:**") console.log(result) }); });
Output
The output produced is as follows −
Connected! -------------------------- **List of Users:** [ { USER: ''TestUser1'' }, { USER: ''TestUser2'' }, { USER: ''TestUser3'' }, { USER: ''TestUser4'' }, { USER: ''mysql.infoschema'' }, { USER: ''mysql.session'' }, { USER: ''mysql.sys'' }, { USER: ''root'' }, { USER: ''sample'' } ]
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class ShowUsers { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/TUTORIALS"; String user = "root"; String password = "password"; ResultSet rs; try { Class.forName("com.mysql.cj.jdbc.Driver"); Connection con = DriverManager.getConnection(url, user, password); Statement st = con.createStatement(); //System.out.println("Database connected successfully...!"); String sql = "CREATE USER ''Vivek''@''localhost'' IDENTIFIED WITH mysql_native_password BY ''password''"; st.execute(sql); System.out.println("User ''Vivek'' created successfully...!"); String sql1 = "SELECT USER FROM MYSQL.user"; rs = st.executeQuery(sql1); System.out.println("Users: "); while(rs.next()) { String users = rs.getNString(1); System.out.println(users); } }catch(Exception e) { e.printStackTrace(); } } }
Output
The output obtained is as shown below −
User ''Vivek'' created successfully...! Users: Sarika Vivek Verma Revathi Sarika Vivek mysql.infoschema mysql.session mysql.sys root
import mysql.connector # creating the connection object connection = mysql.connector.connect( host=''localhost'', user=''root'', password=''password'', ) # Create a cursor object for the connection cursorObj = connection.cursor() cursorObj.execute("SELECT user, host FROM mysql.user") users = cursorObj.fetchall() print("Existing users are:") for user, host in users: print(f"User: {user}, Host: {host}") cursorObj.close() connection.close()
Output
Following is the output of the above code −
Existing users are: User: UserNew, Host: localhost User: mysql.infoschema, Host: localhost User: mysql.session, Host: localhost User: mysql.sys, Host: localhost User: newUser, Host: localhost User: root, Host: localhost
”;