1 package org.cacert.gigi.dbObjects;
4 import java.util.ArrayList;
5 import java.util.Calendar;
6 import java.util.Collections;
7 import java.util.HashSet;
8 import java.util.LinkedList;
10 import java.util.Locale;
13 import org.cacert.gigi.GigiApiException;
14 import org.cacert.gigi.database.GigiPreparedStatement;
15 import org.cacert.gigi.database.GigiResultSet;
16 import org.cacert.gigi.localisation.Language;
17 import org.cacert.gigi.output.DateSelector;
18 import org.cacert.gigi.util.Notary;
19 import org.cacert.gigi.util.PasswordHash;
20 import org.cacert.gigi.util.PasswordStrengthChecker;
22 public class User extends CertificateOwner {
24 private Name name = new Name(null, null, null, null);
30 private Assurance[] receivedAssurances;
32 private Assurance[] madeAssurances;
34 private Locale locale;
36 private final Set<Group> groups = new HashSet<>();
38 protected User(GigiResultSet rs) {
39 super(rs.getInt("id"));
43 private void updateName(GigiResultSet rs) {
44 name = new Name(rs.getString("fname"), rs.getString("lname"), rs.getString("mname"), rs.getString("suffix"));
45 dob = rs.getDate("dob");
46 email = rs.getString("email");
48 String localeStr = rs.getString("language");
49 if (localeStr == null || localeStr.equals("")) {
50 locale = Locale.getDefault();
52 locale = Language.getLocaleFromString(localeStr);
55 try (GigiPreparedStatement psg = new GigiPreparedStatement("SELECT `permission` FROM `user_groups` WHERE `user`=? AND `deleted` is NULL")) {
56 psg.setInt(1, rs.getInt("id"));
58 try (GigiResultSet rs2 = psg.executeQuery()) {
60 groups.add(Group.getByString(rs2.getString(1)));
66 public User(String email, String password, Name name, Date dob, Locale locale) throws GigiApiException {
71 try (GigiPreparedStatement query = new GigiPreparedStatement("INSERT INTO `users` SET `email`=?, `password`=?, " + "`fname`=?, `mname`=?, `lname`=?, " + "`suffix`=?, `dob`=?, `language`=?, id=?")) {
72 query.setString(1, email);
73 query.setString(2, PasswordHash.hash(password));
74 query.setString(3, name.getFname());
75 query.setString(4, name.getMname());
76 query.setString(5, name.getLname());
77 query.setString(6, name.getSuffix());
78 query.setDate(7, dob);
79 query.setString(8, locale.toString());
80 query.setInt(9, getId());
83 new EmailAddress(this, email, locale);
86 public Name getName() {
90 public Date getDoB() {
94 public void setDoB(Date dob) {
98 public String getEmail() {
102 public void changePassword(String oldPass, String newPass) throws GigiApiException {
103 try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `password` FROM `users` WHERE `id`=?")) {
104 ps.setInt(1, getId());
105 try (GigiResultSet rs = ps.executeQuery()) {
107 throw new GigiApiException("User not found... very bad.");
109 if (PasswordHash.verifyHash(oldPass, rs.getString(1)) == null) {
110 throw new GigiApiException("Old password does not match.");
114 setPassword(newPass);
117 private void setPassword(String newPass) throws GigiApiException {
118 PasswordStrengthChecker.assertStrongPassword(newPass, getName(), getEmail());
119 try (GigiPreparedStatement ps = new GigiPreparedStatement("UPDATE users SET `password`=? WHERE id=?")) {
120 ps.setString(1, PasswordHash.hash(newPass));
121 ps.setInt(2, getId());
126 public void setName(Name name) {
130 public boolean canAssure() {
131 if ( !isOfAge(14)) { // PoJAM
134 if (getAssurancePoints() < 100) {
138 return hasPassedCATS();
142 public boolean hasPassedCATS() {
143 try (GigiPreparedStatement query = new GigiPreparedStatement("SELECT 1 FROM `cats_passed` where `user_id`=? AND `variant_id`=?")) {
144 query.setInt(1, getId());
145 query.setInt(2, CATS.ASSURER_CHALLANGE_ID);
146 try (GigiResultSet rs = query.executeQuery()) {
156 public int getAssurancePoints() {
157 try (GigiPreparedStatement query = new GigiPreparedStatement("SELECT sum(points) FROM `notary` where `to`=? AND `deleted` is NULL AND (`expire` IS NULL OR `expire` > CURRENT_TIMESTAMP)")) {
158 query.setInt(1, getId());
160 GigiResultSet rs = query.executeQuery();
164 points = rs.getInt(1);
171 public int getExperiencePoints() {
172 try (GigiPreparedStatement query = new GigiPreparedStatement("SELECT count(*) FROM `notary` where `from`=? AND `deleted` is NULL")) {
173 query.setInt(1, getId());
175 GigiResultSet rs = query.executeQuery();
179 points = rs.getInt(1) * 2;
187 * Gets the maximum allowed points NOW. Note that an assurance needs to
188 * re-check PoJam as it has taken place in the past.
190 * @return the maximal points @
192 public int getMaxAssurePoints() {
197 int exp = getExperiencePoints();
219 public boolean isOfAge(int desiredAge) {
220 Calendar c = Calendar.getInstance();
222 int year = c.get(Calendar.YEAR);
223 int month = c.get(Calendar.MONTH);
224 int day = c.get(Calendar.DAY_OF_MONTH);
225 c.set(year, month, day);
226 c.add(Calendar.YEAR, desiredAge);
227 return System.currentTimeMillis() >= c.getTime().getTime();
230 public boolean isValidName(String name) {
231 return getName().matches(name);
234 public void updateDefaultEmail(EmailAddress newMail) throws GigiApiException {
235 for (EmailAddress email : getEmails()) {
236 if (email.getAddress().equals(newMail.getAddress())) {
237 if ( !email.isVerified()) {
238 throw new GigiApiException("Email not verified.");
241 try (GigiPreparedStatement ps = new GigiPreparedStatement("UPDATE users SET email=? WHERE id=?")) {
242 ps.setString(1, newMail.getAddress());
243 ps.setInt(2, getId());
247 this.email = newMail.getAddress();
252 throw new GigiApiException("Given address not an address of the user.");
255 public void deleteEmail(EmailAddress delMail) throws GigiApiException {
256 if (getEmail().equals(delMail.getAddress())) {
257 throw new GigiApiException("Can't delete user's default e-mail.");
260 for (EmailAddress email : getEmails()) {
261 if (email.getId() == delMail.getId()) {
262 try (GigiPreparedStatement ps = new GigiPreparedStatement("UPDATE `emails` SET `deleted`=CURRENT_TIMESTAMP WHERE `id`=?")) {
263 ps.setInt(1, delMail.getId());
269 throw new GigiApiException("Email not one of user's email addresses.");
272 public synchronized Assurance[] getReceivedAssurances() {
273 if (receivedAssurances == null) {
274 try (GigiPreparedStatement query = new GigiPreparedStatement("SELECT * FROM `notary` WHERE `to`=? AND `deleted` IS NULL")) {
275 query.setInt(1, getId());
277 GigiResultSet res = query.executeQuery();
278 List<Assurance> assurances = new LinkedList<Assurance>();
281 assurances.add(assuranceByRes(res));
284 this.receivedAssurances = assurances.toArray(new Assurance[0]);
288 return receivedAssurances;
291 public synchronized Assurance[] getMadeAssurances() {
292 if (madeAssurances == null) {
293 try (GigiPreparedStatement query = new GigiPreparedStatement("SELECT * FROM notary WHERE `from`=? AND deleted is NULL")) {
294 query.setInt(1, getId());
296 try (GigiResultSet res = query.executeQuery()) {
297 List<Assurance> assurances = new LinkedList<Assurance>();
300 assurances.add(assuranceByRes(res));
303 this.madeAssurances = assurances.toArray(new Assurance[0]);
308 return madeAssurances;
311 public synchronized void invalidateMadeAssurances() {
312 madeAssurances = null;
315 public synchronized void invalidateReceivedAssurances() {
316 receivedAssurances = null;
319 public void updateUserData() throws GigiApiException {
320 synchronized (Notary.class) {
321 if (getReceivedAssurances().length != 0) {
322 throw new GigiApiException("No change after assurance allowed.");
328 protected void rawUpdateUserData() {
329 try (GigiPreparedStatement update = new GigiPreparedStatement("UPDATE users SET fname=?, lname=?, mname=?, suffix=?, dob=? WHERE id=?")) {
330 update.setString(1, name.getFname());
331 update.setString(2, name.getLname());
332 update.setString(3, name.getMname());
333 update.setString(4, name.getSuffix());
334 update.setDate(5, getDoB());
335 update.setInt(6, getId());
336 update.executeUpdate();
340 public Locale getPreferredLocale() {
344 public void setPreferredLocale(Locale locale) {
345 this.locale = locale;
349 public boolean wantsDirectoryListing() {
350 try (GigiPreparedStatement get = new GigiPreparedStatement("SELECT listme FROM users WHERE id=?")) {
351 get.setInt(1, getId());
352 GigiResultSet exec = get.executeQuery();
353 return exec.next() && exec.getBoolean("listme");
357 public String getContactInformation() {
358 try (GigiPreparedStatement get = new GigiPreparedStatement("SELECT contactinfo FROM users WHERE id=?")) {
359 get.setInt(1, getId());
361 GigiResultSet exec = get.executeQuery();
363 return exec.getString("contactinfo");
367 public void setDirectoryListing(boolean on) {
368 try (GigiPreparedStatement update = new GigiPreparedStatement("UPDATE users SET listme = ? WHERE id = ?")) {
369 update.setBoolean(1, on);
370 update.setInt(2, getId());
371 update.executeUpdate();
375 public void setContactInformation(String contactInfo) {
376 try (GigiPreparedStatement update = new GigiPreparedStatement("UPDATE users SET contactinfo = ? WHERE id = ?")) {
377 update.setString(1, contactInfo);
378 update.setInt(2, getId());
379 update.executeUpdate();
383 public boolean isInGroup(Group g) {
384 return groups.contains(g);
387 public Set<Group> getGroups() {
388 return Collections.unmodifiableSet(groups);
391 public void grantGroup(User granter, Group toGrant) {
393 try (GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO `user_groups` SET `user`=?, `permission`=?::`userGroup`, `grantedby`=?")) {
394 ps.setInt(1, getId());
395 ps.setString(2, toGrant.getDatabaseName());
396 ps.setInt(3, granter.getId());
401 public void revokeGroup(User revoker, Group toRevoke) {
402 groups.remove(toRevoke);
403 try (GigiPreparedStatement ps = new GigiPreparedStatement("UPDATE `user_groups` SET `deleted`=CURRENT_TIMESTAMP, `revokedby`=? WHERE `deleted` IS NULL AND `permission`=?::`userGroup` AND `user`=?")) {
404 ps.setInt(1, revoker.getId());
405 ps.setString(2, toRevoke.getDatabaseName());
406 ps.setInt(3, getId());
411 public List<Organisation> getOrganisations() {
412 return getOrganisations(false);
415 public List<Organisation> getOrganisations(boolean isAdmin) {
416 List<Organisation> orgas = new ArrayList<>();
417 try (GigiPreparedStatement query = new GigiPreparedStatement("SELECT `orgid` FROM `org_admin` WHERE `memid`=? AND `deleted` IS NULL" + (isAdmin ? " AND master='y'" : ""))) {
418 query.setInt(1, getId());
419 try (GigiResultSet res = query.executeQuery()) {
421 orgas.add(Organisation.getById(res.getInt(1)));
429 public static synchronized User getById(int id) {
430 CertificateOwner co = CertificateOwner.getById(id);
431 if (co instanceof User) {
438 public static User getByEmail(String mail) {
439 try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `users`.`id` FROM `users` INNER JOIN `certOwners` ON `certOwners`.`id` = `users`.`id` WHERE `email`=? AND `deleted` IS NULL")) {
440 ps.setString(1, mail);
441 GigiResultSet rs = ps.executeQuery();
446 return User.getById(rs.getInt(1));
450 public static User[] findByEmail(String mail) {
451 LinkedList<User> results = new LinkedList<User>();
452 try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `users`.`id` FROM `users` INNER JOIN `certOwners` ON `certOwners`.`id` = `users`.`id` WHERE `users`.`email` LIKE ? AND `deleted` IS NULL GROUP BY `users`.`id` LIMIT 100")) {
453 ps.setString(1, mail);
454 GigiResultSet rs = ps.executeQuery();
456 results.add(User.getById(rs.getInt(1)));
458 return results.toArray(new User[results.size()]);
462 public EmailAddress[] getEmails() {
463 try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `id` FROM `emails` WHERE `memid`=? AND `deleted` IS NULL")) {
464 ps.setInt(1, getId());
466 GigiResultSet rs = ps.executeQuery();
467 LinkedList<EmailAddress> data = new LinkedList<EmailAddress>();
470 data.add(EmailAddress.getById(rs.getInt(1)));
473 return data.toArray(new EmailAddress[0]);
478 public boolean isValidEmail(String email) {
479 for (EmailAddress em : getEmails()) {
480 if (em.getAddress().equals(email)) {
481 return em.isVerified();
488 public String[] getTrainings() {
489 try (GigiPreparedStatement prep = new GigiPreparedStatement("SELECT `pass_date`, `type_text` FROM `cats_passed` LEFT JOIN `cats_type` ON `cats_type`.`id`=`cats_passed`.`variant_id` WHERE `user_id`=? ORDER BY `pass_date` ASC")) {
490 prep.setInt(1, getId());
491 GigiResultSet res = prep.executeQuery();
492 List<String> entries = new LinkedList<String>();
496 entries.add(DateSelector.getDateFormat().format(res.getTimestamp(1)) + " (" + res.getString(2) + ")");
499 return entries.toArray(new String[0]);
504 public int generatePasswordResetTicket(User actor, String token, String privateToken) {
505 try (GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO `passwordResetTickets` SET `memid`=?, `creator`=?, `token`=?, `private_token`=?")) {
506 ps.setInt(1, getId());
507 ps.setInt(2, getId());
508 ps.setString(3, token);
509 ps.setString(4, PasswordHash.hash(privateToken));
511 return ps.lastInsertId();
515 public static User getResetWithToken(int id, String token) {
516 try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `memid` FROM `passwordResetTickets` WHERE `id`=? AND `token`=? AND `used` IS NULL")) {
518 ps.setString(2, token);
519 GigiResultSet res = ps.executeQuery();
523 return User.getById(res.getInt(1));
527 public synchronized void consumePasswordResetTicket(int id, String private_token, String newPassword) throws GigiApiException {
528 try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `private_token` FROM `passwordResetTickets` WHERE `id`=? AND `memid`=? AND `used` IS NULL")) {
530 ps.setInt(2, getId());
531 GigiResultSet rs = ps.executeQuery();
533 throw new GigiApiException("Token not found... very bad.");
535 if (PasswordHash.verifyHash(private_token, rs.getString(1)) == null) {
536 throw new GigiApiException("Private token does not match.");
538 setPassword(newPassword);
540 try (GigiPreparedStatement ps = new GigiPreparedStatement("UPDATE `passwordResetTickets` SET `used` = CURRENT_TIMESTAMP WHERE `id`=?")) {
546 private Assurance assuranceByRes(GigiResultSet res) {
547 return new Assurance(res.getInt("id"), User.getById(res.getInt("from")), User.getById(res.getInt("to")), res.getString("location"), res.getString("method"), res.getInt("points"), res.getString("date"));