From 5045c7ee88c4dd07586179268799415b176e624c Mon Sep 17 00:00:00 2001
From: moglia <moglia@ispconfig3>
Date: Wed, 12 May 2010 14:41:58 -0400
Subject: [PATCH] Improved database list for turns end user life easy. If you are admin you need back to client list do consult id. Only admin can view CLIEND_ID for example and limited users can not know your id using easy way. This issue reported by my ispconfig end users. Direct show of end database user name resolve all of these steps on a single step.
---
server/plugins-available/mysql_clientdb_plugin.inc.php | 210 +++++++++++++++++++++++++++++++++++++++++-----------
1 files changed, 165 insertions(+), 45 deletions(-)
diff --git a/server/plugins-available/mysql_clientdb_plugin.inc.php b/server/plugins-available/mysql_clientdb_plugin.inc.php
index 4699379..fdececf 100644
--- a/server/plugins-available/mysql_clientdb_plugin.inc.php
+++ b/server/plugins-available/mysql_clientdb_plugin.inc.php
@@ -1,7 +1,7 @@
<?php
/*
-Copyright (c) 2008, Till Brehm, projektfarm Gmbh
+Copyright (c) 2007, Till Brehm, projektfarm Gmbh
All rights reserved.
Redistribution and use in source and binary forms, with or without modification,
@@ -33,6 +33,19 @@
var $plugin_name = 'mysql_clientdb_plugin';
var $class_name = 'mysql_clientdb_plugin';
+ //* This function is called during ispconfig installation to determine
+ // if a symlink shall be created for this plugin.
+ function onInstall() {
+ global $conf;
+
+ if($conf['services']['db'] == true) {
+ return true;
+ } else {
+ return false;
+ }
+
+ }
+
/*
This function is called when the plugin is loaded
@@ -53,13 +66,66 @@
}
+ function process_host_list($action, $database_name, $database_user, $database_password, $host_list, $link, $database_rename_user = "") {
+ global $app;
+
+ $action = strtoupper($action);
+
+ // set to all hosts if none given
+ if(trim($host_list) == "") $host_list = "%";
+
+ // process arrays and comma separated strings
+ if(!is_array($host_list)) $host_list = split(",", $host_list);
+
+ $success = true;
+
+ // loop through hostlist
+ foreach($host_list as $db_host) {
+ $db_host = trim($db_host);
+
+ // check if entry is valid ip address
+ $valid = true;
+ if($db_host == "%") {
+ $valid = true;
+ } elseif(preg_match("/^[0-9]{1,3}(\.)[0-9]{1,3}(\.)[0-9]{1,3}(\.)[0-9]{1,3}$/", $db_host)) {
+ $groups = explode(".", $db_host);
+ foreach($groups as $group){
+ if($group<0 OR $group>255)
+ $valid=false;
+ }
+ } else {
+ $valid = false;
+ }
+
+ if($valid == false) continue;
+
+ if($action == "GRANT") {
+ if(!mysql_query("GRANT ALL ON ".mysql_real_escape_string($database_name,$link).".* TO '".mysql_real_escape_string($database_user,$link)."'@'$db_host' IDENTIFIED BY '".mysql_real_escape_string($database_password,$link)."';",$link)) $success = false;
+ } elseif($action == "REVOKE") {
+ //mysql_query("REVOKE ALL PRIVILEGES ON ".mysql_real_escape_string($database_name,$link).".* FROM '".mysql_real_escape_string($database_user,$link)."';",$link);
+ } elseif($action == "DROP") {
+ if(!mysql_query("DROP USER '".mysql_real_escape_string($database_user,$link)."'@'$db_host';",$link)) $success = false;
+ } elseif($action == "RENAME") {
+ if(!mysql_query("RENAME USER '".mysql_real_escape_string($database_user,$link)."'@'$db_host' TO '".mysql_real_escape_string($database_rename_user,$link)."'@'$db_host'",$link)) $success = false;
+ } elseif($action == "PASSWORD") {
+ if(!mysql_query("SET PASSWORD FOR '".mysql_real_escape_string($database_user,$link)."'@'$db_host' = PASSWORD('".mysql_real_escape_string($database_password,$link)."');",$link)) $success = false;
+ }
+ }
+
+ return $success;
+ }
function db_insert($event_name,$data) {
global $app, $conf;
if($data["new"]["type"] == 'mysql') {
- if(!include_once(ISPC_LIB_PATH.'/mysql_clientdb.conf')) {
+ if(!include(ISPC_LIB_PATH.'/mysql_clientdb.conf')) {
$app->log('Unable to open'.ISPC_LIB_PATH.'/mysql_clientdb.conf',LOGLEVEL_ERROR);
+ return;
+ }
+
+ if($data["new"]["database_user"] == 'root') {
+ $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
return;
}
@@ -69,23 +135,33 @@
$app->log('Unable to connect to the database'.mysql_error($link),LOGLEVEL_ERROR);
return;
}
-
+
+ // Charset for the new table
+ if($data["new"]["database_charset"] != '') {
+ $query_charset_table = ' DEFAULT CHARACTER SET '.$data["new"]["database_charset"];
+ } else {
+ $query_charset_table = '';
+ }
+
//* Create the new database
- if (mysql_query('CREATE DATABASE '.addslashes($data["new"]["database_name"]),$link)) {
+ if (mysql_query('CREATE DATABASE '.mysql_real_escape_string($data["new"]["database_name"]).$query_charset_table,$link)) {
$app->log('Created MySQL database: '.$data["new"]["database_name"],LOGLEVEL_DEBUG);
} else {
$app->log('Unable to connect to the database'.mysql_error($link),LOGLEVEL_ERROR);
}
- // Create the database user
- if($data["new"]["remote_access"] == 'y') {
- $db_host = '%';
- } else {
+ // Create the database user if database is active
+ if($data["new"]["active"] == 'y') {
+
+ if($data["new"]["remote_access"] == 'y') {
+ $this->process_host_list("GRANT", $data["new"]["database_name"], $data["new"]["database_user"], $data["new"]["database_password"], $data["new"]["remote_ips"], $link);
+ }
+
$db_host = 'localhost';
+ mysql_query("GRANT ALL ON ".mysql_real_escape_string($data["new"]["database_name"],$link).".* TO '".mysql_real_escape_string($data["new"]["database_user"],$link)."'@'$db_host' IDENTIFIED BY '".mysql_real_escape_string($data["new"]["database_password"],$link)."';",$link);
+
+
}
-
- mysql_query("GRANT ALL ON ".addslashes($data["new"]["database_name"]).".* TO '".addslashes($data["new"]["database_user"])."'@'$db_host' IDENTIFIED BY '".addslashes($data["new"]["database_password"])."';",$link);
- //echo "GRANT ALL ON ".addslashes($data["new"]["database_name"]).".* TO '".addslashes($data["new"]["database_user"])."'@'$db_host' IDENTIFIED BY '".addslashes($data["new"]["database_password"])."';";
mysql_query("FLUSH PRIVILEGES;",$link);
mysql_close($link);
@@ -96,52 +172,88 @@
global $app, $conf;
if($data["new"]["type"] == 'mysql') {
- if(!include_once(ISPC_LIB_PATH.'/mysql_clientdb.conf')) {
+ if(!include(ISPC_LIB_PATH.'/mysql_clientdb.conf')) {
$app->log('Unable to open'.ISPC_LIB_PATH.'/mysql_clientdb.conf',LOGLEVEL_ERROR);
return;
}
-
+
+ if($data["new"]["database_user"] == 'root') {
+ $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
+ return;
+ }
+
//* Connect to the database
$link = mysql_connect($clientdb_host, $clientdb_user, $clientdb_password);
if (!$link) {
$app->log('Unable to connect to the database'.mysql_error($link),LOGLEVEL_ERROR);
+ return;
+ }
+
+ // Create the database user if database was disabled before
+ if($data["new"]["active"] == 'y' && $data["old"]["active"] == 'n') {
+
+ if($data["new"]["remote_access"] == 'y') {
+ $this->process_host_list("GRANT", $data["new"]["database_name"], $data["new"]["database_user"], $data["new"]["database_password"], $data["new"]["remote_ips"], $link);
+ }
+
+ $db_host = 'localhost';
+ mysql_query("GRANT ALL ON ".mysql_real_escape_string($data["new"]["database_name"],$link).".* TO '".mysql_real_escape_string($data["new"]["database_user"],$link)."'@'$db_host' IDENTIFIED BY '".mysql_real_escape_string($data["new"]["database_password"],$link)."';",$link);
+
+ // mysql_query("GRANT ALL ON ".mysql_real_escape_string($data["new"]["database_name"],$link).".* TO '".mysql_real_escape_string($data["new"]["database_user"],$link)."'@'$db_host' IDENTIFIED BY '".mysql_real_escape_string($data["new"]["database_password"],$link)."';",$link);
+ //echo "GRANT ALL ON ".mysql_real_escape_string($data["new"]["database_name"]).".* TO '".mysql_real_escape_string($data["new"]["database_user"])."'@'$db_host' IDENTIFIED BY '".mysql_real_escape_string($data["new"]["database_password"])."';";
+ }
+
+ // Remove database user, if inactive
+ if($data["new"]["active"] == 'n' && $data["old"]["active"] == 'y') {
+
+ if($data["old"]["remote_access"] == 'y') {
+ $this->process_host_list("DROP", "", $data["old"]["database_user"], "", $data["old"]["remote_ips"], $link);
+ }
+
+ $db_host = 'localhost';
+ mysql_query("DROP USER '".mysql_real_escape_string($data["old"]["database_user"],$link)."'@'$db_host';",$link);
+
+
+ //mysql_query("REVOKE ALL PRIVILEGES ON ".mysql_real_escape_string($data["new"]["database_name"],$link).".* FROM '".mysql_real_escape_string($data["new"]["database_user"],$link)."';",$link);
}
//* Rename User
if($data["new"]["database_user"] != $data["old"]["database_user"]) {
- mysql_query("RENAME USER '".addslashes($data["old"]["database_user"])."' TO '".addslashes($data["new"]["database_user"])."'",$link);
+ $db_host = 'localhost';
+ mysql_query("RENAME USER '".mysql_real_escape_string($data["old"]["database_user"],$link)."'@'$db_host' TO '".mysql_real_escape_string($data["new"]["database_user"],$link)."'@'$db_host'",$link);
+ if($data["old"]["remote_access"] == 'y') {
+ $this->process_host_list("RENAME", "", $data["new"]["database_user"], "", $data["new"]["remote_ips"], $link, $data["new"]["database_user"]);
+ }
$app->log('Renaming mysql user: '.$data["old"]["database_user"].' to '.$data["new"]["database_user"],LOGLEVEL_DEBUG);
}
//* Remote access option has changed.
if($data["new"]["remote_access"] != $data["old"]["remote_access"]) {
- if($data["new"]["remote_access"] == 'y') {
- mysql_query("UPDATE mysql.user SET Host = '%' WHERE User = '".addslashes($data["new"]["database_user"])."' and Host = 'localhost';",$link);
- mysql_query("UPDATE mysql.db SET Host = '%' WHERE User = '".addslashes($data["new"]["database_user"])."' and Host = 'localhost';",$link);
+
+ //* revoke old priveliges
+ //mysql_query("REVOKE ALL PRIVILEGES ON ".mysql_real_escape_string($data["new"]["database_name"],$link).".* FROM '".mysql_real_escape_string($data["new"]["database_user"],$link)."';",$link);
+
+ //* set new priveliges
+ if($data["new"]["remote_access"] == 'y') {
+ $this->process_host_list("GRANT", $data["new"]["database_name"], $data["new"]["database_user"], $data["new"]["database_password"], $data["new"]["remote_ips"], $link);
} else {
- mysql_query("UPDATE mysql.user SET Host = 'localhost' WHERE User = '".addslashes($data["new"]["database_user"])."' and Host = '%';",$link);
- mysql_query("UPDATE mysql.db SET Host = 'localhost' WHERE User = '".addslashes($data["new"]["database_user"])."' and Host = '%';",$link);
+ $this->process_host_list("DROP", "", $data["old"]["database_user"], "", $data["old"]["remote_ips"], $link);
}
$app->log('Changing mysql remote access priveliges for database: '.$data["new"]["database_name"],LOGLEVEL_DEBUG);
- }
-
- //* Get the db host setting for the access priveliges
- if($data["new"]["remote_access"] == 'y') {
- $db_host = '%';
- } else {
- $db_host = 'localhost';
- }
-
- /*
- //* Rename database
- if($data["new"]["database_name"] != $data["old"]["database_name"]) {
- mysql_query("",$link);
- }
- */
-
+ } elseif($data["new"]["remote_access"] == 'y' && $data["new"]["remote_ips"] != $data["old"]["remote_ips"]) {
+ //* Change remote access list
+ $this->process_host_list("DROP", "", $data["old"]["database_user"], "", $data["old"]["remote_ips"], $link);
+ $this->process_host_list("GRANT", $data["new"]["database_name"], $data["new"]["database_user"], $data["new"]["database_password"], $data["new"]["remote_ips"], $link);
+ }
+
//* Change password
if($data["new"]["database_password"] != $data["old"]["database_password"]) {
- mysql_query("SET PASSWORD FOR '".addslashes($data["new"]["database_user"])."'@'$db_host' = PASSWORD('".addslashes($data["new"]["database_password"])."');",$link);
+ $db_host = 'localhost';
+ mysql_query("SET PASSWORD FOR '".mysql_real_escape_string($data["new"]["database_user"],$link)."'@'$db_host' = PASSWORD('".mysql_real_escape_string($data["new"]["database_password"],$link)."');",$link);
+
+ if($data["new"]["remote_access"] == 'y') {
+ $this->process_host_list("PASSWORD", "", $data["new"]["database_user"], $data["new"]["database_password"], $data["new"]["remote_ips"], $link);
+ }
$app->log('Changing mysql user password for: '.$data["new"]["database_user"],LOGLEVEL_DEBUG);
}
@@ -155,7 +267,7 @@
global $app, $conf;
if($data["old"]["type"] == 'mysql') {
- if(!include_once(ISPC_LIB_PATH.'/mysql_clientdb.conf')) {
+ if(!include(ISPC_LIB_PATH.'/mysql_clientdb.conf')) {
$app->log('Unable to open'.ISPC_LIB_PATH.'/mysql_clientdb.conf',LOGLEVEL_ERROR);
return;
}
@@ -164,21 +276,29 @@
$link = mysql_connect($clientdb_host, $clientdb_user, $clientdb_password);
if (!$link) {
$app->log('Unable to connect to the database'.mysql_error($link),LOGLEVEL_ERROR);
+ return;
}
//* Get the db host setting for the access priveliges
if($data["old"]["remote_access"] == 'y') {
- $db_host = '%';
+ if($this->process_host_list("DROP", "", $data["old"]["database_user"], "", $data["old"]["remote_ips"], $link)) {
+ $app->log('Dropping mysql user: '.$data["old"]["database_user"],LOGLEVEL_DEBUG);
+ } else {
+ $app->log('Error while dropping mysql user: '.$data["old"]["database_user"].' '.mysql_error($link),LOGLEVEL_ERROR);
+ }
+ }
+ $db_host = 'localhost';
+ if(mysql_query("DROP USER '".mysql_real_escape_string($data["old"]["database_user"],$link)."'@'$db_host';",$link)) {
+ $app->log('Dropping mysql user: '.$data["old"]["database_user"],LOGLEVEL_DEBUG);
} else {
- $db_host = 'localhost';
+ $app->log('Error while dropping mysql user: '.$data["old"]["database_user"].' '.mysql_error($link),LOGLEVEL_ERROR);
}
- mysql_query("DROP USER '".addslashes($data["old"]["database_user"])."'@'$db_host';",$link);
- $app->log('Dropping mysql user: '.$data["old"]["database_user"],LOGLEVEL_DEBUG);
-
- mysql_query('DROP DATABASE '.addslashes($data["old"]["database_name"]),$link);
- $app->log('Dropping mysql database: '.$data["old"]["database_name"],LOGLEVEL_DEBUG);
-
+ if(mysql_query('DROP DATABASE '.mysql_real_escape_string($data["old"]["database_name"],$link),$link)) {
+ $app->log('Dropping mysql database: '.$data["old"]["database_name"],LOGLEVEL_DEBUG);
+ } else {
+ $app->log('Error while dropping mysql database: '.$data["old"]["database_name"].' '.mysql_error($link),LOGLEVEL_ERROR);
+ }
mysql_query("FLUSH PRIVILEGES;",$link);
mysql_close($link);
--
Gitblit v1.9.1