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