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