Grant All Privileges doesn't work on PHP [MySQL]

Question

I'm trying to GRANT ALL PRIVILEGES with php inside a loop. Each user should have full access to their corresponding Database. But for some reason GRANT ALL PRIVILEGES command returns false response.

   $dbArray = [new OnePDO,new TwoPDO];
   $dbNames = ['ONE_PDO','TWO_PDO'];

   for ($foo = 0; $foo < count($dbNames); $foo++) {
        $dbName = $dbNames[$foo];
        $pass = $util -> generateRandomPassword();
        $userName = strtolower($dbName);
        $userName = str_replace('_', '.', $userName);
        $util -> execute("CREATE USER '$userName'@'localhost' IDENTIFIED BY '$pass'", $dbArray[$foo]);
        $util -> execute("GRANT ALL PRIVILEGES ON $dbName.* TO '$userName'@'localhost'", $dbArray[$foo]));
        $util -> execute("FLUSH PRIVILEGES", $dbArray[$foo]);
   }

When trying to use the database...

Access denied for user 'one.pdo'@'localhost' to database 'ONE_PDO'

Adding this just for context...

   public function execute($query, $database) {
        $command = $database -> prepare($query);
        try {
            $command -> execute();
        } catch (Exception $e) {
            $command = false;
        }
        return $command;
    }

GRANT ALL PRIVILEGES needs IDENTIFIED BY 'password'. Here https://stackoverflow.com/questions/5016505/mysql-grant-all-privileges-on-database — Greg Kelesidis, Feb 03 '21 at 19:32
When I add `IDENTIFIED BY '$pass'` it causes a crash [SQL syntax error] — , Feb 03 '21 at 19:41
Hold on, I'm printing the exception of my `execution` function, It says the user, that I'm using for `mysqli_connect`, doesn't have permission to these loop databases. But I have given that user all privileges manually through terminal. (since I saw a post that says, I cannot use `root@localhost` to call `mysqli_connect` && `root@localhost` wasn't working for me) Why is that? — , Feb 03 '21 at 19:51
Access denied for user 'boss'@'localhost' to database 'ONE_PDO' — , Feb 03 '21 at 19:52
There is a database named 'mysql' in the server. You may adjust privileges as needed, for the user running the queries. — Greg Kelesidis, Feb 03 '21 at 19:55
I mean I have already given all privileges to `boss@localhost` via `GRANT ALL PRIVILEGES ON *.* TO 'boss'@'localhost';` this command (through terminal manually) — , Feb 03 '21 at 19:56

score 0 · Accepted Answer · answered Feb 03 '21 at 20:24

I solved it by typing GRANT ALL PRIVILEGES ON *.* TO 'boss'@'localhost' WITH GRANT OPTION; in terminal, then I executed the PHP.

Previously I wasn't using WITH GRANT OPTION that's why the PHP script didn't worked.

Grant All Privileges doesn't work on PHP [MySQL]

1 Answers1