1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20 package Utilities;
21
22 import java.sql.Connection;
23 import java.sql.PreparedStatement;
24 import java.sql.ResultSet;
25 import java.sql.SQLException;
26 import java.util.*;
27
28 public class User {
29 private Utilities.Settable<ArrayList<String>> uManage = new Utilities.Settable(null, null);
30 private Utilities.Settable<Set<String>> years = new Utilities.Settable(null, null);
31 private Utilities.Settable<String> fullName = new Utilities.Settable("", "");
32 private Utilities.Settable<String> userName = new Utilities.Settable("", "");
33 private Utilities.Settable<Integer> groupId = new Utilities.Settable(-1, -1);
34 private Utilities.Settable<Integer> ACL = new Utilities.Settable(-1, -1);
35 private Utilities.Settable<Boolean> Admin = new Utilities.Settable(null, null);
36
37
38
39
40
41
42
43
44
45 public User(String userName, String year, Boolean admin) {
46 this.userName.set(userName);
47 try (Connection con = DbInt.getConnection(year);
48 PreparedStatement prep = con.prepareStatement("SELECT * FROM " + (admin ? "users" : "usersview") + " where userName=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
49 prep.setString(1, userName);
50 try (ResultSet rs = prep.executeQuery()) {
51 if (rs.next()) {
52 this.fullName.set(rs.getString("fullName"));
53 List<String> retL = new ArrayList<String>(Arrays.asList(rs.getString("uManage").split("\\s*,\\s*")));
54 retL.forEach(uName -> {
55 if (!uName.isEmpty()) {
56 ArrayList<String> uMan = this.uManage.orElseGet(() -> {return new ArrayList<String>();});
57 uMan.add(uName);
58 this.uManage.set(uMan);
59 }
60 });
61 this.groupId.set(rs.getInt("groupId"));
62 this.Admin.set(rs.getInt("Admin") > 0);
63 }
64 }
65 } catch (SQLException e) {
66 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
67 }
68 String csvRet = "";
69 Collection<String> ret = new ArrayList<>();
70
71 try (Connection con = DbInt.getConnection("Commons");
72 PreparedStatement prep = con.prepareStatement("SELECT YEARS FROM Users where userName=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
73 prep.setString(1, userName);
74 try (ResultSet rs = prep.executeQuery()) {
75
76 while (rs.next()) {
77
78 csvRet = (rs.getString("YEARS"));
79
80 }
81 }
82
83
84 } catch (SQLException e) {
85 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
86 }
87
88 List<String> yearsL = new ArrayList<String>(Arrays.asList(csvRet.split("\\s*,\\s*")));
89 yearsL.forEach(yearIt -> {
90 if (!yearIt.isEmpty()) {
91 HashSet<String> uMan = (HashSet<String>) this.years.orElseGet(() -> {return new HashSet<String>();});
92 uMan.add(yearIt);
93 this.years.set(uMan);
94 } else {
95 this.years.setIfNot(new HashSet<String>());
96
97 }
98 });
99
100 }
101
102 public User(String year) {
103 this(DbInt.getUserName(), year);
104
105 }
106
107 public User(String userName, String year) {
108 this(userName, year, DbInt.isAdmin());
109 }
110
111 public User(String userName, String fullName, ArrayList<String> uManage, Set<String> years, Boolean admin, int groupId) {
112 this.userName.set(userName);
113 this.fullName.set(fullName);
114 this.uManage.set(uManage);
115 this.groupId.set(groupId);
116 this.years.set(years);
117 this.Admin.set(admin);
118 }
119
120 public User(String userName, String fullName, ArrayList<String> uManage, int groupId) {
121 this(userName, fullName, uManage, null, groupId);
122 }
123
124 public User(String userName, String fullName, ArrayList<String> uManage, Set<String> years, int groupId) {
125 this(userName, fullName, uManage, null, false, groupId);
126 }
127
128 public User(String userName, String fullName, String uManage, String years, boolean admin, int groupId) {
129 this.userName.set(userName);
130 this.fullName.set(fullName);
131 List<String> retL = new ArrayList<String>(Arrays.asList(uManage.split("\\s*,\\s*")));
132 retL.forEach(uName -> {
133 if (!uName.isEmpty()) {
134 ArrayList<String> uMan = this.uManage.orElseGet(() -> {return new ArrayList<String>();});
135 uMan.add(uName);
136 this.uManage.set(uMan);
137
138 }
139 });
140 List<String> yearsL = new ArrayList<String>(Arrays.asList(years.split("\\s*,\\s*")));
141 yearsL.forEach(yearIt -> {
142 if (!yearIt.isEmpty()) {
143 this.years.orElseGet(() -> {return new HashSet<>();}).add(yearIt);
144 HashSet<String> uMan = (HashSet<String>) this.years.orElseGet(() -> {return new HashSet<String>();});
145 uMan.add(yearIt);
146 this.years.set(uMan);
147 }
148 });
149 this.groupId.set(groupId);
150 this.Admin.set(admin);
151 }
152
153 public User(String userName, String fullName, String uManage, int groupId) {
154 this(userName, fullName, uManage, "", groupId);
155
156 }
157
158 public User(String userName, String fullName, String years, boolean admin) {
159 this(userName, fullName, "", years, admin, 1);
160
161 }
162
163 public User(String userName, String fullName, String uManage, String years, int groupId) {
164 this(userName, fullName, uManage, years, false, groupId);
165 }
166
167 public void deleteFromYear(String year) {
168 if (years.get().remove(year) && !isAdmin()) {
169 String[] createAndGrantCommand = {"REVOKE SELECT, INSERT, UPDATE, DELETE ON `" + DbInt.prefix + year + "`.customerview FROM '" + userName + "'@'%'",
170 "REVOKE SELECT, INSERT, UPDATE, DELETE ON `" + DbInt.prefix + year + "`.orderedproductsview FROM '" + userName + "'@'%'",
171 "REVOKE SELECT, INSERT, UPDATE, DELETE ON `" + DbInt.prefix + year + "`.ordersview FROM '" + userName + "'@'%'",
172 "REVOKE SELECT, INSERT, UPDATE, DELETE ON `" + DbInt.prefix + year + "`.usersview FROM '" + userName + "'@'%'",
173 "REVOKE SELECT ON `" + DbInt.prefix + year + "`.products FROM '" + userName + "'@'%'",
174 "REVOKE SELECT ON `" + DbInt.prefix + year + "`.groups FROM '" + userName + "'@'%'",
175 "REVOKE SELECT ON `" + DbInt.prefix + year + "`.categories FROM '" + userName + "'@'%'"};
176 try (Connection con = DbInt.getConnection();
177 PreparedStatement prep = con.prepareStatement("", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
178
179 prep.addBatch(createAndGrantCommand[0]);
180 prep.addBatch(createAndGrantCommand[1]);
181 prep.addBatch(createAndGrantCommand[2]);
182 prep.addBatch(createAndGrantCommand[3]);
183 prep.addBatch(createAndGrantCommand[4]);
184 prep.addBatch(createAndGrantCommand[5]);
185 prep.addBatch(createAndGrantCommand[6]);
186
187 prep.executeBatch();
188 } catch (SQLException e) {
189 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
190 }
191 }
192 try (Connection con = DbInt.getConnection("Commons");
193 PreparedStatement prep = con.prepareStatement("UPDATE Users SET Years=? WHERE userName=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
194 prep.setString(1, arrayToCSV(getYears()));
195 prep.setString(2, getUserName());
196 prep.execute();
197 } catch (SQLException e) {
198 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
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 try (Connection con = DbInt.getConnection(year);
224 PreparedStatement prep = con.prepareStatement("DELETE FROM users WHERE userName=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
225 prep.setString(1, getUserName());
226
227
228 prep.execute();
229 } catch (SQLException e) {
230 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
231 }
232
233 }
234
235 public static User createUser(String uName, String password, String fullName, Boolean admin) {
236 String createAndGrantCommand = "CREATE USER '" + uName + "'@'%' IDENTIFIED BY '" + password + "'";
237 try (Connection con = DbInt.getConnection();
238 PreparedStatement prep = con.prepareStatement("", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
239
240 prep.addBatch(createAndGrantCommand);
241 prep.addBatch("GRANT SELECT ON `" + DbInt.prefix + "Commons`.* TO '" + uName + "'@'%'");
242 if (admin) {
243 prep.addBatch("GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, RELOAD, INDEX, ALTER, SHOW DATABASES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, TRIGGER, SUPER ON *.* TO '" + uName + "'@'%' WITH GRANT OPTION");
244 }
245 prep.executeBatch();
246
247 } catch (SQLException e) {
248 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
249 }
250 try (Connection con = DbInt.getConnection("Commons");
251 PreparedStatement prep = con.prepareStatement("INSERT INTO Users(userName, fullName, Admin, Years) Values (?,?,?, ?)", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
252 prep.setString(1, uName);
253 prep.setString(2, fullName);
254 prep.setInt(3, admin ? 1 : 0);
255 prep.setString(4, "");
256
257 prep.execute();
258 } catch (SQLException e) {
259 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
260 }
261 return new User(uName, fullName, "", admin);
262
263 }
264
265 public static void updateUser(String uName, String password, String fullName, Boolean admin) {
266 if (admin) {
267
268 try (Connection con = DbInt.getConnection();
269 PreparedStatement prep = con.prepareStatement("GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, RELOAD, INDEX, ALTER, SHOW DATABASES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, TRIGGER, SUPER ON *.* TO '" + uName + "'@'%' WITH GRANT OPTION", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
270
271
272 prep.executeBatch();
273
274 } catch (SQLException e) {
275 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
276 }
277 }
278 try (Connection con = DbInt.getConnection("Commons");
279 PreparedStatement prep = con.prepareStatement("UPDATE Users SET fullName=?, Admin=?, Years=? WHERE userName=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
280 prep.setString(1, fullName);
281 prep.setInt(2, admin ? 1 : 0);
282 prep.setString(3, "");
283 prep.setString(4, uName);
284
285 prep.execute();
286 } catch (SQLException e) {
287 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
288 }
289
290 String createAndGrantCommand;
291 if (DbInt.getDatabaseVersion().greaterThanOrEqual("5.7")) {
292 createAndGrantCommand = "ALTER USER '" + uName + "'@'%' IDENTIFIED BY '" + password + "'";
293 } else {
294 createAndGrantCommand = "SET PASSWORD FOR '" + uName + "'@'%' = PASSWORD('" + password + "')";
295 }
296 try (Connection con = DbInt.getConnection();
297 PreparedStatement prep = con.prepareStatement("", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
298 prep.addBatch(createAndGrantCommand);
299 prep.executeBatch();
300
301 } catch (SQLException e) {
302 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
303 }
304
305
306 }
307
308 public void addToYear(String year) {
309 String[] createAndGrantCommand = {"GRANT SELECT, INSERT, UPDATE, DELETE ON `" + DbInt.prefix + year + "`.customerview TO '" + userName + "'@'%'",
310 "GRANT SELECT, INSERT, UPDATE, DELETE ON `" + DbInt.prefix + year + "`.orderedproductsview TO '" + userName + "'@'%'",
311 "GRANT SELECT, INSERT, UPDATE, DELETE ON `" + DbInt.prefix + year + "`.ordersview TO '" + userName + "'@'%'",
312 "GRANT SELECT, INSERT, UPDATE, DELETE ON `" + DbInt.prefix + year + "`.usersview TO '" + userName + "'@'%'",
313 "GRANT SELECT ON `" + DbInt.prefix + year + "`.products TO '" + userName + "'@'%'",
314 "GRANT SELECT ON `" + DbInt.prefix + year + "`.groups TO '" + userName + "'@'%'",
315 "GRANT SELECT ON `" + DbInt.prefix + year + "`.categories TO '" + userName + "'@'%'"};
316 try (Connection con = DbInt.getConnection();
317 PreparedStatement prep = con.prepareStatement("", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
318
319 prep.addBatch(createAndGrantCommand[0]);
320 prep.addBatch(createAndGrantCommand[1]);
321 prep.addBatch(createAndGrantCommand[2]);
322 prep.addBatch(createAndGrantCommand[3]);
323 prep.addBatch(createAndGrantCommand[4]);
324 prep.addBatch(createAndGrantCommand[5]);
325 prep.addBatch(createAndGrantCommand[6]);
326
327 prep.executeBatch();
328 } catch (SQLException e) {
329 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
330 }
331 try (Connection con = DbInt.getConnection("Commons");
332 PreparedStatement prep = con.prepareStatement("UPDATE Users SET Years=CONCAT(Years, ',', ?) WHERE userName=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
333 prep.setString(1, year);
334 prep.setString(2, getUserName());
335 prep.execute();
336 } catch (SQLException e) {
337 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
338 }
339 Integer CommonsID = 0;
340 try (Connection con = DbInt.getConnection("Commons");
341 PreparedStatement prep = con.prepareStatement("SELECT idUsers FROM Users where userName=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
342 prep.setString(1, getUserName());
343 try (ResultSet rs = prep.executeQuery()) {
344 rs.next();
345 CommonsID = rs.getInt("idUsers");
346 }
347 } catch (SQLException e) {
348 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
349 }
350
351 try (Connection con = DbInt.getConnection(year);
352 PreparedStatement prep = con.prepareStatement("INSERT INTO users(userName, fullName, uManage, Admin, commonsID, groupId) VALUES(?,?,?,?,?,?)", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
353 prep.setString(1, getUserName());
354 prep.setString(2, getFullName());
355 prep.setString(3, arrayToCSV(getuManage()));
356 prep.setInt(4, isAdmin() ? 1 : 0);
357 prep.setInt(5, CommonsID);
358 prep.setInt(6, getGroupId());
359 prep.execute();
360 } catch (SQLException e) {
361 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
362 }
363 }
364
365 public void updateYear(String year) {
366 String[] createAndGrantCommand = {"GRANT SELECT, INSERT, UPDATE, DELETE ON `" + DbInt.prefix + year + "`.customerview TO '" + userName + "'@'%'",
367 "GRANT SELECT, INSERT, UPDATE, DELETE ON `" + DbInt.prefix + year + "`.orderedproductsview TO '" + userName + "'@'%'",
368 "GRANT SELECT, INSERT, UPDATE, DELETE ON `" + DbInt.prefix + year + "`.ordersview TO '" + userName + "'@'%'",
369 "GRANT SELECT, INSERT, UPDATE, DELETE ON `" + DbInt.prefix + year + "`.usersview TO '" + userName + "'@'%'",
370 "GRANT SELECT ON `" + DbInt.prefix + year + "`.products TO '" + userName + "'@'%'",
371 "GRANT SELECT ON `" + DbInt.prefix + year + "`.groups TO '" + userName + "'@'%'",
372 "GRANT SELECT ON `" + DbInt.prefix + year + "`.categories TO '" + userName + "'@'%'"};
373 try (Connection con = DbInt.getConnection();
374 PreparedStatement prep = con.prepareStatement("", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
375
376 prep.addBatch(createAndGrantCommand[0]);
377 prep.addBatch(createAndGrantCommand[1]);
378 prep.addBatch(createAndGrantCommand[2]);
379 prep.addBatch(createAndGrantCommand[3]);
380 prep.addBatch(createAndGrantCommand[4]);
381 prep.addBatch(createAndGrantCommand[5]);
382 prep.addBatch(createAndGrantCommand[6]);
383
384 prep.executeBatch();
385 } catch (SQLException e) {
386 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
387 }
388 try (Connection con = DbInt.getConnection("Commons");
389 PreparedStatement prep = con.prepareStatement("UPDATE Users SET Years=?, fullName=?, Admin=? WHERE userName=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
390 prep.setString(1, arrayToCSV(getYears()));
391 prep.setString(2, getFullName());
392 prep.setInt(3, isAdmin() ? 1 : 0);
393 prep.setString(4, getUserName());
394 prep.execute();
395 } catch (SQLException e) {
396 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
397 }
398 Integer CommonsID = 0;
399 try (Connection con = DbInt.getConnection("Commons");
400 PreparedStatement prep = con.prepareStatement("SELECT idUsers FROM Users where userName=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
401 prep.setString(1, getUserName());
402 try (ResultSet rs = prep.executeQuery()) {
403 rs.next();
404 CommonsID = rs.getInt("idUsers");
405 }
406 } catch (SQLException e) {
407 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
408 }
409 String oldUName = getUserName();
410 try (Connection con = DbInt.getConnection(year);
411 PreparedStatement prep = con.prepareStatement("SELECT userName FROM users where commonsID=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
412 prep.setInt(1, CommonsID);
413 try (ResultSet rs = prep.executeQuery()) {
414 if (rs.next()) {
415 oldUName = rs.getString("userName");
416 }
417 }
418 } catch (SQLException e) {
419 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
420 }
421
422 try (Connection con = DbInt.getConnection(year);
423 PreparedStatement prep = con.prepareStatement("INSERT INTO users(userName, fullName, uManage, Admin, commonsID, groupId, ACL) VALUES(?,?,?,?,?,?, 1) " +
424 "ON DUPLICATE KEY UPDATE userName=?, fullName=?, uManage=?, Admin=?, commonsID=?, groupId=?, ACL=1", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
425 prep.setString(1, getUserName());
426 prep.setString(2, getFullName());
427 prep.setString(3, arrayToCSV(getuManage()));
428 prep.setInt(4, isAdmin() ? 1 : 0);
429 prep.setInt(5, CommonsID);
430 prep.setInt(6, getGroupId());
431 prep.setString(7, getUserName());
432 prep.setString(8, getFullName());
433 prep.setString(9, arrayToCSV(getuManage()));
434 prep.setInt(10, isAdmin() ? 1 : 0);
435 prep.setInt(11, CommonsID);
436 prep.setInt(12, getGroupId());
437 prep.execute();
438 } catch (SQLException e) {
439 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
440 }
441 try (Connection con = DbInt.getConnection(year);
442 PreparedStatement prep = con.prepareStatement("UPDATE users SET uManage = REPLACE (uManage, ?, ?)", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
443 prep.setString(1, oldUName);
444 prep.setString(2, getUserName());
445 prep.execute();
446 } catch (SQLException e) {
447 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
448 }
449 }
450
451 public enum STATUS {ENABLED, DISABLED, ARCHIVED}
452
453 public String toString() {
454 return getFullName() + " (" + getFullName() + ")";
455 }
456
457 public boolean isAdmin() {
458 return Admin.get();
459 }
460
461 public void setAdmin(Boolean admin) {
462 Admin.set(admin);
463 }
464
465 public String getUserName() {
466 return userName.get();
467 }
468
469 public void setUserName(String userName) {
470 this.userName.set(userName);
471 }
472
473 public String getFullName() {
474 return fullName.get();
475 }
476
477 public void setFullName(String fullName) {
478 this.fullName.set(fullName);
479 }
480
481 public ArrayList<String> getuManage() {
482 return uManage.get();
483 }
484
485 public void setuManage(ArrayList<String> uManage) {
486 this.uManage.set(uManage);
487 }
488
489 public Set<String> getYears() {
490 return years.get();
491 }
492
493 public void setYears(Set<String> years) {
494 this.years.set(years);
495 }
496
497 public int getGroupId() {
498 return groupId.get();
499 }
500
501 public void setGroupId(int groupId) {
502 this.groupId.set(groupId);
503 }
504
505 private String arrayToCSV(Collection<String> array) {
506 final String[] ret = {""};
507 array.forEach(value -> {
508 if (!ret[0].isEmpty()) {
509 ret[0] = ret[0] + "," + value;
510 } else {
511 ret[0] = value;
512 }
513 });
514 return ret[0];
515 }
516
517 public boolean equals(Object obj) {
518 if (obj == this) {
519 return true;
520 }
521 if (!(obj instanceof User)) {
522 return false;
523 }
524 User other = (User) obj;
525 return this.getUserName().equals(other.getUserName());
526 }
527
528 public int hashCode() {
529 return getUserName().hashCode();
530 }
531 }