DokuWiki

It's better when it's simple

User Tools

Site Tools


Sidebar

Translations of this page?:

Learn about DokuWiki

Advanced Use

Corporate Use

Our Community


Follow us on Facebook, Twitter and other social networks.

auth:mysql_projectpier

« MySQL Authentification Backend

MySQL DokuWiki/ProjectPier

Disclaimer: this procedure was based on the DokuWiki/Gallery2 page

ProjectPier
This assumes that all user/group accounts will be created and maintained through ProjectPier.

This also assumes that the company name in project pier is the group (table pp_companies, field name), so you know what to use in acl.auth.php

Based on ProjectPier_0.8.0-final.zip version
DokuWiki version 2007-06-26b

MySql authentication in Project Pier

ProjectPier stores passwords in SHA1 with a salt, in 2 fields in pp_users table: the token (40 chars, the password) and the salt (13 chars). The token (password) is calculated as “sha1($salt . $typed_pass);”, where $salt is the 13 char value from salt field and $typed_pass is the password supplied by the user.

DokuWiki changes

Due to how ProjectPier stores its passwords as stated above, none of DokuWiki's built-in-encryptions work so one must edit the function auth_verifyPassword in inc/auth.php (the lenght is 53 because its the concatenation of salt and token fields):

...
  }elseif($len == 32){
    $method = 'md5';
  }elseif($len == 53){  // projectpier sha1, 13 chars salt, 40 char token
    $method = 'sha1';
    $privatesalt = substr($crypt,0,13);
    $clear = $privatesalt.$clear;
    $crypt = substr($crypt, 13, 40);
  }elseif($len == 40){
    $method = 'sha1';
...

Lastly the file conf/mysql.conf.php:

/* Options to configure database access. You need to set up this
 * options carefully, otherwise you won't be able to access you
 * database.
 */
$conf['auth']['mysql']['server']   = '';
$conf['auth']['mysql']['user']     = '';
$conf['auth']['mysql']['password'] = '';
$conf['auth']['mysql']['database'] = '';
 
/* This option enables debug messages in the mysql module. It is
 * mostly usefull for system admins.
 */
$conf['auth']['mysql']['debug'] = 0;
 
/* 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 '1' and the cleartext password is forwarded to
 * the database, otherwise the encrypted one.
 */
$conf['auth']['mysql']['forwardClearPass'] = 0;
 
/* 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
 * these array must also contain these aliases. Any unnamed alias will cause
 * a warning during operation. See the example below.
 */
$conf['auth']['mysql']['TablesToLock']= array("pp_users", "pp_users AS u", "pp_companies", "pp_companies AS g");
 
/***********************************************************************/
/*       Basic SQL statements for user authentication (required)       */
/***********************************************************************/
 
/* 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 a alias might be necessary.
 *
 * Following patters will be replaced:
 *   %{user}    user name
 *   %{pass}    encrypted or clear text password (depends on 'encryptPass')
 *   %{dgroup}  default group name
 */
$conf['auth']['mysql']['checkPass']   = "SELECT CONCAT(salt,token) AS pass FROM pp_users as u WHERE username='%{user}'";
 
 
/* This statement should return a table with exact one row containing
 * information about one user. The field needed are:
 * 'pass'  containing the encrypted or clear text password
 * 'name'  the user's full name
 * 'mail'  the user's email address
 *
 * Keep in mind that DokuWiki will access this information through the
 * names listed above so aliases might be necessary.
 *
 * Following patters will be replaced:
 *   %{user}    user name
 */
$conf['auth']['mysql']['getUserInfo'] = "SELECT CONCAT(salt,token) AS pass, display_name AS name, email as mail
                                         FROM pp_users WHERE username='%{user}'";
 
 
/* 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 a alias
 * might be necessary.
 *
 * Following patters will be replaced:
 *   %{user}    user name
 */
 
$conf['auth']['mysql']['getGroups']   = " SELECT name `group`
                                         FROM pp_companies g, pp_users u
                                         WHERE u.company_id = g.id
                                         AND u.username='%{user}' ";
 
/***********************************************************************/
/*      Additional minimum SQL statements to use the user manager      */
/***********************************************************************/
 
/* This statement should return a table containing all user login names
 * that meet certain filter criteria. The filter expressions will be added
 * case dependend by the module. At the end a sort expression will be added.
 * It is important that this list contains no double entries for a user. Each
 * user name is only allowed once in the table.
 *
 * The login name will be accessed as 'user' to a alias might be necessary.
 * No patterns will be replaced in this statement but following patters
 * will be replaced in the filter expressions:
 *   %{user}    in FilterLogin  user's login name
 *   %{name}    in FilterName   user's full name
 *   %{email}   in FilterEmail  user's email address
 *   %{group}   in FilterGroup  group name
 */
 
$conf['auth']['mysql']['getUsers']    = "SELECT DISTINCT username AS user
                                         FROM pp_users AS u
                                         LEFT JOIN pp_companies as g ON u.company_id=g.id";
$conf['auth']['mysql']['FilterLogin'] = "userName LIKE '%{user}'";
$conf['auth']['mysql']['FilterName']  = "display_name LIKE '%{name}'";
$conf['auth']['mysql']['FilterEmail'] = "email LIKE '%{email}'";
$conf['auth']['mysql']['FilterGroup'] = "name LIKE '%{group}'";
$conf['auth']['mysql']['SortOrder']   = "ORDER BY username";
 
 
/***********************************************************************/
/*   Additional SQL statements to add new users with the user manager  */
/***********************************************************************/
 
/* This statement should add a user to the database. Minimum information
 * to store are: login name, password, email address and full name.
 *
 * Following patterns will be replaced:
 *   %{user}    user's login name
 *   %{pass}    password (encrypted or clear text, depends on 'encryptPass')
 *   %{email}   email address
 *   %{name}    user's full name
 */
$conf['auth']['mysql']['addUser']     = ""; /*"INSERT INTO users
                                         (login, pass, email, firstname, lastname)
                                         VALUES ('%{user}', '%{pass}', '%{email}',
                                         SUBSTRING_INDEX('%{name}',' ', 1),
                                         SUBSTRING_INDEX('%{name}',' ', -1))";
*/
/* This statement should add a group to the database.
 * Following patterns will be replaced:
 *   %{group}   group name
 */
$conf['auth']['mysql']['addGroup']    = ""; /*"INSERT INTO groups (name)
                                         VALUES ('%{group}')";
*/
/* This statement should connect a user to a group (a user become member
 * of that group).
 * Following patterns will be replaced:
 *   %{user}    user's login name
 *   %{uid}             id of a user dataset
 *   %{group}   group name
 *   %{gid}             id of a group dataset
 */
$conf['auth']['mysql']['addUserGroup']= ""; /*"INSERT INTO usergroup (uid, gid)
                                         VALUES ('%{uid}', '%{gid}')";
*/
/* This statement should remove a group from the database.
 * Following patterns will be replaced:
 *   %{group}   group name
 *   %{gid}             id of a group dataset
 */
$conf['auth']['mysql']['delGroup']    = ""; /*"DELETE FROM groups
                                         WHERE gid='%{gid}'";
*/
/* This statement should return the database index of a given user name.
 * The module will access the index with the name 'id' so a alias might be
 * necessary.
 * following patters will be replaced:
 *   %{user}    user name
 */
$conf['auth']['mysql']['getUserID']   = ""; /*"SELECT uid AS id
                                         FROM users
                                         WHERE login='%{user}'";
*/
 
/***********************************************************************/
/*   Additional SQL statements to delete users with the user manager   */
/***********************************************************************/
 
/* This statement should remove a user from the database.
 * Following patterns will be replaced:
 *   %{user}    user's login name
 *   %{uid}             id of a user dataset
 */
$conf['auth']['mysql']['delUser']     = ""; /*"DELETE FROM users
                                         WHERE uid='%{uid}'";
*/
/* This statement should remove all connections from a user to any group
 * (a user quits membership of all groups).
 * Following patterns will be replaced:
 *   %{uid}             id of a user dataset
 */
$conf['auth']['mysql']['delUserRefs'] = ""; /*"DELETE FROM usergroup
                                         WHERE uid='%{uid}'";
*/
/***********************************************************************/
/*   Additional SQL statements to modify users with the user manager   */
/***********************************************************************/
 
/* 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.
 *
 * Following patterns will be replaced:
 *   %{user}    user's login name
 *   %{pass}    password (encrypted or clear text, depends on 'encryptPass')
 *   %{email}   email address
 *   %{name}    user's full name
 *   %{uid}     user id that should be updated
 */
$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']  = ""; //"firstname=SUBSTRING_INDEX('%{name}',' ', 1),
                                         //lastname=SUBSTRING_INDEX('%{name}',' ', -1)";
$conf['auth']['mysql']['UpdateTarget']= ""; //"WHERE uid=%{uid}";
 
/* This statement should remove a single connection from a user to a
 * group (a user quits membership of that group).
 *
 * Following patterns will be replaced:
 *   %{user}    user's login name
 *   %{uid}             id of a user dataset
 *   %{group}   group name
 *   %{gid}             id of a group dataset
 */
$conf['auth']['mysql']['delUserGroup']= ""; /*"DELETE FROM usergroup
                                         WHERE uid='%{uid}'
                                         AND gid='%{gid}'";
*/
/* This statement should return the database index of a given group name.
 * The module will access the index with the name 'id' so a alias might
 * be necessary.
 *
 * Following patters will be replaced:
 *   %{group}   group name
 */
 
$conf['auth']['mysql']['getGroupID']  = "SELECT id AS id
                                        FROM pp_companies
                                        WHERE name='%{group}'";

Marcio Ferreira 2008-01-15

auth/mysql_projectpier.txt · Last modified: 2011-06-18 16:12 by ach