MySQL: Access denied to user 'test'@'localhost' except root user (password verification: yes)

Question

I have a problem using a non-root/admin user of mysql, I followed the steps below to create the user and permissions, please correct me if I did something wrong:

I installed mysql on RHEL 5.7 64bit, the installation package is as follows, once I completed the rpm install, we Will

1. Use mysql_install_db to create a mysql database, then
2. Start the mysql service, then
3. Using mysql_upgrade will also operate on the server.

After this process, I can log in using root, but cannot log into the server with a non-root user:

[root@clustertest3 ~]# rpm -qa | grep MySQL
MySQL-client-advanced-5.5.21-1.rhel5
MySQL-server-advanced-5.5.21-1.rhel5


[root@clustertest3 ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[root@clustertest3 ~]# ls -ld /var/lib/mysql/mysql.sock
srwxrwxrwx 1 mysql mysql 0 Nov 30 11:09 /var/lib/mysql/mysql.sock

mysql> CREATE USER 'golden'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

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

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

mysql> SELECT USER(),CURRENT_USER();
 ---------------------------------------------
| USER() | CURRENT_USER() |
 ---------------------------------------------
| root@localhost | root@localhost |
 ---------------------------------------------
1 row in set (0.00 sec)

[root@clustertest3 ~]# mysql -ugolden -p
Enter password:
ERROR 1045 (28000): Access denied for user 'golden'@'localhost' (using password: YES)

This is the problem I encountered, is there any solution?

Answer 1

Do not grant all permissions to non-root users on all databases, it is unsafe (and you already have a "root" user with that role)

GRANT <privileges> ON database.* TO 'user'@'localhost' IDENTIFIED BY 'password';

This statement creates a new user and grants selected permissions. For example:

GRANT INSERT, SELECT, DELETE, UPDATE ON database.* TO 'user'@'localhost' IDENTIFIED BY 'password';

Please check Documentation to view all detailed permissions

EDIT: You can find more information using this query (logged in as "root"):

select Host, User from mysql.user;

See what happened