Table of Contents

MySQL Authentication Backend

Since the release 2013-05-10 “Weatherwax”
see AuthMySQL plugin page

For releases 2012-10-13 “Adora Belle” and older
see info below

This backend uses a MySQL Database to store user data and passwords.

Any database that contains basic user and group information could be used with DokuWiki. How you adapt the SQL statements to match your favorite database will be discussed in this page. All configuration and SQL statements are tested with MySQL 4.0 and are based on the example database structure. The backend is able to work with MySQL 3.23, too, but needs slightly modified SQL statements.

Real World Examples

The backend is used in some real world configurations that uses the flexibility SQL is offering.

Configuration

Add these configuration options to conf/local.php to use MySQL as your Auth information store.

At least as of release 2006-03-09, all options below are in the distribution in /conf/mysql.conf.php.example. Rename the file to mysql.conf.php, edit it for your settings, and then add the line

<?php
    require_once('mysql.conf.php');
?>

to your conf/local.protected.php file and not in conf/local.php, cause there it would be eventually overwritten by the configuration manager. (This way you don't have to try and copy/paste all the below code.)

The version included in the distribution does split up the first and last name fields, as a user mentioned below in an improved example.

Enable MySQL

It will be enabled with the configuration option

$conf['authtype'] = "mysql";

Option 'server'

This option defines the MySQL server to connect to. If you are running MySQL on the same server as your DokuWiki installation, 'localhost' would be sufficient. Otherwise put in your remote MySQL server here. Keep in mind that the remote database server must allow access from your DokuWiki server. See MySQL documentation for details on this issue.

$conf['auth']['mysql']['server'] = 'localhost';

Option 'user'

This option defines which user DokuWiki should use to access the database.

$conf['auth']['mysql']['user'] = 'dbuser';

Option 'password'

Set the database password for 'user' here. Because it is entered in clear text some additional security prophylaxes should be performed.

$conf['auth']['mysql']['password'] = 'dbpassword';

Option 'database'

Last but not least you need to specify the database that stores all the user information.

$conf['auth']['mysql']['database'] = 'users';

Option 'forwardClearPass'

Normally password encryption is done by DokuWiki (recommended) but for some reasons it might be useful to let the database do the encryption. Set 'forwardClearPass' to '0' and DokuWiki do the password encryption. Different encryption algorithms are possible. See Chapter Configuration for full list

:!: If you set 'forwardClearPass' to 1 the backend expects the database to do the crypting. It will forward the clear text password to the database. Be aware of the security risk.

$conf['auth']['mysql']['forwardClearPass'] = 0;

Option 'TablesToLock'

Multiple table operations will be protected by locks. This array tells the module which tables to lock. If you use any aliases for table names the array must also contain these aliases. Any not named alias will cause a warning during operation.

MySQL 3.23 doesn't support transactions so that this mechanism is simulated with LOCK TABLES to be downwards compatible. Future versions of this backend may support transactions natively.

$conf['auth']['mysql']['TablesToLock']= array("users", "users AS u",
              "groups", "groups AS g", "usergroup", "usergroup AS ug");

Option 'debug'

Setting this option to 1 will print out all SQL errors that occur on executing the auth backend. This is useful during configuring the backend but should be disabled when everything works.

$conf['auth']['mysql']['debug']= 1;

You can set this option to 2 to make it print every SQL query that is sent to the database.

SQL User Authentication

The SQL statements in this section are necessary to use DokuWiki with the MySQL authentication backend. They are the minimum set you have to define.

checkPass

This statement is used to grant or deny access to the wiki. The result should be a table with exact one line containing at least the password of the user. If the result table is empty or contains more than one row, access will be denied. The module access the password as 'pass' so an alias might be necessary.

If you set 'forwardClearPass = 1' the password must be verified from the database. An additional WHERE clause will do the job: “AND pass = MD5('%pass')”. A much better way is and I really recommend to do it this way is to set 'forwardClearPass = 0' and let DokuWiki do the crypting.

$conf['auth']['mysql']['checkPass']   = "SELECT pass
                                         FROM usergroup AS ug
                                         JOIN users AS u ON u.uid=ug.uid
                                         JOIN groups AS g ON g.gid=ug.gid
                                         WHERE login='%{user}'
                                         AND name='%{dgroup}'";

If you share the user database with other applications it might be useful to be able to define which user may access the Wiki. Okay, the main spirit of a Wiki is it not to limit the access and give all users the possibility to participate. But hey, you are reading an article about access control, what did you expect? So let us get the donkey from the ice.

As already mentioned we have a user database but not every user in this database should be allowed to log into the wiki. The easiest way to check this is the DokuWiki defaultgroup. Every new DokuWiki user will automatically be a member of this group so all we have to do is to check the group besides the password at login time.

The above SQL statement already does this with “name='%{dgroup}'”.

getUserInfo

This statement should return a table with exact one row containing information about one user. The field needed are:

Keep in mind that DokuWiki will access these information through the names listed above so aliases might be necessary.

$conf['auth']['mysql']['getUserInfo'] = "SELECT pass, fullname AS name, email AS mail
                                         FROM users
                                         WHERE login='%{user}'";

getGroups

This statement is used to get all groups a user is member of. The result should be a table containing all groups the given user is member of. The module access the group name as 'group' so an alias might be necessary.

$conf['auth']['mysql']['getGroups']   = "SELECT name
                                         FROM groups g, users u, usergroup ug
                                         WHERE u.uid = ug.uid
                                         AND g.gid = ug.gid
                                         AND u.login='%{user}'";

SQL Basic User Manager Support

The SQL statements in this section are necessary to use the user manager plugin. They set up only basic support and you will only be able to get and display the user list.

getUsers

This statement should return a table containing all user login names that meet certain filter criteria. The filter expressions will be added case dependent by the module. At the end a sort expression will be added.

Important is that this list contains no double entries to a user. Each user name is only allowed once in the table.

The login name will be accessed as 'user' to an alias might be necessary. No patterns will be replaced in this statement but following patters will be replaced in the filter expressions:

$conf['auth']['mysql']['getUsers']    = "SELECT DISTINCT login AS user
                                         FROM users AS u 
                                         LEFT JOIN usergroup AS ug ON u.uid=ug.uid
                                         LEFT JOIN groups AS g ON ug.gid=g.gid";
$conf['auth']['mysql']['FilterLogin'] = "login LIKE '%{user}'";
$conf['auth']['mysql']['FilterName']  = "fullname LIKE '%{name}'";
$conf['auth']['mysql']['FilterEmail'] = "email LIKE '%{email}'";
$conf['auth']['mysql']['FilterGroup'] = "name LIKE '%{group}'";
$conf['auth']['mysql']['SortOrder']   = "ORDER BY login";

SQL Support for Add User

You additionally need the SQL statements in this section if you want to add new users in the database with the user manager.

addUser

This statement should add a user to the database. Minimum information to store are: login name, password, email address and full name.

$conf['auth']['mysql']['addUser']     = "INSERT INTO users
                                         (login, pass, email, fullname)
                                         VALUES ('%{user}', '%{pass}', '%{email}', '%{name}')";

Please keep in mind that if you set 'forwardClearPass = 1' the clear text password is filled in here. You should at least replace '%p' with MD5('%p') or better set 'forwardClearPass = 0' and let DokuWiki do the crypting. I really recommend the second method.

addGroup

This statement should add a group to the database.

$conf['auth']['mysql']['addGroup']    = "INSERT INTO groups (name)
                                         VALUES ('%{group}')";

addUserGroup

This statement should connect a user to a group (a user become member of that group).

$conf['auth']['mysql']['addUserGroup']= "INSERT INTO usergroup (uid, gid)
                                         VALUES ('%{uid}', '%{gid}')";

delGroup

This statement should remove a group from the database.

$conf['auth']['mysql']['delGroup']    = "DELETE FROM groups
                                         WHERE gid='%{gid}'";

getUserID

This statement should return the database index of a given user name. The module will access the index with the name 'id' so an alias might be necessary.

$conf['auth']['mysql']['getUserID']   = "SELECT uid AS id FROM users WHERE login='%{user}'";

getGroupID

This statement should return the database index of a given group name. The module will access the index with the name 'id' so an alias might be necessary.

$conf['auth']['mysql']['getGroupID']  = "SELECT gid AS id FROM groups WHERE name='%{group}'";

SQL Support for Delete User

You additionally need the SQL statements in this section if you want to remove users from the database with the user manager.

delUser

This statement should remove a user from the database.

$conf['auth']['mysql']['delUser']     = "DELETE FROM users
                                         WHERE uid='%{uid}'";

delUserRefs

This statement should remove all connections from a user to any group (a user quits membership of all groups).

$conf['auth']['mysql']['delUserRefs'] = "DELETE FROM usergroup
                                         WHERE uid='%{uid}'";

SQL Support for Modify User

updateUser

This statements should modify a user entry in the database. The statements UpdateLogin, UpdatePass, UpdateEmail and UpdateName will be added to updateUser on demand. Only changed parameters will be used.

$conf['auth']['mysql']['updateUser']  = "UPDATE users SET";
$conf['auth']['mysql']['UpdateLogin'] = "login='%{user}'";
$conf['auth']['mysql']['UpdatePass']  = "pass='%{pass}'";
$conf['auth']['mysql']['UpdateEmail'] = "email='%{email}'";
$conf['auth']['mysql']['UpdateName']  = "fullname='%{name}'";
$conf['auth']['mysql']['UpdateTarget']= "WHERE uid=%{uid}";

delUserGroup

This statement should remove a single connection from a user to a group (a user quits membership of that group).

$conf['auth']['mysql']['delUserGroup']= "DELETE FROM usergroup
                                         WHERE uid='%{uid}'
                                         AND gid='%{gid}'";

Example Database

This is a very simple example database.

CREATE TABLE `users` (
  `uid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `login` VARCHAR(20) NOT NULL DEFAULT '',
  `pass` VARCHAR(60) NOT NULL DEFAULT '',
  `fullname` VARCHAR(255) NOT NULL DEFAULT '',
  `email` VARCHAR(255) NOT NULL DEFAULT '',
  PRIMARY KEY  (`uid`),
  UNIQUE KEY `login` (`login`)
) ENGINE = MYISAM;
 
CREATE TABLE `groups` (
  `gid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL DEFAULT '',
  PRIMARY KEY  (`gid`),
  UNIQUE KEY `name` (`name`)
) ENGINE = MYISAM;
 
CREATE TABLE `usergroup` (
  `uid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
  `gid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY  (`uid`,`gid`)
) ENGINE = MYISAM;

SQL statements for example user

The following statements adding an example user admin to the created database in order to have a possibility to login. (Username/Password. admin / The hash is generated through the smd5 hash mechanism).

INSERT INTO `groups` (`gid`, `name`) VALUES (1, 'admin'), (2, 'user');
INSERT INTO `usergroup` (`uid`, `gid`) VALUES (1, 1), (1, 2);
INSERT INTO `users` (`uid`, `login`, `pass`, `fullname`, `email`) VALUES
(1, 'admin', '$1$nq5sZRbT$tX6BY1SdA0XzbMp6F7evR.', 'admin', 'admin@dokuwiki.local');

An improved Example

Storing the fullname in one single database field may be sufficient for some basic tasks but it has a lot of advantages to split the name up in firstname and lastname. Only one table of the above example need some tuning to get this to work:

CREATE TABLE `users` (
  `uid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `login` VARCHAR(20) NOT NULL DEFAULT '',
  `pass` VARCHAR(60) NOT NULL DEFAULT '',
  `firstname` VARCHAR(255) NOT NULL DEFAULT '',
  `lastname` VARCHAR(255) NOT NULL DEFAULT '',
  `email` VARCHAR(255) NOT NULL DEFAULT '',
  PRIMARY KEY  (`uid`),
  UNIQUE KEY `login` (`login`)
) TYPE = MYISAM;

To use this slightly changed structure without the need to recode major parts of DokuWiki, it becomes necessary to modify some of our SQL statements:

$conf['auth']['mysql']['getUserInfo'] = "SELECT pass, CONCAT(firstname,' ',lastname) AS name, email AS mail
                                         FROM users
                                         WHERE login='%{user}'";
$conf['auth']['mysql']['addUser']     = "INSERT INTO users
                                         (login, pass, email, firstname, lastname)
                                         VALUES ('%{user}', '%{pass}', '%{email}',
                                         SUBSTRING('%{name}', 1, LOCATE(' ', '%{name}')),
                                         IF(Length('%{name}') = Length(REPLACE('%{name}', ' ', '')),'',SUBSTRING_INDEX('%{name}', ' ',-((Length('%{name}')-Length(REPLACE('%{name}', ' ', '')))))) )";
$conf['auth']['mysql']['FilterName']  = "CONCAT(firstname,' ',lastname) LIKE '%{name}'";
$conf['auth']['mysql']['UpdateName']  = "firstname=SUBSTRING_INDEX('%{name}',' ', 1),
                                         lastname=IF(Length('%{name}') = Length(REPLACE('%{name}', ' ', '')),'', SUBSTRING_INDEX('%{name}', ' ',-((Length('%{name}')-Length(REPLACE('%{name}', ' ', ''))))))";

This modifications in the first statement and also in the FilterName join the firstname and the lastname to fullname used by DokuWiki and the second statement splits the given fullname up into two parts: firstname and lastname. This should also work if the last name consists of multiply words like “de la Cruz”. And anyone who registers with a one-word name will have all of it put into the “lastname” field of the database.

Please keep in mind that if you set 'forwardClearPass = 1' the cleartext password is filled in here. You should at least replace '%p' with MD5('%p') or better set 'forwardClearPass = 0' and let DokuWiki do the crypting. I really recommend the second method.

Discussion

It would be useful to have a tutorial and example script to import the users from file users.auth.php to MySQL tables.

Please add a section to the tutorial that actually walks you through getting this fully configured. I did a fresh install of dokuwiki, followed the steps as provided exactly in this guide and once I switch to mysql authentication from plain I cannot login to wiki, there is no part of this that actually adds a user to the database which gives admin rights, it seems like you might be assuming that the user has the knowledge to create their own insert statement

It seems that you're using MyISAM tables. At least there are no foreign keys constraints in the example database structure although they'd be needed for the usergroup table to ensure referential integrity. Anyway, you should always explicitly specify the table type you want to use in any CREATE TABLE statement otherwise the results are dependent on a config-setting of the DBMS you often don't know. While MyISAM is the default setting in the public MySQL distribution this setting is quite often changed to InnoDB to allow for transactions, foreign keys, automatic referential integrity and other improvements. On such an optimized system your CREATE TABLE statements would actually create tables of a type you're not expecting …

Ok. I added the type explicitly
Matthias Grimm 2006-01-31 19:40

Another point is that in the SQL snippets above I can't find the handling of the usergroup table's fields in case of adding/deleting records to/from the respective master table: The addUserGroup and delUserGroup statements are separated and since there are neither transactions nor FK constraints involved the whole construct is not safe. While this could be left to the DBMS (provided that you're using InnoDB tables with the correct FK/DELETE/UPDATE constraints, which would obsolete as well application level table locking and for example delUserGroup), here (i.e. when using MyISAM) you have to do this on application level.

Table usergroup is handled by addUserGroup and delUserGroup. Multi-statement commands will be protected by table locks. This backend must also work with MySQL 3.23 and because neither transaction are well supported nor innoDB work without a lot of tweaking in 3.23, transactions aren't used here. This may change in future.
Matthias Grimm 2006-01-31 19:44

Hence you should either make sure that there's no way to add/remove records to/from the users / groups tables without updating the usergroup table appropriately or consequently use the DBMS features provided by the InnoDB table type as mentioned above. In any case, however, whatever you choose, always specify the table type you want to use!
– 2005-12-04 Matthias Watermann


Using MySQL 5

I'm developing a wiki (using 2006-03-09b) with ACL integrated into a phpBB database on a XAMPP for Windows system, which has MySQL 5.0.18. It seems to work except when I log in I get an error at the top of the page: Warning: Invalid argument supplied for foreach() in C:\Program Files\XAMPP\xampp\htdocs\wiki\inc\auth\mysql.class.php on line 491

I wondered if this might be an issue with MySQL 5? Any thoughts appreciated.

Using PHP MySQLi library

DokuWiki uses the standard MySQL PHP library. However, some Apache web servers may be configured to use only the MySQLi PHP library. Patching DokuWiki to use MySQLi instead of MySQL is very straight forward. The script below will patch DokuWiki to use PHP MySQLi instead MySQL

(
cat < 'EOF'
26c26
<      * checks if the MySQL interface is available, otherwise it will
---
>      * checks if the MySQLi interface is available, otherwise it will
38c38
<       if(!function_exists('mysql_connect')) {
---
>       if(!function_exists('mysqli_connect')) {
712c712
<         $con = @mysql_connect ($this->cnf['server'], $this->cnf['user'], $this->cnf['password']);
---
>         $con = @mysqli_connect ($this->cnf['server'], $this->cnf['user'], $this->cnf['password']);
714,715c714,715
<           if ((mysql_select_db($this->cnf['database'], $con))) {
<             if ((preg_match("/^(\d+)\.(\d+)\.(\d+).*/", mysql_get_server_info ($con), $result)) == 1) {
---
>           if ((mysqli_select_db($con, $this->cnf['database']))) {
>             if ((preg_match("/^(\d+)\.(\d+)\.(\d+).*/", mysqli_get_server_info ($con), $result)) == 1) {
723c723
<             mysql_close ($con);
---
>             mysqli_close ($con);
743c743
<         mysql_close ($this->dbcon);
---
>         mysqli_close ($this->dbcon);
763c763
<         $result = @mysql_query($query,$this->dbcon);
---
>         $result = @mysqli_query($this->dbcon,$query);
765c765
<           while (($t = mysql_fetch_assoc($result)) !== false)
---
>           while ($t = mysqli_fetch_assoc($result))
767c767
<           mysql_free_result ($result);
---
>           mysqli_free_result ($result);
771c771
<           msg('MySQL err: '.mysql_error($this->dbcon),-1,__LINE__,__FILE__);
---
>           msg('MySQL err: '.mysqli_error($this->dbcon),-1,__LINE__,__FILE__);
789c789
<         $result = @mysql_query($query,$this->dbcon);
---
>         $result = @mysqli_query($this->dbcon,$query);
791c791
<           $rc = mysql_insert_id($this->dbcon); //give back ID on insert
---
>           $rc = mysqli_insert_id($this->dbcon); //give back ID on insert
 
795c795
<           msg('MySQL err: '.mysql_error($this->dbcon),-1,__LINE__,__FILE__);
---
>           msg('MySQL err: '.mysqli_error($this->dbcon),-1,__LINE__,__FILE__);
906c906
<         $string = mysql_real_escape_string($string, $this->dbcon);
---
>         $string = mysqli_real_escape_string($this->dbcon,$string);
EOF
) > /home/apache/dokuwiki-2006-11-06/inc/auth/mysql.class.php.patch
patch /home/apache/dokuwiki-2006-11-06/inc/auth/mysql.class.php /home/apache/dokuwiki-2006-11-06/inc/auth/mysql.class.php.patch
rm /home/apache/dokuwiki-2006-11-06/inc/auth/mysql.class.php.patch

Firstname and Surname columns

This might be of help. If you split your firstname and surname columns you can join them with a select:

In MySQL: SELECT CONCAT(firstname, ' ', surname) AS name FROM …

MySQL Trace Mode warning

If you have trace_mode on in you MySQL config, you may find that even if all your queries are OK, usermanager still reports “Found 0 users”. To fix this find a way to turn off mysql_trace_mode like This one.

Apparently the default setting for this has changed since MySQL version 5.2.6 from off to on.

If you don't want, or can't change mysql_trace_mode, there is another fix. Open the file WIKIPATH/inc/auth/mysql.class.php, find “function getUserCount($filter=array()) {” (must be line 309). Replace the whole getUserCount() function with this:

    function getUserCount($filter=array()) {
      $rc = 0;
      if($this->_openDB()) {
        $sql = $this->_createSQLFilter($this->cnf['getUsers'], $filter);
 
        if ($this->dbver >= 4) {
          $sql = substr($sql, strpos(strtolower($sql),"from"));  /* remove everytnig between 'FROM' */
          $sql = "SELECT COUNT(*) ".$sql." LIMIT 1";
          $result = $this->_queryDB($sql);
          $rc = $result[0]['COUNT(*)'];
        } else if (($result = $this->_queryDB($sql)))
          $rc = count($result);
 
        $this->_closeDB();
      }
      return $rc;
    }

Hope this will save someone the hours I spend investigating.

Jacket 2010/02/10 12:41

Adding the Superuser to the new MySQL Tables

After struggling with trying to figure out how to import my existing superuser into the mysql authentication environment, I discovered that the easiest way to do it was to simply add the superuser using the Add User capability in the User Manager interface. Just remember not to log out before adding the superuser, and remember to include him/her in both the admin and user groups.

If for some reason you mess up and do log out before adding the superuser, use shell access to edit the conf/local.php file to change $conf['authtype'] back to 'plain'. That should let you log back in with the “original” superuser (i.e., the one you set up when first setting up Dokuwiki).

Mark 2010/07/21 16:47

Worst Descriptions EVER!

Are you kidding me!? Whoever wrote this is not very intelligent… it does not describe anything well… this local.protected.php does not exist in my conf folder - am I supposed to create this from scratch, or perhaps copy the current local.php - it sure doesn't say but I can't get this to work….

What about this include, or the example for vbulletin which doesn't seem to operate? Nothing works - and there is no good documentation on this AT ALL… this is terrible - now after spending hundreds of hours building this Wiki because I was under the impression this would work - I need to move to MediaWiki who actually has REAL support for their product… ugh.

I see you are on the good way, asking help when you were lost in for you not so helpful descriptions. There is a forum and a IRC (chat) channel available too. Here you can ask more questions!! :)
Good luck! — Klap-inKlap-in

2013/04/13 10:18

More Help

For users with less experience with MySQL I created a documentation page of my implementation of this plugin for my site. It can be found here: https://www.getshifting.com/wiki/upgradethissite2#mysql_authentication