1<?php 2/** 3 * Copyright 2002-2017 Horde LLC (http://www.horde.org/) 4 * 5 * See the enclosed file COPYING for license information (LGPL). If you did 6 * not receive this file, see http://www.horde.org/licenses/lgpl21. 7 * 8 * @author Ilya Krel <mail@krel.org> 9 * @author Jan Schneider <jan@horde.org> 10 * @category Horde 11 * @license http://www.horde.org/licenses/lgpl21 LGPL-2.1 12 * @package Auth 13 */ 14 15/** 16 * The Horde_Auth_Cyrsql class provides a SQL implementation of the Horde 17 * authentication system for the Cyrus IMAP server. Most of the functionality 18 * is the same as for the SQL class; only what is different overrides the 19 * parent class implementations. 20 * 21 * The table structure for the auth system is as follows: 22 * <pre> 23 * CREATE TABLE accountuser ( 24 * username VARCHAR(255) BINARY NOT NULL DEFAULT '', 25 * password VARCHAR(32) BINARY NOT NULL DEFAULT '', 26 * prefix VARCHAR(50) NOT NULL DEFAULT '', 27 * domain_name VARCHAR(255) NOT NULL DEFAULT '', 28 * UNIQUE KEY username (username) 29 * ); 30 * 31 * CREATE TABLE adminuser ( 32 * username VARCHAR(50) BINARY NOT NULL DEFAULT '', 33 * password VARCHAR(50) BINARY NOT NULL DEFAULT '', 34 * type INT(11) NOT NULL DEFAULT '0', 35 * SID VARCHAR(255) NOT NULL DEFAULT '', 36 * home VARCHAR(255) NOT NULL DEFAULT '', 37 * PRIMARY KEY (username) 38 * ); 39 * 40 * CREATE TABLE alias ( 41 * alias VARCHAR(255) NOT NULL DEFAULT '', 42 * dest LONGTEXT, 43 * username VARCHAR(50) NOT NULL DEFAULT '', 44 * status INT(11) NOT NULL DEFAULT '1', 45 * PRIMARY KEY (alias) 46 * ); 47 * 48 * CREATE TABLE domain ( 49 * domain_name VARCHAR(255) NOT NULL DEFAULT '', 50 * prefix VARCHAR(50) NOT NULL DEFAULT '', 51 * maxaccounts INT(11) NOT NULL DEFAULT '20', 52 * quota INT(10) NOT NULL DEFAULT '20000', 53 * transport VARCHAR(255) NOT NULL DEFAULT 'cyrus', 54 * freenames ENUM('YES','NO') NOT NULL DEFAULT 'NO', 55 * freeaddress ENUM('YES','NO') NOT NULL DEFAULT 'NO', 56 * PRIMARY KEY (domain_name), 57 * UNIQUE KEY prefix (prefix) 58 * ); 59 * 60 * CREATE TABLE domainadmin ( 61 * domain_name VARCHAR(255) NOT NULL DEFAULT '', 62 * adminuser VARCHAR(255) NOT NULL DEFAULT '' 63 * ); 64 * 65 * CREATE TABLE search ( 66 * search_id VARCHAR(255) NOT NULL DEFAULT '', 67 * search_sql TEXT NOT NULL, 68 * perpage INT(11) NOT NULL DEFAULT '0', 69 * timestamp TIMESTAMP(14) NOT NULL, 70 * PRIMARY KEY (search_id), 71 * KEY search_id (search_id) 72 * ); 73 * 74 * CREATE TABLE virtual ( 75 * alias VARCHAR(255) NOT NULL DEFAULT '', 76 * dest LONGTEXT, 77 * username VARCHAR(50) NOT NULL DEFAULT '', 78 * status INT(11) NOT NULL DEFAULT '1', 79 * KEY alias (alias) 80 * ); 81 * 82 * CREATE TABLE log ( 83 * id INT(11) NOT NULL AUTO_INCREMENT, 84 * msg TEXT NOT NULL, 85 * user VARCHAR(255) NOT NULL DEFAULT '', 86 * host VARCHAR(255) NOT NULL DEFAULT '', 87 * time DATETIME NOT NULL DEFAULT '2000-00-00 00:00:00', 88 * pid VARCHAR(255) NOT NULL DEFAULT '', 89 * PRIMARY KEY (id) 90 * ); 91 * </pre> 92 * 93 * @author Ilya Krel <mail@krel.org> 94 * @author Jan Schneider <jan@horde.org> 95 * @category Horde 96 * @copyright 2002-2017 Horde LLC 97 * @license http://www.horde.org/licenses/lgpl21 LGPL-2.1 98 * @package Auth 99 */ 100class Horde_Auth_Cyrsql extends Horde_Auth_Sql 101{ 102 /** 103 * An array of capabilities, so that the driver can report which 104 * operations it supports and which it doesn't. 105 * 106 * @var array 107 */ 108 protected $_capabilities = array( 109 'add' => true, 110 'list' => true, 111 'remove' => true, 112 'resetpassword' => false, 113 'update' => true, 114 'authenticate' => true, 115 ); 116 117 /** 118 * Horde_Imap_Client object. 119 * 120 * @var Horde_Imap_Client_Base 121 */ 122 protected $_imap; 123 124 /** 125 * Constructor. 126 * 127 * @param array $params Parameters: 128 * - domain_field: (string) If set to anything other than 'none' this is 129 * used as field name where domain is stored. 130 * DEFAULT: 'domain_name' 131 * - folders: (array) An array of folders to create under username. 132 * DEFAULT: NONE 133 * - hidden_accounts: (array) An array of system accounts to hide from 134 * the user interface. 135 * DEFAULT: None. 136 * - imap: (Horde_Imap_Client_Base) [REQUIRED] An IMAP client object. 137 * - quota: (integer) The quota (in kilobytes) to grant on the mailbox. 138 * DEFAULT: NONE 139 * - userhierarchy: (string) The user hierarchy prefix (UTF-8). 140 * DEFAULT: 'user.' 141 * 142 * @throws InvalidArgumentException 143 */ 144 public function __construct(array $params = array()) 145 { 146 if (!isset($params['imap']) || 147 !($params['imap'] instanceof Horde_Imap_Client_Base)) { 148 throw new InvalidArgumentException('Missing imap parameter.'); 149 } 150 $this->_imap = $params['imap']; 151 unset($params['imap']); 152 153 $params = array_merge(array( 154 'domain_field' => 'domain_name', 155 'folders' => array(), 156 'hidden_accounts' => array('cyrus'), 157 'quota' => null, 158 'userhierarchy' => 'user.' 159 ), $params); 160 161 parent::__construct($params); 162 } 163 164 /** 165 * Find out if a set of login credentials are valid. 166 * 167 * @param string $userId The userId to check. 168 * @param array $credentials The credentials to use. 169 * 170 * @throws Horde_Auth_Exception 171 */ 172 protected function _authenticate($userId, $credentials) 173 { 174 if (!empty($this->_params['domain_field']) && 175 ($this->_params['domain_field'] != 'none')) { 176 /* Build the SQL query with domain. */ 177 $query = sprintf('SELECT * FROM %s WHERE %s = ? AND %s = ?', 178 $this->_params['table'], 179 $this->_params['username_field'], 180 $this->_params['domain_field']); 181 $values = explode('@', $userId); 182 } else { 183 /* Build the SQL query without domain. */ 184 $query = sprintf('SELECT * FROM %s WHERE %s = ?', 185 $this->_params['table'], 186 $this->_params['username_field']); 187 $values = array($userId); 188 } 189 190 try { 191 $row = $this->_db->selectOne($query, $values); 192 } catch (Horde_Db_Exception $e) { 193 throw new Horde_Auth_Exception('', Horde_Auth::REASON_FAILED); 194 } 195 196 if (!$row || 197 !$this->_comparePasswords($row[$this->_params['password_field']], $credentials['password'])) { 198 throw new Horde_Auth_Exception('', Horde_Auth::REASON_BADLOGIN); 199 } 200 201 $now = time(); 202 if (!empty($this->_params['hard_expiration_field']) && 203 !empty($row[$this->_params['hard_expiration_field']]) && 204 ($now > $row[$this->_params['hard_expiration_field']])) { 205 throw new Horde_Auth_Exception('', Horde_Auth::REASON_EXPIRED); 206 } 207 208 if (!empty($this->_params['soft_expiration_field']) && 209 !empty($row[$this->_params['soft_expiration_field']]) && 210 ($now > $row[$this->_params['soft_expiration_field']])) { 211 $this->setCredential('change', true); 212 } 213 } 214 215 /** 216 * Add a set of authentication credentials. 217 * 218 * @param string $userId The userId to add. 219 * @param array $credentials The credentials to add. 220 * 221 * @throw Horde_Auth_Exception 222 */ 223 public function addUser($userId, $credentials) 224 { 225 if (!empty($this->_params['domain_field']) && 226 ($this->_params['domain_field'] != 'none')) { 227 list($name, $domain) = explode('@', $userId); 228 229 $query = sprintf('INSERT INTO %s (%s, %s, %s) VALUES (?, ?, ?)', 230 $this->_params['table'], 231 $this->_params['username_field'], 232 $this->_params['domain_field'], 233 $this->_params['password_field']); 234 $values = array( 235 $name, 236 $domain, 237 Horde_Auth::getCryptedPassword($credentials['password'], 238 '', 239 $this->_params['encryption'], 240 $this->_params['show_encryption']) 241 ); 242 243 $query2 = 'INSERT INTO virtual (alias, dest, username, status) VALUES (?, ?, ?, 1)'; 244 $values2 = array($userId, $userId, $name); 245 246 try { 247 $this->_db->insert($query, $values); 248 $this->_db->insert($query2, $values2); 249 } catch (Horde_Db_Exception $e) { 250 throw new Horde_Auth_Exception($e); 251 } 252 } else { 253 parent::addUser($userId, $credentials); 254 } 255 256 $mailbox = $this->_params['userhierarchy'] . $userId; 257 258 try { 259 $this->_imap->createMailbox($mailbox); 260 $this->_imap->setACL($mailbox, $this->_params['cyradmin'], array('rights' => 'lrswipcda')); 261 if (isset($this->_params['quota']) && 262 ($this->_params['quota'] >= 0)) { 263 $this->_imap->setQuota($mailbox, array('storage' => $this->_params['quota'])); 264 } 265 } catch (Horde_Imap_Client_Exception $e) { 266 throw new Horde_Auth_Exception($e); 267 } 268 269 foreach ($this->_params['folders'] as $val) { 270 try { 271 $this->_imap->createMailbox($val); 272 $this->_imap->setACL($val, $this->_params['cyradmin'], array('rights' => 'lrswipcda')); 273 } catch (Horde_Imap_Client_Exception $e) {} 274 } 275 } 276 277 /** 278 * Delete a set of authentication credentials. 279 * 280 * @param string $userId The userId to delete. 281 * 282 * @throws Horde_Auth_Exception 283 */ 284 public function removeUser($userId) 285 { 286 if (!empty($this->_params['domain_field']) && 287 ($this->_params['domain_field'] != 'none')) { 288 list($name, $domain) = explode('@', $userId); 289 290 /* Build the SQL query. */ 291 $query = sprintf('DELETE FROM %s WHERE %s = ? and %s = ?', 292 $this->_params['table'], 293 $this->_params['username_field'], 294 $this->_params['domain_field']); 295 $values = array($name, $domain); 296 297 $query2 = 'DELETE FROM virtual WHERE dest = ?'; 298 $values2 = array($userId); 299 300 try { 301 $this->_db->delete($query, $values); 302 $this->_db->delete($query2, $values2); 303 } catch (Horde_Db_Exception $e) { 304 throw new Horde_Auth_Exception($e); 305 } 306 } else { 307 parent::removeUser($userId); 308 } 309 310 /* Set ACL for mailbox deletion. */ 311 list($admin) = explode('@', $this->_params['cyradmin']); 312 313 $mailbox = $this->_params['userhierarchy'] . $userId; 314 315 try { 316 $this->_imap->setACL($mailbox, $admin, array('rights' => 'lrswipcda')); 317 $this->_imap->deleteMailbox($mailbox); 318 } catch (Horde_Imap_Client_Exception $e) { 319 throw new Horde_Auth_Exception($e); 320 } 321 } 322 323 /** 324 * List all users in the system. 325 * 326 * @param boolean $sort Sort the users? 327 * 328 * @return mixed The array of userIds. 329 * @throws Horde_Auth_Exception 330 */ 331 public function listUsers($sort = false) 332 { 333 if (!empty($this->_params['domain_field']) && 334 ($this->_params['domain_field'] != 'none')) { 335 /* Build the SQL query with domain. */ 336 $query = sprintf('SELECT %s, %s FROM %s', 337 $this->_params['username_field'], 338 $this->_params['domain_field'], 339 $this->_params['table']); 340 } else { 341 /* Build the SQL query without domain. */ 342 $query = sprintf('SELECT %s FROM %s', 343 $this->_params['username_field'], 344 $this->_params['table']); 345 } 346 if ($sort) { 347 $query .= sprintf(" ORDER BY %s", $this->_params['username_field']); 348 } 349 350 try { 351 $result = $this->_db->select($query); 352 } catch (Horde_Db_Exception $e) { 353 throw new Horde_Auth_Exception($e); 354 } 355 356 /* Loop through and build return array. */ 357 $users = array(); 358 if (!empty($this->_params['domain_field']) && 359 ($this->_params['domain_field'] != 'none')) { 360 foreach ($result as $ar) { 361 if (!in_array($ar[$this->_params['username_field']], $this->_params['hidden_accounts'])) { 362 $users[] = $ar[$this->_params['username_field']] . '@' . $ar[$this->_params['domain_field']]; 363 } 364 } 365 } else { 366 foreach ($result as $ar) { 367 if (!in_array($ar[$this->_params['username_field']], $this->_params['hidden_accounts'])) { 368 $users[] = $ar[$this->_params['username_field']]; 369 } 370 } 371 } 372 return $users; 373 } 374 375 /** 376 * Update a set of authentication credentials. 377 * 378 * @param string $oldID The old userId. 379 * @param string $newID The new userId. [NOT SUPPORTED] 380 * @param array $credentials The new credentials 381 * 382 * @throws Horde_Auth_Exception 383 */ 384 public function updateUser($oldID, $newID, $credentials) 385 { 386 if (!empty($this->_params['domain_field']) && 387 ($this->_params['domain_field'] != 'none')) { 388 list($name, $domain) = explode('@', $oldID); 389 /* Build the SQL query with domain. */ 390 $query = sprintf( 391 'UPDATE %s SET %s = ? WHERE %s = ? and %s = ?', 392 $this->_params['table'], 393 $this->_params['password_field'], 394 $this->_params['username_field'], 395 $this->_params['domain_field'] 396 ); 397 $values = array( 398 Horde_Auth::getCryptedPassword($credentials['password'], '', $this->_params['encryption'], $this->_params['show_encryption']), 399 $name, 400 $domain 401 ); 402 } else { 403 /* Build the SQL query. */ 404 $query = sprintf( 405 'UPDATE %s SET %s = ? WHERE %s = ?', 406 $this->_params['table'], 407 $this->_params['password_field'], 408 $this->_params['username_field'] 409 ); 410 $values = array( 411 Horde_Auth::getCryptedPassword($credentials['password'], '', $this->_params['encryption'], $this->_params['show_encryption']), 412 $oldID 413 ); 414 } 415 416 try { 417 $this->_db->update($query, $values); 418 } catch (Horde_Db_Exception $e) { 419 throw new Horde_Auth_Exception($e); 420 } 421 } 422 423} 424