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