Sandy's simple note

Hello there!

When managing your MySQL database, it’s important to regularly clean up inactive users. This helps optimize performance, improve security, and ensure only active user accounts remain for your current projects.

THE ISSUE

When I ran one of my codes, I got the following message:

Unable to open JDBC Connection for DDL execution [Access denied for user 'my_other_user'@'your_ip' (using password: YES)] [n/a]

At first, I thought there was no mistake since I had already kept all the active users in the database.

However, after checking the users table, I realized that the account I was using for my project had been accidentally deleted 😅.

I tried logging in and running a few commands to recreate the user, but then I encountered another error:

mysql> CREATE USER 'my_other_user'@'%' IDENTIFIED BY 'password';
ERROR 1396 (HY000): Operation CREATE USER failed for 'my_other_user'@'%'

Before proceeding, I checked the list of existing users by running the following command:

mysql> use database mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> SELECT user FROM user;
+------------------+
| user             |
+------------------+
| for_api          |
| for_batch_app    |
| for_dev          |
| debian-sys-maint |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
+------------------+

If i look the result above, there is no user my_other_user, but why the error says “Create User Failed” ?

What the hell is going on here?? 😭

NOW, START TO SOLVE

I realized, i did delete the user from my phpMyAdmin GUI that was actually only deleted through a delete button, and i checked the delete button was executing a query statement like :

DELETE FROM user WHERE `user`.`Host` = 'your_host' AND `user`.`User` = 'your_user'"

The delete statement above was not enough to delete a user directly. There was something left in MySQL database containing privileges. You should use a DROP USER statement to clean up the data.

So, i’m going to access through MySQL console and execute a DROP USER statement to ensure there is no user’s data dependencies or metadata on its MYSQL’s table.

mysql> DROP USER 'my_other_user'@'%';
Query OK, 0 rows affected (0.00 sec)

And here is the complete MySQL command to DROP and CREATE a user:

mysql> DROP USER 'my_other_user'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'my_other_user'@'%' IDENTIFIED BY 'your_password';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL PRIVILEGES ON my_other_user.* TO 'your_database'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> UPDATE mysql.user SET Super_Priv='Y',Create_user_priv='Y',Grant_priv='Y',Alter_priv='Y' WHERE user='my_other_user' AND host='%';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec

User has been created and my code runs well now 🥰. Now i have been cleaning up an inactive users in my MySQL database.

If you want to learn about my other note, you might visit here for code and database section, it is still related on what you wanted to learn in all about technology itself.

That’s all folks! Happy coding! 🥰😍

^{Note : MySQL version 8.03}