EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 600+ Courses All in One Bundle
  • Login
Home Software Development Software Development Tutorials MariaDB Tutorial MariaDB list users
Secondary Sidebar
MariaDB Tutorial
  • MariaDB
    • MariaDB Versions
    • MariaDB? list users
    • MariaDB Commands
    • MariaDB odbc
    • MariaDB Workbench
    • MariaDB for windows
    • MariaDB Server
    • MariaDB? Data Types
    • MariaDB? boolean
    • MariaDB phpMyAdmin
    • MariaDB Mysqldump
    • MariaDB Java Connector
    • MariaDB insert
    • MariaDB UPDATE
    • MariaDB? rename column
    • MariaDB AUTO_INCREMENT
    • MariaDB Timezone
    • MariaDB GROUP_CONCAT
    • MariaDB wait_timeout
    • MariaDB MaxScale
    • MariaDB? with
    • MariaDB GUI
    • MariaDB? create?table
    • MariaDB? SHOW TABLES
    • MariaDB alter table
    • MariaDB List Tables
    • MariaDB JSON Functions
    • MariaDB Foreign Key
    • MariaDB? trigger
    • MariaDB Grant All Privileges
    • MariaDB Select Database
    • MariaDB? create database
    • MariaDB Delete Database
    • MariaDB Join
    • MariaDB JSON
    • MariaDB? show databases
    • MariaDB List Databases
    • MariaDB Functions
    • MariaDB? TIMESTAMP
    • MariaDB create user
    • MariaDB add user
    • MariaDB Max Connections
    • MariaDB show users
    • MariaDB Delete User
    • MariaDB? change user password
    • MariaDB? change root password
    • MariaDB reset root password
    • MariaDB IF
    • MariaDB bind-address
    • MariaDB Transaction
    • MariaDB Cluster
    • MariaDB Logs
    • MariaDB Encryption
    • MariaDB? backup
    • MariaDB Replication
    • MariaDB max_allowed_packet
    • MariaDB? performance tuning
    • MariaDB export database
    • MariaDB? import SQL

MariaDB list users

MariaDB list users

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

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

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.

  1. Host: This file is used to indicate the host for the user that is localhost.
  2. User: This field is used to store the user name, for example, root.
  3. Password: This field is used to store the password of the user.
  4. 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.
  5. 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.
  6. 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.
  7. 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.
  8. 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.

MariaDB list users output 1

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.

MariaDB list users output 2

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.

output 3

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.

output 4

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.

output 5

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.

output 6

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

This is a guide to MariaDB list users. Here we discuss the basic syntax of MariaDB List User and different examples of List User. You may also have a look at the following articles to learn more –

  1. MariaDB Commands
  2. MariaDB AUTO_INCREMENT
  3. MariaDB UPDATE
  4. MariaDB MaxScale
Popular Course in this category
SQL Training Program (10 Courses, 8+ Projects)
  10 Online Courses |  8 Hands-on Projects |  80+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Java Tutorials
  • Python Tutorials
  • All Tutorials
Certification Courses
  • All Courses
  • Software Development Course - All in One Bundle
  • Become a Python Developer
  • Java Course
  • Become a Selenium Automation Tester
  • Become an IoT Developer
  • ASP.NET Course
  • VB.NET Course
  • PHP Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more