View Javadoc

1   //
2   //  ========================================================================
3   //  Copyright (c) 1995-2014 Mort Bay Consulting Pty. Ltd.
4   //  ------------------------------------------------------------------------
5   //  All rights reserved. This program and the accompanying materials
6   //  are made available under the terms of the Eclipse Public License v1.0
7   //  and Apache License v2.0 which accompanies this distribution.
8   //
9   //      The Eclipse Public License is available at
10  //      http://www.eclipse.org/legal/epl-v10.html
11  //
12  //      The Apache License v2.0 is available at
13  //      http://www.opensource.org/licenses/apache2.0.php
14  //
15  //  You may elect to redistribute this code under either of these licenses.
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   * //TODO JASPI cf JDBCLoginService
49   * DataSourceUserRealm
50   *
51   * Obtain user/password/role information from a database
52   * via jndi DataSource.
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     /** Load user's info from database.
287      * 
288      * @param userName
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      * Lookup the datasource for the jndiName and formulate the
351      * necessary sql query strings based on the configured table
352      * and column names.
353      * 
354      * @throws NamingException
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         //TODO webapp scope?
366         
367         //try finding the datasource in the Server scope
368         if (_server != null)
369         {
370             try
371             {
372                 _datasource = (DataSource)NamingEntryUtil.lookup(_server, _jndiName);
373             }
374             catch (NameNotFoundException e)
375             {
376                 //next try the jvm scope
377             }
378         }
379         
380 
381         //try finding the datasource in the jvm scope
382         if (_datasource==null)
383         {
384             _datasource = (DataSource)NamingEntryUtil.lookup(null, _jndiName);
385         }
386 
387         // set up the select statements based on the table and column names configured
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                 //check if tables exist
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                     //user table default
423                     /*
424                      * create table _userTableName (_userTableKey integer,
425                      * _userTableUserField varchar(100) not null unique,
426                      * _userTablePasswordField varchar(20) not null, primary key(_userTableKey));
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                     //role table default
441                     /*
442                      * create table _roleTableName (_roleTableKey integer,
443                      * _roleTableRoleField varchar(100) not null unique, primary key(_roleTableKey));
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                     //user-role table
458                     /*
459                      * create table _userRoleTableName (_userRoleTableUserKey integer,
460                      * _userRoleTableRoleKey integer,
461                      * primary key (_userRoleTableUserKey, _userRoleTableRoleKey));
462                      * 
463                      * create index idx_user_role on _userRoleTableName (_userRoleTableUserKey);
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 }