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 (final Principal member : members) { 416 ps.setString(1, group.getName()); 417 ps.setString(2, member.getName()); 418 ps.execute(); 419 } 420 421 // Commit and close connection 422 if( m_supportsCommits ) 423 { 424 conn.commit(); 425 } 426 } 427 catch( final SQLException e ) 428 { 429 closeQuietly(conn, ps, null ); 430 throw new WikiSecurityException( e.getMessage(), e ); 431 } 432 finally 433 { 434 closeQuietly(conn, ps, null ); 435 } 436 } 437 438 /** 439 * Initializes the group database based on values from a Properties object. 440 * 441 * @param engine the wiki engine 442 * @param props the properties used to initialize the group database 443 * @throws WikiSecurityException if the database could not be initialized 444 * successfully 445 * @throws NoRequiredPropertyException if a required property is not present 446 */ 447 @Override public void initialize( final Engine engine, final Properties props ) throws NoRequiredPropertyException, WikiSecurityException 448 { 449 final String table; 450 final String memberTable; 451 452 m_engine = engine; 453 454 final String jndiName = props.getProperty( PROP_GROUPDB_DATASOURCE, DEFAULT_GROUPDB_DATASOURCE ); 455 try 456 { 457 final Context initCtx = new InitialContext(); 458 final Context ctx = (Context) initCtx.lookup( "java:comp/env" ); 459 m_ds = (DataSource) ctx.lookup( jndiName ); 460 461 // Prepare the SQL selectors 462 table = props.getProperty( PROP_GROUPDB_TABLE, DEFAULT_GROUPDB_TABLE ); 463 memberTable = props.getProperty( PROP_GROUPDB_MEMBER_TABLE, DEFAULT_GROUPDB_MEMBER_TABLE ); 464 m_name = props.getProperty( PROP_GROUPDB_NAME, DEFAULT_GROUPDB_NAME ); 465 m_created = props.getProperty( PROP_GROUPDB_CREATED, DEFAULT_GROUPDB_CREATED ); 466 m_creator = props.getProperty( PROP_GROUPDB_CREATOR, DEFAULT_GROUPDB_CREATOR ); 467 m_modifier = props.getProperty( PROP_GROUPDB_MODIFIER, DEFAULT_GROUPDB_MODIFIER ); 468 m_modified = props.getProperty( PROP_GROUPDB_MODIFIED, DEFAULT_GROUPDB_MODIFIED ); 469 m_member = props.getProperty( PROP_GROUPDB_MEMBER, DEFAULT_GROUPDB_MEMBER ); 470 471 m_findAll = "SELECT DISTINCT * FROM " + table; 472 m_findGroup = "SELECT DISTINCT * FROM " + table + " WHERE " + m_name + "=?"; 473 m_findMembers = "SELECT * FROM " + memberTable + " WHERE " + m_name + "=?"; 474 475 // Prepare the group insert/update SQL 476 m_insertGroup = "INSERT INTO " + table + " (" + m_name + "," + m_modified + "," + m_modifier + "," + m_created + "," 477 + m_creator + ") VALUES (?,?,?,?,?)"; 478 m_updateGroup = "UPDATE " + table + " SET " + m_modified + "=?," + m_modifier + "=? WHERE " + m_name + "=?"; 479 480 // Prepare the group member insert SQL 481 m_insertGroupMembers = "INSERT INTO " + memberTable + " (" + m_name + "," + m_member + ") VALUES (?,?)"; 482 483 // Prepare the group delete SQL 484 m_deleteGroup = "DELETE FROM " + table + " WHERE " + m_name + "=?"; 485 m_deleteGroupMembers = "DELETE FROM " + memberTable + " WHERE " + m_name + "=?"; 486 } 487 catch( final NamingException e ) 488 { 489 LOG.error( "JDBCGroupDatabase initialization error: " + e ); 490 throw new NoRequiredPropertyException( PROP_GROUPDB_DATASOURCE, "JDBCGroupDatabase initialization error: " + e); 491 } 492 493 // Test connection by doing a quickie select 494 Connection conn = null; 495 PreparedStatement ps = null; 496 try 497 { 498 conn = m_ds.getConnection(); 499 ps = conn.prepareStatement( m_findAll ); 500 ps.executeQuery(); 501 ps.close(); 502 } 503 catch( final SQLException e ) 504 { 505 closeQuietly( conn, ps, null ); 506 LOG.error( "DB connectivity error: " + e.getMessage() ); 507 throw new WikiSecurityException("DB connectivity error: " + e.getMessage(), e ); 508 } 509 finally 510 { 511 closeQuietly( conn, ps, null ); 512 } 513 LOG.info( "JDBCGroupDatabase initialized from JNDI DataSource: " + jndiName ); 514 515 // Determine if the datasource supports commits 516 try 517 { 518 conn = m_ds.getConnection(); 519 final DatabaseMetaData dmd = conn.getMetaData(); 520 if( dmd.supportsTransactions() ) 521 { 522 m_supportsCommits = true; 523 conn.setAutoCommit( false ); 524 LOG.info( "JDBCGroupDatabase supports transactions. Good; we will use them." ); 525 } 526 } 527 catch( final SQLException e ) 528 { 529 closeQuietly( conn, null, null ); 530 LOG.warn( "JDBCGroupDatabase warning: user database doesn't seem to support transactions. Reason: " + e); 531 } 532 finally 533 { 534 closeQuietly( conn, null, null ); 535 } 536 } 537 538 /** 539 * Returns <code>true</code> if the Group exists in back-end storage. 540 * 541 * @param group the Group to look for 542 * @return the result of the search 543 */ 544 private boolean exists( final Group group ) 545 { 546 final String index = group.getName(); 547 try 548 { 549 findGroup( index ); 550 return true; 551 } 552 catch( final NoSuchPrincipalException e ) 553 { 554 return false; 555 } 556 } 557 558 /** 559 * Loads and returns a Group from the back-end database matching a supplied 560 * name. 561 * 562 * @param index the name of the Group to find 563 * @return the populated Group 564 * @throws NoSuchPrincipalException if the Group cannot be found 565 * @throws SQLException if the database query returns an error 566 */ 567 private Group findGroup( final String index ) throws NoSuchPrincipalException 568 { 569 Group group = null; 570 boolean found = false; 571 boolean unique = true; 572 ResultSet rs = null; 573 PreparedStatement ps = null; 574 Connection conn = null; 575 try 576 { 577 // Open the database connection 578 conn = m_ds.getConnection(); 579 580 ps = conn.prepareStatement( m_findGroup ); 581 ps.setString( 1, index ); 582 rs = ps.executeQuery(); 583 while ( rs.next() ) 584 { 585 if( group != null ) 586 { 587 unique = false; 588 break; 589 } 590 group = new Group( index, m_engine.getApplicationName() ); 591 group.setCreated( rs.getTimestamp( m_created ) ); 592 group.setCreator( rs.getString( m_creator ) ); 593 group.setLastModified( rs.getTimestamp( m_modified ) ); 594 group.setModifier( rs.getString( m_modifier ) ); 595 populateGroup( group ); 596 found = true; 597 } 598 } 599 catch( final SQLException e ) 600 { 601 closeQuietly( conn, ps, rs ); 602 throw new NoSuchPrincipalException( e.getMessage() ); 603 } 604 finally 605 { 606 closeQuietly( conn, ps, rs ); 607 } 608 609 if( !found ) 610 { 611 throw new NoSuchPrincipalException( "Could not find group in database!" ); 612 } 613 if( !unique ) 614 { 615 throw new NoSuchPrincipalException( "More than one group in database!" ); 616 } 617 return group; 618 } 619 620 /** 621 * Fills a Group with members. 622 * 623 * @param group the group to populate 624 * @return the populated Group 625 */ 626 private Group populateGroup( final Group group ) 627 { 628 ResultSet rs = null; 629 PreparedStatement ps = null; 630 Connection conn = null; 631 try 632 { 633 // Open the database connection 634 conn = m_ds.getConnection(); 635 636 ps = conn.prepareStatement( m_findMembers ); 637 ps.setString( 1, group.getName() ); 638 rs = ps.executeQuery(); 639 while ( rs.next() ) 640 { 641 final String memberName = rs.getString( m_member ); 642 if( memberName != null ) 643 { 644 final WikiPrincipal principal = new WikiPrincipal( memberName, WikiPrincipal.UNSPECIFIED ); 645 group.add( principal ); 646 } 647 } 648 } 649 catch( final SQLException e ) 650 { 651 // I guess that means there aren't any principals... 652 } 653 finally 654 { 655 closeQuietly( conn, ps, rs ); 656 } 657 return group; 658 } 659 660 void closeQuietly( final Connection conn, final PreparedStatement ps, final ResultSet rs ) { 661 if( conn != null ) { 662 try { 663 conn.close(); 664 } catch( final Exception e ) { 665 } 666 } 667 if( ps != null ) { 668 try { 669 ps.close(); 670 } catch( final Exception e ) { 671 } 672 } 673 if( rs != null ) { 674 try { 675 rs.close(); 676 } catch( final Exception e ) { 677 } 678 } 679 } 680 681}