001 /*
002 Licensed to the Apache Software Foundation (ASF) under one
003 or more contributor license agreements. See the NOTICE file
004 distributed with this work for additional information
005 regarding copyright ownership. The ASF licenses this file
006 to you under the Apache License, Version 2.0 (the
007 "License"); you may not use this file except in compliance
008 with the License. You may obtain a copy of the License at
009
010 http://www.apache.org/licenses/LICENSE-2.0
011
012 Unless required by applicable law or agreed to in writing,
013 software distributed under the License is distributed on an
014 "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
015 KIND, either express or implied. See the License for the
016 specific language governing permissions and limitations
017 under the License.
018 */
019 package org.apache.wiki.auth.authorize;
020
021 import java.security.Principal;
022 import java.sql.*;
023 import java.util.*;
024 import java.util.Date;
025
026 import javax.naming.Context;
027 import javax.naming.InitialContext;
028 import javax.naming.NamingException;
029 import javax.sql.DataSource;
030
031 import org.apache.log4j.Logger;
032 import org.apache.wiki.WikiEngine;
033 import org.apache.wiki.api.exceptions.NoRequiredPropertyException;
034 import org.apache.wiki.auth.NoSuchPrincipalException;
035 import org.apache.wiki.auth.WikiPrincipal;
036 import org.apache.wiki.auth.WikiSecurityException;
037
038 /**
039 * <p>
040 * Implementation of GroupDatabase that persists {@link Group} objects to a JDBC
041 * DataSource, as might typically be provided by a web container. This
042 * implementation looks up the JDBC DataSource using JNDI. The JNDI name of the
043 * datasource, backing table and mapped columns used by this class can be
044 * overridden by adding settings in <code>jspwiki.properties</code>.
045 * </p>
046 * <p>
047 * Configurable properties are these:
048 * </p>
049 * <table>
050 * <tr> <thead>
051 * <th>Property</th>
052 * <th>Default</th>
053 * <th>Definition</th>
054 * <thead> </tr>
055 * <tr>
056 * <td><code>jspwiki.groupdatabase.datasource</code></td>
057 * <td><code>jdbc/GroupDatabase</code></td>
058 * <td>The JNDI name of the DataSource</td>
059 * </tr>
060 * <tr>
061 * <td><code>jspwiki.groupdatabase.table</code></td>
062 * <td><code>groups</code></td>
063 * <td>The table that stores the groups</td>
064 * </tr>
065 * <tr>
066 * <td><code>jspwiki.groupdatabase.membertable</code></td>
067 * <td><code>group_members</code></td>
068 * <td>The table that stores the names of group members</td>
069 * </tr>
070 * <tr>
071 * <td><code>jspwiki.groupdatabase.created</code></td>
072 * <td><code>created</code></td>
073 * <td>The column containing the group's creation timestamp</td>
074 * </tr>
075 * <tr>
076 * <td><code>jspwiki.groupdatabase.creator</code></td>
077 * <td><code>creator</code></td>
078 * <td>The column containing the group creator's name</td>
079 * </tr>
080 * <tr>
081 * <td><code>jspwiki.groupdatabase.name</code></td>
082 * <td><code>name</code></td>
083 * <td>The column containing the group's name</td>
084 * </tr>
085 * <tr>
086 * <td><code>jspwiki.groupdatabase.member</code></td>
087 * <td><code>member</code></td>
088 * <td>The column containing the group member's name</td>
089 * </tr>
090 * <tr>
091 * <td><code>jspwiki.groupdatabase.modified</code></td>
092 * <td><code>modified</code></td>
093 * <td>The column containing the group's last-modified timestamp</td>
094 * </tr>
095 * <tr>
096 * <td><code>jspwiki.groupdatabase.modifier</code></td>
097 * <td><code>modifier</code></td>
098 * <td>The column containing the name of the user who last modified the group</td>
099 * </tr>
100 * </table>
101 * <p>
102 * This class is typically used in conjunction with a web container's JNDI
103 * resource factory. For example, Tomcat versions 4 and higher provide a basic
104 * JNDI factory for registering DataSources. To give JSPWiki access to the JNDI
105 * resource named by <code>jdbc/GroupDatabase</code>, you would declare the
106 * datasource resource similar to this:
107 * </p>
108 * <blockquote><code><Context ...><br/>
109 * ...<br/>
110 * <Resource name="jdbc/GroupDatabase" auth="Container"<br/>
111 * type="javax.sql.DataSource" username="dbusername" password="dbpassword"<br/>
112 * driverClassName="org.hsql.jdbcDriver" url="jdbc:HypersonicSQL:database"<br/>
113 * maxActive="8" maxIdle="4"/><br/>
114 * ...<br/>
115 * </Context></code></blockquote>
116 * <p>
117 * JDBC driver JARs should be added to Tomcat's <code>common/lib</code>
118 * directory. For more Tomcat 5.5 JNDI configuration examples, see <a
119 * href="http://tomcat.apache.org/tomcat-5.5-doc/jndi-resources-howto.html">
120 * http://tomcat.apache.org/tomcat-5.5-doc/jndi-resources-howto.html</a>.
121 * </p>
122 * <p>
123 * JDBCGroupDatabase commits changes as transactions if the back-end database
124 * supports them. If the database supports transactions, group changes are saved
125 * to permanent storage only when the {@link #commit()} method is called. If the
126 * database does <em>not</em> support transactions, then changes are made
127 * immediately (during the {@link #save(Group, Principal)} method), and the
128 * {@linkplain #commit()} method no-ops. Thus, callers should always call the
129 * {@linkplain #commit()} method after saving a profile to guarantee that
130 * changes are applied.
131 * </p>
132 *
133 * @since 2.3
134 */
135 public class JDBCGroupDatabase implements GroupDatabase {
136
137 /** Default column name that stores the JNDI name of the DataSource. */
138 public static final String DEFAULT_GROUPDB_DATASOURCE = "jdbc/GroupDatabase";
139
140 /** Default table name for the table that stores groups. */
141 public static final String DEFAULT_GROUPDB_TABLE = "groups";
142
143 /** Default column name that stores the names of group members. */
144 public static final String DEFAULT_GROUPDB_MEMBER_TABLE = "group_members";
145
146 /** Default column name that stores the the group creation timestamps. */
147 public static final String DEFAULT_GROUPDB_CREATED = "created";
148
149 /** Default column name that stores group creator names. */
150 public static final String DEFAULT_GROUPDB_CREATOR = "creator";
151
152 /** Default column name that stores the group names. */
153 public static final String DEFAULT_GROUPDB_NAME = "name";
154
155 /** Default column name that stores group member names. */
156 public static final String DEFAULT_GROUPDB_MEMBER = "member";
157
158 /** Default column name that stores group last-modified timestamps. */
159 public static final String DEFAULT_GROUPDB_MODIFIED = "modified";
160
161 /** Default column name that stores names of users who last modified groups. */
162 public static final String DEFAULT_GROUPDB_MODIFIER = "modifier";
163
164 /** The JNDI name of the DataSource. */
165 public static final String PROP_GROUPDB_DATASOURCE = "jspwiki.groupdatabase.datasource";
166
167 /** The table that stores the groups. */
168 public static final String PROP_GROUPDB_TABLE = "jspwiki.groupdatabase.table";
169
170 /** The table that stores the names of group members. */
171 public static final String PROP_GROUPDB_MEMBER_TABLE = "jspwiki.groupdatabase.membertable";
172
173 /** The column containing the group's creation timestamp. */
174 public static final String PROP_GROUPDB_CREATED = "jspwiki.groupdatabase.created";
175
176 /** The column containing the group creator's name. */
177 public static final String PROP_GROUPDB_CREATOR = "jspwiki.groupdatabase.creator";
178
179 /** The column containing the group's name. */
180 public static final String PROP_GROUPDB_NAME = "jspwiki.groupdatabase.name";
181
182 /** The column containing the group member's name. */
183 public static final String PROP_GROUPDB_MEMBER = "jspwiki.groupdatabase.member";
184
185 /** The column containing the group's last-modified timestamp. */
186 public static final String PROP_GROUPDB_MODIFIED = "jspwiki.groupdatabase.modified";
187
188 /** The column containing the name of the user who last modified the group. */
189 public static final String PROP_GROUPDB_MODIFIER = "jspwiki.groupdatabase.modifier";
190
191 protected static final Logger log = Logger.getLogger( JDBCGroupDatabase.class );
192
193 private DataSource m_ds = null;
194
195 private String m_created = null;
196
197 private String m_creator = null;
198
199 private String m_name = null;
200
201 private String m_member = null;
202
203 private String m_modified = null;
204
205 private String m_modifier = null;
206
207 private String m_findAll = null;
208
209 private String m_findGroup = null;
210
211 private String m_findMembers = null;
212
213 private String m_insertGroup = null;
214
215 private String m_insertGroupMembers = null;
216
217 private String m_updateGroup = null;
218
219 private String m_deleteGroup = null;
220
221 private String m_deleteGroupMembers = null;
222
223 private boolean m_supportsCommits = false;
224
225 private WikiEngine m_engine = null;
226
227 /**
228 * No-op method that in previous versions of JSPWiki was intended to
229 * atomically commit changes to the user database. Now, the
230 * {@link #save(Group, Principal)} and {@link #delete(Group)} methods are
231 * atomic themselves.
232 *
233 * @throws WikiSecurityException never...
234 * @deprecated there is no need to call this method because the save and
235 * delete methods contain their own commit logic
236 */
237 @Deprecated
238 public void commit() throws WikiSecurityException
239 {
240 }
241
242 /**
243 * Looks up and deletes a {@link Group} from the group database. If the
244 * group database does not contain the supplied Group. this method throws a
245 * {@link NoSuchPrincipalException}. The method commits the results of the
246 * delete to persistent storage.
247 *
248 * @param group the group to remove
249 * @throws WikiSecurityException if the database does not contain the
250 * supplied group (thrown as {@link NoSuchPrincipalException})
251 * or if the commit did not succeed
252 */
253 public void delete( Group group ) throws WikiSecurityException
254 {
255 if( !exists( group ) )
256 {
257 throw new NoSuchPrincipalException( "Not in database: " + group.getName() );
258 }
259
260 String groupName = group.getName();
261 Connection conn = null;
262 PreparedStatement ps = null;
263 try
264 {
265 // Open the database connection
266 conn = m_ds.getConnection();
267 if( m_supportsCommits )
268 {
269 conn.setAutoCommit( false );
270 }
271
272 ps = conn.prepareStatement( m_deleteGroup );
273 ps.setString( 1, groupName );
274 ps.execute();
275 ps.close();
276
277 ps = conn.prepareStatement( m_deleteGroupMembers );
278 ps.setString( 1, groupName );
279 ps.execute();
280
281 // Commit and close connection
282 if( m_supportsCommits )
283 {
284 conn.commit();
285 }
286 }
287 catch( SQLException e )
288 {
289 closeQuietly( conn, ps, null );
290 throw new WikiSecurityException( "Could not delete group " + groupName + ": " + e.getMessage(), e );
291 }
292 finally
293 {
294 closeQuietly( conn, ps, null );
295 }
296 }
297
298 /**
299 * Returns all wiki groups that are stored in the GroupDatabase as an array
300 * of Group objects. If the database does not contain any groups, this
301 * method will return a zero-length array. This method causes back-end
302 * storage to load the entire set of group; thus, it should be called
303 * infrequently (e.g., at initialization time).
304 *
305 * @return the wiki groups
306 * @throws WikiSecurityException if the groups cannot be returned by the
307 * back-end
308 */
309 public Group[] groups() throws WikiSecurityException
310 {
311 Set<Group> groups = new HashSet<Group>();
312 Connection conn = null;
313 PreparedStatement ps = null;
314 ResultSet rs = null;
315 try
316 {
317 // Open the database connection
318 conn = m_ds.getConnection();
319
320 ps = conn.prepareStatement( m_findAll );
321 rs = ps.executeQuery();
322 while ( rs.next() )
323 {
324 String groupName = rs.getString( m_name );
325 if( groupName == null )
326 {
327 log.warn( "Detected null group name in JDBCGroupDataBase. Check your group database." );
328 }
329 else
330 {
331 Group group = new Group( groupName, m_engine.getApplicationName() );
332 group.setCreated( rs.getTimestamp( m_created ) );
333 group.setCreator( rs.getString( m_creator ) );
334 group.setLastModified( rs.getTimestamp( m_modified ) );
335 group.setModifier( rs.getString( m_modifier ) );
336 populateGroup( group );
337 groups.add( group );
338 }
339 }
340 }
341 catch( SQLException e )
342 {
343 closeQuietly( conn, ps, rs );
344 throw new WikiSecurityException( e.getMessage(), e );
345 }
346 finally
347 {
348 closeQuietly( conn, ps, rs );
349 }
350
351 return groups.toArray( new Group[groups.size()] );
352 }
353
354 /**
355 * Saves a Group to the group database. Note that this method <em>must</em>
356 * fail, and throw an <code>IllegalArgumentException</code>, if the
357 * proposed group is the same name as one of the built-in Roles: e.g.,
358 * Admin, Authenticated, etc. The database is responsible for setting
359 * create/modify timestamps, upon a successful save, to the Group. The
360 * method commits the results of the delete to persistent storage.
361 *
362 * @param group the Group to save
363 * @param modifier the user who saved the Group
364 * @throws WikiSecurityException if the Group could not be saved
365 * successfully
366 */
367 public void save( Group group, Principal modifier ) throws WikiSecurityException
368 {
369 if( group == null || modifier == null )
370 {
371 throw new IllegalArgumentException( "Group or modifier cannot be null." );
372 }
373
374 boolean exists = exists( group );
375 Connection conn = null;
376 PreparedStatement ps = null;
377 try
378 {
379 // Open the database connection
380 conn = m_ds.getConnection();
381 if( m_supportsCommits )
382 {
383 conn.setAutoCommit( false );
384 }
385
386 Timestamp ts = new Timestamp( System.currentTimeMillis() );
387 Date modDate = new Date( ts.getTime() );
388 if( !exists )
389 {
390 // Group is new: insert new group record
391 ps = conn.prepareStatement( m_insertGroup );
392 ps.setString( 1, group.getName() );
393 ps.setTimestamp( 2, ts );
394 ps.setString( 3, modifier.getName() );
395 ps.setTimestamp( 4, ts );
396 ps.setString( 5, modifier.getName() );
397 ps.execute();
398
399 // Set the group creation time
400 group.setCreated( modDate );
401 group.setCreator( modifier.getName() );
402 ps.close();
403 }
404 else
405 {
406 // Modify existing group record
407 ps = conn.prepareStatement( m_updateGroup );
408 ps.setTimestamp( 1, ts );
409 ps.setString( 2, modifier.getName() );
410 ps.setString( 3, group.getName() );
411 ps.execute();
412 ps.close();
413 }
414 // Set the group modified time
415 group.setLastModified( modDate );
416 group.setModifier( modifier.getName() );
417
418 // Now, update the group member list
419
420 // First, delete all existing member records
421 ps = conn.prepareStatement( m_deleteGroupMembers );
422 ps.setString( 1, group.getName() );
423 ps.execute();
424 ps.close();
425
426 // Insert group member records
427 ps = conn.prepareStatement( m_insertGroupMembers );
428 Principal[] members = group.members();
429 for( int i = 0; i < members.length; i++ )
430 {
431 Principal member = members[i];
432 ps.setString( 1, group.getName() );
433 ps.setString( 2, member.getName() );
434 ps.execute();
435 }
436
437 // Commit and close connection
438 if( m_supportsCommits )
439 {
440 conn.commit();
441 }
442 }
443 catch( SQLException e )
444 {
445 closeQuietly(conn, ps, null );
446 throw new WikiSecurityException( e.getMessage(), e );
447 }
448 finally
449 {
450 closeQuietly(conn, ps, null );
451 }
452 }
453
454 /**
455 * Initializes the group database based on values from a Properties object.
456 *
457 * @param engine the wiki engine
458 * @param props the properties used to initialize the group database
459 * @throws WikiSecurityException if the database could not be initialized
460 * successfully
461 * @throws NoRequiredPropertyException if a required property is not present
462 */
463 public void initialize( WikiEngine engine, Properties props ) throws NoRequiredPropertyException, WikiSecurityException
464 {
465 String table;
466 String memberTable;
467
468 m_engine = engine;
469
470 String jndiName = props.getProperty( PROP_GROUPDB_DATASOURCE, DEFAULT_GROUPDB_DATASOURCE );
471 try
472 {
473 Context initCtx = new InitialContext();
474 Context ctx = (Context) initCtx.lookup( "java:comp/env" );
475 m_ds = (DataSource) ctx.lookup( jndiName );
476
477 // Prepare the SQL selectors
478 table = props.getProperty( PROP_GROUPDB_TABLE, DEFAULT_GROUPDB_TABLE );
479 memberTable = props.getProperty( PROP_GROUPDB_MEMBER_TABLE, DEFAULT_GROUPDB_MEMBER_TABLE );
480 m_name = props.getProperty( PROP_GROUPDB_NAME, DEFAULT_GROUPDB_NAME );
481 m_created = props.getProperty( PROP_GROUPDB_CREATED, DEFAULT_GROUPDB_CREATED );
482 m_creator = props.getProperty( PROP_GROUPDB_CREATOR, DEFAULT_GROUPDB_CREATOR );
483 m_modifier = props.getProperty( PROP_GROUPDB_MODIFIER, DEFAULT_GROUPDB_MODIFIER );
484 m_modified = props.getProperty( PROP_GROUPDB_MODIFIED, DEFAULT_GROUPDB_MODIFIED );
485 m_member = props.getProperty( PROP_GROUPDB_MEMBER, DEFAULT_GROUPDB_MEMBER );
486
487 m_findAll = "SELECT DISTINCT * FROM " + table;
488 m_findGroup = "SELECT DISTINCT * FROM " + table + " WHERE " + m_name + "=?";
489 m_findMembers = "SELECT * FROM " + memberTable + " WHERE " + m_name + "=?";
490
491 // Prepare the group insert/update SQL
492 m_insertGroup = "INSERT INTO " + table + " (" + m_name + "," + m_modified + "," + m_modifier + "," + m_created + ","
493 + m_creator + ") VALUES (?,?,?,?,?)";
494 m_updateGroup = "UPDATE " + table + " SET " + m_modified + "=?," + m_modifier + "=? WHERE " + m_name + "=?";
495
496 // Prepare the group member insert SQL
497 m_insertGroupMembers = "INSERT INTO " + memberTable + " (" + m_name + "," + m_member + ") VALUES (?,?)";
498
499 // Prepare the group delete SQL
500 m_deleteGroup = "DELETE FROM " + table + " WHERE " + m_name + "=?";
501 m_deleteGroupMembers = "DELETE FROM " + memberTable + " WHERE " + m_name + "=?";
502 }
503 catch( NamingException e )
504 {
505 log.error( "JDBCGroupDatabase initialization error: " + e );
506 throw new NoRequiredPropertyException( PROP_GROUPDB_DATASOURCE, "JDBCGroupDatabase initialization error: " + e);
507 }
508
509 // Test connection by doing a quickie select
510 Connection conn = null;
511 PreparedStatement ps = null;
512 try
513 {
514 conn = m_ds.getConnection();
515 ps = conn.prepareStatement( m_findAll );
516 ps.executeQuery();
517 ps.close();
518 }
519 catch( SQLException e )
520 {
521 closeQuietly( conn, ps, null );
522 log.error( "DB connectivity error: " + e.getMessage() );
523 throw new WikiSecurityException("DB connectivity error: " + e.getMessage(), e );
524 }
525 finally
526 {
527 closeQuietly( conn, ps, null );
528 }
529 log.info( "JDBCGroupDatabase initialized from JNDI DataSource: " + jndiName );
530
531 // Determine if the datasource supports commits
532 try
533 {
534 conn = m_ds.getConnection();
535 DatabaseMetaData dmd = conn.getMetaData();
536 if( dmd.supportsTransactions() )
537 {
538 m_supportsCommits = true;
539 conn.setAutoCommit( false );
540 log.info( "JDBCGroupDatabase supports transactions. Good; we will use them." );
541 }
542 }
543 catch( SQLException e )
544 {
545 closeQuietly( conn, null, null );
546 log.warn( "JDBCGroupDatabase warning: user database doesn't seem to support transactions. Reason: " + e);
547 }
548 finally
549 {
550 closeQuietly( conn, null, null );
551 }
552 }
553
554 /**
555 * Returns <code>true</code> if the Group exists in back-end storage.
556 *
557 * @param group the Group to look for
558 * @return the result of the search
559 */
560 private boolean exists( Group group )
561 {
562 String index = group.getName();
563 try
564 {
565 findGroup( index );
566 return true;
567 }
568 catch( NoSuchPrincipalException e )
569 {
570 return false;
571 }
572 }
573
574 /**
575 * Loads and returns a Group from the back-end database matching a supplied
576 * name.
577 *
578 * @param index the name of the Group to find
579 * @return the populated Group
580 * @throws NoSuchPrincipalException if the Group cannot be found
581 * @throws SQLException if the database query returns an error
582 */
583 private Group findGroup( String index ) throws NoSuchPrincipalException
584 {
585 Group group = null;
586 boolean found = false;
587 boolean unique = true;
588 ResultSet rs = null;
589 PreparedStatement ps = null;
590 Connection conn = null;
591 try
592 {
593 // Open the database connection
594 conn = m_ds.getConnection();
595
596 ps = conn.prepareStatement( m_findGroup );
597 ps.setString( 1, index );
598 rs = ps.executeQuery();
599 while ( rs.next() )
600 {
601 if( group != null )
602 {
603 unique = false;
604 break;
605 }
606 group = new Group( index, m_engine.getApplicationName() );
607 group.setCreated( rs.getTimestamp( m_created ) );
608 group.setCreator( rs.getString( m_creator ) );
609 group.setLastModified( rs.getTimestamp( m_modified ) );
610 group.setModifier( rs.getString( m_modifier ) );
611 populateGroup( group );
612 found = true;
613 }
614 }
615 catch( SQLException e )
616 {
617 closeQuietly( conn, ps, rs );
618 throw new NoSuchPrincipalException( e.getMessage() );
619 }
620 finally
621 {
622 closeQuietly( conn, ps, rs );
623 }
624
625 if( !found )
626 {
627 throw new NoSuchPrincipalException( "Could not find group in database!" );
628 }
629 if( !unique )
630 {
631 throw new NoSuchPrincipalException( "More than one group in database!" );
632 }
633 return group;
634 }
635
636 /**
637 * Fills a Group with members.
638 *
639 * @param group the group to populate
640 * @return the populated Group
641 */
642 private Group populateGroup( Group group )
643 {
644 ResultSet rs = null;
645 PreparedStatement ps = null;
646 Connection conn = null;
647 try
648 {
649 // Open the database connection
650 conn = m_ds.getConnection();
651
652 ps = conn.prepareStatement( m_findMembers );
653 ps.setString( 1, group.getName() );
654 rs = ps.executeQuery();
655 while ( rs.next() )
656 {
657 String memberName = rs.getString( m_member );
658 if( memberName != null )
659 {
660 WikiPrincipal principal = new WikiPrincipal( memberName, WikiPrincipal.UNSPECIFIED );
661 group.add( principal );
662 }
663 }
664 }
665 catch( SQLException e )
666 {
667 // I guess that means there aren't any principals...
668 }
669 finally
670 {
671 closeQuietly( conn, ps, rs );
672 }
673 return group;
674 }
675
676 void closeQuietly( Connection conn, PreparedStatement ps, ResultSet rs ) {
677 if( conn != null ) {
678 try {
679 conn.close();
680 } catch( Exception e ) {
681 }
682 }
683 if( ps != null ) {
684 try {
685 ps.close();
686 } catch( Exception e ) {
687 }
688 }
689 if( rs != null ) {
690 try {
691 rs.close();
692 } catch( Exception e ) {
693 }
694 }
695 }
696
697 }