From fae0d1392935202675dc909e37087f392083269e Mon Sep 17 00:00:00 2001 From: Jens Frank Date: Tue, 24 Aug 2004 20:41:07 +0000 Subject: [PATCH] Split user table into two parts: user and user_rights, for single login. BUG#57 --- config/index.php | 1 + includes/SpecialListadmins.php | 8 ++++---- includes/SpecialListusers.php | 7 ++++--- includes/SpecialMakesysop.php | 22 ++++++++++++++-------- includes/SpecialStatistics.php | 5 ++--- includes/User.php | 17 +++++++++++++---- maintenance/archives/patch-user_rights.sql | 16 ++++++++++++++++ maintenance/tables.sql | 7 ++++++- maintenance/updaters.inc | 11 +++++++++++ maintenance/users.sql | 12 +++++++++--- 10 files changed, 80 insertions(+), 26 deletions(-) create mode 100644 maintenance/archives/patch-user_rights.sql diff --git a/config/index.php b/config/index.php index 3df6ad8cac..0f6f2352e7 100644 --- a/config/index.php +++ b/config/index.php @@ -479,6 +479,7 @@ if( $conf->posted && ( 0 == count( $errs ) ) ) { do_categorylinks_update(); flush(); do_image_name_unique_update(); flush(); do_logging_update(); flush(); + do_user_rights_update(); flush(); if ( isTemplateInitialised() ) { print "Template namespace already initialised\n"; diff --git a/includes/SpecialListadmins.php b/includes/SpecialListadmins.php index 41588d9650..39d86fdc26 100644 --- a/includes/SpecialListadmins.php +++ b/includes/SpecialListadmins.php @@ -18,11 +18,11 @@ class ListAdminsPage extends PageQueryPage { function getSQL() { $dbr =& wfGetDB( DB_SLAVE ); $user = $dbr->tableName( 'user' ); + $user_rights = $dbr->tableName( 'user_rights' ); $userspace = Namespace::getUser(); - return 'SELECT user_rights as type,'.$userspace.' as namespace,'. - 'user_name as title, user_name as value '. - "FROM $user ". - 'WHERE user_rights LIKE "%sysop%"'; + return "SELECT r.user_rights as type,{$userspace} as namespace,". + "u.user_name as title, u.user_name as value ". + "FROM {$user} u,{$user_rights} r WHERE r.user_id=u.user_id AND r.user_rights LIKE \"%sysop%\""; } } diff --git a/includes/SpecialListusers.php b/includes/SpecialListusers.php index 0d8aa6145e..1e32ade1c1 100644 --- a/includes/SpecialListusers.php +++ b/includes/SpecialListusers.php @@ -14,10 +14,11 @@ class ListUsersPage extends QueryPage { function getSQL() { $dbr =& wfGetDB( DB_SLAVE ); - $usertable = $dbr->tableName( 'user' ); + $user = $dbr->tableName( 'user' ); + $user_rights = $dbr->tableName( 'user_rights' ); $userspace = Namespace::getUser(); - return "SELECT user_rights as type, $userspace as namespace, user_name as title, " . - "user_name as value FROM $usertable"; + return "SELECT r.user_rights as type, $userspace as namespace, u.user_name as title, " . + "u.user_name as value FROM $user u LEFT JOIN $user_rights r ON u.user_id = r.user_id"; } function sortDescending() { diff --git a/includes/SpecialMakesysop.php b/includes/SpecialMakesysop.php index cc3cbf8b62..15fa12013a 100644 --- a/includes/SpecialMakesysop.php +++ b/includes/SpecialMakesysop.php @@ -123,16 +123,20 @@ class MakesysopForm { { global $wgOut, $wgUser, $wgLang; global $wgDBname, $wgMemc, $wgLocalDatabases; + + $fname = 'MakesysopForm::doSubmit'; $dbw =& wfGetDB( DB_MASTER ); - $parts = explode( "@", $this->mUser ); - $usertable = $dbw->tableName( 'user' ); + $parts = explode( '@', $this->mUser ); + $user_rights = $dbw->tableName( 'user_rights' ); + $usertable = $dbw->tableName( 'user' ); - if( count( $parts ) == 2 && $wgUser->isDeveloper() && strpos( '.', $usertable ) === false ){ + if( count( $parts ) == 2 && $wgUser->isDeveloper() && strpos( '.', $user_rights ) === false ){ $username = $dbw->strencode( $parts[0] ); if ( array_key_exists( $parts[1], $wgLocalDatabases ) ) { $dbName = $wgLocalDatabases[$parts[1]]; - $usertable = $dbName . "." . $usertable; + $user_rights = $dbName . '.' . $user_rights; + $usertable = $usertable . '.' . $usertable; } else { $this->showFail(); return; @@ -143,10 +147,10 @@ class MakesysopForm { } if ( $username{0} == "#" ) { $id = intval( substr( $username, 1 ) ); - $sql = "SELECT user_id,user_rights FROM $usertable WHERE user_id=$id FOR UPDATE"; + $sql = "SELECT user_id,user_rights FROM $user_rights WHERE user_id=$id FOR UPDATE"; } else { $encName = $dbw->strencode( $username ); - $sql = "SELECT user_id, user_rights FROM $usertable WHERE user_name = '{$username}' FOR UPDATE"; + $sql = "SELECT u.user_id, user_rights FROM $usertable u LEFT JOIN $user_rights r ON u.user_id=r.user_id WHERE user_name = '{$username}' FOR UPDATE"; } $prev = $dbw->ignoreErrors( TRUE ); @@ -190,8 +194,10 @@ class MakesysopForm { if ( count( $rightsNotation ) == 0 ) { $this->showFail(); } else { - $sql = "UPDATE $usertable SET user_rights = '{$newrights}' WHERE user_id = $id LIMIT 1"; - $dbw->query($sql); + #$sql = "UPDATE $user_rights SET user_rights = '{$newrights}' WHERE user_id = $id LIMIT 1"; + #$dbw->query($sql); + $dbw->replace( $user_rights, array( array( 'user_id', 'user_rights' )), + array( 'user_id' => $id, 'user_rights' => $newrights ) , $fname ); $wgMemc->delete( "$dbName:user:id:$id" ); $log = new LogPage( 'rights' ); diff --git a/includes/SpecialStatistics.php b/includes/SpecialStatistics.php index 6995c033e7..67fcc61f01 100644 --- a/includes/SpecialStatistics.php +++ b/includes/SpecialStatistics.php @@ -8,7 +8,7 @@ function wfSpecialStatistics() $wgOut->addHTML( "

" . wfMsg( "sitestats" ) . "

\n" ); $dbr =& wfGetDB( DB_SLAVE ); - extract( $dbr->tableNames( 'cur', 'site_stats', 'user' ) ); + extract( $dbr->tableNames( 'cur', 'site_stats', 'user', 'user_rights' ) ); $sql = "SELECT COUNT(cur_id) AS total FROM $cur"; $res = $dbr->query( $sql, $fname ); @@ -39,8 +39,7 @@ function wfSpecialStatistics() $row = $dbr->fetchObject( $res ); $total = $row->total; - $sql = "SELECT COUNT(user_id) AS total FROM $user " . - "WHERE user_rights LIKE '%sysop%'"; + $sql = "SELECT COUNT(user_id) AS total FROM $user_rights WHERE user_rights LIKE '%sysop%'"; $res = $dbr->query( $sql, $fname ); $row = $dbr->fetchObject( $res ); $admins = $row->total; diff --git a/includes/User.php b/includes/User.php index 294923d67e..7c3fb2f95c 100644 --- a/includes/User.php +++ b/includes/User.php @@ -266,7 +266,7 @@ class User { } # the following stuff is for non-anonymous users only $s = $dbr->getArray( 'user', array( 'user_name','user_password','user_newpassword','user_email', - 'user_real_name','user_options','user_rights','user_touched' ), + 'user_real_name','user_options','user_touched' ), array( 'user_id' => $this->mId ), $fname ); if ( $s !== false ) { @@ -276,8 +276,10 @@ class User { $this->mPassword = $s->user_password; $this->mNewpassword = $s->user_newpassword; $this->decodeOptions( $s->user_options ); - $this->mRights = explode( ",", strtolower( $s->user_rights ) ); $this->mTouched = wfTimestamp(TS_MW,$s->user_touched); + $this->mRights = explode( ",", strtolower( + $dbr->getField( 'user_rights', 'user_rights', array( 'user_id' => $this->mId ) ) + ) ); } $this->mDataLoaded = true; @@ -573,12 +575,13 @@ class User { 'user_real_name' => $this->mRealName, 'user_email' => $this->mEmail, 'user_options' => $this->encodeOptions(), - 'user_rights' => implode( ",", $this->mRights ), 'user_touched' => $dbw->timestamp($this->mTouched) ), array( /* WHERE */ 'user_id' => $this->mId ), $fname ); + $dbw->set( 'user_rights', 'user_rights', implode( ",", $this->mRights ), + 'user_id='. $this->mId, $fname ); $wgMemc->delete( "$wgDBname:user:id:$this->mId" ); } @@ -611,11 +614,17 @@ class User { 'user_newpassword' => $this->mNewpassword, 'user_email' => $this->mEmail, 'user_real_name' => $this->mRealName, - 'user_rights' => implode( ',', $this->mRights ), 'user_options' => $this->encodeOptions() ), $fname ); $this->mId = $dbw->insertId(); + $dbw->insert( 'user_rights', + array( + 'user_id' => $this->mId, + 'user_rights' => implode( ',', $this->mRights ) + ), $fname + ); + } function spreadBlock() diff --git a/maintenance/archives/patch-user_rights.sql b/maintenance/archives/patch-user_rights.sql new file mode 100644 index 0000000000..5d799dea44 --- /dev/null +++ b/maintenance/archives/patch-user_rights.sql @@ -0,0 +1,16 @@ +-- Split user table into two parts: +-- user +-- user_rights +-- The later contains only the permissions of the user. This way, +-- you can store the accounts for several wikis in one central +-- database but keep user rights local to the wiki. + +CREATE TABLE user_rights ( + user_id int(5) unsigned NOT NULL, + user_rights tinyblob NOT NULL default '', + UNIQUE KEY user_id (user_id) +) PACK_KEYS=1; + +INSERT INTO user_rights SELECT user_id,user_rights FROM user; + +ALTER TABLE user DROP COLUMN user_rights; diff --git a/maintenance/tables.sql b/maintenance/tables.sql index 892c271826..2eb8520ac1 100644 --- a/maintenance/tables.sql +++ b/maintenance/tables.sql @@ -8,7 +8,6 @@ CREATE TABLE user ( user_id int(5) unsigned NOT NULL auto_increment, user_name varchar(255) binary NOT NULL default '', user_real_name varchar(255) binary NOT NULL default '', - user_rights tinyblob NOT NULL default '', user_password tinyblob NOT NULL default '', user_newpassword tinyblob NOT NULL default '', user_email tinytext NOT NULL default '', @@ -17,6 +16,12 @@ CREATE TABLE user ( UNIQUE KEY user_id (user_id) ) PACK_KEYS=1; +CREATE TABLE user_rights ( + user_id int(5) unsigned NOT NULL, + user_rights tinyblob NOT NULL default '', + UNIQUE KEY user_id (user_id) +) PACK_KEYS=1; + CREATE TABLE user_newtalk ( user_id int(5) NOT NULL default '0', user_ip varchar(40) NOT NULL default '' diff --git a/maintenance/updaters.inc b/maintenance/updaters.inc index 54b3f04be4..dcfab0bca5 100644 --- a/maintenance/updaters.inc +++ b/maintenance/updaters.inc @@ -217,4 +217,15 @@ function do_logging_update() { } } +function do_user_rights_update() { + global $wgDatabase; + if ( $wgDatabase->tableExists( 'user_rights' ) ) { + echo "...user_rights table already exists.\n"; + } else { + echo 'Creating user rights table...'; + dbsource( 'maintenance/archives/patch-user_rights.sql', $wgDatabase ); + echo "ok\n"; + } +} + ?> diff --git a/maintenance/users.sql b/maintenance/users.sql index 0c2ce7c1b1..dc23ed356f 100644 --- a/maintenance/users.sql +++ b/maintenance/users.sql @@ -18,7 +18,9 @@ GRANT DELETE,INSERT,SELECT,UPDATE ON `{$wgDBname}`.* GRANT DELETE,INSERT,SELECT,UPDATE ON `{$wgDBname}`.* TO {$wgDBuser}@localhost.localdomain IDENTIFIED BY '{$wgDBpassword}'; -GRANT SELECT (user_id,user_name,user_rights,user_options) ON `{$wgDBname}`.user +GRANT SELECT (user_id,user_name,user_options) ON `{$wgDBname}`.user + TO {$wgDBsqluser}@'%' IDENTIFIED BY '{$wgDBsqlpassword}'; +GRANT SELECT ON `{$wgDBname}`.user_rights TO {$wgDBsqluser}@'%' IDENTIFIED BY '{$wgDBsqlpassword}'; GRANT SELECT ON `{$wgDBname}`.cur TO {$wgDBsqluser}@'%' IDENTIFIED BY '{$wgDBsqlpassword}'; @@ -47,9 +49,11 @@ GRANT SELECT ON `{$wgDBname}`.watchlist GRANT SELECT ON `{$wgDBname}`.math TO {$wgDBsqluser}@'%' IDENTIFIED BY '{$wgDBsqlpassword}'; -GRANT SELECT (user_id,user_name,user_rights,user_options) +GRANT SELECT (user_id,user_name,user_options) ON `{$wgDBname}`.user TO {$wgDBsqluser}@localhost IDENTIFIED BY '{$wgDBsqlpassword}'; +GRANT SELECT ON `{$wgDBname}`.user_rights + TO {$wgDBsqluser}@localhost IDENTIFIED BY '{$wgDBsqlpassword}'; GRANT SELECT ON `{$wgDBname}`.cur TO {$wgDBsqluser}@localhost IDENTIFIED BY '{$wgDBsqlpassword}'; GRANT SELECT ON `{$wgDBname}`.old @@ -77,9 +81,11 @@ GRANT SELECT ON `{$wgDBname}`.watchlist GRANT SELECT ON `{$wgDBname}`.math TO {$wgDBsqluser}@localhost IDENTIFIED BY '{$wgDBsqlpassword}'; -GRANT SELECT (user_id,user_name,user_rights,user_options) +GRANT SELECT (user_id,user_name,user_options) ON `{$wgDBname}`.user TO {$wgDBsqluser}@localhost.localdomain IDENTIFIED BY '{$wgDBsqlpassword}'; +GRANT SELECT ON `{$wgDBname}`.user_rights + TO {$wgDBsqluser}@localhost.localdomain IDENTIFIED BY '{$wgDBsqlpassword}'; GRANT SELECT ON `{$wgDBname}`.cur TO {$wgDBsqluser}@localhost.localdomain IDENTIFIED BY '{$wgDBsqlpassword}'; GRANT SELECT ON `{$wgDBname}`.old -- 2.20.1