From 997305cf93cd27c51f44a2e882238f190ad17606 Mon Sep 17 00:00:00 2001 From: Joe Presbrey Date: Sat, 16 Jul 2005 14:44:16 +0000 Subject: [PATCH] first stab at 0.2 better DB structure uses classes instead git-svn-id: svn://presbrey.mit.edu/sql/mitsql@34 a142d4bd-2cfb-0310-9673-cb33a7e74f58 --- .htaccess | 69 ++++++ db/mitsql.sql | 127 +++++++++++ defaults.cfg.php | 24 +++ global.act.php | 20 ++ global.done.php | 9 + index.php | 42 +--- lib/dbaccess.lib.php | 45 ++++ lib/display.lib.php | 23 ++ lib/joe.lib.php | 203 ++++++++++++++++++ lib/mitsql.lib.php | 28 +++ lib/security.lib.php | 113 ++++++++++ login.php | 26 +++ main.php | 0 mitsql.cfg.php | 34 +++ style.inc => mitsql.css | 0 mitsql.lib.php | 463 ---------------------------------------- test.php | 19 +- tpl/foot.inc | 4 + tpl/head.inc | 7 + tpl/index.php | 7 + tpl/login_ssl.php | 13 ++ 21 files changed, 775 insertions(+), 501 deletions(-) create mode 100644 .htaccess create mode 100644 db/mitsql.sql create mode 100644 defaults.cfg.php create mode 100644 global.act.php create mode 100644 global.done.php create mode 100644 lib/dbaccess.lib.php create mode 100644 lib/display.lib.php create mode 100755 lib/joe.lib.php create mode 100755 lib/mitsql.lib.php create mode 100644 lib/security.lib.php create mode 100644 login.php create mode 100644 main.php create mode 100755 mitsql.cfg.php rename style.inc => mitsql.css (100%) delete mode 100755 mitsql.lib.php create mode 100644 tpl/foot.inc create mode 100644 tpl/head.inc create mode 100644 tpl/index.php create mode 100644 tpl/login_ssl.php diff --git a/.htaccess b/.htaccess new file mode 100644 index 0000000..98979d6 --- /dev/null +++ b/.htaccess @@ -0,0 +1,69 @@ +#php_value auto_prepend_file "prepend.php" +php_value auto_append_file "global.done.php" +#php_flag xdebug.remote_enable on +#php_flag xdebug.remote_autostart on +#php_value xdebug.remote_host "localhost" +#php_value xdebug.remote_port "9000" + +RewriteEngine On +RewriteBase /~administrator/mitsql/ + +#RewriteOptions MaxRedirects=1 + +#RewriteCond %{REQUEST_FILENAME}.php -f +#RewriteRule ^.* do/%{REQUEST_FILENAME} +#RewriteCond %{REQUEST_URI} !^/~administrator/mitsql/go/.+ + +##RewriteCond %{REQUEST_URI} +##RewriteCond %{REQUEST_FILENAME} !-s +#RewriteRule ^do/.+$ - [L] + +#RewriteCond %{IS_SUBREQ} !="true" +#RewriteRule ^.*$ do/index [R,L] + +##RewriteCond %{REQUEST_URI} ^.*/do/.+$ +##RewriteRule ^do/.+$ - [C] +#RewriteRule ^do/(.+)$ $1.php [PT,L] + +#RewriteRule ^do/(.+)$ $1.php [L] + +## attempt 2 +#RewriteCond %{REQUEST_URI} !^.*/do/.+$ +#RewriteRule ^.*$ do/index [R,L] +# +#RewriteCond %{THE_REQUEST} ^(GET|HEAD)\ /.+\.php\ HTTP +#RewriteRule \.php$ - [F] +# +##RewriteCond do/%{REQUEST_FILENAME}.php -f +#RewriteRule ^do/(.+)$ $1.php [L] + +# attempt 3 + +#RewriteRule \.php$ - [F,C] + +#RewriteCond %{REQUEST_URI} !^.*/do/.+$ +#RewriteRule ^.*$ do/index [R,L] + +#RewriteCond do/%{REQUEST_FILENAME}.php -f +#RewriteRule ^do/(.+)$ $1.php [L] + +#RewriteCond %{THE_REQUEST} !^(GET|HEAD)\ /.+\.php\ HTTP +#RewriteCond %{THE_REQUEST} ^(GET|HEAD)\ /.+mitsql/do/.+\ HTTP + +## REVISION 4 + +RewriteCond %{REQUEST_URI} ^.*/mitsql/do/(.+)$ +RewriteRule ^do/(.+)$ $1.php [QSA] + +RewriteCond %{REQUEST_FILENAME} !-f +RewriteRule .* do/index [R,L,QSA] + +# now redundant after following conditions +#RewriteCond %{THE_REQUEST} ^(GET|HEAD)\ /.+\.php\ HTTP +#RewriteRule .* do/index [R,L,QSA] + +RewriteCond %{THE_REQUEST} !^(GET|HEAD)\ /.+mitsql/do/.+\ HTTP +RewriteCond %{REQUEST_FILENAME} !\.html$ +RewriteCond %{REQUEST_FILENAME} !\.css$ +RewriteCond %{REQUEST_FILENAME} !\.jpg$ +RewriteRule .* do/index [R,L,QSA] diff --git a/db/mitsql.sql b/db/mitsql.sql new file mode 100644 index 0000000..0c16cd4 --- /dev/null +++ b/db/mitsql.sql @@ -0,0 +1,127 @@ +-- MySQL dump 10.9 +-- +-- Host: sql.mit.edu Database: mitsql +-- ------------------------------------------------------ +-- Server version 4.1.11-Debian_4-log + +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; +/*!40101 SET NAMES utf8 */; +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; + +-- +-- Current Database: `mitsql` +-- + +CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mitsql` /*!40100 DEFAULT CHARACTER SET latin1 */; + +USE `mitsql`; + +-- +-- Table structure for table `db` +-- + +DROP TABLE IF EXISTS `db`; +CREATE TABLE `db` ( + `name` varchar(255) NOT NULL default '', + `owner` varchar(255) NOT NULL default '', + `sizeMax` bigint(20) NOT NULL default '0', + `sizeLast` bigint(20) NOT NULL default '0', + `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + `created` timestamp NOT NULL default '0000-00-00 00:00:00', + PRIMARY KEY (`name`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; + +-- +-- Dumping data for table `db` +-- + + +/*!40000 ALTER TABLE `db` DISABLE KEYS */; +LOCK TABLES `db` WRITE; +INSERT INTO `db` VALUES ('presbrey+123','presbrey',0,0,'2005-03-01 10:50:25','2005-03-01 10:50:25'),('presbrey+12','presbrey',0,0,'2005-03-01 10:50:26','2005-03-01 10:50:26'),('bgelb+db','bgelb',0,0,'2005-01-18 23:39:43','2005-01-18 23:39:43'),('presbrey+12345','presbrey',0,0,'2005-03-01 10:50:31','2005-03-01 10:50:31'),('presbrey+forum','presbrey',0,0,'2005-03-11 12:28:26','2005-03-11 12:28:26'),('test+1','test',0,0,'2005-01-25 03:25:08','2005-01-25 03:25:08'),('test+2','test',0,0,'2005-01-25 03:25:11','2005-01-25 03:25:11'),('test+3','test',0,0,'2005-01-25 03:25:14','2005-01-25 03:25:14'),('test+4','test',0,0,'2005-01-25 03:25:16','2005-01-25 03:25:16'),('test+5','test',0,0,'2005-01-25 03:25:20','2005-01-25 03:25:20'),('presbrey+1234','presbrey',0,0,'2005-03-01 10:50:23','2005-03-01 10:50:23'),('andriko+phpBB','andriko',0,0,'2005-03-11 13:27:28','2005-03-11 13:27:28'),('mrivas03+blog','mrivas03',0,0,'2005-07-10 13:25:18','2005-07-10 13:25:18'); +UNLOCK TABLES; +/*!40000 ALTER TABLE `db` ENABLE KEYS */; + +-- +-- Table structure for table `groups` +-- + +DROP TABLE IF EXISTS `groups`; +CREATE TABLE `groups` ( + `owner` varchar(255) NOT NULL default '', + `group` varchar(255) NOT NULL default '', + PRIMARY KEY (`owner`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; + +-- +-- Dumping data for table `groups` +-- + + +/*!40000 ALTER TABLE `groups` DISABLE KEYS */; +LOCK TABLES `groups` WRITE; +UNLOCK TABLES; +/*!40000 ALTER TABLE `groups` ENABLE KEYS */; + +-- +-- Table structure for table `owner` +-- + +DROP TABLE IF EXISTS `owner`; +CREATE TABLE `owner` ( + `name` varchar(255) NOT NULL default '', + `maxDB` bigint(20) NOT NULL default '0', + `maxSize` bigint(20) NOT NULL default '0', + `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + `created` timestamp NOT NULL default '0000-00-00 00:00:00', + PRIMARY KEY (`name`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; + +-- +-- Dumping data for table `owner` +-- + + +/*!40000 ALTER TABLE `owner` DISABLE KEYS */; +LOCK TABLES `owner` WRITE; +INSERT INTO `owner` VALUES ('bgelb',5,26214400,'2005-01-18 23:39:18','2005-01-18 23:39:18'),('presbrey',5,26214400,'2005-03-01 10:50:21','2005-03-01 10:50:21'),('test',5,1048576,'2005-01-25 03:25:03','2005-01-25 03:25:03'),('jtwang',5,26214400,'2005-03-01 12:32:30','2005-03-01 12:32:30'),('andriko',5,26214400,'2005-03-11 12:32:49','2005-03-11 12:32:49'),('melissa2',5,26214400,'2005-05-19 01:19:44','2005-05-19 01:19:44'),('mrivas03',5,26214400,'2005-07-10 12:06:24','2005-07-10 12:06:24'); +UNLOCK TABLES; +/*!40000 ALTER TABLE `owner` ENABLE KEYS */; + +-- +-- Table structure for table `passwd` +-- + +DROP TABLE IF EXISTS `passwd`; +CREATE TABLE `passwd` ( + `user` varchar(255) NOT NULL default '', + `crypt` varchar(255) NOT NULL default '', + `manages` text NOT NULL, + `admin` tinyint(1) NOT NULL default '0', + PRIMARY KEY (`user`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; + +-- +-- Dumping data for table `passwd` +-- + + +/*!40000 ALTER TABLE `passwd` DISABLE KEYS */; +LOCK TABLES `passwd` WRITE; +INSERT INTO `passwd` VALUES ('test','x4','',0); +UNLOCK TABLES; +/*!40000 ALTER TABLE `passwd` ENABLE KEYS */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + diff --git a/defaults.cfg.php b/defaults.cfg.php new file mode 100644 index 0000000..3e42eea --- /dev/null +++ b/defaults.cfg.php @@ -0,0 +1,24 @@ + diff --git a/global.act.php b/global.act.php new file mode 100644 index 0000000..6c4b540 --- /dev/null +++ b/global.act.php @@ -0,0 +1,20 @@ +getUserId(); + $Name = $Login->getName(); + $Email = $Login->getEmail(); + $UL = $Login->getUL(); +} + +?> diff --git a/global.done.php b/global.done.php new file mode 100644 index 0000000..65c8248 --- /dev/null +++ b/global.done.php @@ -0,0 +1,9 @@ +'; +print_r($_SESSION); +print_r($timings); + +?> diff --git a/index.php b/index.php index 1cdf976..6d810e0 100755 --- a/index.php +++ b/index.php @@ -7,45 +7,17 @@ - to support group sql "lockers" */ -if (file_exists('/mit/presbrey/web_tmp')) - ini_set('session.save_path','/mit/presbrey/web_tmp'); -ini_set('display_errors', 1); -ignore_user_abort(1); -error_reporting(E_ALL); -set_time_limit(0); -session_start(); -if (isset($_GET['reset'])) { session_destroy(); session_start(); } require_once('mitsql.lib.php'); -if (!isset($_SESSION['owner'])) { - if (isset($_GET['owner'])) { - $_SESSION['owner'] = $_GET['owner']; - redirectLocal('/'); - } - if (isset($_POST['owner'])) { - $_SESSION['owner'] = $_POST['owner']; - redirectLocal('/'); - } - if (is_null(getSSLIdent())) { - redirect('https://'.$_SERVER['SERVER_NAME'].$_SERVER['PHP_SELF']); - echo '

Your SSL certificate could not be verified, please authenticate manually below.

'; -// echo '
'; - echo '

(This will be fixed when I do group locker SQL support)

'; - } else { - $_SESSION['owner'] = getSSLIdent(); - } -} -if (isset($_SESSION['realuser'])) { - $realuser = $_SESSION['realuser']; -} else $realuser = null; +isLoggedIn() || redirect('login'); + +redirect('main'); -ob_start(); -include('style.inc'); +exit; if (isset($_SESSION['owner'])) { $owner = $_SESSION['owner']; $dbm = new DBManage($owner); -} else exit; ?>

SQL Databases:

MIT SQL Service Management v
Direct comments and bugs to: presbrey@mit.edu diff --git a/lib/dbaccess.lib.php b/lib/dbaccess.lib.php new file mode 100644 index 0000000..e106698 --- /dev/null +++ b/lib/dbaccess.lib.php @@ -0,0 +1,45 @@ + diff --git a/lib/display.lib.php b/lib/display.lib.php new file mode 100644 index 0000000..b1d6d2d --- /dev/null +++ b/lib/display.lib.php @@ -0,0 +1,23 @@ + diff --git a/lib/joe.lib.php b/lib/joe.lib.php new file mode 100755 index 0000000..2484237 --- /dev/null +++ b/lib/joe.lib.php @@ -0,0 +1,203 @@ + 0) { + $arr = array(); + if (is_null($key)) { + while ($r = mysql_fetch_assoc($rs)) { + $arr[] = $r; + } + } elseif (is_numeric($key)) { + while ($r = mysql_fetch_row($rs)) { + $arr[$r[$key]] = $r; + } + } else { + while ($r = mysql_fetch_assoc($rs)) { + $arr[$r[$key]] = $r; + } + } + mysql_free_result($rs); + return $arr; + } else { + mysql_free_result($rs); + return array(); + } +} + +function printErrors($errArray) { + if (isset($errArray) && count($errArray)) { + echo ''; + } +} + +function buildSQLSet($fields, $values=null) { + $ex = array('NOW()','NULL'); + $sql = 'SET'; + $c = 0; + if (!is_null($values)) { + foreach($fields as $field) { + if ($c++) $sql .= ','; + $sql .= " `$field`='".mysql_escape_string(array_shift($values))."'"; + } + } else { + foreach($fields as $field=>$value) { + if ($c++) $sql .= ','; + if (in_array($value,$ex)) { + $sql .= " `$field`= $value"; + } else { + $sql .= " `$field`='".mysql_escape_string($value)."'"; + } + } + } + return $sql; +} + +function buildSQLInsert($array, $table=null) { + $ex = array('NOW()','NULL'); + $sql = '('; + $c = 0; + foreach($array as $field=>$value) { + if ($c++) $sql .= ','; + $sql .= " `$field` "; + } + $sql .= ') VALUES ('; + $c = 0; + foreach($array as $field=>$value) { + $v = mysql_escape_string($value); + if ($c++) $sql .= ','; + if (in_array($v, $ex)) + $sql .= " $v "; + else + $sql .= " '$v' "; + } + $sql .= ')'; + return (is_null($table)?$sql:('INSERT INTO `'.$table.'` '.$table)); +} + +function build_str($query_array) { + $query_string = array(); + foreach ($query_array as $k => $v) { + $new = $k; + if (strlen($v)) + $new .= '='.$v; + $query_string[] = $new; + } + return join('&', $query_string); +} + +function newQS($key, $val=null) { + /* + parse_str($_SERVER['QUERY_STRING'], $arr); + $arr[$key] = $val; + return '?'.build_str($arr); + */ + return newQSA(array($key=>$val)); +} + +function newQSA($array=array()) { + parse_str($_SERVER['QUERY_STRING'], $arr); + $s = count($arr); + foreach($array as $key=>$val) { + $arr[$key] = $val; + if (is_null($val)) + unset($arr[$key]); + } + return (count($arr)||$s)?'?'.build_str($arr):''; +} + +function formQSA($array=array()) { + if (!count($array)) $array = $_SERVER['QUERY_STRING']; + parse_str($array, $arr); + $text = ''; + foreach($arr as $key=>$val) { + $text .= sprintf('', $key, $val); + } + return $text; +} + +?> diff --git a/lib/mitsql.lib.php b/lib/mitsql.lib.php new file mode 100755 index 0000000..b275c30 --- /dev/null +++ b/lib/mitsql.lib.php @@ -0,0 +1,28 @@ + diff --git a/lib/security.lib.php b/lib/security.lib.php new file mode 100644 index 0000000..ee7847e --- /dev/null +++ b/lib/security.lib.php @@ -0,0 +1,113 @@ +info = count($r)?array_shift($r):null; + } + function exists() { + return !is_null($this->info); + } + function isValid() { + return $this->isEnabled() && $this->getUL()>0; + } + function isEnabled() { + return $this->exists() && $this->info['bEnabled']==1; + } + function getUserId() { + return $this->exists() && $this->info['UserId']; + } + function getUsername() { + return $this->exists() && $this->info['Username']; + } + function getName() { + return $this->exists() && $this->info['Name']; + } + function getEmail() { + return $this->exists() && $this->info['Email']; + } + function getUL() { + return $this->exists() && $this->info['UL']; + } + function expire() { + $this->info = null; + } + function refresh() { + $this->Login($this->getUsername()); + } + function update($name=null,$email=null) { + if (!$this->exists()) return; + $arr = array(); + is_null($name) || $arr['Name'] = $name; + is_null($email) || $arr['Email'] = $email; + $sql = sprintf("UPDATE User %s WHERE UserId = '%s'", + buildSQLSet($arr), mysql_escape_string($this->getUserId())); + DBUpdate($sql); + } +} + + +function isLoggedIn($aLogin=null) { + if (is_null($aLogin)) { + global $Login; + $aLogin = $Login; + } + return !empty($aLogin) && is_a($aLogin, 'Login') && $aLogin->isValid(); +} + +function isSSL() { + return $_SERVER['SERVER_PORT'] == 443; +} + +function getSSLCert() { + if (DEVEL && file_exists('.forceauth')) { + $fu = explode('|',file_get_contents('.forceauth')); + $name = $fu[0]; + $email = $fu[1]; + } else { + $name = isset($_SERVER['SSL_CLIENT_S_DN_CN'])?$_SERVER['SSL_CLIENT_S_DN_CN']:null; + $email = isset($_SERVER['SSL_CLIENT_S_DN_Email'])?$_SERVER['SSL_CLIENT_S_DN_Email']:null; + } + if (!is_null($email)) { + $user = explode('@',$email); + $user = $user[0]; + return array('Username'=>$user, 'Name'=>$name, 'Email'=>$email); + } else { + return null; + } +} + +## 302 REDIRECTS + +function redirect($target=NULL) { + $base = (is_null($target)||substr($target,0,1)=='?')?$_SERVER['REDIRECT_URL']:(dirname($_SERVER['REDIRECT_URL']).'/'); + redirectFull(is_null($target)?$base:($base.$target)); +} +function redirectFull($target) { + redirect2((isSSL()?'https://':'http://').$_SERVER['SERVER_NAME'].$target); +} +function redirect2($target) { + header('Location: '.$target); + exit; +} + +## USER SCRIPTS + +function addUser($sslCredentials) { + global $_NEW_USER; + $arr = array_merge($sslCredentials, $_NEW_USER); + $sql = sprintf("INSERT INTO User %s", + buildSQLInsert($arr)); + return DBInsert($sql); +} + +?> diff --git a/login.php b/login.php new file mode 100644 index 0000000..fa91e4e --- /dev/null +++ b/login.php @@ -0,0 +1,26 @@ +exists()) { + addUser($cred); + $Login->refresh(); + } + } else { + $err[] = 'Your SSL certificate failed to identify you.'; + } +} elseif (isPost()) { +} elseif (isSSL()) { + require('tpl/login_ssl.php'); +} else { +} + +if (isLoggedIn()) + redirect('main'); + +?> diff --git a/main.php b/main.php new file mode 100644 index 0000000..e69de29 diff --git a/mitsql.cfg.php b/mitsql.cfg.php new file mode 100755 index 0000000..ad086c0 --- /dev/null +++ b/mitsql.cfg.php @@ -0,0 +1,34 @@ + diff --git a/style.inc b/mitsql.css similarity index 100% rename from style.inc rename to mitsql.css diff --git a/mitsql.lib.php b/mitsql.lib.php deleted file mode 100755 index fbdba3c..0000000 --- a/mitsql.lib.php +++ /dev/null @@ -1,463 +0,0 @@ -username = $username; - $this->load(); - if ($this->fix()) - $this->load(); - } - } - function load() { - $rs = mysql_db_query(MANAGEDB, - sprintf(SQLSELECT1WHERE, - MANAGEOWNERTABLE, - 'name="'.mysql_escape_string($this->username).'"')); - //or exit(mysql_error()); - while($r = mysql_fetch_assoc($rs)) { - $this->r_owner = $r; - } - mysql_free_result($rs); - - $dbs = array(); - - $rs = mysql_db_query(MANAGEDB, - sprintf(SQLSELECTWHERE, - MANAGEDBTABLE, - 'owner="'.mysql_escape_string($this->username).'" ORDER BY name')) or exit(mysql_error()); - while($r = mysql_fetch_assoc($rs)) { - $t = $r; - $t['registered'] = 1; - $t['granted'] = 0; - $dbs[$r['name']] = $t; - } - mysql_free_result($rs); - - $rs = mysql_db_query('mysql', - sprintf(SQLSELECTWHERE, - MANAGEDBTABLE, - 'User="'.mysql_escape_string($this->username).'" ORDER BY Db')); - while($r = mysql_fetch_assoc($rs)) { - if (!isset($dbs[$r['Db']])) { - $dbs[$r['Db']] = array(); - $dbs[$r['Db']]['registered'] = 0; - } - $dbs[$r['Db']]['granted'] = 1; - $dbs[$r['Db']]['name'] = $r['Db']; - } - mysql_free_result($rs); - - foreach($dbs as $d1=>$d2) { - $size = getDBSize($d1); - if (is_null($size)) { - $dbs[$d1]['sizeNow'] = 0; - $dbs[$d1]['exists'] = 0; - } else { - $dbs[$d1]['sizeNow'] = $size; - $this->sizeNow += $size; - $dbs[$d1]['exists'] = 1; - } - } - $this->dbs = $dbs; - } - function isInit() { - return !is_null($this->r_owner); - } - function grantDB($dbname) { - mysql_query('GRANT ALL PRIVILEGES ON ' - .'`'.mysql_escape_string($dbname).'` . * ' - .'TO \''.mysql_escape_string($this->username).'\'@\'%\'') or exit(mysql_error()); - $this->flushPriv(); - } - function flushPriv() { - mysql_query('FLUSH PRIVILEGES') or exit(mysql_error()); - } - function revokeDB($dbname) { - mysql_db_query('mysql', - 'DELETE FROM `db` WHERE ' - .'User = \''.mysql_escape_string($this->username).'\' ' - .'AND Db = \''.mysql_escape_string($dbname).'\'') or exit(mysql_error()); - $this->flushPriv(); - } - function setPassword($password) { - mysql_query('SET PASSWORD FOR '. - '\''.mysql_escape_string($this->username).'\'@\'%\'='. - 'PASSWORD(\''.mysql_escape_string($password).'\')') or exit(mysql_error()); - } - function init($password, $maxDBs = DEFAULT_MAX_DBS, $maxSize = DEFAULT_MAX_SIZE) { - mysql_query('GRANT USAGE ON * . * TO '. - '\''.mysql_escape_string($this->username).'\'@\'%\' '. - 'IDENTIFIED BY \''.mysql_escape_string($password).'\'') or exit(mysql_error()); - $this->flushPriv(); - mysql_db_query(MANAGEDB, - sprintf(SQLINSERT, - MANAGEOWNERTABLE, - sprintf("'%s','%s','%s',NOW(),NOW()", - mysql_escape_string($this->username), - $maxDBs, - $maxSize))) or exit(mysql_error()); - } - function uninit() { - mysql_db_query('mysql', - 'DELETE FROM `user` WHERE ' - .'User = \''.mysql_escape_string($this->username).'\'') or exit(mysql_error()); - mysql_db_query('mysql', - 'DELETE FROM `db` WHERE ' - .'User = \''.mysql_escape_string($this->username).'\'') or exit(mysql_error()); - mysql_db_query('mysql', - 'DELETE FROM `tables_priv` WHERE ' - .'User = \''.mysql_escape_string($this->username).'\'') or exit(mysql_error()); - mysql_db_query('mysql', - 'DELETE FROM `columns_priv` WHERE ' - .'User = \''.mysql_escape_string($this->username).'\'') or exit(mysql_error()); - mysql_query('FLUSH PRIVILEGES') or exit(mysql_error()); - - mysql_db_query(MANAGEDB, - sprintf(SQLDELETE, - MANAGEDBTABLE, - 'owner=\''.mysql_escape_string($this->username).'\'')) or exit(mysql_error()); - - foreach($this->dbs as $db) - $this->dropDB($db['name']); - - mysql_db_query(MANAGEDB, - sprintf(SQLDELETE1, - MANAGEOWNERTABLE, - 'name=\''.mysql_escape_string($this->username).'\'')) or exit(mysql_error()); - } - function registerDB($name, $maxSize = DEFAULT_MAX_DB_SIZE) { - mysql_db_query(MANAGEDB, - sprintf(SQLINSERT, - MANAGEDBTABLE, - sprintf("'%s','%s','%s','',NOW(),NOW()", - mysql_escape_string($name), - mysql_escape_string($this->username), - $maxSize))) or exit(mysql_error()); - } - function unregisterDB($name) { - mysql_db_query(MANAGEDB, - sprintf(SQLDELETE1, - MANAGEDBTABLE, - 'name="'.mysql_escape_string($name).'"')) or exit(mysql_error()); - } - function createDB($name) { - @mysql_create_db($name); - } - function dropDB($name) { - @mysql_drop_db($name); - } - function addDB($name) { - if (!$this->isExists($name)) - $this->createDB($name); - if (!$this->isRegistered($name)) - $this->registerDB($name); - if (!$this->isGranted($name)) - $this->grantDB($name); - } - function delDB($name) { - $this->revokeDB($name); - $this->unregisterDB($name); - $this->dropDB($name); - } - function getNumDBs() { - return count($this->dbs); - } - function getTotalSize() { - return $this->sizeNow; - } - function isRegistered($name) { - if (isset($this->dbs[$name])) - return $this->dbs[$name]['registered']; - else return false; - } - function isExists($name) { - if (isset($this->dbs[$name])) - return $this->dbs[$name]['exists']; - else return false; - } - function isGranted($name) { - if (isset($this->dbs[$name])) - return $this->dbs[$name]['granted']; - else return false; - } - - function printOwnerHeader() { - echo ''; - echo '

'; - echo '

databases:

'; - echo '

storage:

'; - echo '

modified:

'; -// echo '

created:

'; - echo ''; - } - function printOwner() { - $obj_owner = $this->r_owner; - echo ''; - $this->printOwnerHeader(); - echo ''; - echo ''; - echo ''; - echo ''; - echo ''; - echo ''; - echo ''; - echo ''; -// echo ''; - echo ''; - echo '

status:

',$this->getNumDBs(),'

',sprintSize($this->getTotalSize()),'

limits:

',$obj_owner['maxDB'],'

',sprintSize($obj_owner['maxSize']),'

',sprintTS($obj_owner['modified']),'

',sprintTS($obj_owner['created']),'

'; - } - function printOwnerDBs($showOwner = false) { - $rows = $this->dbs; - echo ''; - echo '

name:

'; - echo '

owner:

'; - echo '

last size:

'; - echo '

size:

'; - echo '

modified:

'; -// echo '

created:

'; - echo ''; - foreach($rows as $name=>$r) { - echo ''; - echo '

',$name,'

'; - echo '

',$r['owner'],'

'; - /* echo '

',sprintSize($r['sizeMax']),'

';*/ - echo '

',sprintSize($r['sizeLast']),'

'; - echo '

',sprintSize($r['sizeNow']),'

'; - echo '

',sprintTS($r['modified']),'

'; -// echo '

',sprintTS($r['created']),'

'; - - echo '
'; - echo ''; - echo '
'; - - echo '
'; - echo ''; - echo '
'; - - echo '
'; - echo ''; - flush(); - } - } - function fix() { - $fixed = false; - foreach($this->dbs as $db) { - if (!$db['registered']) { - echo '

',TEXT_FIXREG,$db['name'],'

'; - flush(); - $this->registerDB($db['name']); - $fixed = true; - } - } - return $fixed; - } - function getNumMaxDBs() { - return $this->r_owner['maxDB']; - } -} - -class DBAdmin { - function mysqlDBList() { - $rs = mysql_query('SHOW DATABASES'); - $dbs = array(); - while($r = mysql_fetch_row($rs)) { - $dbs[$r[0]] = array(); - $dbs[$r[0]]['registered'] = 0; - $dbs[$r[0]]['granted'] = 0; - $dbs[$r[0]]['exists'] = 1; - $dbs[$r[0]]['owner'] = ''; - $dbs[$r[0]]['sizeLast'] = 0; - $dbs[$r[0]]['sizeNow'] = ''; - $dbs[$r[0]]['modified'] = ''; - $dbs[$r[0]]['created'] = ''; - } - return $dbs; - } - function fullDBList() { - //$dbs = array(); - $dbs = $this->mysqlDBList(); - - $rs = mysql_db_query(MANAGEDB, - sprintf(SQLSELECT, - MANAGEDBTABLE)) or exit(mysql_error()); - while($r = mysql_fetch_assoc($rs)) { - $t = $r; - $t['registered'] = 1; - $t['granted'] = 0; - $dbs[$r['name']] = $t; - } - mysql_free_result($rs); - - $rs = mysql_db_query('mysql', - sprintf(SQLSELECT, - MANAGEDBTABLE)); - while($r = mysql_fetch_assoc($rs)) { - if (!isset($dbs[$r['Db']])) { - $dbs[$r['Db']] = array(); - $dbs[$r['Db']]['registered'] = 0; - } - $dbs[$r['Db']]['granted'] = 1; - $dbs[$r['Db']]['name'] = $r['Db']; - } - mysql_free_result($rs); - - foreach($dbs as $d1=>$d2) { - $size = getDBSize($d1); - if (is_null($size)) { - $dbs[$d1]['sizeNow'] = 0; - $dbs[$d1]['exists'] = 0; - } else { - $dbs[$d1]['sizeNow'] = $size; - //$this->sizeNow += $size; - $dbs[$d1]['exists'] = 1; - } - } - return $dbs; - } - function printDBs($rows) { - if (ob_get_contents()) ob_end_flush(); - echo ''; - echo '

name:

'; - echo '

owner:

'; - echo '

last size:

'; - echo '

size:

'; - echo '

modified:

'; - echo '

created:

'; - echo ''; - foreach($rows as $name=>$r) { - echo ''; - echo '

',$name,'

'; -// echo '

',$r['owner'],'

'; - echo '

',$r['owner'],'

'; - /* echo '

',sprintSize($r['sizeMax']),'

';*/ - echo '

',sprintSize($r['sizeLast']),'

'; - echo '

',sprintSize($r['sizeNow']),'

'; - echo '

',sprintTS($r['modified']),'

'; - echo '

',sprintTS($r['created']),'

'; - - echo '
';
-			echo $r['exists']?'E':'';
-			echo $r['granted']?'G':'';
-			echo $r['registered']?'R':'';
-			echo '
'; - - echo '
'; - echo ''; - echo '
'; - - echo '
'; - echo ''; - echo '
'; - - echo '
'; - echo ''; - flush(); - } - } -} diff --git a/test.php b/test.php index 7eb69e2..bbea2a4 100755 --- a/test.php +++ b/test.php @@ -1,3 +1,20 @@
MIT SQL Service Management v
+Direct comments and bugs to: presbrey@mit.edu
+ + diff --git a/tpl/head.inc b/tpl/head.inc new file mode 100644 index 0000000..933a684 --- /dev/null +++ b/tpl/head.inc @@ -0,0 +1,7 @@ + + + MIT SQL Services for Athena + + + + diff --git a/tpl/index.php b/tpl/index.php new file mode 100644 index 0000000..c00f14f --- /dev/null +++ b/tpl/index.php @@ -0,0 +1,7 @@ + diff --git a/tpl/login_ssl.php b/tpl/login_ssl.php new file mode 100644 index 0000000..4526cc0 --- /dev/null +++ b/tpl/login_ssl.php @@ -0,0 +1,13 @@ + + +
+
+
+ +
+ + -- 2.45.1