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     */
019    package org.apache.shiro.realm.jdbc;
020    
021    import org.apache.shiro.authc.*;
022    import org.apache.shiro.authz.AuthorizationException;
023    import org.apache.shiro.authz.AuthorizationInfo;
024    import org.apache.shiro.authz.SimpleAuthorizationInfo;
025    import org.apache.shiro.config.ConfigurationException;
026    import org.apache.shiro.realm.AuthorizingRealm;
027    import org.apache.shiro.subject.PrincipalCollection;
028    import org.apache.shiro.util.ByteSource;
029    import org.apache.shiro.util.JdbcUtils;
030    import org.slf4j.Logger;
031    import org.slf4j.LoggerFactory;
032    
033    import javax.sql.DataSource;
034    import java.sql.Connection;
035    import java.sql.PreparedStatement;
036    import java.sql.ResultSet;
037    import java.sql.SQLException;
038    import java.util.Collection;
039    import java.util.LinkedHashSet;
040    import 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     */
057    public 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    }