2 * Copyright (c) 2014, 2017 Hewlett-Packard Development Company, L.P. and others. All rights reserved.
4 * This program and the accompanying materials are made available under the
5 * terms of the Eclipse Public License v1.0 which accompanies this distribution,
6 * and is available at http://www.eclipse.org/legal/epl-v10.html
8 package org.opendaylight.aaa.datastore.h2;
10 import static java.util.Objects.requireNonNull;
12 import edu.umd.cs.findbugs.annotations.SuppressFBWarnings;
13 import java.sql.Connection;
14 import java.sql.PreparedStatement;
15 import java.sql.ResultSet;
16 import java.sql.SQLException;
17 import java.sql.Statement;
18 import java.util.Objects;
19 import org.apache.commons.text.StringEscapeUtils;
20 import org.opendaylight.aaa.api.IDMStoreUtil;
21 import org.opendaylight.aaa.api.model.User;
22 import org.opendaylight.aaa.api.model.Users;
23 import org.opendaylight.aaa.api.password.service.PasswordHash;
24 import org.opendaylight.aaa.api.password.service.PasswordHashService;
25 import org.slf4j.Logger;
26 import org.slf4j.LoggerFactory;
31 * @author peter.mellquist@hp.com
34 public class UserStore extends AbstractStore<User> {
35 private static final Logger LOG = LoggerFactory.getLogger(UserStore.class);
37 public static final String SQL_ID = "userid";
38 public static final String SQL_DOMAIN_ID = "domainid";
39 public static final String SQL_NAME = "name";
40 public static final String SQL_EMAIL = "email";
41 public static final String SQL_PASSWORD = "password";
42 public static final String SQL_DESCR = "description";
43 public static final String SQL_ENABLED = "enabled";
44 public static final String SQL_SALT = "salt";
45 private static final String TABLE_NAME = "USERS";
47 private final PasswordHashService passwordService;
49 public UserStore(final ConnectionProvider dbConnectionFactory, final PasswordHashService passwordService) {
50 super(dbConnectionFactory, TABLE_NAME);
51 this.passwordService = Objects.requireNonNull(passwordService);
55 protected String getTableCreationStatement() {
56 return "CREATE TABLE users " + "(userid VARCHAR(128) PRIMARY KEY,"
57 + "name VARCHAR(128) NOT NULL, " + "domainid VARCHAR(128) NOT NULL, "
58 + "email VARCHAR(128) NOT NULL, " + "password VARCHAR(128) NOT NULL, "
59 + "description VARCHAR(128) NOT NULL, " + "salt VARCHAR(128) NOT NULL, "
60 + "enabled INTEGER NOT NULL)";
64 protected User fromResultSet(final ResultSet rs) throws SQLException {
65 User user = new User();
67 user.setUserid(rs.getString(SQL_ID));
68 user.setDomainid(rs.getString(SQL_DOMAIN_ID));
69 user.setName(rs.getString(SQL_NAME));
70 user.setEmail(rs.getString(SQL_EMAIL));
71 user.setPassword(rs.getString(SQL_PASSWORD));
72 user.setDescription(rs.getString(SQL_DESCR));
73 user.setEnabled(rs.getInt(SQL_ENABLED) == 1);
74 user.setSalt(rs.getString(SQL_SALT));
75 } catch (SQLException sqle) {
76 LOG.error("SQL Exception: ", sqle);
82 public Users getUsers() throws StoreException {
83 Users users = new Users();
84 users.setUsers(listAll());
88 protected Users getUsers(final String username, final String domain) throws StoreException {
89 LOG.debug("getUsers for: {} in domain {}", username, domain);
91 Users users = new Users();
92 try (Connection conn = dbConnect();
93 PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM USERS WHERE userid = ? ")) {
94 pstmt.setString(1, IDMStoreUtil.createUserid(username, domain));
95 LOG.debug("query string: {}", pstmt);
96 users.setUsers(listFromStatement(pstmt));
97 } catch (SQLException s) {
98 throw new StoreException("SQL Exception : " + s);
103 public User getUser(final String id) throws StoreException {
104 try (Connection conn = dbConnect();
105 PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM USERS WHERE userid = ? ")) {
106 pstmt.setString(1, id);
107 LOG.debug("query string: {}", pstmt);
108 return firstFromStatement(pstmt);
109 } catch (SQLException s) {
110 throw new StoreException("SQL Exception : " + s);
114 protected User createUser(final User user) throws StoreException {
115 requireNonNull(user);
116 requireNonNull(user.getName());
117 requireNonNull(user.getDomainid());
119 final PasswordHash passwordHash = passwordService.getPasswordHash(user.getPassword());
120 user.setSalt(passwordHash.getSalt());
123 + " (userid,domainid,name,email,password,description,enabled,salt) values(?,?,?,?,?,?,?,?)";
124 try (Connection conn = dbConnect(); PreparedStatement statement = conn.prepareStatement(query)) {
125 user.setUserid(IDMStoreUtil.createUserid(user.getName(), user.getDomainid()));
126 statement.setString(1, user.getUserid());
127 statement.setString(2, user.getDomainid());
128 statement.setString(3, user.getName());
129 statement.setString(4, user.getEmail());
130 statement.setString(5, passwordHash.getHashedPassword());
131 statement.setString(6, user.getDescription());
132 statement.setInt(7, user.isEnabled() ? 1 : 0);
133 statement.setString(8, user.getSalt());
134 int affectedRows = statement.executeUpdate();
135 if (affectedRows == 0) {
136 throw new StoreException("Creating user failed, no rows affected.");
139 } catch (SQLException s) {
140 throw new StoreException("SQL Exception : " + s);
144 public User putUser(final User user) throws StoreException {
146 User savedUser = this.getUser(user.getUserid());
147 if (savedUser == null) {
151 if (user.getDescription() != null) {
152 savedUser.setDescription(user.getDescription());
154 if (user.getName() != null) {
155 savedUser.setName(user.getName());
157 if (user.isEnabled() != null) {
158 savedUser.setEnabled(user.isEnabled());
160 if (user.getEmail() != null) {
161 savedUser.setEmail(user.getEmail());
163 if (user.getPassword() != null) {
164 // If a new salt is provided, use it. Otherwise, derive salt from
166 String salt = user.getSalt();
168 salt = savedUser.getSalt();
170 final PasswordHash passwordHash = passwordService.getPasswordHash(user.getPassword(), salt);
171 savedUser.setPassword(passwordHash.getHashedPassword());
174 String query = "UPDATE users SET email = ?, password = ?, description = ?, enabled = ? WHERE userid = ?";
175 try (Connection conn = dbConnect(); PreparedStatement statement = conn.prepareStatement(query)) {
176 statement.setString(1, savedUser.getEmail());
177 statement.setString(2, savedUser.getPassword());
178 statement.setString(3, savedUser.getDescription());
179 statement.setInt(4, savedUser.isEnabled() ? 1 : 0);
180 statement.setString(5, savedUser.getUserid());
181 statement.executeUpdate();
182 } catch (SQLException s) {
183 throw new StoreException("SQL Exception : " + s);
189 @SuppressFBWarnings("SQL_NONCONSTANT_STRING_PASSED_TO_EXECUTE")
190 protected User deleteUser(String userid) throws StoreException {
191 userid = StringEscapeUtils.escapeHtml4(userid);
192 User savedUser = this.getUser(userid);
193 if (savedUser == null) {
197 String query = String.format("DELETE FROM USERS WHERE userid = '%s'", userid);
198 try (Connection conn = dbConnect(); Statement statement = conn.createStatement()) {
199 int deleteCount = statement.executeUpdate(query);
200 LOG.debug("deleted {} records", deleteCount);
202 } catch (SQLException s) {
203 throw new StoreException("SQL Exception : " + s);