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?
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:
InnoDB
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.
MyRocks/RocksDB
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.
XtraDB
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:
- Setting up MariaDB ColumnStore as a stand alone server.
- 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
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
The latest MySQL update has introduced some new security focused features. However, MariaDB comes with a wide range of additional security measures, which are:
- Internal security and password checks.
- PAM and LDAP authentication.
- Database encryption.
Now it is time to learn How to MariaDB/MySQL Show Users and GRANTS Privileges.
MariaDB/MySQL Show Users and GRANTS Privileges
Prerequisites
- Any Linux operating system installed on your server.
- MySQL server or MariaDB server is installed and configured.
- 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:
mariadb-server:
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 http://in.archive.ubuntu.com/ubuntu bionic-updates/universe amd64 Packages
500 http://in.archive.ubuntu.com/ubuntu bionic-updates/universe i386 Packages
500 http://security.ubuntu.com/ubuntu bionic-security/universe amd64 Packages
500 http://security.ubuntu.com/ubuntu bionic-security/universe i386 Packages
100 /var/lib/dpkg/status
1:10.1.29-6 500
500 http://in.archive.ubuntu.com/ubuntu bionic/universe amd64 Packages
500 http://in.archive.ubuntu.com/ubuntu 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)
https://mariadb.com/kb/en/library/systemd/
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.
- 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:
FLUSH PRIVILEGES;
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:
SHOW GRANTS;
Following, you should see all privileges of the root user in the following output:
+------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION |
| 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:
SHOW GRANTS FOR 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 |
+--------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------------------+
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.
