0

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?

Community
  • 1
  • 1
asb
  • 11
  • 1
  • >The user which is use for connection is cPanel user so must be having full privileges< **but this user has not the privileges** – donald123 Oct 31 '14 at 08:27
  • You [shouldn't use mysql_* functions in new code](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use PDO or MySQLi. – Daniel Gelling Oct 31 '14 at 08:28
  • @Daniel, Yeah I know it was deprecated but this is not a point here. I need to drop user using PHP. – asb Oct 31 '14 at 09:31
  • @donald123, then could you please tell how do I delete/drop user using PHP? – asb Oct 31 '14 at 09:31

1 Answers1

0

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;
Harley
  • 151
  • 5
  • Is this command will work if "abc" is cPanel user and not a database user? – asb Oct 31 '14 at 09:33
  • It giving me following error, Access denied for user 'abc'@'localhost' (using password: YES) – asb Oct 31 '14 at 09:39
  • 1.The 'abc' must be a database user; 2.This statement must run by a database user who have 'create user' privileges, practically it means the 'root' user. – Harley Oct 31 '14 at 10:05
  • So I tried with other user which is database user and having full Privileges still it saying "Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation". – asb Oct 31 '14 at 10:10
  • Run the statement by root user and try again. – Harley Oct 31 '14 at 10:16
  • Correct if I'm wrong, As per my knowledge cPanel user is a root user for database as I can connect to database with these cpanel user credentials. – asb Oct 31 '14 at 10:44
  • I never used cPanel and not actually knowing the cPanel user system. As my knowledge, if cPanel provided you a environment as a virtual host, or an independent MySQL instance, you should get an own root user for MySQL. Else you should read the documents provided by cPanel or contract the cPanel customer service. – Harley Nov 03 '14 at 09:23
  • thanks for your response, what I understood in this 2 days is, if you are using Shared Hosting service then your cPanel user don't have access to drop database user (though funny part is it can drop database) as it don't have CREATE or DELETE permission. – asb Nov 03 '14 at 10:57