When you have a database with users, it is important to know how many privileges each user has. In this article we will go through the steps of checking your own current privileges as well as other people’s in Linux.
The “mysql show user privileges” command is a useful tool for checking the MySQL user privileges. This article will explain how to use it in Linux.
The root user is the default database user when MySQL is installed for the first time on any operating system. Only the root user performs the first database transactions/activities.
As a result, any user who requires access to the MySQL database should avoid using the root user credentials. The database administrator should have root user access. He or she will then utilize the root user credentials to create database users and provide rights to conduct various database queries.
It is also recommended that the database administrator not utilize the root user to access the MySQL database, but rather establish a new user with the same access and execution rights as the root user.
Users of MariaDB, MySQL Enterprise Edition, and MySQL Community Edition will benefit from this article guide. To show how to verify MySQL user privileges, we’ll first establish a number of test users with varying MySQL rights.
Make a brand-new MySQL user.
To begin, use the command to acquire root access to your MySQL database from your Linux terminal:
mysql -u root -p $ mysql -u root -p $ mysql -u
The following is the syntax for creating a MySQL user:
CREATE A USER WITH THE NAME ‘username’@’localhost’ AND THE PASSWORD ‘your user password’;
The above scenario is for a MySQL installation on a local workstation. If you’re working on a remote machine/server, replace ‘username’@’localhost’ with ‘username’@’remote machine ip address’.
Follow the following syntax if you want a user who can connect to any MySQL system without giving the hostname or IP address:
USER ‘username’@’ percent’IDENTIFIED BY ‘your user password’; CREATE USER ‘username’@’ percent’IDENTIFIED BY ‘your user password’
Let’s create a few MySQL database users now.
CREATE USER ‘user1’@’localhost’ WITH ‘password1’ AS IDENTIFIER; CREATE USER ‘user2’@’localhost’ WITH ‘password2’ AS IDENTIFIER; IDENTIFIED BY ‘password3’; CREATE USER ‘user3’@’ percent ‘
Due to the fact that these users are just for demonstration reasons, you should create stronger database user passwords for your production system.
Make a New MySQL User
Grant New MySQL User Privileges
The next step is to give distinct responsibilities to these newly generated database users (user privileges). The database operations that various database users are able to execute are determined by these user rights.
These permissions may be broken down into the following categories:
- All Privileges: The user who has been given this privilege has full access to the database.
- This permission allows the user to put data into database table rows.
- Delete: The user who has been given this privilege has the ability to delete database table row data.
- Create: The user with this access has the ability to create new databases and tables.
- Drop: The person with this role has the ability to delete existing databases and tables.
- Select: The person who has been given this permission has the ability to access database information.
- Update: The person who has been given this access has the ability to change the data in database table rows.
- The person who is given this permission has the ability to change the rights of other database user accounts.
For example, if we wanted to provide user1 root-level access to all databases and tables, we’d run the following command:
GIVE ‘user1’@’localhost’ ALL PRIVILEGES ON *.*;
If we wanted to give user2 full access to all database tables in a given database (for example, mysql), we’d run the command:
ALL PRIVILEGES ON MYSQL ARE GRANTED. ‘user2’@’localhost’;
If we were to grant user3 the privilege to only Make a New MySQL User, we would execute the command:
GRANT INSERT TO ‘user3’@’ percent’ ON mysql.user;
Checking MySQL User Privileges
Use the following command syntax to determine a user’s database privileges:
DISPLAY GRANT REQUESTS FOR USERNAME;
To verify the privileges of these three users, go to:
SHOW GRANTS FOR [email protected]; SHOW GRANTS FOR [email protected]; SHOW GRANTS FOR user3; 
Check MySQL User Privileges
Refer to the command syntax to remove user-assigned privileges:
ON THE DATABASE, REVOKE permission type. FROM ‘username’@’hostname’ table;
As an example;
REVOKE INSERT ON mysql.user FROM user3; 
Revoke MySQL User Privileges
[You may also be interested in: Linux MySQL Database Commands Cheat Sheet]
You now know all there is to know about MySQL user rights thanks to this article.
Watch This Video-
The “show grants for user” is a command-line tool that can be used to check the privileges of a MySQL user.
Frequently Asked Questions
How do I set user privileges in MySQL?
A: In order to change your user privileges, you need to run the following commands in terminal.
What are the privileges in MySQL?
A: In MySQL, privileges are permissions granted to users based on their position in the database. They are a way of restricting what different types of data can be accessed by other users or systems.
Related Tags
- grant all privileges mysql
- mysql show users
- mysql create user
- how to change privileges in mysql
- mysql check if user is admin
