访问被拒绝用户'admin'@'localhost'mysql

Question

I created a database via:

create database test_db;

Then I created a user via:

CREATE USER 'test_user'@'test_db' IDENTIFIED BY 'test_pass';
grant all privileges on *.* to 'test'@'test';

now when i try to connect to it via PHP, i used the following code:

    $hostname_localhost ="localhost";  
    $database_localhost ="test_db";  
    $username_localhost ="test_user";  
    $password_localhost ="test_pass";  
    try{
        $dbh = new PDO("mysql:host=$hostname_localhost;dbname=$database_localhost",$username_localhost,$password_localhost);
        echo 'Connected to DB'.'<br />';
        $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $stmt = $dbh->prepare("SELECT user,host from mysql.user");
        $stmt->execute();

        $result = $stmt->fetchAll();

        foreach($result as $row)
        {
            echo $row['user'].'@'.$row['host'].'<br />';
        }

        $dbh = null;
    }
    catch(PDOException $e)
    {
        echo $e->getTraceAsString().$e->getLine().$e->getMessage();
    }

I am getting the following error:

SQLSTATE[28000] [1045] Access denied for user 'test_user'@'localhost' (using password: YES)

please tell me how to fix this. how do i connect to my test user via PHP.

Answer 1

Your create user / grant should be:

CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'test_pass';
grant all privileges on test_db.* to 'test_user'@'localhost';

I also noticed your SQL Select statement is from mysql.user. If you want test_user to have access to query from that database, you would need another GRANT statement and or change the test_db part in the grant statement to *. I am not sure what your use case is and or what you need, but just a heads up.

The syntax for the Create User goes as follows:

CREATE USER 'username'@'host_or_IPAddress' IDENTIFIED BY 'password';

For the grant it is (omitted a few items, please read the referenced material for more in depth options / information):

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'samehostascreateuser';

For your reference / reading: MySQL Create User and Mysql Grant Syntax