Manual:user table

From MediaWiki.org
Jump to: navigation, search

Other languages:
English • ‎español • ‎日本語 • ‎polski
Manual:ContentsManual:Contents MediaWiki database layoutManual:Database layout user table


The user table is where MediaWiki stores information about users. If using Postgres, this table is named mwuser.

Fields[edit]

Some of this information was adapted from schema.doc in the MediaWiki docs/ directory.

user_id[edit]

user_id is the primary key, used to uniquely identify a user

user_name[edit]

user_name: Usernames must be unique, and must not be in the form of an IP address. Shouldn't allow slashes or case conflicts. See also Manual:$wgInvalidUsernameCharactersManual:$wgInvalidUsernameCharacters. Spaces are allowed, and underscores are converted to spaces (the opposite than with page names).

user_real_name[edit]

MediaWiki version: 1.3

user_real_name stores the user's real name (optional) as provided by the user in their "Preferences" section.

user_password[edit]

user_password is one of three formats, depending on the setting of $wgPasswordSaltManual:$wgPasswordSalt and $wgPasswordDefaultManual:$wgPasswordDefault:

  • Since MediaWiki 1.24, $wgPasswordDefault defaults to pbkdf2. In this case you will get a concatenation of:
    • The string ":pbkdf2:".
    • The hashing algorithm used inside the pbkdf2 layer, by default "sha256".
    • The colon character (":").
    • The cost for this algorithm, by default "10000".
    • The colon character (":").
    • The length of the derived key; "128" by default.
    • The colon character (":").
    • Another string, e.g. "kkdejKlBYFV7+LP2m2thYA=="
    • And finally another key. (TODO: Describe me better!)
  • Since MediaWiki 1.24, if the maintenance script wrapOldPasswords.phpManual:WrapOldPasswords.php has been used, passwords may also start with ":pbkdf2-legacyA:" or ":pbkdf2-legacyB:" like ":pbkdf2-legacyB:!sha256:10000:128!...".
  • In MediaWiki 1.23 and older, if $wgPasswordSalt is true (default) it is a concatenation of:
    • The string ":B:",
    • A pseudo-random hexadecimal 31-bit salt between 0x0 and 0x7fff ffff (inclusive),
    • The colon character (":"), and
    • The MD5 hash of a concatenation of the salt, a dash ("-"), and the MD5 hash of the password.
  • In MediaWiki 1.23 and older, if $wgPasswordSalt is false, it is a concatenation of:
    • The string ":A:" and
    • The MD5 hash of the password.

user_newpassword[edit]

user_newpassword is generated for the mail-a-new-password feature.

user_newpass_time[edit]

user_newpass_time is set to the current timestampManual:timestamp (wfTimestampNow()) when a new password is set. Like the other timestamps, it is in in MediaWiki's timestamp format (yyyymmddhhmmss, e.g. 20130824025644).

user_email[edit]

user_email Note Note: email should be restricted, not public info. Same with passwords. ;)

user_options[edit]

MediaWiki version: 1.18

user_options is no longer used as of MediaWiki 1.16. See Manual:User properties tableManual:User properties table.

On older versions of mediawiki, it is a newline-separated list of name=value pairs of the information of Special:Preferences. Old user accounts who haven't logged in since the field stopped being used may still have this field set.

user_touched[edit]

user_touched the last time a user made a change on the site, including logins, changes to pages (any namespace), watchlistings, and preference changes.

Note Note: The user_touched time resets when a user is left a talkpage message.

user_token[edit]

MediaWiki version: 1.4

user_token is a pseudorandomly generated value. When a user checks "Remember my login on this browser" the value is stored in a persistent browser cookie ${wgCookiePrefix}Token that authenticates the user while being resistant to spoofing.

user_email_authenticated[edit]

user_email_authenticated is the timestamp of when the user email was authenticated. Defaults to NULL.

user_email_token[edit]

MediaWiki version: 1.5

user_email_token is a token used for confirming an email address. See User::newFromConfirmationCode in User.php

user_email_token_expires[edit]

user_email_token_expires is the expiration timestamp of the email token.

user_registration[edit]

MediaWiki version: 1.6

user_registration is the timestamp of when the user registered. For old users, they may have a value of NULL for this field. The fixUserRegistration.phpManual:fixUserRegistration.php script can be used to back-populate this field.

user_editcount[edit]

MediaWiki version: 1.9

user_editcount Count of edits and edit-like actions.

  • NOT* intended to be an accurate copy of COUNT(*) WHERE rev_user=user_id.

May contain NULL for old accounts if batch-update scripts haven't been run, as well as listing deleted edits and other myriad ways it could be out of sync. Execute the script initEditCount.phpManual:initEditCount.php to update this table column.

Meant primarily for heuristic checks to give an impression of whether the account has been used much.

user_emailauthenticationtimestamp[edit]

MediaWiki version: 1.3

Removed in v1.4.

user_password_expires[edit]

MediaWiki version: 1.23

Date when user's password expires; null for no expiration date. Can also be set manually by calling User->expirePassword().

Schema summary[edit]

(Current as of v1.23)

MediaWiki version: 1.23
mysql> describe user;

+--------------------------+------------------+------+-----+---------+----------------+
| Field                    | Type             | Null | Key | Default | Extra          |
+--------------------------+------------------+------+-----+---------+----------------+
| user_id                  | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| user_name                | varbinary(255)   | NO   | UNI |         |                |
| user_real_name           | varbinary(255)   | NO   |     |         |                |
| user_password            | tinyblob         | NO   |     | NULL    |                |
| user_newpassword         | tinyblob         | NO   |     | NULL    |                |
| user_newpass_time        | binary(14)       | YES  |     | NULL    |                |
| user_email               | tinyblob         | NO   | MUL | NULL    |                |
| user_touched             | binary(14)       | NO   |     |         |                |
| user_token               | binary(32)       | NO   |     |         |                |
| user_email_authenticated | binary(14)       | YES  |     | NULL    |                |
| user_email_token         | binary(32)       | YES  | MUL | NULL    |                |
| user_email_token_expires | binary(14)       | YES  |     | NULL    |                |
| user_registration        | binary(14)       | YES  |     | NULL    |                |
| user_editcount           | int(11)          | YES  |     | NULL    |                |
| user_password_expires    | varbinary(14)    | YES  |     | NULL    |                |
+--------------------------+------------------+------+-----+---------+----------------+
MediaWiki version: 1.19
mysql> describe user;

+--------------------------+------------------+------+-----+---------+----------------+
| Field                    | Type             | Null | Key | Default | Extra          |
+--------------------------+------------------+------+-----+---------+----------------+
| user_id                  | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| user_name                | varbinary(255)   | NO   | UNI |         |                |
| user_real_name           | varbinary(255)   | NO   |     |         |                |
| user_password            | tinyblob         | NO   |     | NULL    |                |
| user_newpassword         | tinyblob         | NO   |     | NULL    |                |
| user_newpass_time        | binary(14)       | YES  |     | NULL    |                |
| user_email               | tinyblob         | NO   | MUL | NULL    |                |
| user_touched             | binary(14)       | NO   |     |         |                |
| user_token               | binary(32)       | NO   |     |         |                |
| user_email_authenticated | binary(14)       | YES  |     | NULL    |                |
| user_email_token         | binary(32)       | YES  | MUL | NULL    |                |
| user_email_token_expires | binary(14)       | YES  |     | NULL    |                |
| user_registration        | binary(14)       | YES  |     | NULL    |                |
| user_editcount           | int(11)          | YES  |     | NULL    |                |
+--------------------------+------------------+------+-----+---------+----------------+


MediaWiki version: 1.13
mysql> describe user;

+--------------------------+------------------+------+-----+---------+----------------+
| Field                    | Type             | Null | Key | Default | Extra          |
+--------------------------+------------------+------+-----+---------+----------------+
| user_id                  | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| user_name                | varbinary(255)   | NO   | UNI |         |                |
| user_real_name           | varbinary(255)   | NO   |     |         |                |
| user_password            | tinyblob         | NO   |     |         |                |
| user_newpassword         | tinyblob         | NO   |     |         |                |
| user_newpass_time        | binary(14)       | YES  |     | NULL    |                |
| user_email               | tinyblob         | NO   |     |         |                |
| user_options             | blob             | NO   |     |         |                |
| user_touched             | binary(14)       | NO   |     |         |                |
| user_token               | binary(32)       | NO   |     |         |                |
| user_email_authenticated | binary(14)       | YES  |     | NULL    |                |
| user_email_token         | binary(32)       | YES  | MUL | NULL    |                |
| user_email_token_expires | binary(14)       | YES  |     | NULL    |                |
| user_registration        | binary(14)       | YES  |     | NULL    |                |
| user_editcount           | int(11)          | YES  |     | NULL    |                |
+--------------------------+------------------+------+-----+---------+----------------+


MediaWiki version: 1.11
mysql> describe user;

+--------------------------+------------------+------+-----+---------+----------------+
| Field                    | Type             | Null | Key | Default | Extra          |
+--------------------------+------------------+------+-----+---------+----------------+
| user_id                  | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| user_name                | varchar(255)     | NO   | UNI |         |                |
| user_real_name           | varchar(255)     | NO   |     |         |                |
| user_password            | tinyblob         | NO   |     |         |                |
| user_newpassword         | tinyblob         | NO   |     |         |                |
| user_newpass_time        | binary(14)       | YES  |     | NULL    |                |
| user_email               | tinytext         | NO   |     |         |                |
| user_options             | blob             | NO   |     |         |                |
| user_touched             | binary(14)       | NO   |     |         |                |
| user_token               | binary(32)       | NO   |     |         |                |
| user_email_authenticated | binary(14)       | YES  |     | NULL    |                |
| user_email_token         | binary(32)       | YES  | MUL | NULL    |                |
| user_email_token_expires | binary(14)       | YES  |     | NULL    |                |
| user_registration        | binary(14)       | YES  |     | NULL    |                |
| user_editcount           | int(11)          | YES  |     | NULL    |                |
+--------------------------+------------------+------+-----+---------+----------------+


MediaWiki version: 1.9
mysql> describe user;

+--------------------------+-----------------+------+-----+-------------------+----------------+
| Field                    | Type            | Null | Key | Default           | Extra          |
+--------------------------+-----------------+------+-----+-------------------+----------------+
| user_id                  | int(5) unsigned | NO   | PRI | NULL              | auto_increment |
| user_name                | varchar(255)    | NO   | UNI | NULL              |                |
| user_real_name           | varchar(255)    | NO   |     | NULL              |                |
| user_password            | tinyblob        | NO   |     | NULL              |                |
| user_newpassword         | tinyblob        | NO   |     | NULL              |                |
| user_email               | tinytext        | NO   |     | NULL              |                |
| user_options             | blob            | NO   |     | NULL              |                |
| user_touched             | char(14)        | NO   |     | NULL              |                |
| user_token               | char(32)        | NO   |     | NULL              |                |
| user_email_authenticated | char(14)        | YES  |     | NULL              |                |
| user_email_token         | char(32)        | YES  | MUL | NULL              |                |
| user_email_token_expires | char(14)        | YES  |     | NULL              |                |
| user_registration        | char(14)        | YES  |     | NULL              |                |
| user_newpass_time        | timestamp       | YES  |     | CURRENT_TIMESTAMP |                |
| user_editcount           | int(11)         | YES  |     | NULL              |                |
+--------------------------+-----------------+------+-----+-------------------+----------------+


MediaWiki version: 1.8
mysql> describe user;

+--------------------------+-----------------+------+-----+---------+----------------+
| Field                    | Type            | Null | Key | Default | Extra          |
+--------------------------+-----------------+------+-----+---------+----------------+
| user_id                  | int(5) unsigned | NO   | PRI | NULL    | auto_increment |
| user_name                | varchar(255)    | NO   | UNI | NULL    |                |
| user_real_name           | varchar(255)    | NO   |     | NULL    |                |
| user_password            | tinyblob        | NO   |     | NULL    |                |
| user_newpassword         | tinyblob        | NO   |     | NULL    |                |
| user_email               | tinytext        | NO   |     | NULL    |                |
| user_options             | blob            | NO   |     | NULL    |                |
| user_touched             | char(14)        | NO   |     | NULL    |                |
| user_token               | char(32)        | NO   |     | NULL    |                |
| user_email_authenticated | char(14)        | YES  |     | NULL    |                |
| user_email_token         | char(32)        | YES  | MUL | NULL    |                |
| user_email_token_expires | char(14)        | YES  |     | NULL    |                |
| user_registration        | char(14)        | YES  |     | NULL    |                |
+--------------------------+-----------------+------+-----+---------+----------------+


MediaWiki version: 1.5
mysql> describe user;

+--------------------------+-----------------+------+-----+---------+----------------+
| Field                    | Type            | Null | Key | Default | Extra          |
+--------------------------+-----------------+------+-----+---------+----------------+
| user_id                  | int(5) unsigned | NO   | PRI | NULL    | auto_increment |
| user_name                | varchar(255)    | NO   | UNI |         |                |
| user_real_name           | varchar(255)    | NO   |     |         |                |
| user_password            | tinyblob        | NO   |     |         |                |
| user_newpassword         | tinyblob        | NO   |     |         |                |
| user_email               | tinytext        | NO   |     |         |                |
| user_options             | blob            | NO   |     |         |                |
| user_touched             | char(14)        | NO   |     |         |                |
| user_token               | char(32)        | NO   |     |         |                |
| user_email_authenticated | char(14)        | YES  |     | NULL    |                |
| user_email_token         | char(32)        | YES  | MUL | NULL    |                |
| user_email_token_expires | char(14)        | YES  |     | NULL    |                |
+--------------------------+-----------------+------+-----+---------+----------------+


MediaWiki version: 1.4
mysql> describe user;

+------------------+---------------------+------+-----+---------+----------------+
| Field            | Type                | Null | Key | Default | Extra          |
+------------------+---------------------+------+-----+---------+----------------+
| user_id          | int(5) unsigned     |      | PRI | NULL    | auto_increment |
| user_name        | varchar(255) binary |      |     |         |                |
| user_rights      | tinyblob            |      |     |         |                |
| user_password    | tinyblob            |      |     |         |                |
| user_newpassword | tinyblob            |      |     |         |                |
| user_email       | tinytext            |      |     |         |                |
| user_options     | blob                |      |     |         |                |
| user_touched     | varchar(14) binary  |      |     |         |                |
+------------------+---------------------+------+-----+---------+----------------+

See also[edit]

DatabasesManual:Database layout Engines: MySQL/MariaDBManual:MySQLOracleManual:OraclePostgreSQLManual:PostgreSQLSQLiteManual:SQLite
Technical documentation: SchemaManual:Database layout (tablesCategory:MediaWiki database tables) – API property associationsAPI:Database field and API property associationsField prefixesDatabase field prefixesPrimary key storage in other fieldsManual:Primary key storage in other fieldsWikimedia extension tablesCategory:Wikimedia extension database tables
Configuration: SettingsManual:Configuration_settings#Database_settingsSharingManual:Shared database
Development: AccessManual:Database accessOptimizationDatabase optimizationPolicyDevelopment policy#Database_patchesUpdaterManual:DatabaseUpdater.phpExtension schema updatesManual:Hooks/LoadExtensionSchemaUpdatesPatch fileManual:SQL patch file
Core tables: archivecategorycategorylinkschange_tagconfigexternallinksfilearchivehitcounterimageimagelinksinterwikiiwlinksipblocksjobl10n_cachelanglinkslogginglog_searchmsg_resourcemsg_resource_linksmodule_depsobjectcacheoldimagepagepagelinkspage_propspage_restrictionsprotected_titlesquerycachequerycachetwoquerycache_inforecentchangesredirectrevisionsearchindexsitessite_identifiers - site_statstag_summarytemplatelinkstexttranscacheupdateloguploadstashuseruser_former_groupsuser_groupsuser_newtalkuser_propertiestag_summaryvalid_tagwatchlist