1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package org.springframework.security.oauth2.provider.approval;
17
18 import static org.springframework.security.oauth2.provider.approval.Approval.ApprovalStatus.APPROVED;
19
20 import java.sql.PreparedStatement;
21 import java.sql.ResultSet;
22 import java.sql.SQLException;
23 import java.sql.Timestamp;
24 import java.util.Collection;
25 import java.util.Date;
26 import java.util.List;
27
28 import javax.sql.DataSource;
29
30 import org.apache.commons.logging.Log;
31 import org.apache.commons.logging.LogFactory;
32 import org.springframework.dao.DataAccessException;
33 import org.springframework.jdbc.core.JdbcTemplate;
34 import org.springframework.jdbc.core.PreparedStatementSetter;
35 import org.springframework.jdbc.core.RowMapper;
36 import org.springframework.security.oauth2.provider.approval.Approval.ApprovalStatus;
37 import org.springframework.util.Assert;
38
39
40
41
42
43 public class JdbcApprovalStore implements ApprovalStore {
44
45 private final JdbcTemplate jdbcTemplate;
46
47 private final Log logger = LogFactory.getLog(getClass());
48
49 private final RowMapper<Approval> rowMapper = new AuthorizationRowMapper();
50
51 private static final String TABLE_NAME = "oauth_approvals";
52
53 private static final String FIELDS = "expiresAt,status,lastModifiedAt,userId,clientId,scope";
54
55 private static final String WHERE_KEY = "where userId=? and clientId=?";
56
57 private static final String WHERE_KEY_AND_SCOPE = WHERE_KEY + " and scope=?";
58
59 private static final String DEFAULT_ADD_APPROVAL_STATEMENT = String.format("insert into %s ( %s ) values (?,?,?,?,?,?)", TABLE_NAME,
60 FIELDS);
61
62 private static final String DEFAULT_REFRESH_APPROVAL_STATEMENT = String.format(
63 "update %s set expiresAt=?, status=?, lastModifiedAt=? " + WHERE_KEY_AND_SCOPE, TABLE_NAME);
64
65 private static final String DEFAULT_GET_APPROVAL_SQL = String.format("select %s from %s " + WHERE_KEY, FIELDS, TABLE_NAME);
66
67 private static final String DEFAULT_DELETE_APPROVAL_SQL = String.format("delete from %s " + WHERE_KEY_AND_SCOPE,
68 TABLE_NAME);
69
70 private static final String DEFAULT_EXPIRE_APPROVAL_STATEMENT = String.format("update %s set expiresAt = ? " + WHERE_KEY_AND_SCOPE,
71 TABLE_NAME);
72
73 private String addApprovalStatement = DEFAULT_ADD_APPROVAL_STATEMENT;
74
75 private String refreshApprovalStatement = DEFAULT_REFRESH_APPROVAL_STATEMENT;
76
77 private String findApprovalStatement = DEFAULT_GET_APPROVAL_SQL;
78
79 private String deleteApprovalStatment = DEFAULT_DELETE_APPROVAL_SQL;
80
81 private String expireApprovalStatement = DEFAULT_EXPIRE_APPROVAL_STATEMENT;
82
83 private boolean handleRevocationsAsExpiry = false;
84
85 public JdbcApprovalStore(DataSource dataSource) {
86 Assert.notNull(dataSource);
87 this.jdbcTemplate = new JdbcTemplate(dataSource);
88 }
89
90 public void setHandleRevocationsAsExpiry(boolean handleRevocationsAsExpiry) {
91 this.handleRevocationsAsExpiry = handleRevocationsAsExpiry;
92 }
93
94 public void setAddApprovalStatement(String addApprovalStatement) {
95 this.addApprovalStatement = addApprovalStatement;
96 }
97
98 public void setFindApprovalStatement(String findApprovalStatement) {
99 this.findApprovalStatement = findApprovalStatement;
100 }
101
102 public void setDeleteApprovalStatment(String deleteApprovalStatment) {
103 this.deleteApprovalStatment = deleteApprovalStatment;
104 }
105
106 public void setExpireApprovalStatement(String expireApprovalStatement) {
107 this.expireApprovalStatement = expireApprovalStatement;
108 }
109
110 public void setRefreshApprovalStatement(String refreshApprovalStatement) {
111 this.refreshApprovalStatement = refreshApprovalStatement;
112 }
113
114 @Override
115 public boolean addApprovals(final Collection<Approval> approvals) {
116 if (logger.isDebugEnabled()) {
117 logger.debug(String.format("adding approvals: [%s]", approvals));
118 }
119 boolean success = true;
120 for (Approval approval : approvals) {
121 if (!updateApproval(refreshApprovalStatement, approval)) {
122 if (!updateApproval(addApprovalStatement, approval)) {
123 success = false;
124 }
125 }
126 }
127 return success;
128 }
129
130 @Override
131 public boolean revokeApprovals(Collection<Approval> approvals) {
132 if (logger.isDebugEnabled()) {
133 logger.debug(String.format("Revoking approvals: [%s]", approvals));
134 }
135 boolean success = true;
136 for (final Approval approval : approvals) {
137 if (handleRevocationsAsExpiry) {
138 int refreshed = jdbcTemplate.update(expireApprovalStatement, new PreparedStatementSetter() {
139 @Override
140 public void setValues(PreparedStatement ps) throws SQLException {
141 ps.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
142 ps.setString(2, approval.getUserId());
143 ps.setString(3, approval.getClientId());
144 ps.setString(4, approval.getScope());
145 }
146 });
147 if (refreshed != 1) {
148 success = false;
149 }
150 }
151 else {
152 int refreshed = jdbcTemplate.update(deleteApprovalStatment, new PreparedStatementSetter() {
153 @Override
154 public void setValues(PreparedStatement ps) throws SQLException {
155 ps.setString(1, approval.getUserId());
156 ps.setString(2, approval.getClientId());
157 ps.setString(3, approval.getScope());
158 }
159 });
160 if (refreshed != 1) {
161 success = false;
162 }
163 }
164 }
165 return success;
166 }
167
168 public boolean purgeExpiredApprovals() {
169 logger.debug("Purging expired approvals from database");
170 try {
171 int deleted = jdbcTemplate.update(deleteApprovalStatment + " where expiresAt <= ?",
172 new PreparedStatementSetter() {
173 @Override
174 public void setValues(PreparedStatement ps) throws SQLException {
175 ps.setTimestamp(1, new Timestamp(new Date().getTime()));
176 }
177 });
178 if (logger.isDebugEnabled()) {
179 logger.debug(deleted + " expired approvals deleted");
180 }
181 }
182 catch (DataAccessException ex) {
183 logger.error("Error purging expired approvals", ex);
184 return false;
185 }
186 return true;
187 }
188
189 @Override
190 public List<Approval> getApprovals(String userName, String clientId) {
191 return jdbcTemplate.query(findApprovalStatement, rowMapper, userName, clientId);
192 }
193
194 private boolean updateApproval(final String sql, final Approval approval) {
195 if (logger.isDebugEnabled()) {
196 logger.debug(String.format("refreshing approval: [%s]", approval));
197 }
198 int refreshed = jdbcTemplate.update(sql, new PreparedStatementSetter() {
199 @Override
200 public void setValues(PreparedStatement ps) throws SQLException {
201 ps.setTimestamp(1, new Timestamp(approval.getExpiresAt().getTime()));
202 ps.setString(2, (approval.getStatus() == null ? APPROVED : approval.getStatus()).toString());
203 ps.setTimestamp(3, new Timestamp(approval.getLastUpdatedAt().getTime()));
204 ps.setString(4, approval.getUserId());
205 ps.setString(5, approval.getClientId());
206 ps.setString(6, approval.getScope());
207 }
208 });
209 if (refreshed != 1) {
210 return false;
211 }
212 return true;
213 }
214
215 private static class AuthorizationRowMapper implements RowMapper<Approval> {
216
217 @Override
218 public Approval mapRow(ResultSet rs, int rowNum) throws SQLException {
219 String userName = rs.getString(4);
220 String clientId = rs.getString(5);
221 String scope = rs.getString(6);
222 Date expiresAt = rs.getTimestamp(1);
223 String status = rs.getString(2);
224 Date lastUpdatedAt = rs.getTimestamp(3);
225
226 return new Approval(userName, clientId, scope, expiresAt, ApprovalStatus.valueOf(status), lastUpdatedAt);
227 }
228 }
229 }