The Series of MySQL -2- MySQL User Management - EP 03_03
HI Hi, Greetings!
Happy January
Hi Everyone,
Today, I would like to share with you an article about the third episode of Series of MySQL (MySQL User Management), and the content is given below.
a. MySQL Create User
b. MySQL Drop User
c. MySQL Show Users
d. MySQL User Password Change
MySQL Show User
You may need to administer a database in MySQL on occasion. In such a situation, we'll need access to a database that has a list of all user accounts. Most of the time, we presume that the database server has a SHOW USERS command, similar to SHOW DATABASES, SHOW TABLES, and so on, for presenting a list of all users. Unfortunately, there is no SHOW USERS command in the MySQL database to display a list of all users on the MySQL server. To see a list of all users on the database server, run the following query:
mysql> Select user from mysql.user;
Following the successful execution of the above command, we will retrieve user data from the MySQL database server's user table.
Let's take a look at how we can use this query. To begin, we must use the mysql client tool to connect to the MySQL server and log in as an administrator to the server database. Run the following command:
> mysql -u root -p
Enter password: *********
mysql> use mysql;
Database changed
mysql> SELECT user FROM user;
We'll see the five users in our local database in the output that follows::
Execute the following command to see more information about the user table:
mysql> DESC user;
It will print the following result, which contains a list of all the columns available in the mysql.user database:
Execute the following query to acquire the desired information, such as hostname, password expiration status, and account locking:
mysql> SELECT user, host, account_locked, password_expired FROM user;
It will produce the following output after a successful execution:
Display current user
The user() or current user() functions can be used to acquire information about the current user, as seen below:
mysql> Select user();
or,
mysql> Select current_user();
We will obtain the following output after running the aforementioned command:
Display current logged user
Using the following query on the MySQL server, we can determine who is currently logged in to the database server:
mysql> SELECT user, host, db, command FROM information_schema.processes;
The output of the command above is as follows:
We can see from this output that there are presently four users logged into the database, one of them is running a query while the others are in the Sleep states.
You are welcome to like and leave feedback in the comment area.
Link:
EP 03_01:
The Series of MySQL -3- MySQL User Management - EP 03_01
EP 03_02:
The Series of MySQL -3- MySQL User Management - EP 03_02
EP 03_04:
The Series of MySQL -2- MySQL User Management - EP 03_04
Source:
· https://www.w3schools.com/mySQl/default.asp
· https://www.mysqltutorial.org/mysql-basics/
M M Zaheer Hussain
Stay Safe!