====== MySql DokuWiki/ProjectPier ======
Disclaimer: this procedure was based on the dokuwiki+gallery2 page at http://www.dokuwiki.org/wiki:auth:mysql_gallery2
[[http://projectpier.org/|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 DokuWikis 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 usefull 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 tolds
* the module which tables to lock. If you use any aliases for table names
* these array must also contain these aliases. Any unamed 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 thise information through the
* names listed above so aliasses might be neseccary.
*
* 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 nessecary.
*
* 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.
* Important is that this list contains no double entries fo 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 neseccary.
* 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 fom 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 fom 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}'";
--- //[[no@email.com|Marcio Ferreira]] 2008-01-15//