Welcome to my website


My name is Richard Watson and I'm a professional PHP developer
from Lurgan in Northern Ireland


Adding users to MySQL and creating new databases

Posted by richard
News | 02-02-2016


bootstrap mariadb php phpmyadmin mysql

As part of creating Imprete, I had the idea of creating/managing sub sites (e.g. clan hosting). While incomplete, what I did finish was how to create user accounts for MySQL while creating security trimmed databases linked to the same username.

This does require Bootstrap for the design (included in code snippet) and some basic modification but it's essentially a complete, standalone script.

<!doctype HTML>
<html>
<head>
<!-- Bootstrap because I'm lazy -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/css/bootstrap.min.css">
<title>Database account management</title>
</head>
<body style="background: #6a7a88;">
<?php class DbManager
{
public $db = NULL;

function __construct()
{
// Only calls methods on post
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
$this->dbconnect();
$this->createDatabaseAndUser();
}
}

public function dbconnect() {
// Set up PDO connection
// User requires full access in order to modify MYSQL table
define("DB_HOST", "server");
define("DB_NAME", "test");
define("DB_USER", "richard");
define("DB_PASS", "password");

$this->db = new PDO('mysql:host='.DB_HOST.'; dbname='.DB_NAME, DB_USER, DB_PASS);
}

public function createDatabaseAndUser()
{
// Basic post data check, tweak as you see fit
if (isset($_POST['submit']) AND $_POST['user'] != "" AND $_POST['pass'] != "") {
$user = strip_tags(html_entity_decode(urldecode(trim("$_POST[user]"))));

// Add remote account
$sql = "INSERT INTO mysql.user VALUES (:host, :usr, PASSWORD(:pass), :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, '','','','', 0, 0, 0, 0, '', '')";
$query = $this->db->prepare($sql);
$query->bindValue(':usr', $_POST['user']);
$query->bindValue(':pass', $_POST['pass']);
$query->bindValue(':host', "%");
$query->bindValue(':n', "N");
$query->execute();

// Create randomly generated password
$pass = "";
$salt_chars = array_merge(range('A','Z'), range('a','z'), range(0,9));

for($i=0; $i < 8; $i++) {
$pass .= $salt_chars[array_rand($salt_chars)];
}

// Add localhost account (required for PHPMYADMIN sign-in)
$sql = "INSERT INTO mysql.user VALUES (:host, :usr, PASSWORD(:pass), :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, :n, '','','','', 0, 0, 0, 0, '', '')";
$query = $this->db->prepare($sql);
$query->bindValue(':usr', $user);
$query->bindValue(':pass', $_POST['pass']);
$query->bindValue(':host', "localhost");
$query->bindValue(':n', "N");
$query->execute();

// Create a test database for user - ensure special chars and tags are removed
$dbname = "test_".$user;

// What are you doing?! Why is $this->dbname not bound?
// Because see the following: http://us3.php.net/manual/en/book.pdo.php#69304
// Table and Column names cannot be replaced by parameters in PDO.
$sql = "CREATE DATABASE IF NOT EXISTS " . $dbname;
$query = $this->db->prepare($sql);
$query->execute();
$count = $query->rowCount();

if ($count == 1) {
echo "<div class='alert alert-success'>Success: Created database</div>";

// Grant user privledges to created database
$sql = "INSERT INTO mysql.db VALUES (:host, :dbname, :usr, 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y')";
$query = $this->db->prepare($sql);
$query->bindValue(':usr', $_POST['user']);
$query->bindValue(':dbname', $dbname);
$query->bindValue(':host', "%");
$query->execute();
}
}
}
}

// Call class
$create = new DbManager();
?>

<div class="container" style="width: 50%; margin-top: 150px;">
<div class="row">
<div class="panel panel-default">
<div class="panel-heading">Database account manager</div>
<div class="panel-body">

<div class="well">
<h1 class="text-primary" style="margin: 0;">Instructions</h1>
<p class="text-success" style="margin: 0;">Create a new user account on your MySQL server; A new database linked to the username (Called test_%username%) will be automatically
created and the new user account granted full access to the new database only, as well as access to PHPMyAdmin. The user will be able to access the DB remotely.</p>
</div><br>

<form method="post" action="createdb.php">
<input type="text" name="user" class="form-control" placeholder="Username..." required><br>
<input type="text" name="pass" class="form-control" placeholder="Password..." required><Br><br>
<button name="submit" value="Add user" class="btn btn-default"><span class="glyphicon glyphicon-user"></span> Add user</button>
</form>
</div>
</div>
</div>
</div>
</body>
</html>


Play around with it and let me know what you think.


You are not signed in


Register or sign-in to post comments
Twitter
Popular tags

certificates

XML

tag

design

check-in

Laravel

lightbox

types

Test

project

files

phpmyadmin