Updated April 11, 2023
Introduction to MariaDB list users
MariaDB provides different functionality to the users, in which that list user is the one of the functionality to list all users from the MariaDB server. MariaDB store all details of users in the system table name as mysql.user table that means when we execute a query against the system table to list all user from the MariaDB server at that time, it acts as a database administrator and fetches the list of created users from mysql.user table. The user table is used to store the user name and the password of the user, user privileges, and other information of the user related to a user.
Syntax
select host, user as user_name, password, password_expired from mysql.user;Explanation
In the above syntax, we use a select clause with different attributes such as host name, user name, password and password expiry date. Here host name is used to indicate the limit of the host. If % this symbol is a parent that means all host, user name means that we need to list out from system table, the password field is used to see the password of a user in encoded format and password expiry date attributes is used to see the expiry date of a particular user.
One row indicates the single user in the database, and if more than one row is displayed, it indicates all users from a database; we can also be called the scope of row.
How to list users in MariaDB using various ways?
Basically, when we need to list users from the system table, we must first log into the MariaDB server as administrator, then we can list the user.
Let’s see different ways to list the user from the MariaDB server as follows.
In a first way, we can list user by using a query as mentioned in syntax; with the help of this syntax, we can easily list all created user on the MariaDB server. In this method, we no need extra privileges to list the user.
We can list distinct users from the system table in a second way, which means we avoided the repetition of user names.
The mysql.user table consists following field as follows.
- Host: This file is used to indicate the host for the user that is localhost.
- User: This field is used to store the user name, for example, root.
- Password: This field is used to store the password of the user.
- Select privileges: This field is used to indicate whether a select privilege is assigned to a user or not in the form of Y or N.
- Insert privileges: This field is used to indicate whether insert privileges are assigned to a user or not in the form of Y or N.
- Update privileges: This field is used to indicate whether update privileges are assigned to a user or not in the form of Y or N.
- Delete privileges: This field is used to indicate whether delete privileges are assigned to a user or not in the form of Y or N.
- Create privileges: This field is used to indicate whether create a privilege is assigned to a user or not in the form of Y or N.
Like above mentioned filed, the mysql.user table also consists other many more filed.
Examples
Let’s see a different example of a list user as follows.
SELECT User FROM mysql.user;Explanation
In the above example, we use a select clause followed by a user keyword for fetching all users from mysql.user table, or we can also say that system table. The final output of the show databases queries we illustrate by using the following snapshot.
Now create a new user by using the following statement as follows.
create user king;Explanation
With the help of the above statement, we created a new user name as king; in this example, we use to create a user statement to create a new user. After that, we can see the created user using the above syntax and the final output of the show databases query we illustrate using the following snapshot.
Example: If we need to show users with a host name at that time, we use the following statement.
select user, host from mysql.user;Explanation
Sometimes we need to see user details with their host name at that we can use the above statement that fetches details from mysql.user table, or we can say system table. The final output of the show databases queries we illustrate by using the following snapshot.
Example: show user along with host name and password as follows.
SELECT user, password, host FROM mysql.user;Explanation
In the above example, we use a select clause with a different attribute such as user name, password of user and host name of the user as shown in the above statement. The final output of the show databases queries we illustrate by using the following snapshot.
See in above all examples shows repetitions of user name, but we need to see the distinct name at that time we can use the following syntax.
Syntax
select user name distinct from system table name;Explanation
In the above syntax, we use a distinct keyword to avoid repetition of user names; here, the system table name means mysql.user table name.
Suppose the user needs to find out user rights at that time; we can use the following statement as follows.
SELECT host, db, user from mysql.db;Explanation
With the help of the above statement, we are able to find the different rights of the user. The final output of the show databases queries we illustrate by using the following snapshot.
Now let’s see how we list the user with their privileges as follows.
show grants for 'king'@'%';Explanation
Suppose the user need to show user assigned privileges at that time we use the above statement. The final output of the show databases queries we illustrate by using the following snapshot.
Conclusion
We hope from this article you have understood about the MariaDB List User. From this article, we have learned the basic syntax of MariaDB List User, and we also see different examples of MariaDB List User. From this article, we learned how and when we use MariaDB List User.
Recommended Articles
We hope that this EDUCBA information on “MariaDB list users” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
