Skip to content

Instantly share code, notes, and snippets.

@tanakaedu
Created February 13, 2020 14:09
Show Gist options
  • Save tanakaedu/53170166a36d045c1b06e6c06291e532 to your computer and use it in GitHub Desktop.
Save tanakaedu/53170166a36d045c1b06e6c06291e532 to your computer and use it in GitHub Desktop.
MySQLにユーザーを作成して、そのユーザー専用のデータベースの作成とGRANT設定をするPHPスクリプト。
<?php
define("user", "root");
// TODO: rootpassを実際のrootユーザーのパスワードに変更
define("passwd", "rootpass");
// TODO: 設定したいユーザー名とパスワードを以下に設定
$Users = array(
array('uid'=>'user_01', 'passwd'=>'pwd1'),
array('uid'=>'user_02', 'passwd'=>'pwd2'),
array('uid'=>'user_03', 'passwd'=>'pwd3'),
);
$dbh = new PDO('mysql:host=127.0.0.1', user, passwd);
// ユーザー作成
$createUser = $dbh->prepare("CREATE USER IF NOT EXISTS ?@'%' IDENTIFIED BY ?");
$createUser->bindParam(1, $uid);
$createUser->bindParam(2, $passwd);
foreach($Users as $user) {
$uid = $user['uid'];
$passwd = $user['passwd'];
$createUser->execute();
}
// データベース作成
try {
foreach($Users as $user) {
$sql = "CREATE DATABASE IF NOT EXISTS ".$user['uid']." CHARACTER SET utf8mb4 collate utf8mb4_bin;";
$dbh->exec($sql);
}
} catch (PDOException $e) {
die('Create DB failed: ' . $e->getMessage());
}
// 権限設定
try {
foreach($Users as $user) {
$sql = "GRANT ALL ON ".$user['uid'].".* TO '".$user['uid']."'@'%';";
$dbh->exec($sql);
}
} catch (PDOException $e) {
die('Grant failed: ' . $e->getMessage());
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment