PDO access denied for user 'username'@'%'

Question

I need to connect to a remote mysql server using a php page;
the connection itself works as a charm, but the moment i try to create the database, as it doesn't exist yet, i get this error:

exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1044 Access denied for user '[myusername]'@'%' to database '[mydbname]'' in [myurl]/php/db_manager.php:76

As you can see, i have access denied to "%".
Now: what is "%"?

Furthermore:

Main file

private function createDB() {
if($this->cm->update("CREATE DATABASE IF NOT EXISTS " . $this->dbName . " DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;", array())) { // Error here
  $this->cm->update("USE " . $this->dbName . ";", array());
  return true;
}
return false;
}

$this->cm is an instance of a correctly initialized PDO wrapper

PDO wrapper file

public function update($query, $values) 
try{
        $sql = $this->db->prepare($query);
        $sql->execute($values);
        return true;
    } catch(PDOException $e) {
        $this->l->error($e); // <- Error here
        return false;
    }
}

$this->db is a correctly instantiated, fully connected PDO object;
These are the lines used to connect

$this->db = new PDO($connection, $this->db_user, $this->db_password, array(PDO::ATTR_PERSISTENT => true));
$this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

I have full access on the Mysql server

Root privileges for the Mysql user is not granted. Thats why SQL server is returning this error. — Ashu Jha, Aug 29 '16 at 07:36
I do have them, since if i connect manually to the server with my login data i can create/edit/drop databases without any issue — JValker, Aug 29 '16 at 07:41
Take a look [here](http://stackoverflow.com/questions/6239131/how-to-grant-remote-access-permissions-to-mysql-server-for-user) — Ashu Jha, Aug 29 '16 at 07:54

score 7 · Accepted Answer · answered Aug 29 '16 at 08:03

Access denied for user '[myusername]'@'%' to database '[mydbname]'

MySQL permissions are granular: not all users have full access to all databases on the server.

You need to sign in with an administrator and grant the appropriate permissions. For instance, to grant full access:

GRANT ALL
ON mydbname.*
TO 'myusername'@'%'
WITH GRANT OPTION;
FLUSH PRIVILEGES;

... or you can be more selective to your liking:

GRANT SELECT, ALTER, CREATE, DELETE, DROP, INDEX, INSERT, REFERENCES, TRIGGER, UPDATE
ON mydbname.*
TO 'myusername'@'%';
FLUSH PRIVILEGES;

Please check Privileges Supported by MySQL for a full list.

% is a wildcard explained in detail at Account Names and Passwords that means "connection from any host".

score 1 · Answer 2 · answered Nov 14 '20 at 16:59

I had similar problem on xampp. changed my password to auto generated password(by Generate Password Button) and then copy + paste in config file. worked successfully!

code :

// config information
require_once "config.php";

// make connection string
$connection_string = "mysql:host=" . DB_HOST . ":" . DB_PORT . ";dbname=" . DB_NAME;
$connection = null;

// show connection string
echo $connection_string."</br>";

// try to connect to db or catch exceptions
try{
    $connection = new PDO( $connection_string, DB_USER, DB_PASS );
}catch (PDOException $exc){
    echo '<pre>';
    print_r($exc);
    echo '</pre>';
}

echo '</br>';

// connection status
if ( $connection ) {
    echo "Connected Successfully!";
} else {
    echo "Connection Failed!";
}

Result:

mysql:host=127.0.0.1:3306;dbname=php_pdo_db

Connected Successfully!

score 0 · Answer 3 · answered Jan 07 '23 at 18:21

In the PDO query add the port after host, (in my case changed the port to 8111) and it should work.

code :

//write in database
try {
    $dbh = new PDO("mysql:host=$db_host:8111;dbname=$db_name", $db_user, $db_password);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $dbh->exec($database);
}
catch(PDOException $e) {
    if ($e->getCode() == 2002) {
        header('location: step3.php?_error=Unable to Connect Database, Please make sure Host info is correct and try again !');
        exit;
    }
    elseif ($e->getCode() == 1045) {
        header('location: step3.php?_error=Unable to Connect Database, Please make sure database username and password is correct and try again !');
        exit;
    }
    elseif ($e->getCode() == 1049) {
        header('location: step3.php?_error=Unable to Connect Database, Please make sure database name is correct and try again !');
        exit;
    }
}

PDO access denied for user 'username'@'%'

Main file

PDO wrapper file

3 Answers3

code :

Result: