/
JdbcScimGroupExternalMembershipManager.java
327 lines (282 loc) · 15.1 KB
/
JdbcScimGroupExternalMembershipManager.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
/*******************************************************************************
* Cloud Foundry
* Copyright (c) [2009-2016] Pivotal Software, Inc. All Rights Reserved.
*
* This product is licensed to you under the Apache License, Version 2.0 (the "License").
* You may not use this product except in compliance with the License.
*
* This product includes a number of subcomponents with
* separate copyright notices and license terms. Your use of these
* subcomponents is subject to the terms and conditions of the
* subcomponent's license, as noted in the LICENSE file.
*******************************************************************************/
package org.cloudfoundry.identity.uaa.scim.jdbc;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.cloudfoundry.identity.uaa.resources.jdbc.AbstractQueryable;
import org.cloudfoundry.identity.uaa.resources.jdbc.JdbcPagingListFactory;
import org.cloudfoundry.identity.uaa.resources.jdbc.SearchQueryConverter;
import org.cloudfoundry.identity.uaa.scim.ScimGroup;
import org.cloudfoundry.identity.uaa.scim.ScimGroupExternalMember;
import org.cloudfoundry.identity.uaa.scim.ScimGroupExternalMembershipManager;
import org.cloudfoundry.identity.uaa.scim.ScimGroupProvisioning;
import org.cloudfoundry.identity.uaa.scim.exception.MemberAlreadyExistsException;
import org.cloudfoundry.identity.uaa.scim.exception.ScimResourceConstraintFailedException;
import org.cloudfoundry.identity.uaa.scim.exception.ScimResourceNotFoundException;
import org.cloudfoundry.identity.uaa.zone.IdentityZoneHolder;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jca.cci.InvalidResultSetAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.util.Assert;
import org.springframework.util.StringUtils;
public class JdbcScimGroupExternalMembershipManager extends AbstractQueryable<ScimGroupExternalMember>
implements ScimGroupExternalMembershipManager {
private JdbcTemplate jdbcTemplate;
private final Log logger = LogFactory.getLog(getClass());
public static final String EXTERNAL_GROUP_MAPPING_FIELDS = "group_id,external_group,added,origin";
public static final String JOIN_EXTERNAL_GROUP_MAPPING_FIELDS = "gm.group_id,gm.external_group,gm.added,g.displayName,gm.origin";
public static final String EXTERNAL_GROUP_MAPPING_TABLE = "external_group_mapping";
public static final String GROUP_TABLE = "groups";
public static final String JOIN_GROUP_TABLE = String.format("%s g, %s gm",GROUP_TABLE, EXTERNAL_GROUP_MAPPING_TABLE);
public static final String JOIN_WHERE_ID = "g.id = gm.group_id and gm.origin = ?";
public static final String ADD_EXTERNAL_GROUP_MAPPING_SQL = String.format("insert into %s ( %s ) values (?,lower(?),?,?)",
EXTERNAL_GROUP_MAPPING_TABLE, EXTERNAL_GROUP_MAPPING_FIELDS);
public static final String GET_EXTERNAL_GROUP_MAPPINGS_SQL =
String.format("select %s from %s where gm.group_id=? and %s",
JOIN_EXTERNAL_GROUP_MAPPING_FIELDS, JOIN_GROUP_TABLE, JOIN_WHERE_ID);
public static final String GET_GROUPS_BY_EXTERNAL_GROUP_MAPPING_SQL = String.format("select %s from %s where %s and lower(external_group)=lower(?)",
JOIN_EXTERNAL_GROUP_MAPPING_FIELDS, JOIN_GROUP_TABLE, JOIN_WHERE_ID);
public static final String GET_GROUPS_WITH_EXTERNAL_GROUP_MAPPINGS_SQL =
String.format("select %s from %s where g.id=? and %s and lower(external_group) like lower(?)",
JOIN_EXTERNAL_GROUP_MAPPING_FIELDS, JOIN_GROUP_TABLE, JOIN_WHERE_ID);
public static final String DELETE_EXTERNAL_GROUP_MAPPING_SQL =
String.format("delete from %s where group_id=? and lower(external_group)=lower(?) and origin=?",
EXTERNAL_GROUP_MAPPING_TABLE);
public static final String DELETE_ALL_MAPPINGS_FOR_GROUP_SQL =
String.format("delete from %s where group_id = ?", EXTERNAL_GROUP_MAPPING_TABLE);
private final RowMapper<ScimGroupExternalMember> rowMapper = new ScimGroupExternalMemberRowMapper();
private ScimGroupProvisioning scimGroupProvisioning;
public JdbcScimGroupExternalMembershipManager(JdbcTemplate jdbcTemplate, JdbcPagingListFactory pagingListFactory) {
super(jdbcTemplate, pagingListFactory, new ScimGroupExternalMemberRowMapper());
Assert.notNull(jdbcTemplate);
this.jdbcTemplate = jdbcTemplate;
setQueryConverter(new ScimSearchQueryConverter());
}
protected String adjustFilterForJoin(String filter) {
if (StringUtils.hasText(filter)) {
filter = filter.replace("displayName", "g.displayName");
filter = filter.replace("externalGroup", "gm.external_group");
filter = filter.replace("groupId", "g.id");
filter = filter.replace("origin", "gm.origin");
}
return filter;
}
@Override
protected String getTableName() {
return EXTERNAL_GROUP_MAPPING_TABLE;
}
@Override
public List<ScimGroupExternalMember> query(String filter) {
return super.query(filter);
}
@Override
public int delete(String filter) {
SearchQueryConverter.ProcessedFilter where = getQueryConverter().convert(filter, null, false);
logger.debug("Filtering groups with SQL: " + where);
try {
String completeSql = "DELETE FROM "+getTableName()+" WHERE ";
if (StringUtils.hasText(where.getSql())) {
completeSql += where.getSql() + " AND ";
}
completeSql += "group_id IN (SELECT id FROM groups WHERE identity_zone_id='"+ IdentityZoneHolder.get().getId()+"')";
logger.debug("delete sql: " + completeSql + ", params: " + where.getParams());
return new NamedParameterJdbcTemplate(jdbcTemplate).update(completeSql, where.getParams());
} catch (DataAccessException e) {
logger.debug("Filter '" + filter + "' generated invalid SQL", e);
throw new IllegalArgumentException("Invalid delete filter: " + filter);
}
}
@Override
public List<ScimGroupExternalMember> query(String filter, String sortBy, boolean ascending) {
return super.query(adjustFilterForJoin(filter), sortBy, ascending);
}
@Override
public ScimGroupExternalMember mapExternalGroup(final String groupId,
final String externalGroup,
final String origin)
throws ScimResourceNotFoundException, MemberAlreadyExistsException {
ScimGroup group = scimGroupProvisioning.retrieve(groupId);
if (!StringUtils.hasText(externalGroup)) {
throw new ScimResourceConstraintFailedException("external group must not be null when mapping an external group");
}
if (!StringUtils.hasText(origin)) {
throw new ScimResourceConstraintFailedException("origin must not be null when mapping an external group");
}
if (null != group) {
try {
int result = jdbcTemplate.update(ADD_EXTERNAL_GROUP_MAPPING_SQL, new PreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps) throws SQLException {
ps.setString(1, groupId);
ps.setString(2, externalGroup);
ps.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
ps.setString(4, origin);
}
});
System.out.println("update count = " + result);
} catch (DuplicateKeyException e) {
// we should not throw, if the mapping exist, we should leave it
// there.
logger.info("The mapping between group " + group.getDisplayName() + " and external group "
+ externalGroup + " already exists");
// throw new
// MemberAlreadyExistsException("The mapping between group " +
// group.getDisplayName() + " and external group " +
// externalGroup + " already exists");
}
return getExternalGroupMap(groupId, externalGroup, origin);
} else {
throw new ScimResourceNotFoundException("Group does not exist");
}
}
@Override
public ScimGroupExternalMember unmapExternalGroup(final String groupId,
final String externalGroup,
final String origin)
throws ScimResourceNotFoundException {
ScimGroup group = scimGroupProvisioning.retrieve(groupId);
ScimGroupExternalMember result = getExternalGroupMap(groupId, externalGroup, origin);
if (null != group && null != result) {
int count = jdbcTemplate.update(DELETE_EXTERNAL_GROUP_MAPPING_SQL, new PreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps) throws SQLException {
ps.setString(1, groupId);
ps.setString(2, externalGroup);
ps.setString(3, origin);
}
});
if (count==1) {
return result;
} else if (count==0) {
throw new ScimResourceNotFoundException("No group mappings deleted.");
} else {
throw new InvalidResultSetAccessException("More than one mapping deleted count="+count, new SQLException());
}
} else {
return null;
}
}
@Override
public List<ScimGroupExternalMember> getExternalGroupMapsByGroupId(final String groupId,
final String origin)
throws ScimResourceNotFoundException {
scimGroupProvisioning.retrieve(groupId);
return jdbcTemplate.query(GET_EXTERNAL_GROUP_MAPPINGS_SQL, new PreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps) throws SQLException {
ps.setString(1, groupId);
ps.setString(2, origin);
}
}, rowMapper);
}
@Override
public List<ScimGroupExternalMember> getExternalGroupMapsByGroupName(final String groupName,
final String origin)
throws ScimResourceNotFoundException {
final List<ScimGroup> groups = scimGroupProvisioning.query(String.format("displayName eq \"%s\"", groupName));
if (null != groups && groups.size() > 0) {
return jdbcTemplate.query(GET_EXTERNAL_GROUP_MAPPINGS_SQL, new PreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps) throws SQLException {
ps.setString(1, groups.get(0).getId());
ps.setString(2, origin);
}
}, rowMapper);
} else {
return null;
}
}
@Override
public void unmapAll(String groupId) throws ScimResourceNotFoundException {
ScimGroup group = scimGroupProvisioning.retrieve(groupId);
if (null == group) {
throw new ScimResourceNotFoundException("Group not found for ID " + groupId);
}
jdbcTemplate.update(DELETE_ALL_MAPPINGS_FOR_GROUP_SQL, new PreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps) throws SQLException {
ps.setString(1, groupId);
}
});
}
@Override
public List<ScimGroupExternalMember> getExternalGroupMapsByExternalGroup(final String externalGroup,
final String origin)
throws ScimResourceNotFoundException {
return jdbcTemplate.query(GET_GROUPS_BY_EXTERNAL_GROUP_MAPPING_SQL, new PreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps) throws SQLException {
ps.setString(1, origin);
ps.setString(2, externalGroup);
}
}, rowMapper);
}
@Override
protected String getQuerySQL(String filter, SearchQueryConverter.ProcessedFilter where) {
boolean containsWhereClause = getBaseSqlQuery().contains(" where ");
return filter == null || filter.trim().length()==0 ?
getBaseSqlQuery() :
getBaseSqlQuery() + (containsWhereClause ? " and " : " where ") + where.getSql();
}
private ScimGroupExternalMember getExternalGroupMap(final String groupId,
final String externalGroup,
final String origin)
throws ScimResourceNotFoundException {
try {
ScimGroupExternalMember u = jdbcTemplate.queryForObject(GET_GROUPS_WITH_EXTERNAL_GROUP_MAPPINGS_SQL,
rowMapper, groupId, origin, externalGroup);
return u;
} catch (EmptyResultDataAccessException e) {
throw new ScimResourceNotFoundException("The mapping between groupId " + groupId + " and external group "
+ externalGroup + " does not exist");
}
}
private static final class ScimGroupExternalMemberRowMapper implements RowMapper<ScimGroupExternalMember> {
@Override
public ScimGroupExternalMember mapRow(ResultSet rs, int rowNum) throws SQLException {
String groupId = rs.getString(1);
String externalGroup = rs.getString(2);
Timestamp added = rs.getTimestamp(3);
String displayName = rs.getString(4);
String origin = rs.getString(5);
ScimGroupExternalMember result = new ScimGroupExternalMember(groupId, externalGroup);
result.setDisplayName(displayName);
result.setOrigin(origin);
result.getMeta().setCreated(added);
result.getMeta().setLastModified(added);
return result;
}
}
public void setScimGroupProvisioning(ScimGroupProvisioning scimGroupProvisioning) {
this.scimGroupProvisioning = scimGroupProvisioning;
}
@Override
protected String getBaseSqlQuery() {
return String.format("select %s from %s where %s",
JOIN_EXTERNAL_GROUP_MAPPING_FIELDS, JOIN_GROUP_TABLE, "g.id = gm.group_id and g.identity_zone_id='"+IdentityZoneHolder.get().getId()+"'");
}
@Override
protected void validateOrderBy(String orderBy) throws IllegalArgumentException {
super.validateOrderBy(orderBy, EXTERNAL_GROUP_MAPPING_FIELDS);
}
}