创建MySQL数据库与用户后权限授予失败问题求助

I see exactly what's causing your permission issue—let's fix this up step by step:

The Core Issues in Your Code #

Let's break down the problems that are stopping the grants from working:

• Missing quotes in the GRANT statement: When specifying the user and password in your GRANT query, you didn't wrap them in single quotes. MySQL treats unquoted values as database/table identifiers, so it wasn't actually applying the permissions to your bdsat_boishakhi29@localhost user.
• Unnecessary reconnection: You reconnected to the database right before running the GRANT command—this is totally redundant and doesn't help anything.
• Broken error handling: When checking for grant errors, you referenced $connection2 which doesn't exist; you should use your existing $connection variable instead.
• Invalid SHOW GRANTS call: The final mysqli_query for SHOW GRANTS didn't pass the database connection as the first parameter, so it wasn't executing correctly.

Corrected Code #

Here's the fixed version with explanations for each change:

<?php
$username = "bdsat_boishakhi29";
$password = $username;
$databasename = $username;
$admin_db = "mysql";
$admin_user = "bdsat";
$admin_password = "demo123456";

// Initial connection (keep this, no need to reconnect later)
$connection = mysqli_connect("localhost", $admin_user, $admin_password);
if (mysqli_connect_errno()) {
    echo "<br>Failed to connect to MySQL: " . mysqli_connect_error();
} else {
    echo "<br>Connected successfully!";
}

// Create Database
$sql = "CREATE DATABASE $databasename";
if (mysqli_query($connection, $sql)) {
    echo "<br>Database <b>$databasename</b> created successfully!";
} else {
    echo "<br>Error creating database: " . mysqli_error($connection);
}

// Create User
$sql = "CREATE USER '" . $username . "'@'localhost' IDENTIFIED BY '" . $password . "'";
if (mysqli_query($connection, $sql)) {
    echo "<p>User <b>$username</b> created successfully!</p>";
} else {
    echo "<br>Error creating database user: " . mysqli_error($connection);
}

// Grant Permissions (fixed quotes around user and password)
$sql = "GRANT ALL PRIVILEGES ON $databasename.* TO '" . $username . "'@'localhost' IDENTIFIED BY '" . $password . "'";
if (mysqli_query($connection, $sql)) {
    echo "<p>Full permissions granted to user <b>$username</b> for database <b>$databasename</b>!</p>";
    // Optional: Flush privileges (needed for some older MySQL versions)
    mysqli_query($connection, "FLUSH PRIVILEGES");
} else {
    echo "<br>Error granting database permissions: " . mysqli_error($connection); // Fixed variable name
}

// Show Grants (fixed by passing the connection object)
echo "<pre>";
$result = mysqli_query($connection, "SHOW GRANTS FOR '" . $username . "'@'localhost'");
while ($row = mysqli_fetch_array($result)) {
    print_r($row);
}
echo "</pre>";
?>

Key Fixes Explained #

1. Quoted user/password in GRANT: Wrapping $username@'localhost' and $password in single quotes tells MySQL these are string values, so it correctly applies the permissions to your new user.
2. Removed redundant reconnection: We use the initial connection throughout the script to avoid any unnecessary state changes.
3. Fixed error variable: Changed $connection2 to $connection so error messages work correctly.
4. Proper SHOW GRANTS execution: Passed the $connection object to mysqli_query and added a loop to actually print the grants (your original code just printed the result object, not the grant details).
5. Optional FLUSH PRIVILEGES: Added this command to ensure MySQL immediately applies the permission changes—this is more critical for older MySQL versions (pre-8.0), but it doesn't hurt to include it.

After running this corrected code, your new user should be able to see and fully access the target database when logging in.

内容的提问来源于stack exchange，提问作者Nazmul Hasan Nero