0

I run a small web development company and we run multiple production servers, each with their own MySQL database server. I'm trying to figure out the best way of giving access to these separate MySQL instances to my team.

I've set up phpMyAdmin on one server, and setting up the different DB hosts is pretty straightforward on the face of it, using cookie based auth to log in to all of them.

The problem with this approach is that we'll then need to maintain user accounts for each team member on each server. This then creates a headache for password management, as well as on- and off-boarding of employees.

What I want to do is to use the signon authentication provided by phpMyAdmin so we only have to maintain a single set of user privileges. It feels like we should be able to use the framework of our existing PHP apps to do this, and I've been digging around in the example signon.php and signon-script.php files to try to make this work.

I have so far failed. So my questions are:

  1. Has anyone got experience of setting this up and can point me to the best way of doing it, or some better documentation than phpMyAdmin provides?
  2. Does anyone know a good open source project that will provide this functionality as a little stand-alone PHP application?

I also thought for a bit about using config based auth and then putting Apache's HTTP Basic Auth on top of that, but the drawbacks of that are well documented. Any suggestions appreciated.

james-geldart
  • 101
  • 2
  • afaik phlmyadmin supports LDAP, moreover how do you basically manage for now the users? – djdomi Jun 10 '22 at 16:47
  • I don't think it does support LDAP, unless there's a plugin I can't find? MySQL itself does, but only in the enterprise edition not the community edition (https://dev.mysql.com/doc/refman/8.0/en/ldap-pluggable-authentication.html). We currently have individual credentials for each server that we each need access to. It's far from ideal... – james-geldart Jun 13 '22 at 08:48
  • i think you wanna look for [this tutorial](https://www.digitalocean.com/community/tutorials/how-to-install-and-configure-openldap-and-phpldapadmin-on-ubuntu-16-04) in example, that show how phpadmin might work together - however since phpmyadmin is a admin panel i think it might be not on topic for server fault – djdomi Jun 13 '22 at 11:44
  • That's how to install phpLDAPadmin, which is a totally different product from phpMyAdmin – james-geldart Jun 16 '22 at 14:12

1 Answers1

0

So I mostly solved my problem. There is still a bug that you have to log out and back in again to switch servers. But otherwise it works, using the first named server for login credentials (which might actually not be the best way of doing it from a security perspective). Anyway, here's some code, this goes in a new file located at signon/index.php:

<?php

include '../config.inc.php';

$session_name = $cfg['Servers'][1]['SignonSession'];
session_set_cookie_params(0, '/', '', false, true);
session_name($session_name);

@session_start();

if(@$_SESSION['PMA_single_signon_user'] && !@$_GET['logout']) {
    header('Location: ../index.php');
    exit;
}
else {
    $_SESSION = [];
}

$error = "";

if($_POST) {
    
    //try to log in to the primary server with the credentials supplied
    $mysqli = mysqli_connect($cfg['Servers'][1]['host'], $_POST['username'], $_POST['password']);
    
    if($mysqli) {
        //find the host
        foreach($cfg['Servers'] as $svr) {
            if(@$svr['host'] == $_POST['host']) break;
        }
        
        /* Store the credentials */
        $_SESSION['PMA_single_signon_user'] = $svr['signon_user'];
        $_SESSION['PMA_single_signon_password'] = $svr['signon_password'];
        $_SESSION['PMA_single_signon_host'] = $_POST['host'];
        $_SESSION['PMA_single_signon_port'] = '';
        /* Update another field of server configuration */
        $_SESSION['PMA_single_signon_cfgupdate'] = ['verbose' => $svr['verbose'] ?? $svr['host'], 'host' => $svr['host']];
        $_SESSION['PMA_single_signon_HMAC_secret'] = hash('sha1', uniqid(strval(random_int(0, mt_getrandmax())), true));
        $id = session_id();
        /* Close that session */
        @session_write_close();
        
        /* Redirect to phpMyAdmin (should use absolute URL here!) */
        header('Location: ../index.php');
        exit;
    }
    else {
        $error = "<p>The supplied credentials are not valid for the primary server</p>";
    }
}
print <<<abc
        <form method="post">
            {$error}
            <table>
                <tr>
                    <th>Username: </th>
                    <td><input name="username" type="text"></td>
                </tr>
                <tr>
                    <th>Password: </th>
                    <td><input name="password" type="password"></td>
                </tr>
                <tr>
                    <th>Host: </th>
                    <td><select name="host">
abc;
    foreach($cfg['Servers'] as $i => $c) {
        print "<option value=\"{$c['host']}\">" . (@$c['verbose'] ?? $c['host']) . "</option>\n";
    }
print <<<abc
                    </select></td>
                </tr>
            </table>
            
            <input type="submit" value="Log in">
        </form>
abc;

You will need the following config settings for each server:

$cfg['Servers'][$i]['signon_user'] = 'user for this server';
$cfg['Servers'][$i]['signon_password'] = 'password for this server;
$cfg['Servers'][$i]['SignonSession'] = 'SignonSession';
$cfg['Servers'][$i]['SignonCookieParams'] = [];
$cfg['Servers'][$i]['SignonURL'] = 'signon/?server=' . $i;
$cfg['Servers'][$i]['LogoutURL'] = 'signon/?logout=1';

Suggestions for improvements welcome!

james-geldart
  • 101
  • 2