1<?php
2/*
3 * This is an example configuration for the mysql auth plugin.
4 *
5 * This SQL statements are optimized for following table structure.
6 * If you use a different one you have to change them accordingly.
7 * See comments of every statement for details.
8 *
9 * TABLE users
10 *     uid   login   pass   firstname   lastname   email
11 *
12 * TABLE groups
13 *     gid   name
14 *
15 * TABLE usergroup
16 *     uid   gid
17 *
18 * To use this configuration you have to copy them to local.protected.php
19 * or at least include this file in local.protected.php.
20 */
21
22/* Options to configure database access. You need to set up this
23 * options carefully, otherwise you won't be able to access you
24 * database.
25 */
26$conf['plugin']['authmysql']['server']   = '';
27$conf['plugin']['authmysql']['user']     = '';
28$conf['plugin']['authmysql']['password'] = '';
29$conf['plugin']['authmysql']['database'] = '';
30
31/* This option enables debug messages in the mysql plugin. It is
32 * mostly useful for system admins.
33 */
34$conf['plugin']['authmysql']['debug'] = 0;
35
36/* Normally password encryption is done by DokuWiki (recommended) but for
37 * some reasons it might be useful to let the database do the encryption.
38 * Set 'forwardClearPass' to '1' and the cleartext password is forwarded to
39 * the database, otherwise the encrypted one.
40 */
41$conf['plugin']['authmysql']['forwardClearPass'] = 0;
42
43/* Multiple table operations will be protected by locks. This array tells
44 * the plugin which tables to lock. If you use any aliases for table names
45 * these array must also contain these aliases. Any unnamed alias will cause
46 * a warning during operation. See the example below.
47 */
48$conf['plugin']['authmysql']['TablesToLock']= array("users", "users AS u","groups", "groups AS g", "usergroup", "usergroup AS ug");
49
50/***********************************************************************/
51/*       Basic SQL statements for user authentication (required)       */
52/***********************************************************************/
53
54/* This statement is used to grant or deny access to the wiki. The result
55 * should be a table with exact one line containing at least the password
56 * of the user. If the result table is empty or contains more than one
57 * row, access will be denied.
58 *
59 * The plugin accesses the password as 'pass' so an alias might be necessary.
60 *
61 * Following patters will be replaced:
62 *   %{user}    user name
63 *   %{pass}    encrypted or clear text password (depends on 'encryptPass')
64 *   %{dgroup}  default group name
65 */
66$conf['plugin']['authmysql']['checkPass']   = "SELECT pass
67                                               FROM usergroup AS ug
68                                               JOIN users AS u ON u.uid=ug.uid
69                                               JOIN groups AS g ON g.gid=ug.gid
70                                               WHERE login='%{user}'
71                                               AND name='%{dgroup}'";
72
73/* This statement should return a table with exact one row containing
74 * information about one user. The field needed are:
75 * 'pass'  containing the encrypted or clear text password
76 * 'name'  the user's full name
77 * 'mail'  the user's email address
78 *
79 * Keep in mind that Dokuwiki will access this information through the
80 * names listed above so aliases might be necessary.
81 *
82 * Following patters will be replaced:
83 *   %{user}    user name
84 */
85$conf['plugin']['authmysql']['getUserInfo'] = "SELECT pass, CONCAT(firstname,' ',lastname) AS name, email AS mail
86                                               FROM users
87                                               WHERE login='%{user}'";
88
89/* This statement is used to get all groups a user is member of. The
90 * result should be a table containing all groups the given user is
91 * member of. The plugin accesses the group name as 'group' so an alias
92 * might be necessary.
93 *
94 * Following patters will be replaced:
95 *   %{user}    user name
96 */
97$conf['plugin']['authmysql']['getGroups']   = "SELECT name as `group`
98                                               FROM groups g, users u, usergroup ug
99                                               WHERE u.uid = ug.uid
100                                               AND g.gid = ug.gid
101                                               AND u.login='%{user}'";
102
103/***********************************************************************/
104/*      Additional minimum SQL statements to use the user manager      */
105/***********************************************************************/
106
107/* This statement should return a table containing all user login names
108 * that meet certain filter criteria. The filter expressions will be added
109 * case dependent by the plugin. At the end a sort expression will be added.
110 * Important is that this list contains no double entries for a user. Each
111 * user name is only allowed once in the table.
112 *
113 * The login name will be accessed as 'user' to an alias might be necessary.
114 * No patterns will be replaced in this statement but following patters
115 * will be replaced in the filter expressions:
116 *   %{user}    in FilterLogin  user's login name
117 *   %{name}    in FilterName   user's full name
118 *   %{email}   in FilterEmail  user's email address
119 *   %{group}   in FilterGroup  group name
120 */
121$conf['plugin']['authmysql']['getUsers']    = "SELECT DISTINCT login AS user
122                                               FROM users AS u
123                                               LEFT JOIN usergroup AS ug ON u.uid=ug.uid
124                                               LEFT JOIN groups AS g ON ug.gid=g.gid";
125$conf['plugin']['authmysql']['FilterLogin'] = "login LIKE '%{user}'";
126$conf['plugin']['authmysql']['FilterName']  = "CONCAT(firstname,' ',lastname) LIKE '%{name}'";
127$conf['plugin']['authmysql']['FilterEmail'] = "email LIKE '%{email}'";
128$conf['plugin']['authmysql']['FilterGroup'] = "name LIKE '%{group}'";
129$conf['plugin']['authmysql']['SortOrder']   = "ORDER BY login";
130
131/***********************************************************************/
132/*   Additional SQL statements to add new users with the user manager  */
133/***********************************************************************/
134
135/* This statement should add a user to the database. Minimum information
136 * to store are: login name, password, email address and full name.
137 *
138 * Following patterns will be replaced:
139 *   %{user}    user's login name
140 *   %{pass}    password (encrypted or clear text, depends on 'encryptPass')
141 *   %{email}   email address
142 *   %{name}    user's full name
143 */
144$conf['plugin']['authmysql']['addUser']     = "INSERT INTO users
145                                               (login, pass, email, firstname, lastname)
146                                               VALUES ('%{user}', '%{pass}', '%{email}',
147                                               SUBSTRING_INDEX('%{name}',' ', 1),
148                                               SUBSTRING_INDEX('%{name}',' ', -1))";
149
150/* This statement should add a group to the database.
151 * Following patterns will be replaced:
152 *   %{group}   group name
153 */
154$conf['plugin']['authmysql']['addGroup']    = "INSERT INTO groups (name)
155                                               VALUES ('%{group}')";
156
157/* This statement should connect a user to a group (a user become member
158 * of that group).
159 * Following patterns will be replaced:
160 *   %{user}    user's login name
161 *   %{uid}     id of a user dataset
162 *   %{group}   group name
163 *   %{gid}     id of a group dataset
164 */
165$conf['plugin']['authmysql']['addUserGroup']= "INSERT INTO usergroup (uid, gid)
166                                               VALUES ('%{uid}', '%{gid}')";
167
168/* This statement should remove a group fom the database.
169 * Following patterns will be replaced:
170 *   %{group}   group name
171 *   %{gid}     id of a group dataset
172 */
173$conf['plugin']['authmysql']['delGroup']    = "DELETE FROM groups
174                                               WHERE gid='%{gid}'";
175
176/* This statement should return the database index of a given user name.
177 * The plugin will access the index with the name 'id' so an alias might be
178 * necessary.
179 * following patters will be replaced:
180 *   %{user}    user name
181 */
182$conf['plugin']['authmysql']['getUserID']   = "SELECT uid AS id
183                                               FROM users
184                                               WHERE login='%{user}'";
185
186/***********************************************************************/
187/*   Additional SQL statements to delete users with the user manager   */
188/***********************************************************************/
189
190/* This statement should remove a user fom the database.
191 * Following patterns will be replaced:
192 *   %{user}    user's login name
193 *   %{uid}     id of a user dataset
194 */
195$conf['plugin']['authmysql']['delUser']     = "DELETE FROM users
196                                               WHERE uid='%{uid}'";
197
198/* This statement should remove all connections from a user to any group
199 * (a user quits membership of all groups).
200 * Following patterns will be replaced:
201 *   %{uid}     id of a user dataset
202 */
203$conf['plugin']['authmysql']['delUserRefs'] = "DELETE FROM usergroup
204                                               WHERE uid='%{uid}'";
205
206/***********************************************************************/
207/*   Additional SQL statements to modify users with the user manager   */
208/***********************************************************************/
209
210/* This statements should modify a user entry in the database. The
211 * statements UpdateLogin, UpdatePass, UpdateEmail and UpdateName will be
212 * added to updateUser on demand. Only changed parameters will be used.
213 *
214 * Following patterns will be replaced:
215 *   %{user}    user's login name
216 *   %{pass}    password (encrypted or clear text, depends on 'encryptPass')
217 *   %{email}   email address
218 *   %{name}    user's full name
219 *   %{uid}     user id that should be updated
220 */
221$conf['plugin']['authmysql']['updateUser']  = "UPDATE users SET";
222$conf['plugin']['authmysql']['UpdateLogin'] = "login='%{user}'";
223$conf['plugin']['authmysql']['UpdatePass']  = "pass='%{pass}'";
224$conf['plugin']['authmysql']['UpdateEmail'] = "email='%{email}'";
225$conf['plugin']['authmysql']['UpdateName']  = "firstname=SUBSTRING_INDEX('%{name}',' ', 1),
226                                               lastname=SUBSTRING_INDEX('%{name}',' ', -1)";
227$conf['plugin']['authmysql']['UpdateTarget']= "WHERE uid=%{uid}";
228
229/* This statement should remove a single connection from a user to a
230 * group (a user quits membership of that group).
231 *
232 * Following patterns will be replaced:
233 *   %{user}    user's login name
234 *   %{uid}     id of a user dataset
235 *   %{group}   group name
236 *   %{gid}     id of a group dataset
237 */
238$conf['plugin']['authmysql']['delUserGroup']= "DELETE FROM usergroup
239                                               WHERE uid='%{uid}'
240                                               AND gid='%{gid}'";
241
242/* This statement should return the database index of a given group name.
243 * The plugin will access the index with the name 'id' so an alias might
244 * be necessary.
245 *
246 * Following patters will be replaced:
247 *   %{group}   group name
248 */
249$conf['plugin']['authmysql']['getGroupID']  = "SELECT gid AS id
250                                               FROM groups
251                                               WHERE name='%{group}'";
252
253
254