+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 '',
`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,
`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` (
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';
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`;
`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;
--- /dev/null
+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<CertificateOwner> 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;
+ }
+
+}
--- /dev/null
+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;
+ }
+}
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);
private Set<Group> 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)));
public User() {}
- public int getId() {
- return id;
- }
-
public String getFname() {
return name.fname;
}
}
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);
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.");
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.");
}
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;
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()) {
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()) {
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();
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();
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();
ps.execute();
}
- private static ObjectCache<User> 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) {
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();
--- /dev/null
+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 {
+
+ }
+
+}
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);