1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20 package org.eclipse.jetty.plus.security;
21
22 import java.sql.Connection;
23 import java.sql.DatabaseMetaData;
24 import java.sql.PreparedStatement;
25 import java.sql.ResultSet;
26 import java.sql.SQLException;
27 import java.sql.Statement;
28 import java.util.ArrayList;
29 import java.util.List;
30 import java.util.Locale;
31
32 import javax.naming.InitialContext;
33 import javax.naming.NameNotFoundException;
34 import javax.naming.NamingException;
35 import javax.sql.DataSource;
36
37 import org.eclipse.jetty.plus.jndi.NamingEntryUtil;
38 import org.eclipse.jetty.security.IdentityService;
39 import org.eclipse.jetty.security.MappedLoginService;
40 import org.eclipse.jetty.server.Server;
41 import org.eclipse.jetty.server.UserIdentity;
42 import org.eclipse.jetty.util.log.Log;
43 import org.eclipse.jetty.util.log.Logger;
44 import org.eclipse.jetty.util.security.Password;
45
46
47
48
49
50
51
52
53
54
55 public class DataSourceLoginService extends MappedLoginService
56 {
57 private static final Logger LOG = Log.getLogger(DataSourceLoginService.class);
58
59 private String _jndiName = "javax.sql.DataSource/default";
60 private DataSource _datasource;
61 private Server _server;
62 private String _userTableName = "users";
63 private String _userTableKey = "id";
64 private String _userTableUserField = "username";
65 private String _userTablePasswordField = "pwd";
66 private String _roleTableName = "roles";
67 private String _roleTableKey = "id";
68 private String _roleTableRoleField = "role";
69 private String _userRoleTableName = "user_roles";
70 private String _userRoleTableUserKey = "user_id";
71 private String _userRoleTableRoleKey = "role_id";
72 private int _cacheMs = 30000;
73 private String _userSql;
74 private String _roleSql;
75 private boolean _createTables = false;
76
77
78 public DataSourceLoginService()
79 {
80 }
81
82
83 public DataSourceLoginService(String name)
84 {
85 setName(name);
86 }
87
88
89 public DataSourceLoginService(String name, IdentityService identityService)
90 {
91 setName(name);
92 setIdentityService(identityService);
93 }
94
95
96 public void setJndiName (String jndi)
97 {
98 _jndiName = jndi;
99 }
100
101
102 public String getJndiName ()
103 {
104 return _jndiName;
105 }
106
107
108 public void setServer (Server server)
109 {
110 _server=server;
111 }
112
113
114 public Server getServer()
115 {
116 return _server;
117 }
118
119
120 public void setCreateTables(boolean createTables)
121 {
122 _createTables = createTables;
123 }
124
125
126 public boolean getCreateTables()
127 {
128 return _createTables;
129 }
130
131
132 public void setUserTableName (String name)
133 {
134 _userTableName=name;
135 }
136
137
138 public String getUserTableName()
139 {
140 return _userTableName;
141 }
142
143
144 public String getUserTableKey()
145 {
146 return _userTableKey;
147 }
148
149
150
151 public void setUserTableKey(String tableKey)
152 {
153 _userTableKey = tableKey;
154 }
155
156
157
158 public String getUserTableUserField()
159 {
160 return _userTableUserField;
161 }
162
163
164
165 public void setUserTableUserField(String tableUserField)
166 {
167 _userTableUserField = tableUserField;
168 }
169
170
171
172 public String getUserTablePasswordField()
173 {
174 return _userTablePasswordField;
175 }
176
177
178
179 public void setUserTablePasswordField(String tablePasswordField)
180 {
181 _userTablePasswordField = tablePasswordField;
182 }
183
184
185
186 public String getRoleTableName()
187 {
188 return _roleTableName;
189 }
190
191
192
193 public void setRoleTableName(String tableName)
194 {
195 _roleTableName = tableName;
196 }
197
198
199
200 public String getRoleTableKey()
201 {
202 return _roleTableKey;
203 }
204
205
206
207 public void setRoleTableKey(String tableKey)
208 {
209 _roleTableKey = tableKey;
210 }
211
212
213
214 public String getRoleTableRoleField()
215 {
216 return _roleTableRoleField;
217 }
218
219
220
221 public void setRoleTableRoleField(String tableRoleField)
222 {
223 _roleTableRoleField = tableRoleField;
224 }
225
226
227
228 public String getUserRoleTableName()
229 {
230 return _userRoleTableName;
231 }
232
233
234
235 public void setUserRoleTableName(String roleTableName)
236 {
237 _userRoleTableName = roleTableName;
238 }
239
240
241
242 public String getUserRoleTableUserKey()
243 {
244 return _userRoleTableUserKey;
245 }
246
247
248
249 public void setUserRoleTableUserKey(String roleTableUserKey)
250 {
251 _userRoleTableUserKey = roleTableUserKey;
252 }
253
254
255
256 public String getUserRoleTableRoleKey()
257 {
258 return _userRoleTableRoleKey;
259 }
260
261
262
263 public void setUserRoleTableRoleKey(String roleTableRoleKey)
264 {
265 _userRoleTableRoleKey = roleTableRoleKey;
266 }
267
268
269 public void setCacheMs (int ms)
270 {
271 _cacheMs=ms;
272 }
273
274
275 public int getCacheMs ()
276 {
277 return _cacheMs;
278 }
279
280
281 @Override
282 protected void loadUsers()
283 {
284 }
285
286
287
288
289
290
291 @Override
292 protected UserIdentity loadUser (String userName)
293 {
294 try
295 {
296 initDb();
297 try (Connection connection = getConnection();
298 PreparedStatement statement1 = connection.prepareStatement(_userSql))
299 {
300 statement1.setObject(1, userName);
301 try (ResultSet rs1 = statement1.executeQuery())
302 {
303 if (rs1.next())
304 {
305 int key = rs1.getInt(_userTableKey);
306 String credentials = rs1.getString(_userTablePasswordField);
307 List<String> roles = new ArrayList<String>();
308 try (PreparedStatement statement2 = connection.prepareStatement(_roleSql))
309 {
310 statement2.setInt(1, key);
311 try (ResultSet rs2 = statement2.executeQuery())
312 {
313 while (rs2.next())
314 roles.add(rs2.getString(_roleTableRoleField));
315 }
316 }
317 return putUser(userName,new Password(credentials), roles.toArray(new String[roles.size()]));
318 }
319 }
320 }
321 }
322 catch (NamingException e)
323 {
324 LOG.warn("No datasource for "+_jndiName, e);
325 }
326 catch (SQLException e)
327 {
328 LOG.warn("Problem loading user info for "+userName, e);
329 }
330 return null;
331 }
332
333
334
335
336
337
338
339
340
341 public void initDb() throws NamingException, SQLException
342 {
343 if (_datasource != null)
344 return;
345
346 @SuppressWarnings("unused")
347 InitialContext ic = new InitialContext();
348 assert ic!=null;
349
350
351
352
353 if (_server != null)
354 {
355 try
356 {
357 _datasource = (DataSource)NamingEntryUtil.lookup(_server, _jndiName);
358 }
359 catch (NameNotFoundException e)
360 {
361
362 }
363 }
364
365
366
367 if (_datasource==null)
368 {
369 _datasource = (DataSource)NamingEntryUtil.lookup(null, _jndiName);
370 }
371
372
373 _userSql = "select " + _userTableKey + "," + _userTablePasswordField
374 + " from " + _userTableName
375 + " where "+ _userTableUserField + " = ?";
376
377 _roleSql = "select r." + _roleTableRoleField
378 + " from " + _roleTableName + " r, " + _userRoleTableName
379 + " u where u."+ _userRoleTableUserKey + " = ?"
380 + " and r." + _roleTableKey + " = u." + _userRoleTableRoleKey;
381
382 prepareTables();
383 }
384
385
386
387 private void prepareTables()
388 throws NamingException, SQLException
389 {
390 if (_createTables)
391 {
392 boolean autocommit = true;
393 Connection connection = getConnection();
394 try (Statement stmt = connection.createStatement())
395 {
396 autocommit = connection.getAutoCommit();
397 connection.setAutoCommit(false);
398 DatabaseMetaData metaData = connection.getMetaData();
399
400
401 String tableName = (metaData.storesLowerCaseIdentifiers()? _userTableName.toLowerCase(Locale.ENGLISH): (metaData.storesUpperCaseIdentifiers()?_userTableName.toUpperCase(Locale.ENGLISH): _userTableName));
402 try (ResultSet result = metaData.getTables(null, null, tableName, null))
403 {
404 if (!result.next())
405 {
406
407
408
409
410
411
412 stmt.executeUpdate("create table "+_userTableName+ "("+_userTableKey+" integer,"+
413 _userTableUserField+" varchar(100) not null unique,"+
414 _userTablePasswordField+" varchar(20) not null, primary key("+_userTableKey+"))");
415 if (LOG.isDebugEnabled()) LOG.debug("Created table "+_userTableName);
416 }
417 }
418
419 tableName = (metaData.storesLowerCaseIdentifiers()? _roleTableName.toLowerCase(Locale.ENGLISH): (metaData.storesUpperCaseIdentifiers()?_roleTableName.toUpperCase(Locale.ENGLISH): _roleTableName));
420 try (ResultSet result = metaData.getTables(null, null, tableName, null))
421 {
422 if (!result.next())
423 {
424
425
426
427
428
429 String str = "create table "+_roleTableName+" ("+_roleTableKey+" integer, "+
430 _roleTableRoleField+" varchar(100) not null unique, primary key("+_roleTableKey+"))";
431 stmt.executeUpdate(str);
432 if (LOG.isDebugEnabled()) LOG.debug("Created table "+_roleTableName);
433 }
434 }
435
436 tableName = (metaData.storesLowerCaseIdentifiers()? _userRoleTableName.toLowerCase(Locale.ENGLISH): (metaData.storesUpperCaseIdentifiers()?_userRoleTableName.toUpperCase(Locale.ENGLISH): _userRoleTableName));
437 try (ResultSet result = metaData.getTables(null, null, tableName, null))
438 {
439 if (!result.next())
440 {
441
442
443
444
445
446
447
448
449 stmt.executeUpdate("create table "+_userRoleTableName+" ("+_userRoleTableUserKey+" integer, "+
450 _userRoleTableRoleKey+" integer, "+
451 "primary key ("+_userRoleTableUserKey+", "+_userRoleTableRoleKey+"))");
452 stmt.executeUpdate("create index indx_user_role on "+_userRoleTableName+"("+_userRoleTableUserKey+")");
453 if (LOG.isDebugEnabled()) LOG.debug("Created table "+_userRoleTableName +" and index");
454 }
455 }
456 connection.commit();
457 }
458 finally
459 {
460 try
461 {
462 connection.setAutoCommit(autocommit);
463 }
464 catch (SQLException e)
465 {
466 if (LOG.isDebugEnabled()) LOG.debug("Prepare tables", e);
467 }
468 finally
469 {
470 try
471 {
472 connection.close();
473 }
474 catch (SQLException e)
475 {
476 if (LOG.isDebugEnabled()) LOG.debug("Prepare tables", e);
477 }
478 }
479 }
480 }
481 else if (LOG.isDebugEnabled())
482 {
483 LOG.debug("createTables false");
484 }
485 }
486
487
488 private Connection getConnection ()
489 throws NamingException, SQLException
490 {
491 initDb();
492 return _datasource.getConnection();
493 }
494
495 }