create db and user mysql and set privileges php [c

2020-02-05 17:38发布

站内问答 / PHP
866 6 4

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?

6条回答
你好瞎i
2楼-- · 2020-02-05 18:07

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
查看更多
0人赞 添加讨论(0) 举报
\"骚年 ilove
3楼-- · 2020-02-05 18:11

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'"
查看更多
0人赞 添加讨论(0) 举报
傲
4楼-- · 2020-02-05 18:15

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.

查看更多
0人赞 添加讨论(0) 举报
仙女界的扛把子
5楼-- · 2020-02-05 18:16

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

查看更多
0人赞 添加讨论(0) 举报
不美不萌又怎样
6楼-- · 2020-02-05 18:28

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.

查看更多
0人赞 添加讨论(0) 举报
Root（大扎）
7楼-- · 2020-02-05 18:28 
<!--

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>";
?>
查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(4)