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