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.servlet.ServletRequest;
36 import javax.sql.DataSource;
37
38 import org.eclipse.jetty.plus.jndi.NamingEntryUtil;
39 import org.eclipse.jetty.security.IdentityService;
40 import org.eclipse.jetty.security.MappedLoginService;
41 import org.eclipse.jetty.server.Server;
42 import org.eclipse.jetty.server.UserIdentity;
43 import org.eclipse.jetty.util.log.Log;
44 import org.eclipse.jetty.util.log.Logger;
45 import org.eclipse.jetty.util.security.Credential;
46
47
48
49
50
51
52
53
54 public class DataSourceLoginService extends MappedLoginService
55 {
56 private static final Logger LOG = Log.getLogger(DataSourceLoginService.class);
57
58 private String _jndiName = "javax.sql.DataSource/default";
59 private DataSource _datasource;
60 private Server _server;
61 private String _userTableName = "users";
62 private String _userTableKey = "id";
63 private String _userTableUserField = "username";
64 private String _userTablePasswordField = "pwd";
65 private String _roleTableName = "roles";
66 private String _roleTableKey = "id";
67 private String _roleTableRoleField = "role";
68 private String _userRoleTableName = "user_roles";
69 private String _userRoleTableUserKey = "user_id";
70 private String _userRoleTableRoleKey = "role_id";
71 private int _cacheMs = 30000;
72 private long _lastPurge = 0;
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
292
293 @Override
294 protected UserIdentity loadUser (String userName)
295 {
296 try
297 {
298 try (Connection connection = getConnection();
299 PreparedStatement statement1 = connection.prepareStatement(_userSql))
300 {
301 statement1.setObject(1, userName);
302 try (ResultSet rs1 = statement1.executeQuery())
303 {
304 if (rs1.next())
305 {
306 int key = rs1.getInt(_userTableKey);
307 String credentials = rs1.getString(_userTablePasswordField);
308 List<String> roles = new ArrayList<String>();
309 try (PreparedStatement statement2 = connection.prepareStatement(_roleSql))
310 {
311 statement2.setInt(1, key);
312 try (ResultSet rs2 = statement2.executeQuery())
313 {
314 while (rs2.next())
315 {
316 roles.add(rs2.getString(_roleTableRoleField));
317 }
318 }
319 }
320 return putUser(userName, Credential.getCredential(credentials), roles.toArray(new String[roles.size()]));
321 }
322 }
323 }
324 }
325 catch (NamingException e)
326 {
327 LOG.warn("No datasource for "+_jndiName, e);
328 }
329 catch (SQLException e)
330 {
331 LOG.warn("Problem loading user info for "+userName, e);
332 }
333 return null;
334 }
335
336
337
338
339 @Override
340 public UserIdentity login(String username, Object credentials, ServletRequest request)
341 {
342 long now = System.currentTimeMillis();
343 if (now - _lastPurge > _cacheMs || _cacheMs == 0)
344 {
345 _users.clear();
346 _lastPurge = now;
347 }
348
349 return super.login(username,credentials, request);
350 }
351
352
353
354
355
356
357
358
359
360
361 public void initDb() throws NamingException, SQLException
362 {
363 if (_datasource != null)
364 return;
365
366 @SuppressWarnings("unused")
367 InitialContext ic = new InitialContext();
368 assert ic!=null;
369
370
371
372
373 if (_server != null)
374 {
375 try
376 {
377 _datasource = (DataSource)NamingEntryUtil.lookup(_server, _jndiName);
378 }
379 catch (NameNotFoundException e)
380 {
381
382 }
383 }
384
385
386
387 if (_datasource==null)
388 {
389 _datasource = (DataSource)NamingEntryUtil.lookup(null, _jndiName);
390 }
391
392
393 _userSql = "select " + _userTableKey + "," + _userTablePasswordField
394 + " from " + _userTableName
395 + " where "+ _userTableUserField + " = ?";
396
397 _roleSql = "select r." + _roleTableRoleField
398 + " from " + _roleTableName + " r, " + _userRoleTableName
399 + " u where u."+ _userRoleTableUserKey + " = ?"
400 + " and r." + _roleTableKey + " = u." + _userRoleTableRoleKey;
401
402 prepareTables();
403 }
404
405
406
407 private void prepareTables()
408 throws NamingException, SQLException
409 {
410 if (_createTables)
411 {
412 boolean autocommit = true;
413 Connection connection = getConnection();
414 try (Statement stmt = connection.createStatement())
415 {
416 autocommit = connection.getAutoCommit();
417 connection.setAutoCommit(false);
418 DatabaseMetaData metaData = connection.getMetaData();
419
420
421 String tableName = (metaData.storesLowerCaseIdentifiers()? _userTableName.toLowerCase(Locale.ENGLISH): (metaData.storesUpperCaseIdentifiers()?_userTableName.toUpperCase(Locale.ENGLISH): _userTableName));
422 try (ResultSet result = metaData.getTables(null, null, tableName, null))
423 {
424 if (!result.next())
425 {
426
427
428
429
430
431
432 stmt.executeUpdate("create table "+_userTableName+ "("+_userTableKey+" integer,"+
433 _userTableUserField+" varchar(100) not null unique,"+
434 _userTablePasswordField+" varchar(20) not null, primary key("+_userTableKey+"))");
435 if (LOG.isDebugEnabled()) LOG.debug("Created table "+_userTableName);
436 }
437 }
438
439 tableName = (metaData.storesLowerCaseIdentifiers()? _roleTableName.toLowerCase(Locale.ENGLISH): (metaData.storesUpperCaseIdentifiers()?_roleTableName.toUpperCase(Locale.ENGLISH): _roleTableName));
440 try (ResultSet result = metaData.getTables(null, null, tableName, null))
441 {
442 if (!result.next())
443 {
444
445
446
447
448
449 String str = "create table "+_roleTableName+" ("+_roleTableKey+" integer, "+
450 _roleTableRoleField+" varchar(100) not null unique, primary key("+_roleTableKey+"))";
451 stmt.executeUpdate(str);
452 if (LOG.isDebugEnabled()) LOG.debug("Created table "+_roleTableName);
453 }
454 }
455
456 tableName = (metaData.storesLowerCaseIdentifiers()? _userRoleTableName.toLowerCase(Locale.ENGLISH): (metaData.storesUpperCaseIdentifiers()?_userRoleTableName.toUpperCase(Locale.ENGLISH): _userRoleTableName));
457 try (ResultSet result = metaData.getTables(null, null, tableName, null))
458 {
459 if (!result.next())
460 {
461
462
463
464
465
466
467
468
469 stmt.executeUpdate("create table "+_userRoleTableName+" ("+_userRoleTableUserKey+" integer, "+
470 _userRoleTableRoleKey+" integer, "+
471 "primary key ("+_userRoleTableUserKey+", "+_userRoleTableRoleKey+"))");
472 stmt.executeUpdate("create index indx_user_role on "+_userRoleTableName+"("+_userRoleTableUserKey+")");
473 if (LOG.isDebugEnabled()) LOG.debug("Created table "+_userRoleTableName +" and index");
474 }
475 }
476 connection.commit();
477 }
478 finally
479 {
480 try
481 {
482 connection.setAutoCommit(autocommit);
483 }
484 catch (SQLException e)
485 {
486 if (LOG.isDebugEnabled()) LOG.debug("Prepare tables", e);
487 }
488 finally
489 {
490 try
491 {
492 connection.close();
493 }
494 catch (SQLException e)
495 {
496 if (LOG.isDebugEnabled()) LOG.debug("Prepare tables", e);
497 }
498 }
499 }
500 }
501 else if (LOG.isDebugEnabled())
502 {
503 LOG.debug("createTables false");
504 }
505 }
506
507
508 private Connection getConnection ()
509 throws NamingException, SQLException
510 {
511 initDb();
512 return _datasource.getConnection();
513 }
514
515 }