AcegiJdbcDaoImpl.java
1 package org.security.acegi;
2
3 import java.sql.ResultSet;
4
5 import java.sql.SQLException;
6
7 import java.sql.Types;
8
9 import java.util.List;
10
11 import javax.sql.DataSource;
12
13 import org.acegisecurity.GrantedAuthority;
14
15 import org.acegisecurity.GrantedAuthorityImpl;
16
17 import org.acegisecurity.userdetails.UsernameNotFoundException;
18
19 import org.acegisecurity.userdetails.jdbc.JdbcDaoImpl;
20
21 import org.security.IUserDetails;
22
23 import org.security.UserDetailsImpl;
24
25 import org.springframework.dao.DataAccessException;
26
27 import org.springframework.jdbc.core.SqlParameter;
28
29 import org.springframework.jdbc.object.MappingSqlQuery;
30
31 /**
32 * The class AcegiJdbcDaoImpl provides the method to
* get IUserDetail information from db which contains userId,
* companyId and UserDetail information.
33 *
34 * @author wade
35 *
36 */
37 public class AcegiJdbcDaoImpl extends JdbcDaoImpl {
38
39 public static final String DEF_USERS_BY_USERNAME_QUERY =
40
41 "SELECT u.user_id, u.company_id, email, password, enabled "+"from role r, user_role ur, user u where r.role_id = ur.role_id "+"and ur.user_id = u.user_id and email = ? limit 1";
42
43 public static final String DEF_AUTHORITIES_BY_USERNAME_QUERY =
44
45 "SELECT username,authority FROM authorities WHERE username = ?";
46
47 protected MappingSqlQuery rolesByUsernameMapping;
48
49 protected MappingSqlQuery usersByNameMapping;
50
51 private String authoritiesByUsernameQuery;
52
53 private String rolePrefix = "";
54
55 private String usersByUsernameQuery;
56
57 private boolean usernameBasedPrimaryKey = true;
58
59 public AcegiJdbcDaoImpl(){
60
61 usersByUsernameQuery = DEF_USERS_BY_USERNAME_QUERY;
62
63 authoritiesByUsernameQuery = DEF_AUTHORITIES_BY_USERNAME_QUERY;
64
65 }
66
67 public String getAuthoritiesByUsernameQuery() {
68
69 return authoritiesByUsernameQuery;
70
71 }
72
73 public String getRolePrefix() {
74
75 return rolePrefix;
76
77 }
78
79 public String getUsersByUsernameQuery() {
80
81 return usersByUsernameQuery;
82
83 }
84
85 protected void initMappingSqlQueries() {
86
87 this.usersByNameMapping = new UsersByUsernameMapping(getDataSource());
88
89 this.rolesByUsernameMapping = new AuthoritiesByUsernameMapping(getDataSource());
90
91 }
92
93 /**
94 * Allows the default query string used to retrieve authorities based on
* username to be overriden, if
95 * default table or column names need to be changed. The default query is {@link
96 * #DEF_AUTHORITIES_BY_USERNAME_QUERY}; when modifying this query,
* ensure that all returned columns are mapped
97 * back to the same column names as in the default query.
98 *
99 * @param queryString The query string to set
100 */
101 public void setAuthoritiesByUsernameQuery(String queryString) {
102
103 authoritiesByUsernameQuery = queryString;
104
105 }
106
107 /**
108 * Allows a default role prefix to be specified. If this is set to a non-empty value,
then it is
109 * automatically prepended to any roles read in from the db. This may for example be used to add the
110 * <code>ROLE_</code> prefix expected to exist in role names (by default) by some other Acegi Security framework
111 * classes, in the case that the prefix is not already present in the db.
112 *
113 * @param rolePrefix the new prefix
114 */
115 public void setRolePrefix(String rolePrefix) {
116
117 this.rolePrefix = rolePrefix;
118
119 }
120
121 /**
122 * If <code>true</code> (the default), indicates the {@link #getUsersByUsernameQuery()} returns a username
123 * in response to a query. If <code>false</code>, indicates that a primary key is used instead. If set to
124 * <code>true</code>, the class will use the database-derived username in the returned <code>UserDetailsImpl</code>.
125 * If <code>false</code>, the class will use the {@link #loadUserByUsername(String)} derived username in the
126 * returned <code>UserDetailsImpl</code>.
127 *
128 * @param usernameBasedPrimaryKey <code>true</code> if the mapping queries return the username <code>String</code>,
129 * or <code>false</code> if the mapping returns a database primary key.
130 */
131 public void setUsernameBasedPrimaryKey(boolean usernameBasedPrimaryKey) {
132
133 this.usernameBasedPrimaryKey = usernameBasedPrimaryKey;
134
135 }
136
137 /**
138 * Allows the default query string used to retrieve users based on username to be overriden, if default
139 * table or column names need to be changed. The default query is {@link #DEF_USERS_BY_USERNAME_QUERY}; when
140 * modifying this query, ensure that all returned columns are mapped back to the same column names as in the
141 * default query. If the 'enabled' column does not exist in the source db, a permanent true value for this column
142 * may be returned by using a query similar to <br><pre>
143 * "SELECT username,password,'true' as enabled FROM users WHERE username = ?"</pre>
144 *
145 * @param usersByUsernameQueryString The query string to set
146 */
147 public void setUsersByUsernameQuery(String usersByUsernameQueryString) {
148
149 this.usersByUsernameQuery = usersByUsernameQueryString;
150
151 }
152
153 public IUserDetails loadUserByUsername(String username)
154
155 throws UsernameNotFoundException, DataAccessException {
156
157 List users = usersByNameMapping.execute(username);
158
159 if (users.size() == 0) {
160
161 throw new UsernameNotFoundException("User not found");
162
163 }
164
165 IUserDetails user = (IUserDetails) users.get(0); // contains no GrantedAuthority[]
166 List dbAuths = rolesByUsernameMapping.execute(user.getUsername());
167 addCustomAuthorities(user.getUsername(), dbAuths);
168 if (dbAuths.size() == 0) {
169
170 throw new UsernameNotFoundException("User has no GrantedAuthority");
171
172 }
173
174 GrantedAuthority[] arrayAuths = (GrantedAuthority[]) dbAuths.toArray(new GrantedAuthority[dbAuths.size()]);
175
176 user.setAuthorities(arrayAuths);
177
178 if (!usernameBasedPrimaryKey) {
179
180 user.setUsername(username);
181
182 }
183
184 return user;
185
186 }
187
188 /**
189 * Query object to look up a user's authorities.
190 */
191 protected class AuthoritiesByUsernameMapping extends MappingSqlQuery {
192
193 protected AuthoritiesByUsernameMapping(DataSource ds) {
194
195 super(ds, authoritiesByUsernameQuery);
196
197 declareParameter(new SqlParameter(Types.VARCHAR));
198
199 compile();
200
201 }
202
203 protected Object mapRow(ResultSet rs, int rownum)
204
205 throws SQLException {
206
207 String roleName = rolePrefix + rs.getString(2);
208
209 GrantedAuthorityImpl authority = new GrantedAuthorityImpl(roleName);
210
211 return authority;
212
213 }
214
215 }
216
217 /**
218 * Query object to look up a user.
219 */
220 protected class UsersByUsernameMapping extends MappingSqlQuery {
221
222 protected UsersByUsernameMapping(DataSource ds) {
223
224 super(ds, usersByUsernameQuery);
225
226 declareParameter(new SqlParameter(Types.VARCHAR));
227
228 compile();
229
230 }
231
232 protected Object mapRow(ResultSet rs, int rownum)
233
234 throws SQLException {
235
236 int user_id = rs.getInt(1);
237
238 int company_id = rs.getInt(2);
239
240 String username = rs.getString(3);
241
242 String password = rs.getString(4);
243
244 boolean enabled = rs.getBoolean(5);
245
246 IUserDetails user = new UserDetailsImpl(username, password, enabled, true, true, true,
247
248 new GrantedAuthority[] {new GrantedAuthorityImpl("HOLDER")});
249
250 user.setUserId(user_id);
251
252 user.setCompanyId(company_id);
253
254 return user;
255
256 }
257
258 }
259
260 }
2
3 import java.sql.ResultSet;
4
5 import java.sql.SQLException;
6
7 import java.sql.Types;
8
9 import java.util.List;
10
11 import javax.sql.DataSource;
12
13 import org.acegisecurity.GrantedAuthority;
14
15 import org.acegisecurity.GrantedAuthorityImpl;
16
17 import org.acegisecurity.userdetails.UsernameNotFoundException;
18
19 import org.acegisecurity.userdetails.jdbc.JdbcDaoImpl;
20
21 import org.security.IUserDetails;
22
23 import org.security.UserDetailsImpl;
24
25 import org.springframework.dao.DataAccessException;
26
27 import org.springframework.jdbc.core.SqlParameter;
28
29 import org.springframework.jdbc.object.MappingSqlQuery;
30
31 /**
32 * The class AcegiJdbcDaoImpl provides the method to
* get IUserDetail information from db which contains userId,
* companyId and UserDetail information.
33 *
34 * @author wade
35 *
36 */
37 public class AcegiJdbcDaoImpl extends JdbcDaoImpl {
38
39 public static final String DEF_USERS_BY_USERNAME_QUERY =
40
41 "SELECT u.user_id, u.company_id, email, password, enabled "+"from role r, user_role ur, user u where r.role_id = ur.role_id "+"and ur.user_id = u.user_id and email = ? limit 1";
42
43 public static final String DEF_AUTHORITIES_BY_USERNAME_QUERY =
44
45 "SELECT username,authority FROM authorities WHERE username = ?";
46
47 protected MappingSqlQuery rolesByUsernameMapping;
48
49 protected MappingSqlQuery usersByNameMapping;
50
51 private String authoritiesByUsernameQuery;
52
53 private String rolePrefix = "";
54
55 private String usersByUsernameQuery;
56
57 private boolean usernameBasedPrimaryKey = true;
58
59 public AcegiJdbcDaoImpl(){
60
61 usersByUsernameQuery = DEF_USERS_BY_USERNAME_QUERY;
62
63 authoritiesByUsernameQuery = DEF_AUTHORITIES_BY_USERNAME_QUERY;
64
65 }
66
67 public String getAuthoritiesByUsernameQuery() {
68
69 return authoritiesByUsernameQuery;
70
71 }
72
73 public String getRolePrefix() {
74
75 return rolePrefix;
76
77 }
78
79 public String getUsersByUsernameQuery() {
80
81 return usersByUsernameQuery;
82
83 }
84
85 protected void initMappingSqlQueries() {
86
87 this.usersByNameMapping = new UsersByUsernameMapping(getDataSource());
88
89 this.rolesByUsernameMapping = new AuthoritiesByUsernameMapping(getDataSource());
90
91 }
92
93 /**
94 * Allows the default query string used to retrieve authorities based on
* username to be overriden, if
95 * default table or column names need to be changed. The default query is {@link
96 * #DEF_AUTHORITIES_BY_USERNAME_QUERY}; when modifying this query,
* ensure that all returned columns are mapped
97 * back to the same column names as in the default query.
98 *
99 * @param queryString The query string to set
100 */
101 public void setAuthoritiesByUsernameQuery(String queryString) {
102
103 authoritiesByUsernameQuery = queryString;
104
105 }
106
107 /**
108 * Allows a default role prefix to be specified. If this is set to a non-empty value,
then it is
109 * automatically prepended to any roles read in from the db. This may for example be used to add the
110 * <code>ROLE_</code> prefix expected to exist in role names (by default) by some other Acegi Security framework
111 * classes, in the case that the prefix is not already present in the db.
112 *
113 * @param rolePrefix the new prefix
114 */
115 public void setRolePrefix(String rolePrefix) {
116
117 this.rolePrefix = rolePrefix;
118
119 }
120
121 /**
122 * If <code>true</code> (the default), indicates the {@link #getUsersByUsernameQuery()} returns a username
123 * in response to a query. If <code>false</code>, indicates that a primary key is used instead. If set to
124 * <code>true</code>, the class will use the database-derived username in the returned <code>UserDetailsImpl</code>.
125 * If <code>false</code>, the class will use the {@link #loadUserByUsername(String)} derived username in the
126 * returned <code>UserDetailsImpl</code>.
127 *
128 * @param usernameBasedPrimaryKey <code>true</code> if the mapping queries return the username <code>String</code>,
129 * or <code>false</code> if the mapping returns a database primary key.
130 */
131 public void setUsernameBasedPrimaryKey(boolean usernameBasedPrimaryKey) {
132
133 this.usernameBasedPrimaryKey = usernameBasedPrimaryKey;
134
135 }
136
137 /**
138 * Allows the default query string used to retrieve users based on username to be overriden, if default
139 * table or column names need to be changed. The default query is {@link #DEF_USERS_BY_USERNAME_QUERY}; when
140 * modifying this query, ensure that all returned columns are mapped back to the same column names as in the
141 * default query. If the 'enabled' column does not exist in the source db, a permanent true value for this column
142 * may be returned by using a query similar to <br><pre>
143 * "SELECT username,password,'true' as enabled FROM users WHERE username = ?"</pre>
144 *
145 * @param usersByUsernameQueryString The query string to set
146 */
147 public void setUsersByUsernameQuery(String usersByUsernameQueryString) {
148
149 this.usersByUsernameQuery = usersByUsernameQueryString;
150
151 }
152
153 public IUserDetails loadUserByUsername(String username)
154
155 throws UsernameNotFoundException, DataAccessException {
156
157 List users = usersByNameMapping.execute(username);
158
159 if (users.size() == 0) {
160
161 throw new UsernameNotFoundException("User not found");
162
163 }
164
165 IUserDetails user = (IUserDetails) users.get(0); // contains no GrantedAuthority[]
166 List dbAuths = rolesByUsernameMapping.execute(user.getUsername());
167 addCustomAuthorities(user.getUsername(), dbAuths);
168 if (dbAuths.size() == 0) {
169
170 throw new UsernameNotFoundException("User has no GrantedAuthority");
171
172 }
173
174 GrantedAuthority[] arrayAuths = (GrantedAuthority[]) dbAuths.toArray(new GrantedAuthority[dbAuths.size()]);
175
176 user.setAuthorities(arrayAuths);
177
178 if (!usernameBasedPrimaryKey) {
179
180 user.setUsername(username);
181
182 }
183
184 return user;
185
186 }
187
188 /**
189 * Query object to look up a user's authorities.
190 */
191 protected class AuthoritiesByUsernameMapping extends MappingSqlQuery {
192
193 protected AuthoritiesByUsernameMapping(DataSource ds) {
194
195 super(ds, authoritiesByUsernameQuery);
196
197 declareParameter(new SqlParameter(Types.VARCHAR));
198
199 compile();
200
201 }
202
203 protected Object mapRow(ResultSet rs, int rownum)
204
205 throws SQLException {
206
207 String roleName = rolePrefix + rs.getString(2);
208
209 GrantedAuthorityImpl authority = new GrantedAuthorityImpl(roleName);
210
211 return authority;
212
213 }
214
215 }
216
217 /**
218 * Query object to look up a user.
219 */
220 protected class UsersByUsernameMapping extends MappingSqlQuery {
221
222 protected UsersByUsernameMapping(DataSource ds) {
223
224 super(ds, usersByUsernameQuery);
225
226 declareParameter(new SqlParameter(Types.VARCHAR));
227
228 compile();
229
230 }
231
232 protected Object mapRow(ResultSet rs, int rownum)
233
234 throws SQLException {
235
236 int user_id = rs.getInt(1);
237
238 int company_id = rs.getInt(2);
239
240 String username = rs.getString(3);
241
242 String password = rs.getString(4);
243
244 boolean enabled = rs.getBoolean(5);
245
246 IUserDetails user = new UserDetailsImpl(username, password, enabled, true, true, true,
247
248 new GrantedAuthority[] {new GrantedAuthorityImpl("HOLDER")});
249
250 user.setUserId(user_id);
251
252 user.setCompanyId(company_id);
253
254 return user;
255
256 }
257
258 }
259
260 }
修改spring配置, 使用我们新建立的类:
1 <bean id="userDetailsService"
2 class="org.security.acegi.AcegiJdbcDaoImpl">
3 <property name="dataSource">
4 <ref bean="dataSource" />
5 </property>
6 <property name="usersByUsernameQuery">
7 <value>
8 SELECT u.user_id, u.company_id, email, password, enabled
9 from role r, user_role ur, user u where r.role_id = ur.role_id and ur.user_id = u.user_id
10 and email = ?
11 limit 1
12 </value>
13 </property>
14 <property name="authoritiesByUsernameQuery">
15 <value>
16 SELECT u.email, r.role_name FROM user_role ur, user u, role r WHERE
17 ur.user_id = u.user_id and ur.role_id = r.role_id and u.email = ?
18 </value>
19 </property>
20 </bean>
2 class="org.security.acegi.AcegiJdbcDaoImpl">
3 <property name="dataSource">
4 <ref bean="dataSource" />
5 </property>
6 <property name="usersByUsernameQuery">
7 <value>
8 SELECT u.user_id, u.company_id, email, password, enabled
9 from role r, user_role ur, user u where r.role_id = ur.role_id and ur.user_id = u.user_id
10 and email = ?
11 limit 1
12 </value>
13 </property>
14 <property name="authoritiesByUsernameQuery">
15 <value>
16 SELECT u.email, r.role_name FROM user_role ur, user u, role r WHERE
17 ur.user_id = u.user_id and ur.role_id = r.role_id and u.email = ?
18 </value>
19 </property>
20 </bean>
好了, 如果再有用户登录,就会调用我们的loadUserByUsername, 从数据库中读取用户数据了, 那用户的权限都有什么呢? 一个用户又对应着哪些ROLE呢? 下面先讲一下ACEGI 例子中的权限设置
