search

Home  >  Q&A  >  body text

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

<p>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:</p> <p>I installed <code>mysql</code> on <code>RHEL 5.7 64bit</code>, the installation package is as follows, once I completed the <code>rpm install</code>, we Will</p> <ol> <li>Use <code>mysql_install_db</code> to create a mysql database, then </li> <li>Start the mysql service, then</li> <li>Using <code>mysql_upgrade</code> will also operate on the server. </li> </ol> <p>After this process, I can log in using <code>root</code>, but cannot log into the server with a non-root user: </p> <pre class="brush:php;toolbar:false;">[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)</pre> <p>This is the problem I encountered, is there any solution? </p>
P粉080643975P粉080643975450 days ago487

reply all(1)I'll reply

  • P粉301523298

    P粉3015232982023-08-22 10:52:24

    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

    reply
    0
  • Cancelreply