identified by password in the grant query affecting irrelevant connection

Question

In the code below there are 2 mysql connections, the issue is that the second connection gives a connection error if the password coming in from the form is anything but 123456.

Now, password coming in from the form should have nothing to do with the second connection because that password in for the new database that is made before the second conenction starts. The second connection is connecting to another database all together.

However, if i use the commented out $grantQ query, the second connection works fine. That means that the issue is got something to do with identified by '{$pass}' in the $grantQ query.

The identified by '{$pass}' query should only affect the new database that has been created. Why is it affecting the existing db that the second connection is connecting to?

please help .. sorry about the long summary !

      <?php
        if(isset($_POST['submit'])){
            // SUPER CONNECTION
            $maindb_db = "little_maindb"; 
            $maindb_server = "localhost";
            $maindb_username = "admin@littlesidegym.com"; 
            $maindb_password    =  "123456";//
            $conn = new mysqli($maindb_server, $maindb_username, $maindb_password, $maindb_db);
            if ($conn->connect_error) {
                die($contact_cus_supp);
            }   
            //NAME OF DB FROM POST - CUSTOMER VIEW
            $new_dbname = mysqli_real_escape_string($conn, $_POST['db_name']);
            $new_pass = mysqli_real_escape_string($conn, $_POST['pass']);
            // CREATING DATABASE
            $sql = "CREATE DATABASE IF NOT EXISTS $new_dbname ";
            if (!mysqli_query($conn, $sql)) {
                mysqli_close($conn);
                exit();
            }
            $host = "localhost";
            $user = $_SESSION['sess_email'];
            $flush_pri = "FLUSH PRIVILEGES";
            $pass = $new_pass;
            // GRANT USER PRIVILEGES
            echo $current_project;
            $grantQ = "GRANT ALL PRIVILEGES ON " . $new_dbname . ".* TO '{$user}'@'{$host}' identified by '{$pass}'";
            //$grantQ = "GRANT ALL PRIVILEGES ON " . $new_dbname . ".* TO '{$user}'@'{$host}'";
            if(!mysqli_query($conn,$grantQ)){
                mysqli_close($conn);
                exit();
            }
            if(!mysqli_query($conn,$flush_pri)){
                mysqli_close($conn);
                exit();
            }
            // SUPER CONNECTION CLOSE
            mysqli_close($conn);
            // ALTOGETHER A DIFFERENT CONNECTION
            $secdb_db = "little_userdb"; 
            $secdb_server = "localhost";
            $secdb_username = "user@littlesidegym.com"; 
            $secdb_password =  "123456";//
            $conn = new mysqli($secdb_server, $secdb_username, $secdb_password, $secdb_db);
            if ($conn->connect_error) {
                die($contact_cus_supp);
            }
            echo "HELLO WORLD";
        ?>  

// FORM

 echo '<table>';
        echo '<form action="create_project.php" method="POST" id = "myform" name = "myform" >';
        echo '<tr><th></th></tr><tr><td><input type = "text" value="" name = "db_name" class = "req alphanums" placeholder = "DB Name" ><td></tr>';

        echo '<tr><th></th></tr><tr><td><input type = "password" value="" name = "pass" class = "req" placeholder = "Password" ><td></tr>';

        echo '<tr><th></th></tr><tr><td><div id = "submit"><input type="submit" id = "submit" name = "submit" value = "Create Project"></div><td></tr>';
        echo '</form>';
        echo '</table>';

Answer 1

You are using the same user name for newly created database and for accessing little_userdb, thus changing its password each time.

In mysql user is identified by name and hostname mask (mysql looks for first match). So your code changes password for existing user and grants him access to the new db.