All geek questions in one place

Create db and custom mysql and set php privileges

Is there a way to create a new MySQL database, a new MySQL user and provide new user privileges in the new database using PHP?

+6
source share
5 answers

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

However, I would refrain from running PHP scripts with the root user connecting to the database.

+8
source

You can 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 can see the MySQL documentation on GRANT and CREATE USER

+6
source

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

You must use 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 just do a google search for it.

This is what worked great for me.

+5
source

I suggest you use phpmyadmin.

you need to follow these steps:

  • open phpmyadmin
  • go to admin section
  • click to add user account
  • enter username and password
  • set privileges
  • press the [go] button

that everyone see in action on youtube [ click here ]

if you want to know more about phpMyadmin go with the official

but if there is any special reason to do this with php, here is the comm. sql

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
source 
 <!-- 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>"; ?>
-1
source

Source: https://habr.com/ru/post/910798/

More articles:


All Articles