User:RichMorin/mw user

The user table contains basic account information, authentication keys, etc.

Some multi-wiki sites may share a single central user table between separate wikis, using the $wgSharedDB setting.

Note that when a external authentication plugin is used, user table entries still need to be created to store preferences and to key tracking information in the other tables.

Inter-table Relationships

NA

MySQL Table Description

mysql> desc mw_user;
+--------------------------+-----------------+------+-----+---------+----------------+
| Field                    | Type            | Null | Key | Default | Extra          |
+--------------------------+-----------------+------+-----+---------+----------------+
| user_id                  | int(5) unsigned |      | PRI | NULL    | auto_increment |
| user_name                | varchar(255)    |      | UNI |         |                |
| user_real_name           | varchar(255)    |      |     |         |                |
| user_password            | tinyblob        |      |     |         |                |
| user_newpassword         | tinyblob        |      |     |         |                |
| user_email               | tinytext        |      |     |         |                |
| user_options             | blob            |      |     |         |                |
| user_touched             | varchar(14)     |      |     |         |                |
| user_token               | varchar(32)     |      |     |         |                |
| user_email_authenticated | varchar(14)     | YES  |     | NULL    |                |
| user_email_token         | varchar(32)     | YES  | MUL | NULL    |                |
| user_email_token_expires | varchar(14)     | YES  |     | NULL    |                |
| user_registration        | varchar(14)     | YES  |     | NULL    |                |
+--------------------------+-----------------+------+-----+---------+----------------+
13 rows in set

Annotated Table Creation Code

CREATE TABLE /*$wgDBprefix*/user (

  user_id         int(5)         unsigned     NOT NULL  auto_increment,
  
  -- Usernames must be unique, must not be in the form of
  -- an IP address. _Shouldn't_ allow slashes or case
  -- conflicts. Spaces are allowed, and are _not_ converted
  -- to underscores like titles. See the User::newFromName() for
  -- the specific tests that usernames have to pass.

  user_name       varchar(255)   binary       NOT NULL  default '',
  
  -- Optional 'real name' to be displayed in credit listings

  user_real_name  varchar(255)   binary       NOT NULL  default '',
  
  -- Password hashes, normally hashed like so:
  --   MD5(CONCAT(user_id,'-',MD5(plaintext_password)))
  --
  -- See wfEncryptPassword() in GlobalFunctions.php

  user_password   tinyblob                    NOT NULL  default '',
  
  -- When using 'mail me a new password', a random
  -- password is generated and the hash stored here.
  -- The previous password is left in place until
  -- someone actually logs in with the new password,
  -- at which point the hash is moved to user_password
  -- and the old password is invalidated.

  user_newpassword  tinyblob                  NOT NULL  default '',
  
  -- Note: email should be restricted, not public info.
  -- Same with passwords.

  user_email      tinytext                    NOT NULL  default '',
  
  -- Newline-separated list of name=value pairs,
  -- defining the user preferences

  user_options    blob                        NOT NULL  default '',
  
  -- This is a timestamp which is updated when a user
  -- logs in, logs out, changes preferences, or performs
  -- some other action requiring HTML cache invalidation
  -- to ensure that the UI is updated.

  user_touched    char(14)       binary       NOT NULL  default '',
  
  -- A pseudorandomly generated value that is stored in
  -- a cookie when the "remember password" feature is
  -- used (previously, a hash of the password was used, but
  -- this was vulnerable to cookie-stealing attacks)

  user_token      char(32)       binary       NOT NULL  default '',
  
  -- Initially NULL; when a user's e-mail address has been
  -- validated by returning with a mailed token, this is
  -- set to the current timestamp.

  user_email_authenticated  char(14) binary,
  
  -- Randomly generated token created when the e-mail address
  -- is set and a confirmation test mail sent.

  user_email_token  char(32)     binary,
  
  -- Expiration date for the user_email_token

  user_email_token_expires  char(14) binary,

  -- Timestamp of account registration.
  -- Accounts predating this schema addition may contain NULL.

  user_registration  char(14)    binary,


  PRIMARY KEY  user_id (user_id),
  UNIQUE INDEX user_name (user_name),
  INDEX        (user_email_token)

) ENGINE=InnoDB;