This was born out of a need to create database users on a new server to allow views to be created correctly when restoring databases. This is designed to be run from a command line NOT via a web server. It works with MySQL and MariaDB.
It creates 2 files, one that contains the grants to create the users and a second that creates the grants specific to databases when users only have access to specific commands and/or databases. A sample of each of the output files is included after the code.
I'm just looking for another set of eyes (or several dozen) to see if there is anything that I might be able to do to improve it.
<?php
declare(strict_types=1);
ini_set('display_errors','1');
ini_set('display_startup_errors','1');
error_reporting(E_ALL);
//
// You must modify the 4 variables below for your environment
//
$dbuser = 'root'; // DB user with authority to SHOW GRANTS from mysql.user
$dbpassword = 'password'; // password for the DB user
$useroutfile = '/temp/Users.sql'; // where to write the user file that may be imported on new server
$grantoutfile = '/temp/Grants.sql'; // where to write the grant file that may be imported on new server
$ignore_users = ['root']; // array of users that should NOT be exported
//
// No reason to modify anything below this comment
//
$dsn = 'mysql:host=localhost;charset=utf8mb4';
$opt = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION ,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC ,
PDO::ATTR_EMULATE_PREPARES => true ,
];
try {
$ourdb = new PDO ($dsn,$dbuser,$dbpassword,$opt);
} catch (PDOException $e) {
error_log('Error ' . $e->getCode() . ' on line ' .
$e->getLine() . ' in ' .
$e->getFile() . ' -> ' .
$e->getMessage()); // log the error so it may be looked at later
echo 'Could not connect to the SQL server';
exit;
} // end of the try/catch block
$notuser = implode(',',array_map('add_quotes',$ignore_users));
//
// We got connected to the database so now let's make sure we can open the
// output files for writing - note that using mode w will overwrite any
// existing files so we'll always start off cleanly
//
$userout = fopen($useroutfile,'w');
if ($userout === false) { // could not open the output file for writing for some reason
echo 'Could not open the output file for writing (' . $useroutfile . ')';
error_log('Could not open the output file for writing (' . $useroutfile . ')');
exit;
} // end of if we could not open the output file for writing
$grantout = fopen($grantoutfile,'w');
if ($grantout === false) { // could not open the output file for writing for some reason
echo 'Could not open the output file for writing (' . $grantout . ')';
error_log('Could not open the output file for writing (' . $grantout . ')');
exit;
} // end of if we could not open the output file for writing
$Query = $ourdb->query("
SELECT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';') AS query
FROM mysql.user
WHERE user NOT IN(" . implode(',',array_map('add_quotes',$ignore_users)) . ")
");
$users = $Query->fetchAll(PDO::FETCH_COLUMN);
foreach ($users as $GrantQ) { // go through each of the users found
$UserQ = $ourdb->query("$GrantQ"); // retrieve the grants for a user
$grants = $UserQ->fetchAll(PDO::FETCH_COLUMN);
foreach ($grants as $grant) { // go through each of the grants found for this user
if (stripos($grant,'IDENTIFIED BY PASSWORD') === false) {
fwrite($grantout,$grant . ';' . PHP_EOL);
} else {
fwrite($userout,$grant . ';' . PHP_EOL);
}
} // end of foreach through the grants found
} // end of foreach through the queries to show the grants for each user
fwrite($userout ,'FLUSH PRIVILEGES;' . PHP_EOL); // make sure SQL knows about the new users and privileges
fwrite($grantout,'FLUSH PRIVILEGES;' . PHP_EOL); // make sure SQL knows about the new users and privileges
fclose($userout); // close our output file
fclose($grantout); // close our output file
echo 'The grants for ' . count($users) . ' users were written to ' . $useroutfile . PHP_EOL;
function add_quotes($str) {return sprintf("'%s'", $str);}
Example of the Users.sql file that is created.
GRANT SELECT ON *.* TO 'blah1'@'localhost' IDENTIFIED BY PASSWORD '*73259F6BF87CF1DD3D9B6070AD869CA8972ACA23';
GRANT ALL PRIVILEGES ON *.* TO 'blah2'@'localhost' IDENTIFIED BY PASSWORD '*3BE30E0E232031AD5ED5B33C6A96EB1357A533AD' WITH GRANT OPTION;
GRANT SELECT, INSERT, UPDATE, CREATE TEMPORARY TABLES ON *.* TO 'blah3'@'localhost' IDENTIFIED BY PASSWORD '*2F1649FADAF4E72546D4AE902AE3FABD9AA047EF';
Example of the Grants.sql file that is created.
GRANT ALL PRIVILEGES ON `abcdb`.* TO 'user'@'10.%';
GRANT SELECT, CREATE TEMPORARY TABLES, SHOW VIEW ON `efgdb`.* TO 'user'@'10.%';
GRANT DELETE ON `hijdb`.`table1` TO 'user1'@'localhost';