How to Check MySQL User Privileges in Linux

The first/fresh installation of a MySQL on any operating system only considers the root user as the default database user. The first database transactions/activities are performed by the root user only.

Therefore, it is not ideal for any user that needs access to the MySQL database to gain entry via the root user credentials. Root user access should be reserved to the database administrator who will then use the root user credentials to create database users and grant privileges to execute different database queries.

It is also ideal practice for the database administrator to avoid using the root user to access the MySQL database but instead create another user and grant this user the same access and execution privileges as the root user.

This article guide is ideal for MariaDB, MySQL Enterprise Edition, and MySQL Community Edition users. To demonstrate how to check MySQL user privileges, we will first create different test users with different MySQL privileges.

Create a New MySQL User

First, gain root access to your MySQL database from your Linux terminal using the command:

$ mysql -u root -p

The command syntax for creating a MySQL user is as follows:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'your_user_password';

The above use case applies to a MySQL installed on a local machine. If you were using a remote machine/server, then you would have to replace ‘username’@’localhost’ with ‘username’@’remote_machine_ip_address’.

If you want a user that can connect to any MySQL installed system without specifying the hostname or IP address, adhere to the command syntax:

CREATE USER 'username'@'%' IDENTIFIED BY 'your_user_password';  

Let us now create several MySQL database users.

CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password1';  
CREATE USER 'user2'@'localhost' IDENTIFIED BY 'password2';  
CREATE USER 'user3'@'%' IDENTIFIED BY 'password3';  

Note that these users are for demo purposes and therefore consider creating stronger database user passwords for your production environment.

Grant Privileges to New MySQL User

The next step is to assign these created database users different roles (user privileges). These user privileges relate to the database actions that the different database users are allowed to perform.

We can breakdown these permissions to:

• All Privileges: The user assigned this permission can perform all database roles.
• Insert: User assigned this permission can insert database table row data.
• Delete: The user assigned this permission can delete database table row data.
• Create: The user assigned this permission can create non-existing databases and tables.
• Drop: The user assigned this role can remove existing databases and tables.
• Select: The user assigned this permission can read databases information.
• Update: The user assigned this permission can modify database table row data.
• Grant Option: The user assigned this permission can modify the privileges of other database user accounts.

For instance, if we were to grant user1 all privileges to all databases and tables to be like the root user, we would execute the command:

GRANT ALL PRIVILEGES ON *.* TO 'user1'@'localhost';

If we were to grant user2 all privileges to all the database tables of a specific database (e.g mysql), we would execute the command:

GRANT ALL PRIVILEGES ON mysql.* TO 'user2'@'localhost';

If we were to grant user3 the privilege to only create new MySQL users, we would execute the command:

GRANT INSERT ON mysql.user TO 'user3'@'%';

Checking User Privileges in MySQL

To check a user’s database privileges, reference the command syntax:

SHOW GRANTS FOR username;

To check on these three users privileges:

SHOW GRANTS FOR user1@localhost;
SHOW GRANTS FOR user2@localhost;
SHOW GRANTS FOR user3;

To revoke user assigned privileges, reference the command syntax:

REVOKE permission_type ON database.table FROM 'username'@'hostname'; 

For Example;

REVOKE INSERT ON mysql.user FROM user3;

[ You might also like: MySQL Database Commands Cheat Sheet for Linux ]

With this article, you now understand everything there is to know about MySQL user privileges.