I'm trying to DROP database and its user using PHP. Though I could able to DROP database but failed to DROP user.
Following command which I have tried,
$cpanel_user = 'abc';
$cpanel_user_password = 'xyz';
$conn = mysql_connect($dbhost = 'localhost', $cpanel_user , $cpanel_user_password );
$sql = 'DROP DATABASE dbname';
$retval = mysql_query( $sql, $conn ); // works perfect
$drop_user = "DROP USER 'username'@'localhost'";
$retval = mysql_query( $drop_user , $conn );
Unfortunately it showing "Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation".
The user which is use for connection is cPanel user so must be having full privileges.
I refereed other threads like, MySQL: Check if the user exists and drop it but no luck.
Do I missing anything?
You should grant 'CREATE USER' Privileges to the user you used to connect to the database.
As your code, you should run this statement by the 'root' user:
GRANT CREATE USER ON *.* TO abc;