From 123b7724f9b8ab944a7aa6ab2df9499f34b02a4b Mon Sep 17 00:00:00 2001 From: =?utf8?q?Felix=20D=C3=B6rre?= Date: Fri, 26 Sep 2014 19:55:10 +0200 Subject: [PATCH] ADD: org certs. --- doc/tableStructure.sql | 51 +++++++++--- .../gigi/dbObjects/CertificateOwner.java | 56 +++++++++++++ .../cacert/gigi/dbObjects/Organisation.java | 66 ++++++++++++++++ src/org/cacert/gigi/dbObjects/User.java | 79 +++++++------------ src/org/cacert/gigi/pages/main/Signup.java | 2 +- .../gigi/pages/orga/CreateNewOrgPage.java | 21 +++++ src/org/cacert/gigi/pages/wot/AssurePage.java | 2 +- 7 files changed, 217 insertions(+), 60 deletions(-) create mode 100644 src/org/cacert/gigi/dbObjects/CertificateOwner.java create mode 100644 src/org/cacert/gigi/dbObjects/Organisation.java create mode 100644 src/org/cacert/gigi/pages/orga/CreateNewOrgPage.java diff --git a/doc/tableStructure.sql b/doc/tableStructure.sql index a6f78ed0..2d78f951 100644 --- a/doc/tableStructure.sql +++ b/doc/tableStructure.sql @@ -1,6 +1,15 @@ +DROP TABLE IF EXISTS `certOwners`; +CREATE TABLE `certOwners` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + `modified` timestamp NULL DEFAULT NULL, + `deleted` timestamp NULL DEFAULT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; + DROP TABLE IF EXISTS `users`; CREATE TABLE `users` ( - `id` int(11) NOT NULL AUTO_INCREMENT, + `id` int(11) NOT NULL, `email` varchar(255) NOT NULL DEFAULT '', `password` varchar(255) NOT NULL DEFAULT '', `fname` varchar(255) NOT NULL DEFAULT '', @@ -15,20 +24,26 @@ CREATE TABLE `users` ( `listme` int(1) NOT NULL DEFAULT '0', `contactinfo` varchar(255) NOT NULL DEFAULT '', `language` varchar(5) NOT NULL DEFAULT '', - `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `deleted` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`), KEY `ccid` (`ccid`), KEY `regid` (`regid`), KEY `locid` (`locid`), KEY `email` (`email`), - KEY `stats_users_created` (`created`), - KEY `stats_users_verified` (`verified`), - KEY `userverified` (`verified`) -) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; + KEY `stats_users_verified` (`verified`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +DROP TABLE IF EXISTS `organisations`; +CREATE TABLE IF NOT EXISTS `organisations` ( + `id` int(11) NOT NULL, + `name` varchar(100) NOT NULL, + `state` varchar(2) NOT NULL, + `province` varchar(100) NOT NULL, + `city` varchar(100) NOT NULL, + `creator` int(11) NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + DROP TABLE IF EXISTS `domains`; CREATE TABLE `domains` ( `id` int(11) NOT NULL AUTO_INCREMENT, @@ -159,7 +174,7 @@ CREATE TABLE `clientcerts` ( `disablelogin` int(1) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) -) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `profiles`; CREATE TABLE `profiles` ( @@ -172,6 +187,7 @@ CREATE TABLE `profiles` ( PRIMARY KEY (`id`), UNIQUE (`keyname`) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; + INSERT INTO `profiles` SET rootcert=0, keyname='client', name='ssl-client (unassured)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='clientAuth'; INSERT INTO `profiles` SET rootcert=0, keyname='mail', name='mail (unassured)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='emailProtection'; INSERT INTO `profiles` SET rootcert=0, keyname='client-mail', name='ssl-client + mail (unassured)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='clientAuth, emailProtection'; @@ -181,6 +197,13 @@ INSERT INTO `profiles` SET rootcert=1, keyname='client-a', name='ssl-client (ass INSERT INTO `profiles` SET rootcert=1, keyname='mail-a', name='mail (assured)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='emailProtection'; INSERT INTO `profiles` SET rootcert=1, keyname='client-mail-a', name='ssl-client + mail(assured)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='clientAuth, emailProtection'; INSERT INTO `profiles` SET rootcert=1, keyname='server-a', name='ssl-server (assured)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='serverAuth'; +INSERT INTO `profiles` SET rootcert=2, keyname='code-a', name='codesign (assured)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='codeSigning, msCodeInd, msCodeCom'; + +INSERT INTO `profiles` SET rootcert=3, keyname='client-orga', name='ssl-client (orga)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='clientAuth'; +INSERT INTO `profiles` SET rootcert=3, keyname='mail-orga', name='mail (orga)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='emailProtection'; +INSERT INTO `profiles` SET rootcert=3, keyname='client-mail-orga', name='ssl-client + mail(orga)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='clientAuth, emailProtection'; +INSERT INTO `profiles` SET rootcert=3, keyname='server-orga', name='ssl-server (orga)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='serverAuth'; +INSERT INTO `profiles` SET rootcert=4, keyname='code-orga', name='codesign (orga)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='codeSigning, msCodeInd, msCodeCom'; -- 0=unassured, 1=assured, 2=codesign, 3=orga, 4=orga-sign DROP TABLE IF EXISTS `subjectAlternativeNames`; @@ -278,3 +301,13 @@ CREATE TABLE IF NOT EXISTS `user_groups` ( `revokedby` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +DROP TABLE IF EXISTS `org_admin`; +CREATE TABLE IF NOT EXISTS `org_admin` ( + `orgid` int(11) NOT NULL, + `memid` int(11) NOT NULL, + `creator` int(11) NOT NULL, + `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + `deleted` timestamp NULL DEFAULT NULL, + PRIMARY KEY (`orgid`, `memid`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; diff --git a/src/org/cacert/gigi/dbObjects/CertificateOwner.java b/src/org/cacert/gigi/dbObjects/CertificateOwner.java new file mode 100644 index 00000000..cba952c8 --- /dev/null +++ b/src/org/cacert/gigi/dbObjects/CertificateOwner.java @@ -0,0 +1,56 @@ +package org.cacert.gigi.dbObjects; + +import org.cacert.gigi.database.DatabaseConnection; +import org.cacert.gigi.database.GigiPreparedStatement; +import org.cacert.gigi.database.GigiResultSet; + +public abstract class CertificateOwner implements IdCachable { + + private int id; + + public CertificateOwner(int id) { + this.id = id; + } + + public CertificateOwner() {} + + public int getId() { + return id; + } + + private static ObjectCache myCache = new ObjectCache<>(); + + public static synchronized CertificateOwner getById(int id) { + CertificateOwner u = myCache.get(id); + if (u == null) { + GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT *, users.id AS uid, organisations.id AS oid FROM certOwners LEFT JOIN users ON users.id=certOwners.id LEFT JOIN organisations ON organisations.id = certOwners.id WHERE certOwners.id=?"); + ps.setInt(1, id); + GigiResultSet rs = ps.executeQuery(); + if ( !rs.next()) { + System.out.println("no " + id); + } + if (rs.getString("uid") != null) { + myCache.put(u = new User(rs)); + } else if (rs.getString("oid") != null) { + myCache.put(u = new Organisation(rs)); + } else { + System.err.print("Malformed cert owner: " + id); + } + } + return u; + } + + protected int insert() { + if (id != 0) { + throw new Error("refusing to insert"); + } + synchronized (User.class) { + GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO certOwners() VALUES()"); + ps.execute(); + id = ps.lastInsertId(); + myCache.put(this); + } + return id; + } + +} diff --git a/src/org/cacert/gigi/dbObjects/Organisation.java b/src/org/cacert/gigi/dbObjects/Organisation.java new file mode 100644 index 00000000..7151203d --- /dev/null +++ b/src/org/cacert/gigi/dbObjects/Organisation.java @@ -0,0 +1,66 @@ +package org.cacert.gigi.dbObjects; + +import org.cacert.gigi.database.DatabaseConnection; +import org.cacert.gigi.database.GigiPreparedStatement; +import org.cacert.gigi.database.GigiResultSet; + +public class Organisation extends CertificateOwner { + + private final String name; + + private final String state; + + private final String province; + + private final String city; + + public Organisation(String name, String state, String province, String city, User creator) { + this.name = name; + this.state = state; + this.province = province; + this.city = city; + int id = super.insert(); + GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO organisations SET id=?, name=?, state=?, province=?, city=?, creator=?"); + ps.setInt(1, id); + ps.setString(2, name); + ps.setString(3, state); + ps.setString(4, province); + ps.setString(5, city); + ps.setInt(6, creator.getId()); + synchronized (Organisation.class) { + ps.execute(); + } + + } + + protected Organisation(GigiResultSet rs) { + name = rs.getString("name"); + state = rs.getString("state"); + province = rs.getString("province"); + city = rs.getString("city"); + } + + public String getName() { + return name; + } + + public String getState() { + return state; + } + + public String getProvince() { + return province; + } + + public String getCity() { + return city; + } + + public static synchronized Organisation getById(int id) { + CertificateOwner co = CertificateOwner.getById(id); + if (co instanceof Organisation) { + return (Organisation) co; + } + return null; + } +} diff --git a/src/org/cacert/gigi/dbObjects/User.java b/src/org/cacert/gigi/dbObjects/User.java index 7a4ce0df..31ce4421 100644 --- a/src/org/cacert/gigi/dbObjects/User.java +++ b/src/org/cacert/gigi/dbObjects/User.java @@ -16,9 +16,7 @@ import org.cacert.gigi.util.Notary; import org.cacert.gigi.util.PasswordHash; import org.cacert.gigi.util.PasswordStrengthChecker; -public class User implements IdCachable { - - private int id; +public class User extends CertificateOwner { private Name name = new Name(null, null, null, null); @@ -32,29 +30,23 @@ public class User implements IdCachable { private Set groups = new HashSet<>(); - private User(int id) { - this.id = id; - updateName(id); + protected User(GigiResultSet rs) { + super(rs.getInt("id")); + updateName(rs); } - private void updateName(int id) { - GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `fname`, `lname`,`mname`, `suffix`, `dob`, `email`, `language` FROM `users` WHERE id=?"); - ps.setInt(1, id); - GigiResultSet rs = ps.executeQuery(); - if (rs.next()) { - name = new Name(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4)); - dob = rs.getDate(5); - email = rs.getString(6); - String localeStr = rs.getString(7); - if (localeStr == null || localeStr.equals("")) { - locale = Locale.getDefault(); - } else { - locale = Language.getLocaleFromString(localeStr); - } + private void updateName(GigiResultSet rs) { + name = new Name(rs.getString("fname"), rs.getString("lname"), rs.getString("mname"), rs.getString("suffix")); + dob = rs.getDate("dob"); + email = rs.getString("email"); + String localeStr = rs.getString("language"); + if (localeStr == null || localeStr.equals("")) { + locale = Locale.getDefault(); + } else { + locale = Language.getLocaleFromString(localeStr); } - rs.close(); GigiPreparedStatement psg = DatabaseConnection.getInstance().prepare("SELECT permission FROM user_groups WHERE user=? AND deleted is NULL"); - psg.setInt(1, id); + psg.setInt(1, rs.getInt("id")); GigiResultSet rs2 = psg.executeQuery(); while (rs2.next()) { groups.add(Group.getByString(rs2.getString(1))); @@ -64,10 +56,6 @@ public class User implements IdCachable { public User() {} - public int getId() { - return id; - } - public String getFname() { return name.fname; } @@ -121,10 +109,8 @@ public class User implements IdCachable { } public void insert(String password) { - if (id != 0) { - throw new Error("refusing to insert"); - } - GigiPreparedStatement query = DatabaseConnection.getInstance().prepare("insert into `users` set `email`=?, `password`=?, " + "`fname`=?, `mname`=?, `lname`=?, " + "`suffix`=?, `dob`=?, `created`=NOW(), `language`=?"); + int id = super.insert(); + GigiPreparedStatement query = DatabaseConnection.getInstance().prepare("insert into `users` set `email`=?, `password`=?, " + "`fname`=?, `mname`=?, `lname`=?, " + "`suffix`=?, `dob`=?, `language`=?, id=?"); query.setString(1, email); query.setString(2, PasswordHash.hash(password)); query.setString(3, name.fname); @@ -133,16 +119,13 @@ public class User implements IdCachable { query.setString(6, name.suffix); query.setDate(7, new java.sql.Date(dob.getTime())); query.setString(8, locale.toString()); - synchronized (User.class) { - query.execute(); - id = query.lastInsertId(); - myCache.put(this); - } + query.setInt(9, id); + query.execute(); } public void changePassword(String oldPass, String newPass) throws GigiApiException { GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `password` FROM users WHERE id=?"); - ps.setInt(1, id); + ps.setInt(1, getId()); GigiResultSet rs = ps.executeQuery(); if ( !rs.next()) { throw new GigiApiException("User not found... very bad."); @@ -154,7 +137,7 @@ public class User implements IdCachable { PasswordStrengthChecker.assertStrongPassword(newPass, this); ps = DatabaseConnection.getInstance().prepare("UPDATE users SET `password`=? WHERE id=?"); ps.setString(1, PasswordHash.hash(newPass)); - ps.setInt(2, id); + ps.setInt(2, getId()); if (ps.executeUpdate() != 1) { throw new GigiApiException("Password update failed."); } @@ -174,7 +157,7 @@ public class User implements IdCachable { public boolean hasPassedCATS() { GigiPreparedStatement query = DatabaseConnection.getInstance().prepare("SELECT 1 FROM `cats_passed` where `user_id`=?"); - query.setInt(1, id); + query.setInt(1, getId()); GigiResultSet rs = query.executeQuery(); if (rs.next()) { return true; @@ -185,7 +168,7 @@ public class User implements IdCachable { public int getAssurancePoints() { GigiPreparedStatement query = DatabaseConnection.getInstance().prepare("SELECT sum(points) FROM `notary` where `to`=? AND `deleted`=0"); - query.setInt(1, id); + query.setInt(1, getId()); GigiResultSet rs = query.executeQuery(); int points = 0; if (rs.next()) { @@ -197,7 +180,7 @@ public class User implements IdCachable { public int getExperiencePoints() { GigiPreparedStatement query = DatabaseConnection.getInstance().prepare("SELECT count(*) FROM `notary` where `from`=? AND `deleted`=0"); - query.setInt(1, id); + query.setInt(1, getId()); GigiResultSet rs = query.executeQuery(); int points = 0; if (rs.next()) { @@ -266,7 +249,7 @@ public class User implements IdCachable { public EmailAddress[] getEmails() { GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT id FROM emails WHERE memid=? AND deleted=0"); - ps.setInt(1, id); + ps.setInt(1, getId()); GigiResultSet rs = ps.executeQuery(); rs.last(); int count = rs.getRow(); @@ -285,7 +268,7 @@ public class User implements IdCachable { public Domain[] getDomains() { GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT id FROM domains WHERE memid=? AND deleted IS NULL"); - ps.setInt(1, id); + ps.setInt(1, getId()); GigiResultSet rs = ps.executeQuery(); rs.last(); int count = rs.getRow(); @@ -304,7 +287,7 @@ public class User implements IdCachable { public Certificate[] getCertificates() { GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT serial FROM certs WHERE memid=? AND revoked=0"); - ps.setInt(1, id); + ps.setInt(1, getId()); GigiResultSet rs = ps.executeQuery(); rs.last(); int count = rs.getRow(); @@ -504,14 +487,12 @@ public class User implements IdCachable { ps.execute(); } - private static ObjectCache myCache = new ObjectCache<>(); - public static synchronized User getById(int id) { - User u = myCache.get(id); - if (u == null) { - myCache.put(u = new User(id)); + CertificateOwner co = CertificateOwner.getById(id); + if (co instanceof User) { + return (User) co; } - return u; + return null; } public boolean canIssue(CertificateProfile p) { diff --git a/src/org/cacert/gigi/pages/main/Signup.java b/src/org/cacert/gigi/pages/main/Signup.java index a512a017..24562ac8 100644 --- a/src/org/cacert/gigi/pages/main/Signup.java +++ b/src/org/cacert/gigi/pages/main/Signup.java @@ -120,7 +120,7 @@ public class Signup extends Form { return false; } GigiPreparedStatement q1 = DatabaseConnection.getInstance().prepare("select * from `emails` where `email`=? and `deleted`=0"); - GigiPreparedStatement q2 = DatabaseConnection.getInstance().prepare("select * from `users` where `email`=? and `deleted`=0"); + GigiPreparedStatement q2 = DatabaseConnection.getInstance().prepare("select * from certOwners inner join users on users.id=certOwners.id where `email`=? and `deleted`=0"); q1.setString(1, buildup.getEmail()); q2.setString(1, buildup.getEmail()); GigiResultSet r1 = q1.executeQuery(); diff --git a/src/org/cacert/gigi/pages/orga/CreateNewOrgPage.java b/src/org/cacert/gigi/pages/orga/CreateNewOrgPage.java new file mode 100644 index 00000000..12f24525 --- /dev/null +++ b/src/org/cacert/gigi/pages/orga/CreateNewOrgPage.java @@ -0,0 +1,21 @@ +package org.cacert.gigi.pages.orga; + +import java.io.IOException; + +import javax.servlet.http.HttpServletRequest; +import javax.servlet.http.HttpServletResponse; + +import org.cacert.gigi.pages.Page; + +public class CreateNewOrgPage extends Page { + + public CreateNewOrgPage() { + super("Create Organisation"); + } + + @Override + public void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException { + + } + +} diff --git a/src/org/cacert/gigi/pages/wot/AssurePage.java b/src/org/cacert/gigi/pages/wot/AssurePage.java index aa99a2fb..5e7abfb7 100644 --- a/src/org/cacert/gigi/pages/wot/AssurePage.java +++ b/src/org/cacert/gigi/pages/wot/AssurePage.java @@ -75,7 +75,7 @@ public class AssurePage extends Page { GigiResultSet rs = null; try { - GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT id, verified FROM users WHERE email=? AND dob=? AND deleted=0"); + GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT users.id, verified FROM users inner join certOwners on certOwners.id=users.id WHERE email=? AND dob=? AND deleted is null"); ps.setString(1, req.getParameter("email")); String day = req.getParameter("year") + "-" + req.getParameter("month") + "-" + req.getParameter("day"); ps.setString(2, day); -- 2.39.2