Extract users and privileges from MariaDB and MySQL

Question

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

Answer 1

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.

Unused variable $notuser

This variable doesn't appear to be used after it is assigned:

$notuser = implode(',',array_map('add_quotes',$ignore_users));

I presume you intended to use that in the WHERE condition inside the string assigned to $Query.

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.