How to check and assign privileges to a MySQL User

User management is the process of controlling which users are allowed to connect to the MySQL server. And also, what permissions they are allowed to have on each database. phpMyAdmin does not handle user management, rather it passes the username and password on to the MySQL server which then determines whether a user is permitted to perform a particular action. In this article, you will learn How to check and assign privileges to a MySQL User. Here are some related guides: How to delete a MySQL User Account, What to note when assigning Multiple IPs’ to an Instance.

Within phpMyAdmin, administrators have full control over creating users, viewing and editing privileges for existing users, and removing users.

By default, MySQL installs and works under the root user with all privileges. However, not everyone who accesses the database should have all rights over the data for security reasons.

MySQL provides methods to create new user accounts and grant privileges over the database. A simple command helps provide valuable information about what privileges the users currently have. This tutorial shows you how to check and assign Privileges to a missing Db user on a MySQL server.

Please here are some exciting articles: Error: Access denied to the harddrive and the security tab missing under Active Directory. See how to find the pathname of a mapped network drive, and how to Import PST file into Exchange Server 2016

How to Check Privileges for a User via phpmyAdmin?

Note: Users are assigned to databases by editing the user record from the User accounts link on the phpMyAdmin page. When you edit that user, and remove the privileges The user will disappear from the list of users having access to the database. If you wish to re-assign the privileges to this user, this guide is here to help you.

To check for a user account privilege, you can do this via the command line. And then log into the MySQL server client as root. But in this article, I will be showing you how to do this over the pypMyAdmin interface as shown below.

As you can see, a user I created as a “Teampass Password Manager Database administrator” is missing because I deliberately removed all rights granted to this user. For the net steps to find and re-assign the privileges to these users.

kindly take a look at these related guides: How to migrate Veeam MsSQL Database to a new MsSQL Server, how to migrate Veeam MsSQL Database to a new MsSQL Server, and how to access MySQL Server from command Prompt.

Re-assign the privileges

To fix this, log on to the phpMyAdmin page with your username and password.

Select the database in question and click on the SQL query button as shown below

Next, run the following SQL query to display the user you want to remove by running a command that lists users from the MySQL server.

SELECT User, Host FROM mysql.user;

The result of the above query is displayed in the image below. To re-assign the privileges, click on the Edit button for this specific user.

The user update privilege table will open upon clicking on the Edit button as shown below. Assign the necessary rights. For me, I will assign all the rights as I want the user to have complete control over the DB. When this is done, the user will be available and accessible to the DB.

Select the privilege you want by selecting the button "Y".

To manually grant privileges, here are some sample sql statement

UPDATE `user` SET `Grant_priv` = 'Y' WHERE `user`.`User` = 'ur_username' AND `user`.`Host` = '%';

See how to run apps as an administrator on Windows 10 and Windows 11, and how to determine the effect of renaming an MBAM or BitLocker protected Computer.

Revoke all MySQL User Privileges

If you wish to revoke all privileges for a specific user, here is a SQL statement you can use.

REVOKE ALL PRIVILEGES ON . FROM 'ur_username'@'%'; GRANT ALL PRIVILEGES ON . TO 'ur_username'@'%'; ALTER USER 'ur_username'@'%' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;

Please refer to these related to learn more about MySQL. How to use MySQL Workbench to download your MySQL database, and MySQL Workbench could not connect to MySQL server.

I hope you found this blog post helpful on how to check and assign privileges to a MySQL User. If you have any questions, please let me know in the comment section.