How to MariaDB/MySQL Show Users and GRANTS Privileges

How to MariaDB/MySQL Show Users and GRANTS Privileges. In this post, we will introduce MariaDB, its features, and main advantages. In addition, we explain how to show users and grant privileges in MariaDB/MySQL.

Shall we start with How to MariaDB/MySQL Show Users and GRANTS Privileges.

What Is MariaDB?

For a fast moving, open source database landscape, MariaDB is a good option. Branched from MySQL in 2009, MariaDB has become one of the top open source databases today.

First of all, MariaDB is the most widely used relational database management system (RDMS) in the world. Especially, one of the favourite open source solutions that supports most SQL features.

Similarly, MariaDB is a client/server model database. One of the factors that contributed to the rise and adoption of MySQL is its application of phpMyAdmin and WordPress.

Well, MariaDB is a relational database that manages predefined relationships between data items. Additionally, it organizes those items as a set of tables, columns, and rows. Each row in a table contains primary keys. Moreover, rows among multiple tables have foreign keys.

Furthermore, it ensures that data tables, views, and indexes are distinguished from the database’s physical storage. This way, admins can effortlessly manage physical data without affecting access to the data like a logical structure.

With logical structure comes logical operations, which enable the application to specify the content needed. Indeed, physical processes are how data should be accessed and carried out task wise. Hence, it is simple, powerful, and broadly adaptable by organizations of all shapes and sizes.

Features Of MariaDB

Below are the key features of the MariaDB database:


Basically, it is a general purpose storage engine known for balancing high reliability and performance. InnoDB is a default storage engine in MySQL and a prevalent choice to use with MariaDB. Basically, it offers ACID standard compliance transaction features and supports foreign keys. Besides, MariaDB also provides powerful storage engines such as FederatedX, Maria, XtraDB, PBXT, InnoDB, TokuDB and so on that can integrate with other relational DBMS.


Additionally, we have a piece of open source software that the database engineering team developed at Facebook. Considered an alpha stage storage system, you can use it for optimizing low latency and fast storage. Aims at keeping storage savings efficient. This way, you can save on things like SSD wear and tear, the amount of storage used, and better IO capacity for managing queries.

Sequence Storage Engine

With the help of the Sequence Storage Engine, you can create ascending or descending number sequences using a given starting value, increment value, and ending the deal. It also makes virtual ephemeral tables as and when required.


Primarily designed like a drop in storage engine for MariaDB. In MariaDB 10.2, it has become a default storage engine for Maria.

Galera Cluster

A ‘True multi master cluster’ as its development is based on synchronous replication. Mainly, aims to provide a high uptime, prevent data loss, and scalable growth. Synchronous replication means that the slave does not lag, and there will be no data loss if the node crashes. You can read and write any node at any time. Also, multi threads the salve for better performance. For example, its HotStandby feature mitigates downtime during a failover since it doesn’t have any failovers.

Advantages Of MariaDB

Some of the major advantages of the MariaDB database is shown below:

User Friendly Installation

You do not have to worry about an extended deployment destroying your company’s system. Altogether, MariaDB provides you with two options:

  • Using it with Hybrid Transactional/Analytical Processing Platform.

Offers Columnar Storage

So, using this storage option provides you with multiple benefits. For instance, it includes faster performance upon working with massive database operations. Besides, it shifts workloads to the storage type.

Provides Cost Effective Cloud Based Storage

Another benefit is that, it provides cloud native storage support for data support. It lowers the data storage cost with the help of object storage services instead of other storage solutions.

Enterprises can simply use one all inclusive, powerful database for their business specific needs, be it in the cloud or on prem. Deploy MariaDB within minutes to achieve unparalleled operational agility along with full SQL and ACID compliance. Even it is ideal for various hybrid cloud, analytical and transactional use cases.

Imports Data Seamlessly

Certainly, MariaDB restricts you from suffering arduous data migrations. As it supports various connectors, such as Kafka, Spike, Python, Java, and C. A streamlined data import experience provides you with all relevant data sources to acquire a complete set for your analytical tools.

Seamless Scaling

Another key point is that, MariaDB works with multiple columns and rows and even produces results within seconds. Supports massively parallel processing to improve the database’s analytics speed significantly. A new set of requirements and growth in your data sets helps your systems grow.

Provides Tight Security Measures

Security is a crucial concern of any website owner. Because hacking rises annually, and breaches can have severe consequences. Therefore, ensuring that your database software management is adequately secured is essential.

The latest MySQL update has introduced some new security focused features. However, MariaDB comes with a wide range of additional security measures, which are:

  • Database encryption.

Now it is time to learn How to MariaDB/MySQL Show Users and GRANTS Privileges.

MariaDB/MySQL Show Users and GRANTS Privileges


  • Any Linux operating system installed on your server.
  • Access to the MySQL root user account.

Verify MariaDB Installation

Before starting, you will need to verify whether the MariaDB or MySQL is installed on your server. You can verify it with the following command:

					apt-cache policy mariadb-server

If everything is fine, you will get the following output:

  Installed: 1:10.1.48-0ubuntu0.18.04.1
  Candidate: 1:10.1.48-0ubuntu0.18.04.1
  Version table:
 *** 1:10.1.48-0ubuntu0.18.04.1 500
        500 bionic-updates/universe amd64 Packages
        500 bionic-updates/universe i386 Packages
        500 bionic-security/universe amd64 Packages
        500 bionic-security/universe i386 Packages
        100 /var/lib/dpkg/status
     1:10.1.29-6 500
        500 bionic/universe amd64 Packages
        500 bionic/universe i386 Packages


You will also need to check the running status of the MariaDB server. You can check it with the following command:

					systemctl status mariadb

Then, you should see the following output:

					● mariadb.service - MariaDB 10.1.48 database server
   Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
   Active: activating (start) since Tue 2022-09-13 09:30:59 IST; 9min ago
     Docs: man:mysqld(8)
  Process: 17246 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -
  Process: 17241 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 17233 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
 Main PID: 17321 (mysqld)
    Tasks: 27 (limit: 4915)
   CGroup: /system.slice/mariadb.service
           └─17321 /usr/sbin/mysqld

Sep 13 09:30:59 vyompc systemd[1]: Starting MariaDB 10.1.48 database server...


If the MariaDB server is not installed on your server. You can install it by just running the following command:

					apt-get install mariadb-server -y

After the successful MariaDB installation, you can proceed to the next step.

Create a User and Grant Privileges in MariaDB

At this point, the MariaDB server is installed and running on your server. Now, you will need to create a database, user and grant privileges.

  1. Firstly, log in to MariaDB shell with the following command:
					mysql -u root -p

2. You will need to provide your MariaDB root password to log in to the MariaDB shell. After the successful authentication, run the following command to create a database:

					CREATE DATABASE testdb;

3. Next, create a new MariaDB user named testuser with the following command:

					CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'password';

4. Now, grant all the privileges to the testuser with the following command:

					GRANT ALL ON *.* TO 'testuser'@'localhost' WITH GRANT OPTION;

5. After that, flush the privileges to apply the changes:


Show Users and GRANTS Privileges in MariaDB/MySQL

When you install the MariaDB/MySQL server, it works under the root user with all the privileges. For one thing, other user does not have permission to access all databases. In this case, you will need to grant privileges to the user to access the specific database.

The basic syntax to show “GRANTS Privileges” is shown below:

					SHOW GRANTS FOR [user@host]

A first step is to list all MariaDB users available in your system by running the following command:

					SELECT user,host FROM mysql.user;

Then you will get a list of all users in the following output:

| user     | host      |
| root     | localhost |
| testuser | localhost |
2 rows in set (0.00 sec)


Please next is to show grant privileges of the account that you are using to connect to the server, run the following command:


Following, you should see all privileges of the root user in the following output:

| Grants for root@localhost                                                                      |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                  |


Further, you can also run the following command to show the grant privileges of the current user:


Fourthly, if you want to show the grant privileges of the testuser, run the following command:

					SHOW GRANTS FOR testuser@localhost;

Finally, you should see the grant privileges of the testuser in the following output:

| Grants for testuser@localhost                                                                                                              |
1 row in set (0.00 sec)


Thank you for reading How to MariaDB/MySQL Show Users and GRANTS Privileges. It is time to summarize. 

How to MariaDB/MySQL Show Users and GRANTS Privileges Conclusion

In this post, we explained how to show users and grant privileges in MariaDB and MySQL. Display display user’s privileges and control database access in MariaDB, with ease. Check out the MySQL official documentation for more information about show grant privileges.

Please check out our blog for more MariaDB content. 

Avatar for Hitesh Jethva
Hitesh Jethva

I am a fan of open source technology and have more than 10 years of experience working with Linux and Open Source technologies. I am one of the Linux technical writers for Cloud Infrastructure Services.

0 0 votes
Article Rating
Notify of
Inline Feedbacks
View all comments
Would love your thoughts, please comment.x