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 com.google.common.annotations.VisibleForTesting;
13 import java.sql.ResultSet;
14 import java.sql.SQLException;
15 import java.sql.Statement;
16 import org.opendaylight.aaa.api.IDMStoreUtil;
17 import org.opendaylight.aaa.api.model.User;
18 import org.opendaylight.aaa.api.model.Users;
19 import org.opendaylight.aaa.api.password.service.PasswordHashService;
20 import org.slf4j.Logger;
21 import org.slf4j.LoggerFactory;
26 * @author peter.mellquist@hp.com
28 final class UserStore extends AbstractStore<User> {
29 private static final Logger LOG = LoggerFactory.getLogger(UserStore.class);
31 static final String TABLE = "AAA_USERS";
34 SQLTable.USER.verifyTable(TABLE);
38 * Column storing {@link User#getUserid()}, which is a flat namespace.
40 // FIXME: rename to "id"
42 static final String COL_ID = "userid";
45 static final String COL_DOMAIN_ID = "domainid";
48 static final String COL_NAME = "name";
51 static final String COL_EMAIL = "email";
54 static final String COL_PASSWORD = "password";
57 static final String COL_DESC = "description";
60 static final String COL_ENABLED = "enabled";
62 private static final String COL_SALT = "salt";
64 private final PasswordHashService passwordService;
66 UserStore(final ConnectionProvider dbConnectionFactory, final PasswordHashService passwordService) {
67 super(dbConnectionFactory, TABLE);
68 this.passwordService = requireNonNull(passwordService);
72 void createTable(final Statement stmt) throws SQLException {
73 stmt.executeUpdate("CREATE TABLE " + TABLE + " ("
74 + COL_ID + " VARCHAR(128) PRIMARY KEY, "
75 + COL_NAME + " VARCHAR(128) NOT NULL, "
76 // FIXME: foreign key to DomainStore.COL_ID?
77 + COL_DOMAIN_ID + " VARCHAR(128) NOT NULL, "
78 + COL_EMAIL + " VARCHAR(128) NOT NULL, "
79 + COL_DESC + " VARCHAR(128) NOT NULL, "
80 // FIXME: is 'salt' even used? Some comparators are not storing hashes, either
81 + COL_PASSWORD + " VARCHAR(128) NOT NULL, "
82 + COL_SALT + " VARCHAR(128) NOT NULL, "
83 + COL_ENABLED + " BOOLEAN NOT NULL)");
87 void cleanTable(final Statement stmt) throws SQLException {
88 stmt.execute("DELETE FROM " + TABLE);
92 protected User fromResultSet(final ResultSet rs) throws SQLException {
93 User user = new User();
95 user.setUserid(rs.getString(COL_ID));
96 user.setDomainid(rs.getString(COL_DOMAIN_ID));
97 user.setName(rs.getString(COL_NAME));
98 user.setEmail(rs.getString(COL_EMAIL));
99 user.setPassword(rs.getString(COL_PASSWORD));
100 user.setDescription(rs.getString(COL_DESC));
101 user.setEnabled(rs.getBoolean(COL_ENABLED));
102 user.setSalt(rs.getString(COL_SALT));
103 } catch (SQLException e) {
104 LOG.error("SQL Exception: ", e);
110 Users getUsers() throws StoreException {
111 Users users = new Users();
112 users.setUsers(listAll());
116 Users getUsers(final String username, final String domain) throws StoreException {
118 try (var conn = dbConnect();
119 var stmt = conn.prepareStatement("SELECT * FROM " + TABLE + " USERS WHERE " + COL_ID + " = ?")) {
120 stmt.setString(1, IDMStoreUtil.createUserid(username, domain));
121 LOG.debug("getUsers() request: {}", stmt);
124 users.setUsers(listFromStatement(stmt));
125 } catch (SQLException s) {
126 throw new StoreException("SQL Exception : " + s);
131 User getUser(final String id) throws StoreException {
132 try (var conn = dbConnect();
133 var stmt = conn.prepareStatement("SELECT * FROM " + TABLE + " WHERE " + COL_ID + " = ? ")) {
134 stmt.setString(1, id);
135 LOG.debug("getUser() request: {}", stmt);
137 return firstFromStatement(stmt);
138 } catch (SQLException s) {
139 throw new StoreException("SQL Exception : " + s);
143 User createUser(final User user) throws StoreException {
144 requireNonNull(user);
145 requireNonNull(user.getName());
146 requireNonNull(user.getDomainid());
148 final var passwordHash = passwordService.getPasswordHash(user.getPassword());
149 user.setSalt(passwordHash.getSalt());
151 try (var conn = dbConnect();
152 var stmt = conn.prepareStatement("INSERT INTO " + TABLE + " ("
153 + COL_ID + ", " + COL_DOMAIN_ID + ", " + COL_NAME + ", " + COL_EMAIL + ", " + COL_PASSWORD + ", "
154 + COL_DESC + ", " + COL_ENABLED + ", " + COL_SALT + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?)")) {
155 user.setUserid(IDMStoreUtil.createUserid(user.getName(), user.getDomainid()));
156 stmt.setString(1, user.getUserid());
157 stmt.setString(2, user.getDomainid());
158 stmt.setString(3, user.getName());
159 stmt.setString(4, user.getEmail());
160 stmt.setString(5, passwordHash.getHashedPassword());
161 stmt.setString(6, user.getDescription());
162 stmt.setBoolean(7, user.isEnabled());
163 stmt.setString(8, user.getSalt());
164 LOG.debug("createUser() request: {}", stmt);
166 if (stmt.executeUpdate() == 0) {
167 throw new StoreException("Creating user failed, no rows affected.");
170 } catch (SQLException s) {
171 throw new StoreException("SQL Exception : " + s);
175 User putUser(final User user) throws StoreException {
176 final var savedUser = getUser(user.getUserid());
177 if (savedUser == null) {
181 if (user.getDescription() != null) {
182 savedUser.setDescription(user.getDescription());
184 if (user.getName() != null) {
185 savedUser.setName(user.getName());
187 if (user.isEnabled() != null) {
188 savedUser.setEnabled(user.isEnabled());
190 if (user.getEmail() != null) {
191 savedUser.setEmail(user.getEmail());
193 if (user.getPassword() != null) {
194 // If a new salt is provided, use it. Otherwise, derive salt from existing.
195 String salt = user.getSalt();
197 salt = savedUser.getSalt();
199 final var passwordHash = passwordService.getPasswordHash(user.getPassword(), salt);
200 savedUser.setPassword(passwordHash.getHashedPassword());
203 try (var conn = dbConnect();
204 var stmt = conn.prepareStatement("UPDATE " + TABLE + " SET " + COL_EMAIL + " = ?, "
205 + COL_PASSWORD + " = ?," + COL_DESC + " = ?, " + COL_ENABLED + "= ? WHERE " + COL_ID + " = ?")) {
206 stmt.setString(1, savedUser.getEmail());
207 stmt.setString(2, savedUser.getPassword());
208 stmt.setString(3, savedUser.getDescription());
209 stmt.setBoolean(4, savedUser.isEnabled());
210 stmt.setString(5, savedUser.getUserid());
211 LOG.debug("putUser() request: {}", stmt);
213 stmt.executeUpdate();
214 } catch (SQLException s) {
215 throw new StoreException("SQL Exception : " + s);
221 User deleteUser(final String userid) throws StoreException {
222 final var savedUser = getUser(userid);
223 if (savedUser == null) {
227 try (var conn = dbConnect();
228 var stmt = conn.prepareStatement("DELETE FROM " + TABLE + " WHERE " + COL_ID + " = ?")) {
229 stmt.setString(1, userid);
230 LOG.debug("deleteUser() request: {}", stmt);
232 int deleteCount = stmt.executeUpdate();
233 LOG.debug("deleted {} records", deleteCount);
235 } catch (SQLException s) {
236 throw new StoreException("SQL Exception : " + s);