ERROR 1045 (28000): Access denied for user 'username'@'%' (using password: YES)

database, mysql, permissions, privileges, sql-grant

Solution

First of, I can't imagine the reason why you've deleted `root` user. But back to the question - you should specify `WITH GRANT OPTION`, like this:

(However this should be opted after you get the mysql re-installed as if you don't have the `root` access and the user is not having the sufficient privileges also, then the best is to restart the install process and make the user and grant them privileges the way defined below)

mysql> create user 'golden'@'localhost' identified by 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to golden@localhost with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

and then

mysql> select current_user();
+------------------+
| current_user()   |
+------------------+
| golden@localhost |
+------------------+
1 row in set (0.00 sec)

mysql> create database testing;
Query OK, 1 row affected (0.03 sec)

mysql> grant all privileges on testing.* to test;
Query OK, 0 rows affected (0.02 sec)

mysql> drop database testing;
Query OK, 0 rows affected (0.12 sec)

-but yet again, think twice before deleting `root` user.

Problem

I Installed MySQL on my CentOS 6.4 server. I logged into my root and changed its password. Later I thought that I should make a new user and use that user as my default user, So I created a new user name `golden` using the following command: ``` CREATE USER 'golden'@'%' IDENTIFIED BY 'password'; ``` Then I applied permission to the user `golden`: ``` GRANT ALL PRIVILEGES ON * . * TO 'golden'@'%'; FLUSH PRIVILEGES; ``` Now this user: `golden` was able to do everything. So I finally `Deleted the root user`. Now I am stuck up on granting privilege to my one another new user. I created another user, when I was logged in through `golden` (At this time I had already deleted the `root` user and command successfully created and the new user I am able to see it in list also) ``` CREATE USER 'fashion'@'%' IDENTIFIED BY 'password'; ``` Then the following commands below gives me error: ``` GRANT ALL PRIVILEGES ON *.* TO 'fashion'@'%'; ``` ERROR: ERROR 1045 (28000): Access denied for user 'golden'@'%' (using password: YES) I also tried the following command the result is below: ``` mysql> SELECT USER(),CURRENT_USER(); +------------------+----------------+ | USER() | CURRENT_USER() | +------------------+----------------+ | golden@localhost | golden@% | +------------------+----------------+ ``` If I wont be able to give access to this user then how can I login and use the database? Kindly help. EDIT 1: The following command gives me the following result ``` mysql> select user, host FROM mysql.user; +------------+-------+ | user | host | +------------+-------+ | golden | % | | fashion | % | +------------+-------+ ```

Original source