MySQL创建用户和授予权限脚本

Question

因此，我试图使用mysql和php以及html和css在我的本地主机上建立一个简单的成员站点。我正在使用一个类来尝试保持我的php OOP称为DbConnection，它具有选择、插入、更新和删除以及从数据库连接和断开连接的方法。我没有问题，建立一个连接和插入用户到我的特定表。我的问题在于授予新创建的用户创建、删除、插入或更新任何数据的权限。我的grantUserStandardPermissions()函数中有错误吗？还是不允许运行多个查询？

我的数据库: db_test表:用户、产品、订单、愿望列表

我想将这些用户存储在我的db_test中的User表中。不在我的mysql.user桌子上！

请帮帮忙！

以下是我所拥有的：

我使用以下函数连接到数据库：

   /*=================================================================*/
//
// Function creates the connecton to the database   
public function connect(){

    // If there is no connection already in existence
    if(!$this->con){

    // Create the Connection to the database, the object oriented way
     $this->connection = @mysql_connect($this->IP_ADDRESS, $this->DB_USER, $this->DB_PASSWORD, $this->DB_NAME) or die(@mysqli_err());

        // If the connection was successful
        if($this->connection){

            // Connect to the Body Block Database
            $select_database = @mysql_select_db($this->DB_NAME, $this->connection);

            // If the Body Block database was selected
            if($select_database){

                // Connection was successful alert the user
                echo 'Connected to the Database<br/>';

                // Set the Connection to true
                $this->con = true;

                // Return tre
                return true;

            // Else the database was not selected    
            }else{

                // Could not find the database
                echo 'The Database does not Exist<br/>';

                // Return false
                return false;              
            }

        // Else, the connection was unsuccessful    
        }else{

            // Unable to connect, server problem
            echo 'Unable to make a connection.<br/>';

            // Return false
            return false;          
        }
    // There is a connection already    
    }else{

        // Already connected
        echo 'A connection to the database already exists.<br/>';

        // Return true
        return true;         
    }
} // End connect()

然后，我插入一个具有以下功能的用户：

/*=================================================================*/
//
// Function creates a new row with the passed in data
public function insert($table_name, $values, $rows = null){

    // If the table exists
    if($this->tableExists($table_name)){

        // The query statement to run is created
        $this->query = 'INSERT INTO ' .$table_name;

        // If rows where passed in
        if($rows != null){

            // implode the rows array at a comma
            $rows = implode(',', $rows);

            // Append the rows to the query
            $this->query .= ' ('.$rows.')';

        }

            // Loop throught the values we want to insert
            for($i = 0; $i < count($values); $i++){

                // If the values are strings
                if(is_string($values[$i])){

                    // Set the values as strings to insert
                    $values[$i] = '\''.$values[$i].'\'';
                }
            }

            // Implode the values where there is a ,
            $values = implode(',',$values);

            // Create the insert query to run on the database
            $this->query .= 'VALUES ('. $values.')';

            echo "$this->query"."<br/>";

            // Run the query against the database
            $result = mysql_query($this->query);

            // If there is a result
            if($result){

                // Echo the Success
                echo 'Success';

                // Return true
                return true;

            // Else there is no result    
            }else{

                // Return false
                return false;
            }
     // Else the table does not exist   
    }else{

        echo 'Failed to find table!';

        // Return false
        return false;
    }
}// End insert()

然后，我尝试使用以下函数授予该用户权限：

UPDATE --我删除了这个函数，使用我的all access管理员创建了用户，然后创建了第二个管理员来处理CRUD，从而为用户查询用户表，试图执行这些操作。

 //=============================================================================
// Grant the permissions for the new User
public function grantUserStandardPermissions($username, $password){
    $grantPermission = "GRANT SELECT, INSERT, UPDATE, DELETE ON db_test.* TO ";
    $grantPermission .= $username;
    $grantPermission .= " @'%' IDENTIFIED BY '". $password. "'";

    echo "$grantPermission";

    $result = mysql_query($grantPermission);

    if($result){
        mysql_query("FLUSH PRIVELAGES");
        echo 'ran command';
        return true;
    }else{
        return false;
    }

}

Answer 1

初始化MySQL db时，我运行：

SET sql_safe_updates=0;

delete from mysql.user where user = '';
commit;

CREATE SCHEMA `petshop` DEFAULT CHARACTER SET utf8;

CREATE USER `petshop-dml` identified by 'petshop-dml123';

GRANT SELECT ON petshop.* TO `petshop-dml`;
GRANT INSERT ON petshop.* TO `petshop-dml`;
GRANT UPDATE ON petshop.* TO `petshop-dml`;
GRANT DELETE ON petshop.* TO `petshop-dml`;
GRANT EXECUTE ON petshop.* TO `petshop-dml`;

//optionally create your own user, that executes DDLs:
//CREATE USER `petshop-admin` identified by 'petshop-admin123';
//GRANT ALL PRIVILEGES ON *.* TO `petshop-admin`@'%';

根据您的需要调整模式、密码和用户名，并执行它，例如通过PHP管理或任何您喜欢的工具:)

然后在应用程序中使用已创建的用户(在本例中为petshop-dml)插入、更新、删除、查询。