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