Drop user from MySQL doesn't work

Question

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.

Yes I have , the connection was already made with an admin account — Oumaya, Apr 21 '15 at 10:56
you can check error by setting PDO error mode to exception $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); by the way $conn->exec($sql_deleteuser); should also work, no need to prepare! — Anil, Apr 21 '15 at 10:56
`sys.database_principal` this is sql server change your tags — Mihai, Apr 21 '15 at 11:03
in fact it is a privilege issue as I get `Syntax error or access violation` , but I have `ALL PRIVILEGES` for the admin as `SuperUser localhost global ALL PRIVILEGES Yes ` for `user` table. — Oumaya, Apr 21 '15 at 11:06

score 1 · Accepted Answer · edited May 23 '17 at 11:51

1

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

edited May 23 '17 at 11:51

Community

1
1

answered Apr 21 '15 at 11:00

Zelldon

4,988
3
31
41

Yes it is privileges issue, despite of having `ALL PRIVILEGES` for `user` table the user admin cannot execute `DELETE` or `DROP` – Oumaya Apr 21 '15 at 11:19
have you checked the open connections ? – Zelldon Apr 21 '15 at 11:19
Yes I didn't open any connection with the user target account, is there a way to force closing it ? – Oumaya Apr 21 '15 at 11:20
see my edit post and do you tried the droping with the root user or a use with only root like privileges? if second try it with the root user. – Zelldon Apr 21 '15 at 11:32
Yes it drop it with root user, how to make it with my admin user he has the same privileges as the root !!! – Oumaya Apr 21 '15 at 11:47
1

As a solution I revoked all privileges from my user instead of dropping it completely and tell the admin to drop it manually later if needed. – Oumaya Apr 22 '15 at 13:12

Anil · Answer 2 · 2015-08-26T07:28:00.247

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!

First, you're not escaping '$username'; second you should be dropping another user account and not the one you're using to connect to the db. Third, in the your exec call you're not checking the return value...and just assuming success. — Svetoslav Marinov, Aug 24 '15 at 07:34

Drop user from MySQL doesn't work

2 Answers2