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>&lt;Context ...&gt;<br/>
116 *  &nbsp;&nbsp;...<br/>
117 *  &nbsp;&nbsp;&lt;Resource name="jdbc/GroupDatabase" auth="Container"<br/>
118 *  &nbsp;&nbsp;&nbsp;&nbsp;type="javax.sql.DataSource" username="dbusername" password="dbpassword"<br/>
119 *  &nbsp;&nbsp;&nbsp;&nbsp;driverClassName="org.hsql.jdbcDriver" url="jdbc:HypersonicSQL:database"<br/>
120 *  &nbsp;&nbsp;&nbsp;&nbsp;maxActive="8" maxIdle="4"/&gt;<br/>
121 *  &nbsp;...<br/>
122 * &lt;/Context&gt;</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}