/**
* 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);
}
}
if ($nonefound) {
showUpdateStep("No missing settings found");
lastStepStatus(0);
}
updateToVersion('0.9.10-svn1');
}
if (isFroxlorVersion('0.9.10-svn1')) {
showUpdateStep("Updating from 0.9.10-svn1 to 0.9.10-svn2", false);
showUpdateStep("Updating database table definition for panel_databases");
Database::query("ALTER TABLE `" . TABLE_PANEL_DATABASES . "` ADD `apsdb` tinyint(1) NOT NULL default '0' AFTER `dbserver`;");
lastStepStatus(0);
showUpdateStep("Adding APS databases to customers overview");
$count_dbupdates = 0;
Database::needRoot(true);
$result = Database::query("SHOW DATABASES;");
Database::needRoot(false);
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
if (preg_match('/^web([0-9]+)aps([0-9]+)$/', $row['Database'], $matches)) {
$cid = $matches[1];
$databasedescription = 'APS DB';
$result = Database::prepare("\n\t\t\t\tINSERT INTO `" . TABLE_PANEL_DATABASES . "` SET\n\t\t\t\t\t`customerid` = :cid,\n\t\t\t\t\t`databasename` = :dbname,\n\t\t\t\t\t`description` = :dbdesc,\n\t\t\t\t\t`dbserver` = '0',\n\t\t\t\t\t`apsdb` = '1'");
Database::pexecute($result, array('cid' => $cid, 'dbname' => $row['Database'], 'dbdesc' => $databasedescription));
Database::query('UPDATE `' . TABLE_PANEL_CUSTOMERS . '` SET `mysqls_used`=`mysqls_used`+1 WHERE `customerid`="' . (int) $cid . '"');
$count_dbupdates++;
}
}
if ($count_dbupdates > 0) {
lastStepStatus(0, "Found " . $count_dbupdates . " customer APS databases");
} else {
lastStepStatus(0, "None found");
}
/**
* creates a new database and a user with the
* same name with all privileges granted on the db.
* DB-name and user-name are being generated and
* the password for the user will be set
*
* @param string $loginname
* @param string $password
* @param int $last_accnumber
*
* @return string|bool $username if successful or false of username is equal to the password
*/
public function createDatabase($loginname = null, $password = null, $last_accnumber = 0)
{
Database::needRoot(true);
// check whether we shall create a random username
if (strtoupper(Settings::Get('customer.mysqlprefix')) == 'RANDOM') {
// get all usernames from db-manager
$allsqlusers = $this->getManager()->getAllSqlUsers();
// generate random username
$username = $loginname . '-' . substr(md5(uniqid(microtime(), 1)), 20, 3);
// check whether it exists on the DBMS
while (in_array($username, $allsqlusers)) {
$username = $loginname . '-' . substr(md5(uniqid(microtime(), 1)), 20, 3);
}
} else {
$username = $loginname . Settings::Get('customer.mysqlprefix') . (intval($last_accnumber) + 1);
}
// don't use a password that is the same as the username
if ($username == $password) {
return false;
}
// now create the database itself
$this->getManager()->createDatabase($username);
$this->_log->logAction(USR_ACTION, LOG_INFO, "created database '" . $username . "'");
// and give permission to the user on every access-host we have
foreach (array_map('trim', explode(',', Settings::Get('system.mysql_access_host'))) as $mysql_access_host) {
$this->getManager()->grantPrivilegesTo($username, $password, $mysql_access_host);
$this->_log->logAction(USR_ACTION, LOG_NOTICE, "grant all privileges for '" . $username . "'@'" . $mysql_access_host . "'");
}
$this->getManager()->flushPrivileges();
Database::needRoot(false);
return $username;
}
/**
* depending on the give choice, the customers web-data, email-data and databases are being backup'ed
*
* @param array $data
*
* @return void
*
*/
function createCustomerBackup($data = null, $customerdocroot = null, &$cronlog)
{
$cronlog->logAction(CRON_ACTION, LOG_INFO, 'Creating Backup for user "' . $data['loginname'] . '"');
// create tmp folder
$tmpdir = makeCorrectDir($data['destdir'] . '/.tmp/');
$cronlog->logAction(CRON_ACTION, LOG_DEBUG, 'Creating tmp-folder "' . $tmpdir . '"');
$cronlog->logAction(CRON_ACTION, LOG_DEBUG, 'shell> mkdir -p ' . escapeshellarg($tmpdir));
safe_exec('mkdir -p ' . escapeshellarg($tmpdir));
$create_backup_tar_data = "";
// MySQL databases
if ($data['backup_dbs'] == 1) {
$cronlog->logAction(CRON_ACTION, LOG_DEBUG, 'Creating mysql-folder "' . makeCorrectDir($tmpdir . '/mysql') . '"');
$cronlog->logAction(CRON_ACTION, LOG_DEBUG, 'shell> mkdir -p ' . escapeshellarg(makeCorrectDir($tmpdir . '/mysql')));
safe_exec('mkdir -p ' . escapeshellarg(makeCorrectDir($tmpdir . '/mysql')));
// get all customer database-names
$sel_stmt = Database::prepare("SELECT `databasename` FROM `" . TABLE_PANEL_DATABASES . "` WHERE `customerid` = :cid");
Database::pexecute($sel_stmt, array('cid' => $data['customerid']));
Database::needRoot(true);
Database::needSqlData();
$sql_root = Database::getSqlData();
Database::needRoot(false);
$has_dbs = false;
while ($row = $sel_stmt->fetch()) {
$cronlog->logAction(CRON_ACTION, LOG_DEBUG, 'shell> mysqldump -u ' . escapeshellarg($sql_root['user']) . ' -pXXXXX ' . $row['databasename'] . ' > ' . makeCorrectFile($tmpdir . '/mysql/' . $row['databasename'] . '_' . date('YmdHi', time()) . '.sql'));
$bool_false = false;
safe_exec('mysqldump -u ' . escapeshellarg($sql_root['user']) . ' -p' . $sql_root['passwd'] . ' ' . $row['databasename'] . ' > ' . makeCorrectFile($tmpdir . '/mysql/' . $row['databasename'] . '_' . date('YmdHi', time()) . '.sql'), $bool_false, array('>'));
$has_dbs = true;
}
if ($has_dbs) {
$create_backup_tar_data .= './mysql ';
}
unset($sql_root);
}
// E-mail data
if ($data['backup_mail'] == 1) {
$cronlog->logAction(CRON_ACTION, LOG_DEBUG, 'Creating mail-folder "' . makeCorrectDir($tmpdir . '/mail') . '"');
safe_exec('mkdir -p ' . escapeshellarg(makeCorrectDir($tmpdir . '/mail')));
// get all customer mail-accounts
$sel_stmt = Database::prepare("SELECT `homedir`, `maildir` FROM `" . TABLE_MAIL_USERS . "` WHERE `customerid` = :cid");
Database::pexecute($sel_stmt, array('cid' => $data['customerid']));
$tar_file_list = "";
$mail_homedir = "";
while ($row = $sel_stmt->fetch()) {
$tar_file_list .= escapeshellarg("./" . $row['maildir']) . " ";
$mail_homedir = $row['homedir'];
}
if (!empty($tar_file_list)) {
$cronlog->logAction(CRON_ACTION, LOG_DEBUG, 'shell> tar cfvz ' . escapeshellarg(makeCorrectFile($tmpdir . '/mail/' . $data['loginname'] . '-mail.tar.gz')) . ' -C ' . escapeshellarg($mail_homedir) . ' ' . trim($tar_file_list));
safe_exec('tar cfz ' . escapeshellarg(makeCorrectFile($tmpdir . '/mail/' . $data['loginname'] . '-mail.tar.gz')) . ' -C ' . escapeshellarg($mail_homedir) . ' ' . trim($tar_file_list));
$create_backup_tar_data .= './mail ';
}
}
// Web data
if ($data['backup_web'] == 1) {
$cronlog->logAction(CRON_ACTION, LOG_DEBUG, 'Creating web-folder "' . makeCorrectDir($tmpdir . '/web') . '"');
safe_exec('mkdir -p ' . escapeshellarg(makeCorrectDir($tmpdir . '/web')));
$cronlog->logAction(CRON_ACTION, LOG_DEBUG, 'shell> tar cfz ' . escapeshellarg(makeCorrectFile($tmpdir . '/web/' . $data['loginname'] . '-web.tar.gz')) . ' --exclude=' . escapeshellarg(str_replace($customerdocroot, "./", makeCorrectFile($tmpdir . '/*'))) . ' --exclude=' . escapeshellarg(str_replace($customerdocroot, "./", substr(makeCorrectDir($tmpdir), 0, -1))) . ' -C ' . escapeshellarg($customerdocroot) . ' .');
safe_exec('tar cfz ' . escapeshellarg(makeCorrectFile($tmpdir . '/web/' . $data['loginname'] . '-web.tar.gz')) . ' --exclude=' . escapeshellarg(str_replace($customerdocroot, "./", makeCorrectFile($tmpdir . '/*'))) . ' --exclude=' . escapeshellarg(str_replace($customerdocroot, "./", substr(makeCorrectFile($tmpdir), 0, -1))) . ' -C ' . escapeshellarg($customerdocroot) . ' .');
$create_backup_tar_data .= './web ';
}
if (!empty($create_backup_tar_data)) {
$backup_file = makeCorrectFile($tmpdir . '/' . $data['loginname'] . '-backup_' . date('YmdHi', time()) . '.tar.gz');
$cronlog->logAction(CRON_ACTION, LOG_INFO, 'Creating backup-file "' . $backup_file . '"');
// pack all archives in tmp-dir to one
$cronlog->logAction(CRON_ACTION, LOG_DEBUG, 'shell> tar cfz ' . escapeshellarg($backup_file) . ' -C ' . escapeshellarg($tmpdir) . ' ' . trim($create_backup_tar_data));
safe_exec('tar cfz ' . escapeshellarg($backup_file) . ' -C ' . escapeshellarg($tmpdir) . ' ' . trim($create_backup_tar_data));
// move to destination directory
$cronlog->logAction(CRON_ACTION, LOG_DEBUG, 'shell> mv ' . escapeshellarg($backup_file) . ' ' . escapeshellarg($data['destdir']));
safe_exec('mv ' . escapeshellarg($backup_file) . ' ' . escapeshellarg($data['destdir']));
// remove tmp-files
$cronlog->logAction(CRON_ACTION, LOG_DEBUG, 'shell> rm -rf ' . escapeshellarg($tmpdir));
safe_exec('rm -rf ' . escapeshellarg($tmpdir));
// set owner to customer
$cronlog->logAction(CRON_ACTION, LOG_DEBUG, 'shell> chown -R ' . (int) $data['uid'] . ':' . (int) $data['gid'] . ' ' . escapeshellarg($data['destdir']));
safe_exec('chown -R ' . (int) $data['uid'] . ':' . (int) $data['gid'] . ' ' . escapeshellarg($data['destdir']));
}
}
