2 * Copyright (c) 2014, 2015 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
9 package org.opendaylight.aaa.h2.persistence;
11 import com.google.common.base.Preconditions;
13 import java.sql.Connection;
14 import java.sql.DatabaseMetaData;
15 import java.sql.PreparedStatement;
16 import java.sql.ResultSet;
17 import java.sql.SQLException;
18 import java.sql.Statement;
19 import java.util.ArrayList;
20 import java.util.List;
22 import org.opendaylight.aaa.api.IDMStoreUtil;
23 import org.opendaylight.aaa.api.SHA256Calculator;
24 import org.opendaylight.aaa.api.model.User;
25 import org.opendaylight.aaa.api.model.Users;
26 import org.slf4j.Logger;
27 import org.slf4j.LoggerFactory;
31 * @author peter.mellquist@hp.com
34 public class UserStore {
35 private static final Logger LOG = LoggerFactory.getLogger(UserStore.class);
36 protected Connection dbConnection = null;
37 protected final static String SQL_ID = "userid";
38 protected final static String SQL_DOMAIN_ID = "domainid";
39 protected final static String SQL_NAME = "name";
40 protected final static String SQL_EMAIL = "email";
41 protected final static String SQL_PASSWORD = "password";
42 protected final static String SQL_DESCR = "description";
43 protected final static String SQL_ENABLED = "enabled";
44 protected final static String SQL_SALT = "salt";
45 public final static int MAX_FIELD_LEN = 128;
47 protected UserStore() {
50 protected Connection getDBConnect() throws StoreException {
51 dbConnection = H2Store.getConnection(dbConnection);
55 protected void dbClean() throws StoreException, SQLException {
56 Connection c = dbConnect();
57 String sql = "delete from users where true";
58 c.createStatement().execute(sql);
62 protected Connection dbConnect() throws StoreException {
65 conn = getDBConnect();
66 } catch (StoreException se) {
70 DatabaseMetaData dbm = conn.getMetaData();
71 String[] tableTypes = { "TABLE" };
72 ResultSet rs = dbm.getTables(null, null, "USERS", tableTypes);
74 LOG.debug("users Table already exists");
76 LOG.info("users Table does not exist, creating table");
77 Statement stmt = null;
78 stmt = conn.createStatement();
79 String sql = "CREATE TABLE users " + "(userid VARCHAR(128) PRIMARY KEY,"
80 + "name VARCHAR(128) NOT NULL, "
81 + "domainid VARCHAR(128) NOT NULL, "
82 + "email VARCHAR(128) NOT NULL, "
83 + "password VARCHAR(128) NOT NULL, "
84 + "description VARCHAR(128) NOT NULL, "
85 + "salt VARCHAR(15) NOT NULL, "
86 + "enabled INTEGER NOT NULL)";
87 stmt.executeUpdate(sql);
90 } catch (SQLException sqe) {
91 throw new StoreException("Cannot connect to database server " + sqe);
96 protected void dbClose() {
97 if (dbConnection != null) {
100 } catch (Exception e) {
101 LOG.error("Cannot close Database Connection", e);
107 protected void finalize() throws Throwable {
112 protected User rsToUser(ResultSet rs) throws SQLException {
113 User user = new User();
115 user.setUserid(rs.getString(SQL_ID));
116 user.setDomainid(rs.getString(SQL_DOMAIN_ID));
117 user.setName(rs.getString(SQL_NAME));
118 user.setEmail(rs.getString(SQL_EMAIL));
119 user.setPassword(rs.getString(SQL_PASSWORD));
120 user.setDescription(rs.getString(SQL_DESCR));
121 user.setEnabled(rs.getInt(SQL_ENABLED) == 1 ? true : false);
122 user.setSalt(rs.getString(SQL_SALT));
123 } catch (SQLException sqle) {
124 LOG.error("SQL Exception: ", sqle);
130 protected Users getUsers() throws StoreException {
131 Users users = new Users();
132 List<User> userList = new ArrayList<User>();
133 Connection conn = dbConnect();
134 Statement stmt = null;
135 String query = "SELECT * FROM users";
137 stmt = conn.createStatement();
138 ResultSet rs = stmt.executeQuery(query);
140 User user = rsToUser(rs);
145 } catch (SQLException s) {
146 throw new StoreException("SQL Exception");
150 users.setUsers(userList);
154 protected Users getUsers(String username, String domain) throws StoreException {
155 LOG.debug("getUsers for: {} in domain {}", username, domain);
157 Users users = new Users();
158 List<User> userList = new ArrayList<User>();
159 Connection conn = dbConnect();
161 PreparedStatement pstmt = conn
162 .prepareStatement("SELECT * FROM USERS WHERE userid = ? ");
163 pstmt.setString(1, IDMStoreUtil.createUserid(username, domain));
164 LOG.debug("query string: {}", pstmt.toString());
165 ResultSet rs = pstmt.executeQuery();
167 User user = rsToUser(rs);
172 } catch (SQLException s) {
173 throw new StoreException("SQL Exception : " + s);
177 users.setUsers(userList);
181 protected User getUser(String id) throws StoreException {
182 Connection conn = dbConnect();
184 PreparedStatement pstmt = conn
185 .prepareStatement("SELECT * FROM USERS WHERE userid = ? ");
186 pstmt.setString(1, id);
187 LOG.debug("query string: {}", pstmt.toString());
188 ResultSet rs = pstmt.executeQuery();
190 User user = rsToUser(rs);
199 } catch (SQLException s) {
200 throw new StoreException("SQL Exception : " + s);
206 protected User createUser(User user) throws StoreException {
207 Preconditions.checkNotNull(user);
208 Preconditions.checkNotNull(user.getName());
209 Preconditions.checkNotNull(user.getDomainid());
211 Connection conn = dbConnect();
213 user.setSalt(SHA256Calculator.generateSALT());
214 String query = "insert into users (userid,domainid,name,email,password,description,enabled,salt) values(?,?,?,?,?,?,?,?)";
215 PreparedStatement statement = conn.prepareStatement(query);
216 user.setUserid(IDMStoreUtil.createUserid(user.getName(), user.getDomainid()));
217 statement.setString(1, user.getUserid());
218 statement.setString(2, user.getDomainid());
219 statement.setString(3, user.getName());
220 statement.setString(4, user.getEmail());
221 statement.setString(5, SHA256Calculator.getSHA256(user.getPassword(), user.getSalt()));
222 statement.setString(6, user.getDescription());
223 statement.setInt(7, user.isEnabled() ? 1 : 0);
224 statement.setString(8, user.getSalt());
225 int affectedRows = statement.executeUpdate();
226 if (affectedRows == 0) {
227 throw new StoreException("Creating user failed, no rows affected.");
230 } catch (SQLException s) {
231 throw new StoreException("SQL Exception : " + s);
237 protected User putUser(User user) throws StoreException {
239 User savedUser = this.getUser(user.getUserid());
240 if (savedUser == null) {
244 if (user.getDescription() != null) {
245 savedUser.setDescription(user.getDescription());
247 if (user.getName() != null) {
248 savedUser.setName(user.getName());
250 if (user.isEnabled() != null) {
251 savedUser.setEnabled(user.isEnabled());
253 if (user.getEmail() != null) {
254 savedUser.setEmail(user.getEmail());
256 if (user.getPassword() != null) {
257 savedUser.setPassword(SHA256Calculator.getSHA256(user.getPassword(), user.getSalt()));
260 Connection conn = dbConnect();
262 String query = "UPDATE users SET email = ?, password = ?, description = ?, enabled = ? WHERE userid = ?";
263 PreparedStatement statement = conn.prepareStatement(query);
264 statement.setString(1, savedUser.getEmail());
265 statement.setString(2, savedUser.getPassword());
266 statement.setString(3, savedUser.getDescription());
267 statement.setInt(4, savedUser.isEnabled() ? 1 : 0);
268 statement.setString(5, savedUser.getUserid());
269 statement.executeUpdate();
271 } catch (SQLException s) {
272 throw new StoreException("SQL Exception : " + s);
280 protected User deleteUser(String userid) throws StoreException {
281 User savedUser = this.getUser(userid);
282 if (savedUser == null) {
286 Connection conn = dbConnect();
288 String query = "DELETE FROM USERS WHERE userid = ?";
289 PreparedStatement statement = conn.prepareStatement(query);
290 statement.setString(1, savedUser.getUserid());
291 int deleteCount = statement.executeUpdate(query);
292 LOG.debug("deleted {} records", deleteCount);
295 } catch (SQLException s) {
296 throw new StoreException("SQL Exception : " + s);