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.shiro.realm.jdbc;
020
021import org.apache.shiro.authc.AccountException;
022import org.apache.shiro.authc.AuthenticationException;
023import org.apache.shiro.authc.AuthenticationInfo;
024import org.apache.shiro.authc.AuthenticationToken;
025import org.apache.shiro.authc.SimpleAuthenticationInfo;
026import org.apache.shiro.authc.UnknownAccountException;
027import org.apache.shiro.authc.UsernamePasswordToken;
028import org.apache.shiro.authz.AuthorizationException;
029import org.apache.shiro.authz.AuthorizationInfo;
030import org.apache.shiro.authz.SimpleAuthorizationInfo;
031import org.apache.shiro.config.ConfigurationException;
032import org.apache.shiro.realm.AuthorizingRealm;
033import org.apache.shiro.subject.PrincipalCollection;
034import org.apache.shiro.util.ByteSource;
035import org.apache.shiro.util.JdbcUtils;
036import org.slf4j.Logger;
037import org.slf4j.LoggerFactory;
038
039import javax.sql.DataSource;
040import java.sql.Connection;
041import java.sql.PreparedStatement;
042import java.sql.ResultSet;
043import java.sql.SQLException;
044import java.util.Collection;
045import java.util.LinkedHashSet;
046import java.util.Set;
047
048
049/**
050 * Realm that allows authentication and authorization via JDBC calls.  The default queries suggest a potential schema
051 * for retrieving the user's password for authentication, and querying for a user's roles and permissions.  The
052 * default queries can be overridden by setting the query properties of the realm.
053 * <p/>
054 * If the default implementation
055 * of authentication and authorization cannot handle your schema, this class can be subclassed and the
056 * appropriate methods overridden. (usually {@link #doGetAuthenticationInfo(org.apache.shiro.authc.AuthenticationToken)},
057 * {@link #getRoleNamesForUser(java.sql.Connection,String)}, and/or {@link #getPermissions(java.sql.Connection,String,java.util.Collection)}
058 * <p/>
059 * This realm supports caching by extending from {@link org.apache.shiro.realm.AuthorizingRealm}.
060 *
061 * @since 0.2
062 */
063public class JdbcRealm extends AuthorizingRealm {
064
065    //TODO - complete JavaDoc
066
067    /*--------------------------------------------
068    |             C O N S T A N T S             |
069    ============================================*/
070    /**
071     * The default query used to retrieve account data for the user.
072     */
073    protected static final String DEFAULT_AUTHENTICATION_QUERY = "select password from users where username = ?";
074    
075    /**
076     * The default query used to retrieve account data for the user when {@link #saltStyle} is COLUMN.
077     */
078    protected static final String DEFAULT_SALTED_AUTHENTICATION_QUERY = "select password, password_salt from users where username = ?";
079
080    /**
081     * The default query used to retrieve the roles that apply to a user.
082     */
083    protected static final String DEFAULT_USER_ROLES_QUERY = "select role_name from user_roles where username = ?";
084
085    /**
086     * The default query used to retrieve permissions that apply to a particular role.
087     */
088    protected static final String DEFAULT_PERMISSIONS_QUERY = "select permission from roles_permissions where role_name = ?";
089
090    private static final Logger log = LoggerFactory.getLogger(JdbcRealm.class);
091    
092    /**
093     * Password hash salt configuration. <ul>
094     *   <li>NO_SALT - password hashes are not salted.</li>
095     *   <li>CRYPT - password hashes are stored in unix crypt format.</li>
096     *   <li>COLUMN - salt is in a separate column in the database.</li> 
097     *   <li>EXTERNAL - salt is not stored in the database. {@link #getSaltForUser(String)} will be called
098     *       to get the salt</li></ul>
099     */
100    public enum SaltStyle {NO_SALT, CRYPT, COLUMN, EXTERNAL};
101
102    /*--------------------------------------------
103    |    I N S T A N C E   V A R I A B L E S    |
104    ============================================*/
105    protected DataSource dataSource;
106
107    protected String authenticationQuery = DEFAULT_AUTHENTICATION_QUERY;
108
109    protected String userRolesQuery = DEFAULT_USER_ROLES_QUERY;
110
111    protected String permissionsQuery = DEFAULT_PERMISSIONS_QUERY;
112
113    protected boolean permissionsLookupEnabled = false;
114    
115    protected SaltStyle saltStyle = SaltStyle.NO_SALT;
116
117    /*--------------------------------------------
118    |         C O N S T R U C T O R S           |
119    ============================================*/
120
121    /*--------------------------------------------
122    |  A C C E S S O R S / M O D I F I E R S    |
123    ============================================*/
124    
125    /**
126     * Sets the datasource that should be used to retrieve connections used by this realm.
127     *
128     * @param dataSource the SQL data source.
129     */
130    public void setDataSource(DataSource dataSource) {
131        this.dataSource = dataSource;
132    }
133
134    /**
135     * Overrides the default query used to retrieve a user's password during authentication.  When using the default
136     * implementation, this query must take the user's username as a single parameter and return a single result
137     * with the user's password as the first column.  If you require a solution that does not match this query
138     * structure, you can override {@link #doGetAuthenticationInfo(org.apache.shiro.authc.AuthenticationToken)} or
139     * just {@link #getPasswordForUser(java.sql.Connection,String)}
140     *
141     * @param authenticationQuery the query to use for authentication.
142     * @see #DEFAULT_AUTHENTICATION_QUERY
143     */
144    public void setAuthenticationQuery(String authenticationQuery) {
145        this.authenticationQuery = authenticationQuery;
146    }
147
148    /**
149     * Overrides the default query used to retrieve a user's roles during authorization.  When using the default
150     * implementation, this query must take the user's username as a single parameter and return a row
151     * per role with a single column containing the role name.  If you require a solution that does not match this query
152     * structure, you can override {@link #doGetAuthorizationInfo(PrincipalCollection)} or just
153     * {@link #getRoleNamesForUser(java.sql.Connection,String)}
154     *
155     * @param userRolesQuery the query to use for retrieving a user's roles.
156     * @see #DEFAULT_USER_ROLES_QUERY
157     */
158    public void setUserRolesQuery(String userRolesQuery) {
159        this.userRolesQuery = userRolesQuery;
160    }
161
162    /**
163     * Overrides the default query used to retrieve a user's permissions during authorization.  When using the default
164     * implementation, this query must take a role name as the single parameter and return a row
165     * per permission with three columns containing the fully qualified name of the permission class, the permission
166     * name, and the permission actions (in that order).  If you require a solution that does not match this query
167     * structure, you can override {@link #doGetAuthorizationInfo(org.apache.shiro.subject.PrincipalCollection)} or just
168     * {@link #getPermissions(java.sql.Connection,String,java.util.Collection)}</p>
169     * <p/>
170     * <b>Permissions are only retrieved if you set {@link #permissionsLookupEnabled} to true.  Otherwise,
171     * this query is ignored.</b>
172     *
173     * @param permissionsQuery the query to use for retrieving permissions for a role.
174     * @see #DEFAULT_PERMISSIONS_QUERY
175     * @see #setPermissionsLookupEnabled(boolean)
176     */
177    public void setPermissionsQuery(String permissionsQuery) {
178        this.permissionsQuery = permissionsQuery;
179    }
180
181    /**
182     * Enables lookup of permissions during authorization.  The default is "false" - meaning that only roles
183     * are associated with a user.  Set this to true in order to lookup roles <b>and</b> permissions.
184     *
185     * @param permissionsLookupEnabled true if permissions should be looked up during authorization, or false if only
186     *                                 roles should be looked up.
187     */
188    public void setPermissionsLookupEnabled(boolean permissionsLookupEnabled) {
189        this.permissionsLookupEnabled = permissionsLookupEnabled;
190    }
191    
192    /**
193     * Sets the salt style.  See {@link #saltStyle}.
194     * 
195     * @param saltStyle new SaltStyle to set.
196     */
197    public void setSaltStyle(SaltStyle saltStyle) {
198        this.saltStyle = saltStyle;
199        if (saltStyle == SaltStyle.COLUMN && authenticationQuery.equals(DEFAULT_AUTHENTICATION_QUERY)) {
200            authenticationQuery = DEFAULT_SALTED_AUTHENTICATION_QUERY;
201        }
202    }
203
204    /*--------------------------------------------
205    |               M E T H O D S               |
206    ============================================*/
207
208    protected AuthenticationInfo doGetAuthenticationInfo(AuthenticationToken token) throws AuthenticationException {
209
210        UsernamePasswordToken upToken = (UsernamePasswordToken) token;
211        String username = upToken.getUsername();
212
213        // Null username is invalid
214        if (username == null) {
215            throw new AccountException("Null usernames are not allowed by this realm.");
216        }
217
218        Connection conn = null;
219        SimpleAuthenticationInfo info = null;
220        try {
221            conn = dataSource.getConnection();
222
223            String password = null;
224            String salt = null;
225            switch (saltStyle) {
226            case NO_SALT:
227                password = getPasswordForUser(conn, username)[0];
228                break;
229            case CRYPT:
230                // TODO: separate password and hash from getPasswordForUser[0]
231                throw new ConfigurationException("Not implemented yet");
232                //break;
233            case COLUMN:
234                String[] queryResults = getPasswordForUser(conn, username);
235                password = queryResults[0];
236                salt = queryResults[1];
237                break;
238            case EXTERNAL:
239                password = getPasswordForUser(conn, username)[0];
240                salt = getSaltForUser(username);
241            }
242
243            if (password == null) {
244                throw new UnknownAccountException("No account found for user [" + username + "]");
245            }
246
247            info = new SimpleAuthenticationInfo(username, password.toCharArray(), getName());
248            
249            if (salt != null) {
250                info.setCredentialsSalt(ByteSource.Util.bytes(salt));
251            }
252
253        } catch (SQLException e) {
254            final String message = "There was a SQL error while authenticating user [" + username + "]";
255            if (log.isErrorEnabled()) {
256                log.error(message, e);
257            }
258
259            // Rethrow any SQL errors as an authentication exception
260            throw new AuthenticationException(message, e);
261        } finally {
262            JdbcUtils.closeConnection(conn);
263        }
264
265        return info;
266    }
267
268    private String[] getPasswordForUser(Connection conn, String username) throws SQLException {
269
270        String[] result;
271        boolean returningSeparatedSalt = false;
272        switch (saltStyle) {
273        case NO_SALT:
274        case CRYPT:
275        case EXTERNAL:
276            result = new String[1];
277            break;
278        default:
279            result = new String[2];
280            returningSeparatedSalt = true;
281        }
282        
283        PreparedStatement ps = null;
284        ResultSet rs = null;
285        try {
286            ps = conn.prepareStatement(authenticationQuery);
287            ps.setString(1, username);
288
289            // Execute query
290            rs = ps.executeQuery();
291
292            // Loop over results - although we are only expecting one result, since usernames should be unique
293            boolean foundResult = false;
294            while (rs.next()) {
295
296                // Check to ensure only one row is processed
297                if (foundResult) {
298                    throw new AuthenticationException("More than one user row found for user [" + username + "]. Usernames must be unique.");
299                }
300
301                result[0] = rs.getString(1);
302                if (returningSeparatedSalt) {
303                    result[1] = rs.getString(2);
304                }
305
306                foundResult = true;
307            }
308        } finally {
309            JdbcUtils.closeResultSet(rs);
310            JdbcUtils.closeStatement(ps);
311        }
312
313        return result;
314    }
315
316    /**
317     * This implementation of the interface expects the principals collection to return a String username keyed off of
318     * this realm's {@link #getName() name}
319     *
320     * @see #getAuthorizationInfo(org.apache.shiro.subject.PrincipalCollection)
321     */
322    @Override
323    protected AuthorizationInfo doGetAuthorizationInfo(PrincipalCollection principals) {
324
325        //null usernames are invalid
326        if (principals == null) {
327            throw new AuthorizationException("PrincipalCollection method argument cannot be null.");
328        }
329
330        String username = (String) getAvailablePrincipal(principals);
331
332        Connection conn = null;
333        Set<String> roleNames = null;
334        Set<String> permissions = null;
335        try {
336            conn = dataSource.getConnection();
337
338            // Retrieve roles and permissions from database
339            roleNames = getRoleNamesForUser(conn, username);
340            if (permissionsLookupEnabled) {
341                permissions = getPermissions(conn, username, roleNames);
342            }
343
344        } catch (SQLException e) {
345            final String message = "There was a SQL error while authorizing user [" + username + "]";
346            if (log.isErrorEnabled()) {
347                log.error(message, e);
348            }
349
350            // Rethrow any SQL errors as an authorization exception
351            throw new AuthorizationException(message, e);
352        } finally {
353            JdbcUtils.closeConnection(conn);
354        }
355
356        SimpleAuthorizationInfo info = new SimpleAuthorizationInfo(roleNames);
357        info.setStringPermissions(permissions);
358        return info;
359
360    }
361
362    protected Set<String> getRoleNamesForUser(Connection conn, String username) throws SQLException {
363        PreparedStatement ps = null;
364        ResultSet rs = null;
365        Set<String> roleNames = new LinkedHashSet<String>();
366        try {
367            ps = conn.prepareStatement(userRolesQuery);
368            ps.setString(1, username);
369
370            // Execute query
371            rs = ps.executeQuery();
372
373            // Loop over results and add each returned role to a set
374            while (rs.next()) {
375
376                String roleName = rs.getString(1);
377
378                // Add the role to the list of names if it isn't null
379                if (roleName != null) {
380                    roleNames.add(roleName);
381                } else {
382                    if (log.isWarnEnabled()) {
383                        log.warn("Null role name found while retrieving role names for user [" + username + "]");
384                    }
385                }
386            }
387        } finally {
388            JdbcUtils.closeResultSet(rs);
389            JdbcUtils.closeStatement(ps);
390        }
391        return roleNames;
392    }
393
394    protected Set<String> getPermissions(Connection conn, String username, Collection<String> roleNames) throws SQLException {
395        PreparedStatement ps = null;
396        Set<String> permissions = new LinkedHashSet<String>();
397        try {
398            ps = conn.prepareStatement(permissionsQuery);
399            for (String roleName : roleNames) {
400
401                ps.setString(1, roleName);
402
403                ResultSet rs = null;
404
405                try {
406                    // Execute query
407                    rs = ps.executeQuery();
408
409                    // Loop over results and add each returned role to a set
410                    while (rs.next()) {
411
412                        String permissionString = rs.getString(1);
413
414                        // Add the permission to the set of permissions
415                        permissions.add(permissionString);
416                    }
417                } finally {
418                    JdbcUtils.closeResultSet(rs);
419                }
420
421            }
422        } finally {
423            JdbcUtils.closeStatement(ps);
424        }
425
426        return permissions;
427    }
428    
429    protected String getSaltForUser(String username) {
430        return username;
431    }
432
433}