/**
* loop over all mysql databases and create/delete users according to $access_hosts.
*
* This function is called when system.mysql_access_hosts or system.ipaddress is changed
*
* @param array $access_hosts list of hosts from which mysql access should be allowed
*/
function correctMysqlUsers($access_hosts)
{
global $log;
Database::needRoot(false);
$databases_stmt = Database::query("SELECT * FROM `" . TABLE_PANEL_DATABASES . "` ORDER BY `dbserver`");
$current_server = -1;
$flush_privileges = false;
$dbm = null;
while ($dbdata = $databases_stmt->fetch(PDO::FETCH_ASSOC)) {
// next server?
if ($current_server != $dbdata['dbserver']) {
// flush privileges if necessary
if ($flush_privileges) {
$dbm->getManager()->flushPrivileges();
}
// connect to the server which hosts this database
Database::needRoot(true, $dbdata['dbserver'], true);
$dbm = new DbManager($log);
}
// get the list of users belonging to this database
$users = $dbm->getManager()->getAllSqlUsers(false, $dbdata['databasename']);
// compare required access hosts with actual data
foreach ($users as $username => $data) {
$hosts_to_create = $access_hosts;
foreach ($data['hosts'] as $host) {
if (($key = array_search($host, $hosts_to_create)) !== false) {
// host is already in access_hosts, no need to create
unset($hosts_to_create[$key]);
} else {
// host not in access_hosts, remove it
$dbm->getManager()->deleteUser($username, $host);
$flush_privileges = true;
}
}
// create missing host permissions
foreach ($hosts_to_create as $host) {
$dbm->getManager()->grantPrivilegesTo($username, $data['password'], $host, true);
}
}
}
if ($flush_privileges) {
$dbm->getManager()->flushPrivileges();
}
Database::needRoot(false);
}
/**
* This file is part of the Froxlor project.
* Copyright (c) 2003-2009 the SysCP Team (see authors).
* Copyright (c) 2010 the Froxlor Team (see authors).
*
* For the full copyright and license information, please view the COPYING
* file that was distributed with this source code. You can also view the
* COPYING file online at http://files.froxlor.org/misc/COPYING.txt
*
* @copyright (c) the authors
* @author Florian Lippert <*****@*****.**> (2003-2009)
* @author Froxlor team <*****@*****.**> (2010-)
* @license GPLv2 http://files.froxlor.org/misc/COPYING.txt
* @package Functions
*
*/
function correctMysqlUsers($mysql_access_host_array)
{
global $log;
// get sql-root access data
Database::needRoot(true);
Database::needSqlData();
$sql_root = Database::getSqlData();
Database::needRoot(false);
$dbservers_stmt = Database::query("SELECT DISTINCT `dbserver` FROM `" . TABLE_PANEL_DATABASES . "`");
$mysql_servers = '';
while ($dbserver = $dbservers_stmt->fetch(PDO::FETCH_ASSOC)) {
Database::needRoot(true, $dbserver['dbserver']);
Database::needSqlData();
$sql_root = Database::getSqlData();
$dbm = new DbManager($log);
$users = $dbm->getManager()->getAllSqlUsers(false);
$databases = array($sql_root['db']);
$databases_result_stmt = Database::prepare("\n\t\t\tSELECT * FROM `" . TABLE_PANEL_DATABASES . "`\n\t\t\tWHERE `dbserver` = :mysqlserver\n\t\t");
Database::pexecute($databases_result_stmt, array('mysqlserver' => $dbserver['dbserver']));
while ($databases_row = $databases_result_stmt->fetch(PDO::FETCH_ASSOC)) {
$databases[] = $databases_row['databasename'];
}
foreach ($databases as $username) {
if (isset($users[$username]) && is_array($users[$username]) && isset($users[$username]['hosts']) && is_array($users[$username]['hosts'])) {
$password = $users[$username]['password'];
foreach ($mysql_access_host_array as $mysql_access_host) {
$mysql_access_host = trim($mysql_access_host);
if (!in_array($mysql_access_host, $users[$username]['hosts'])) {
$dbm->getManager()->grantPrivilegesTo($username, $password, $mysql_access_host, true);
}
}
foreach ($users[$username]['hosts'] as $mysql_access_host) {
if (!in_array($mysql_access_host, $mysql_access_host_array)) {
$dbm->getManager()->deleteUser($username, $mysql_access_host);
}
}
}
}
$dbm->getManager()->flushPrivileges();
Database::needRoot(false);
}
}
$upd_stmt = Database::prepare("\n\t\t\t\t\t\t\tUPDATE `" . TABLE_MAIL_USERS . "` SET `postfix`= :yesno, `pop3` = :pop3, `imap` = :imap WHERE `customerid` = :customerid");
Database::pexecute($upd_stmt, array('yesno' => $yesno, 'pop3' => $pop3, 'imap' => $imap, 'customerid' => $id));
$upd_stmt = Database::prepare("\n\t\t\t\t\t\t\tUPDATE `" . TABLE_FTP_USERS . "` SET `login_enabled` = :yesno WHERE `customerid` = :customerid");
Database::pexecute($upd_stmt, array('yesno' => $yesno, 'customerid' => $id));
$upd_stmt = Database::prepare("\n\t\t\t\t\t\t\tUPDATE `" . TABLE_PANEL_DOMAINS . "` SET `deactivated`= :deactivated WHERE `customerid` = :customerid");
Database::pexecute($upd_stmt, array('deactivated' => $deactivated, 'customerid' => $id));
// Retrieve customer's databases
$databases_stmt = Database::prepare("SELECT * FROM " . TABLE_PANEL_DATABASES . " WHERE customerid = :customerid ORDER BY `dbserver`");
Database::pexecute($databases_stmt, array('customerid' => $id));
Database::needRoot(true);
$last_dbserver = 0;
$dbm = new DbManager($log);
// For each of them
while ($row_database = $databases_stmt->fetch(PDO::FETCH_ASSOC)) {
if ($last_dbserver != $row_database['dbserver']) {
$dbm->getManager()->flushPrivileges();
Database::needRoot(true, $row_database['dbserver']);
$last_dbserver = $row_database['dbserver'];
}
foreach (array_unique(explode(',', Settings::Get('system.mysql_access_host'))) as $mysql_access_host) {
$mysql_access_host = trim($mysql_access_host);
// Prevent access, if deactivated
if ($deactivated) {
// failsafe if user has been deleted manually (requires MySQL 4.1.2+)
$dbm->getManager()->disableUser($row_database['databasename'], $mysql_access_host);
} else {
// Otherwise grant access
$dbm->getManager()->enableUser($row_database['databasename'], $mysql_access_host);
}
}
}
AND `id`="' . (int) $id . '"');
Database::pexecute($result_stmt, array("customerid" => $userinfo['customerid']));
$result = $result_stmt->fetch(PDO::FETCH_ASSOC);
if (isset($result['databasename']) && $result['databasename'] != '') {
Database::needRoot(true, $result['dbserver']);
Database::needSqlData();
$sql_root = Database::getSqlData();
Database::needRoot(false);
if (!isset($sql_root[$result['dbserver']]) || !is_array($sql_root[$result['dbserver']])) {
$result['dbserver'] = 0;
}
if (isset($_POST['send']) && $_POST['send'] == 'send') {
// Begin root-session
Database::needRoot(true, $result['dbserver']);
$dbm = new DbManager($log);
$dbm->getManager()->deleteDatabase($result['databasename']);
$log->logAction(USR_ACTION, LOG_INFO, "deleted database '" . $result['databasename'] . "'");
Database::needRoot(false);
// End root-session
$stmt = Database::prepare("DELETE FROM `" . TABLE_PANEL_DATABASES . "`\n\t\t\t\t\tWHERE `customerid` = :customerid\n\t\t\t\t\tAND `id` = :id");
Database::pexecute($stmt, array("customerid" => $userinfo['customerid'], "id" => $id));
$resetaccnumber = $userinfo['mysqls_used'] == '1' ? " , `mysql_lastaccountnumber` = '0' " : '';
$stmt = Database::prepare("UPDATE `" . TABLE_PANEL_CUSTOMERS . "`\n\t\t\t\t\tSET `mysqls_used` = `mysqls_used` - 1 " . $resetaccnumber . "\n\t\t\t\t\tWHERE `customerid` = :customerid");
Database::pexecute($stmt, array("customerid" => $userinfo['customerid']));
redirectTo($filename, array('page' => $page, 's' => $s));
} else {
$dbnamedesc = $result['databasename'];
if (isset($result['description']) && $result['description'] != '') {
$dbnamedesc .= ' (' . $result['description'] . ')';
}
ask_yesno('mysql_reallydelete', $filename, array('id' => $id, 'page' => $page, 'action' => $action), $dbnamedesc);
