auth:mysql
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
auth:mysql [2013-04-07 21:11] – [Release Weatherwax and newer] lupo49 | auth:mysql [Unknown date] (current) – removed - external edit (Unknown date) 127.0.0.1 | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | ====== MySQL Authentication Backend ====== | ||
- | | Since the release candidate 2013-03-06 “Weatherwax”\\ see [[plugin: | ||
- | |||
- | 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|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. | ||
- | |||
- | * [[mysql_UNB|Unclassified News Board]] ([[http:// | ||
- | * [[mysql_UNB2|Another Unclassified News Board]] ([[http:// | ||
- | * [[mysql_phorum|Phorum]] - a message board / forum | ||
- | * [[mysql_fudforum|FUDforum]] - a message board / forum | ||
- | * [[mysql_invision|InvisionBoard]] - a message board / forum | ||
- | * [[mysql_phpbb|phpBB Board]] - a message board / forum | ||
- | * [[mysql_flyspray|Flyspray]] - a bug tracker | ||
- | * [[mysql_drupal|Drupal]] - a fine content management system (CMS) | ||
- | * [[mysql_e107|e107]] - another fine content management system (CMS) | ||
- | * [[mysql_nucleus|Nucleus]] - a powerful blogging system | ||
- | * [[mysql_gallery2|Gallery2]] - a gallery: [[http:// | ||
- | * [[mysql_nuked-klan|Nuked Klan]] - a CMS for gamers : | ||
- | * [[mysql_postnuke|PostNuke]] - a CMS [[http:// | ||
- | * [[mysql_smf|Simple Machines Forum]] - Simple Machines Forum [[http:// | ||
- | * [[mysql_projectpier|ProjectPier]] - collaboration system: [[http:// | ||
- | * [[http:// | ||
- | * [[mysql_typo3|Typo3]] - an enterprise related content management system (CMS) | ||
- | * [[mysql_vbulletin|VBulletin]] - a message board / forum | ||
- | * [[mysql_wbb|Woltlab Burning Board]] - a message board / forum | ||
- | * [[mysql_moodle|Moodle]] - an elearning system | ||
- | |||
- | ===== Configuration ===== | ||
- | |||
- | |||
- | ==== Older releases ==== | ||
- | |||
- | Add these configuration options to '' | ||
- | |||
- | At least as of [[: | ||
- | | ||
- | <?php | ||
- | require_once(' | ||
- | ?> | ||
- | | ||
- | to your '' | ||
- | |||
- | The version included in the distribution does split up the first and last name fields, as a user mentioned below in [[MySQL#an improved example]]. | ||
- | |||
- | |||
- | ==== Enable MySQL ==== | ||
- | It will be enabled with the configuration option | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | </ | ||
- | |||
- | ==== Option ' | ||
- | |||
- | This option defines the MySQL server to connect to. If you are running MySQL on the same server as your DokuWiki installation, | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | </ | ||
- | |||
- | ==== Option ' | ||
- | This option defines which user DokuWiki should use to access the database. | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | </ | ||
- | |||
- | ==== Option ' | ||
- | Set the database password for ' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | </ | ||
- | |||
- | ==== Option ' | ||
- | Last but not least you need to specify the database that stores all the user information. | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | </ | ||
- | |||
- | ==== Option ' | ||
- | |||
- | Normally password encryption is done by DokuWiki (recommended) but for some reasons it might be useful to let the database do the encryption. Set ' | ||
- | |||
- | :!: If you set ' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | </ | ||
- | |||
- | ==== Option ' | ||
- | 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' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | " | ||
- | </ | ||
- | |||
- | ==== Option ' | ||
- | |||
- | Setting this option to '' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | </ | ||
- | |||
- | You can set this option to '' | ||
- | |||
- | ===== 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 ' | ||
- | |||
- | If you set ' | ||
- | |||
- | * ' | ||
- | * ' | ||
- | * ' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | FROM usergroup AS ug | ||
- | JOIN users AS u ON u.uid=ug.uid | ||
- | JOIN groups AS g ON g.gid=ug.gid | ||
- | WHERE login=' | ||
- | AND name=' | ||
- | </ | ||
- | |||
- | 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 '' | ||
- | |||
- | The above SQL statement already does this with " | ||
- | |||
- | ==== 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. | ||
- | |||
- | * ' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | FROM users | ||
- | WHERE login=' | ||
- | </ | ||
- | |||
- | ==== 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 ' | ||
- | |||
- | * ' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | FROM groups g, users u, usergroup ug | ||
- | WHERE u.uid = ug.uid | ||
- | AND g.gid = ug.gid | ||
- | AND u.login=' | ||
- | </ | ||
- | |||
- | ===== 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 ' | ||
- | No patterns will be replaced in this statement but following patters will be | ||
- | replaced in the filter expressions: | ||
- | |||
- | * ' | ||
- | * ' | ||
- | * ' | ||
- | * ' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | 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[' | ||
- | $conf[' | ||
- | $conf[' | ||
- | $conf[' | ||
- | $conf[' | ||
- | </ | ||
- | |||
- | ===== 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. | ||
- | |||
- | * ' | ||
- | * ' | ||
- | * ' | ||
- | * ' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | | ||
- | | ||
- | </ | ||
- | |||
- | Please keep in mind that if you set ' | ||
- | |||
- | ==== addGroup ==== | ||
- | This statement should add a group to the database. | ||
- | |||
- | * ' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | | ||
- | </ | ||
- | |||
- | ==== addUserGroup ==== | ||
- | This statement should connect a user to a group (a user become member | ||
- | of that group). | ||
- | |||
- | * ' | ||
- | * ' | ||
- | * ' | ||
- | * ' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | | ||
- | </ | ||
- | |||
- | ==== delGroup ==== | ||
- | This statement should remove a group from the database. | ||
- | |||
- | * ' | ||
- | * ' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | WHERE gid=' | ||
- | </ | ||
- | |||
- | ==== getUserID ==== | ||
- | This statement should return the database index of a given user name. | ||
- | The module will access the index with the name ' | ||
- | necessary. | ||
- | |||
- | * ' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | </ | ||
- | |||
- | ==== getGroupID ==== | ||
- | This statement should return the database index of a given group name. | ||
- | The module will access the index with the name ' | ||
- | necessary. | ||
- | |||
- | * ' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | </ | ||
- | |||
- | ===== 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. | ||
- | |||
- | * ' | ||
- | * ' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | WHERE uid=' | ||
- | </ | ||
- | |||
- | ==== delUserRefs ==== | ||
- | This statement should remove all connections from a user to any group | ||
- | (a user quits membership of all groups). | ||
- | |||
- | * ' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | WHERE uid=' | ||
- | </ | ||
- | |||
- | ===== SQL Support for Modify User ===== | ||
- | |||
- | ==== updateUser ==== | ||
- | This statements should modify a user entry in the database. The statements UpdateLogin, | ||
- | |||
- | * ' | ||
- | * ' | ||
- | * ' | ||
- | * ' | ||
- | * ' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | $conf[' | ||
- | $conf[' | ||
- | $conf[' | ||
- | $conf[' | ||
- | $conf[' | ||
- | </ | ||
- | |||
- | ==== delUserGroup ==== | ||
- | This statement should remove a single connection from a user to a | ||
- | group (a user quits membership of that group). | ||
- | |||
- | * ' | ||
- | * ' | ||
- | * ' | ||
- | * ' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | WHERE uid=' | ||
- | AND gid=' | ||
- | </ | ||
- | |||
- | ===== Example Database ===== | ||
- | This is a very simple example database. | ||
- | |||
- | <code sql> | ||
- | 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 ' | ||
- | `gid` int(10) unsigned NOT NULL default ' | ||
- | PRIMARY KEY (`uid`, | ||
- | ) ENGINE = MYISAM; | ||
- | |||
- | </ | ||
- | |||
- | ==== SQL statements for example user ==== | ||
- | |||
- | The following statements adding an example user '' | ||
- | |||
- | <code sql> | ||
- | |||
- | INSERT INTO `groups` (`gid`, `name`) VALUES (1, ' | ||
- | INSERT INTO `usergroup` (`uid`, `gid`) VALUES (1, 1), (1, 2); | ||
- | INSERT INTO `users` (`uid`, `login`, `pass`, `fullname`, `email`) VALUES | ||
- | (1, ' | ||
- | </ | ||
- | |||
- | |||
- | |||
- | ===== An improved Example ===== | ||
- | |||
- | Storing the '' | ||
- | |||
- | <code sql> | ||
- | 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: | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | FROM users | ||
- | WHERE login=' | ||
- | $conf[' | ||
- | | ||
- | | ||
- | | ||
- | | ||
- | $conf[' | ||
- | $conf[' | ||
- | | ||
- | </ | ||
- | |||
- | This modifications in the first statement and also in the FilterName join the '' | ||
- | |||
- | Please keep in mind that if you set ' | ||
- | |||
- | ===== Discussion ===== | ||
- | |||
- | It would be useful to have a tutorial and example script to import the users from file '' | ||
- | |||
- | **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 '' | ||
- | no foreign keys constraints in the [[#Example Database|example database]] structure although they'd be needed for the '' | ||
- | table to ensure referential integrity. Anyway, you should always | ||
- | explicitly specify the table type you want to use in any | ||
- | '' | ||
- | config-setting of the DBMS you often don't know. While '' | ||
- | default setting in the public MySQL distribution this setting is quite often | ||
- | changed to '' | ||
- | referential integrity and other improvements. On such an optimized system | ||
- | your '' | ||
- | you're not expecting ... | ||
- | |||
- | > Ok. I added the type explicitly\\ | ||
- | |||
- | Another point is that in the SQL snippets above I can't find the handling | ||
- | of the '' | ||
- | to/from the respective master table: The '' | ||
- | transactions nor FK constraints involved the whole construct is //not safe// | ||
- | While this could be left to the DBMS (provided that you're using '' | ||
- | tables with the correct FK/ | ||
- | well application level table locking and for example '' | ||
- | (i.e. when using '' | ||
- | |||
- | > 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.\\ | ||
- | |||
- | Hence you should either make sure that there' | ||
- | to/from the '' | ||
- | table appropriately or consequently use the DBMS features provided by the '' | ||
- | In any case, however, whatever you choose, //always// specify the table type | ||
- | you want to use! | ||
- | \\ //-- 2005-12-04 [[mailto: | ||
- | |||
- | |||
- | |||
- | ---- | ||
- | |||
- | ==== Using MySQL 5 ==== | ||
- | |||
- | I'm developing a wiki (using 2006-03-09b) with ACL integrated into a phpBB database on a [[http:// | ||
- | '' | ||
- | |||
- | I wondered if this might be an issue with MySQL 5? Any thoughts appreciated. | ||
- | |||
- | * I didn't get exactly this error, but at another line of the file. It was about having a filter on users which led to an empty result. This is not particularly a problem of MySQL, but of PHP. Well, it's a bug in the code, but I guess older versions of php silently ignored foreach on undefined / empty / non-array variables, but newer versions do complain about it. This should be fixed, though (see below). -- Johannes Jordan | ||
- | * Easy workaround: surround the foreach() statement with if ($result) { } | ||
- | |||
- | ==== 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 | ||
- | |||
- | <code php> | ||
- | ( | ||
- | cat < ' | ||
- | 26c26 | ||
- | < * checks if the MySQL interface is available, otherwise it will | ||
- | --- | ||
- | > * checks if the MySQLi interface is available, otherwise it will | ||
- | 38c38 | ||
- | < | ||
- | --- | ||
- | > | ||
- | 712c712 | ||
- | < $con = @mysql_connect ($this-> | ||
- | --- | ||
- | > $con = @mysqli_connect ($this-> | ||
- | 714, | ||
- | < if ((mysql_select_db($this-> | ||
- | < if ((preg_match("/ | ||
- | --- | ||
- | > if ((mysqli_select_db($con, | ||
- | > if ((preg_match("/ | ||
- | 723c723 | ||
- | < | ||
- | --- | ||
- | > | ||
- | 743c743 | ||
- | < | ||
- | --- | ||
- | > | ||
- | 763c763 | ||
- | < | ||
- | --- | ||
- | > | ||
- | 765c765 | ||
- | < while (($t = mysql_fetch_assoc($result)) !== false) | ||
- | --- | ||
- | > while ($t = mysqli_fetch_assoc($result)) | ||
- | 767c767 | ||
- | < | ||
- | --- | ||
- | > | ||
- | 771c771 | ||
- | < | ||
- | --- | ||
- | > | ||
- | 789c789 | ||
- | < | ||
- | --- | ||
- | > | ||
- | 791c791 | ||
- | < $rc = mysql_insert_id($this-> | ||
- | --- | ||
- | > $rc = mysqli_insert_id($this-> | ||
- | |||
- | 795c795 | ||
- | < | ||
- | --- | ||
- | > | ||
- | 906c906 | ||
- | < | ||
- | --- | ||
- | > | ||
- | EOF | ||
- | ) > / | ||
- | patch / | ||
- | rm / | ||
- | </ | ||
- | |||
- | ===== 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, | ||
- | ===== 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 [[http:// | ||
- | |||
- | 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, | ||
- | |||
- | <code php> | ||
- | function getUserCount($filter=array()) { | ||
- | $rc = 0; | ||
- | if($this-> | ||
- | $sql = $this-> | ||
- | |||
- | if ($this-> | ||
- | $sql = substr($sql, | ||
- | $sql = " | ||
- | $result = $this-> | ||
- | $rc = $result[0][' | ||
- | } else if (($result = $this-> | ||
- | $rc = count($result); | ||
- | |||
- | $this-> | ||
- | } | ||
- | return $rc; | ||
- | } | ||
- | </ | ||
- | |||
- | Hope this will save someone the hours I spend investigating. | ||
- | |||
- | |||
- | --- // | ||
- | |||
- | ===== 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, | ||
- | |||
- | If for some reason you mess up and do log out before adding the superuser, use shell access to edit the conf/ | ||
- | |||
- | --- // |
auth/mysql.1365361863.txt.gz · Last modified: 2013-04-07 21:11 by lupo49