# Extract users and privileges from MariaDB and MySQL

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 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';


# General thoughts

The script looks decent. I like how most lines have sufficient spacing that lends itself to readability and there are ample comments throughout the code.

It is good that the script exits early when it cannot connect to the database or cannot open a file for writing. Initially I thought about suggesting that file_put_contents() be used instead of fopen(), fwrite() and fclose() but that would require dramatic changes to the structure of the script and might lead to queries being run before the file paths could be checked.

# Suggestions

## Constants

While it appears you intend to have users of this script modify them, $useroutfile and $grantoutfile could be declared as constants, since the value is never re-assigned. The same would also apply to the values stored in variables for the database connection info (e.g. $dbuser, $dbpassword).

Another thing to consider for those values is to get them from command line arguments or else user input.

## Database Server name hard-coded

The value for $dsn contains localhost for the host name: $dsn = 'mysql:host=localhost;charset=utf8mb4';


Some users might need to modify a database server other than localhost. It may be wise to support a different host name with a variable/constant that can be configured at the top of the script.

## Use consistent variable naming patterns

I see most variables are in all lowercase, but there are a couple outliers:

• $ignore_users • $Query

It is best to stick to a common convention for naming variables.

• Thanks very much Sam. I changed the variables to constants as suggested and added the host name as a constant so it may be changed too. Totally missed that $notuser and removed it. It was a leftover and I had moved what was being created into the actual query itself. Changed the case of $Query too. :) – Dave Mar 6 '20 at 9:54
• I had considered supporting command line options but could not justify, in my mind, the additional code required to do that. This isn't something that is likely to be used frequently so I made the decision to have it be modified if needed more than once. – Dave Mar 6 '20 at 9:56