CODEWITHSUNDEEP
phpmysqlsql-delete
Oumaya
Oumaya

Reputation: 655

Drop user from MySQL doesn't work

I am trying to delete a user from MySQL server : 

$conn = new PDO("mysql:host=$host;port=$port;dbname=mysql;charset=utf8","$adminname", "$pass",array( PDO::ATTR_PERSISTENT => true));
$sql_deleteuser="DELETE FROM `mysql`.`user` WHERE `user`.`User` = '$username'";                 
//$sql_deleteuser="SELECT `User` FROM `mysql`.`user`";  
$PDOStatement3=$conn->prepare($sql_deleteuser);
//$PDOStatement3->bindParam(':username', $username, PDO::PARAM_STR);   
$res_exec=$PDOStatement3->execute();

but it doesn't work and no error in errorInfo() , I tried

$sql_deleteuser="DROP USER '$username'@'%'";

and

$sql_deleteuser="IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'$username') DROP USER [$username]"; same result.

The connection was fine I test it with a SELECT query and it works.

Did I miss something. Any help will be appreciated.

Upvotes: 0

Views: 2517

Answers (2)

Anil
Anil

Reputation: 3752

try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

//Edit: Use REVOKE to revoke the privileges. This removes rows for the account from all the grant tables except the user table, and revokes any global privileges listed in the user table.

// sql to delete a record, use $host to remove a specific user
$sql = "DELETE FROM `mysql`.`user` WHERE `user`.`User` = '$username" AND `user`.`Host` = '$host";


// use exec() because no results are returned
$conn->exec($sql);
echo "Record deleted successfully";
}
catch(PDOException $e)
{
echo $sql . "<br>" . $e->getMessage();
}

try with above code, you will find also out the error!

Upvotes: 0

Zelldon
Zelldon

Reputation: 5516

Either you have not the priviliges / rights to drop a user

The DROP USER statement removes one or more MySQL accounts. To use it, you must have the DELETE privilege for the mysql database

OR

perhaps the problem is that the user has a open connection because the mysql doc says:

Important

DROP USER does not automatically close any open user sessions. Rather, in the event that a user with an open session is dropped, the statement does not take effect until that user's session is closed. Once the session is closed, the user is dropped, and that user's next attempt to log in will fail. This is by design.

How to kill the connections see this answer:

how to kill mySQL connections

See DOC:

https://dev.mysql.com/doc/refman/5.0/en/drop-user.html

Upvotes: 1

Related Questions