CODEWITHSUNDEEP
phpmysqlpdo
JValker
JValker

Reputation: 374

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

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

Upvotes: 0

Views: 8421

Answers (3)

Sherwin Samuel
Sherwin Samuel

Reputation: 41

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;
    }
}

Upvotes: 0

Mohammad Javad Boroumandi
Mohammad Javad Boroumandi

Reputation: 11

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!

Upvotes: 1

&#193;lvaro Gonz&#225;lez
&#193;lvaro Gonz&#225;lez

Reputation: 146460

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".

Upvotes: 7

Related Questions