12

Is there a way to create a new MySQL database, a new MySQL user and give the new user privileges on the new database all using PHP?

skaffman
  • 398,947
  • 96
  • 818
  • 769
user1218364
  • 153
  • 1
  • 3
  • 7

6 Answers6

12

You could do something like this:

mysql_connect('localhost','user',password);
mysql_query("CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';");
mysql_query("GRANT ALL ON db1.* TO 'username'@'localhost'");
mysql_query("CREATE DATABASE newdatabase");
mysql_close();

You may look at the MySQL documentation on GRANT and CREATE USER

Fabian
  • 3,465
  • 4
  • 34
  • 42
8

Yes, as all these actions can be performed by regular SQL queries.

However, I'd refrain from running PHP scripts with database connection from root user.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • 7
    +1 Use a dedicated account that is only able to create databases and users, because it prevents accidents and provides damage limitation if the account is compromised. It can't drop anything, and has no access to data. – Dan Blows Mar 16 '12 at 11:54
6

If you are hosting your project on CPanel, then the mysql_query method will not work to create databases, users, and to grant permissions.

You have to use the XML Api for CPanel.

<?php
include("xmlapi.php");

$db_host = 'yourdomain.com'; 
$cpaneluser = 'your cpanel username';
$cpanelpass = 'your cpanel password'; 

$databasename = 'testdb';
$databaseuser = 'test'; // Warning: in most of cases this can't be longer than 8 characters
$databasepass = 'dbpass'; // Warning: be sure the password is strong enough, else the CPanel will reject it

$xmlapi = new xmlapi($db_host);    
$xmlapi->password_auth("".$cpaneluser."","".$cpanelpass."");    
$xmlapi->set_port(2082);
$xmlapi->set_debug(1);//output actions in the error log 1 for true and 0 false  
$xmlapi->set_output('array');//set this for browser output  
//create database    
$createdb = $xmlapi->api1_query($cpaneluser, "Mysql", "adddb", array($databasename));   
//create user 
$usr = $xmlapi->api1_query($cpaneluser, "Mysql", "adduser", array($databaseuser, $databasepass));   
 //add user 
$addusr = $xmlapi->api1_query($cpaneluser, "Mysql", "adduserdb", array("".$cpaneluser."_".$databasename."", "".$cpaneluser."_".$databaseuser."", 'all'));
?>

Download xmlapi.php from here, or simply search on google for it.

This is what worked for me perfectly.

Tamás Pap
  • 17,777
  • 15
  • 70
  • 102
  • Database Created Successfully as well as user also created successfully but the newly created user not assign to the database. –  Apr 11 '15 at 06:39
  • you can download the xmlapi.php from here : https://github.com/CpanelInc/xmlapi-php/blob/master/xmlapi.php ( download link in the answer is moved ) – sudip Aug 02 '17 at 20:12
1

i will suggest you to use phpmyadmin.

you need to do steps:

  1. open phpmyadmin
  2. go to admin section
  3. hit on add user account
  4. put user name and password
  5. set privileges
  6. hit the [ go ]button

that's all see in action on youtube [ click here ]

in case if you want to know more about phpMyadmin go with official

but if there is any special reason to do so with php the here is the sql commend 

CREATE USER 'tesrytss'@'%'//user name
IDENTIFIED VIA mysql_native_password USING '***';.//set pass
GRANT SELECT, INSERT, UPDATE, DELETE, FILE ON *.* TO 'tesrytss'@'%' // previlages and username and location
REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0; //other requerment
doppelgreener
  • 4,809
  • 10
  • 46
  • 63
insCode
  • 1,227
  • 14
  • 10
-1

open PHP myadmin or MySql workbench go to query window then run below query

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON db1.* TO 'username'@'localhost'"
doppelgreener
  • 4,809
  • 10
  • 46
  • 63
Aravinthkumar Kandasamy
  • 1
  • 4
-3
<!--

Go to setup on line 48
Created by kierzo@kierzo.com

-->

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<meta content="en-gb" http-equiv="Content-Language" />
<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
<title>Create Database</title>
</head>

<body>




<!-- Title -->
<p><h1>Create Database</h1></p>
<p></p>
<p>


<!-- The form -->
<form action="<?php $currentFile = $_SERVER["PHP_SELF"];$parts = Explode('/', $currentFile);echo $parts[count($parts) - 1];?>" method="post">

Database Name: <input name="databasename" type="text" />
<br>
DB Pass: <input name="dbpass" type="text" />
<br>
Admin Pass: <input name="passbox" type="password" />

<p><input name="Submit1" type="submit" value="submit" /></p>

</form>

<!-- end form -->

</p>

</body>

</html>



<?php

//*********************** CONFIG SETUP ************************************//
// Created by kierzo@kierzo.com
//
// set the admin pass for the page
$adminpass = "*******";   // change ******* with your page pass
//
// set mysql root pass
$mysqlRootPass = "*******";  // change ******* with your mysql root pass
//
//
//*********************** CONFIG SETUP ************************************//


// if isset set the varibables

if(isset($_POST["passbox"]) && ($_POST["databasename"])){


$databasename = $_POST["databasename"];
$password = $_POST["passbox"];
$dbpass = $_POST["dbpass"];

}
else { exit;}

if(($password) == ($adminpass)) {


}
else {

echo "Incorrect Password!";

exit;}

// store connection info...

$connection=mysqli_connect("localhost","root","$mysqlRootPass");


// check connection...

if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }


  // Create database
  echo "<br><br>";
#echo "$sql";
$sql="CREATE DATABASE $databasename";

if (mysqli_query($connection,$sql))
  {
  echo "<h2>Database <b>$databasename</b> created successfully!</h2>";
  }
else
  {
  echo "Error creating database: " . mysqli_error($con);
  }


    // Create user

$sql='grant usage on *.* to ' . $databasename . '@localhost identified by ' . "'" . "$dbpass" . "'";
echo "<br><br>";
#echo "$sql";
if (mysqli_query($connection,$sql))
  {
  echo "<h2>User Created... <b>$databasename</b> created successfully!</h2>";
  }
else
  {
  echo "Error creating database user: " . mysqli_error($con);
  }


      // Create user permissions

$sql="grant all privileges on $databasename.* to $databasename@localhost";
echo "<br><br>";
#echo "$sql";
if (mysqli_query($connection,$sql))
  {
  echo "<h2>User permissions Created... <b>$databasename</b> created successfully!</h2>";
  }
else
  {
  echo "Error creating database user: " . mysqli_error($con);
  }

  echo "<p>Database Name: $databasename</p>";
  echo "<p>Database Username: $databasename</p>";
  echo "<p>Database Password: $dbpass</p>";
  echo "<p>Database Host: localhost</p>";
?>
kierzo
  • 113
  • 4