Other Tools By Katy: Code Snippets | Writing Progress Tracker | Threader: Easy Threads Maker | Word Cloud Maker

Katy's Code Bank

MYSQL: Check User Exists & Grant Privileges On Database

MYSQL

Check if the User Exists

SELECT user, host FROM mysql.user WHERE user = 'your_username';
SQL

If the user doesn’t exist, create them first:

CREATE USER 'your_username'@'%' IDENTIFIED BY 'your_password';
SQL

Grant Privileges Before Revoking

GRANT ALL PRIVILEGES ON your_database.* TO 'your_username'@'%';
FLUSH PRIVILEGES;
SQL

Then, if needed, revoke specific privileges:

REVOKE CREATE, DROP, GRANT OPTION, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON your_database.* FROM 'your_username'@'%';
SQL

If the user is created with ‘your_username’@’localhost’, but you are applying @’%’, adjust your queries:

GRANT ALL PRIVILEGES ON your_database.* TO 'your_username'@'localhost';
SQL

If nothing else works, deleting and recreating the user may help:

DROP USER IF EXISTS 'your_username'@'%';
CREATE USER 'your_username'@'%' IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON your_database.* TO 'your_username'@'%';
FLUSH PRIVILEGES;
SQL
Disclaimer: The code on this website is provided "as is" and comes with no warranty. The author of this website does not accept any responsibility for issues arising from the use of code on this website. Before making any significant changes, ensure you take a backup of all files and do not work directly on a live/production website without thoughly testing your changes first.

Leave a Reply

Your email address will not be published. Required fields are marked *