Manual / Installation / Database config / SQL statements

Usually, the Conseal Server database is set up automatically during installation. In case, however, you wish to set up the server manually, here are the MySQL statements needed to create the Conseal Server database.

The highlighted values database name, user name and user password are user-specifiable.

CREATE DATABASE conseal;
GRANT ALL PRIVILEGES ON conseal.* TO conseal@'%' IDENTIFIED BY '[enter password here]';
USE conseal;
CREATE TABLE IF NOT EXISTS accessrules (accessruleid mediumint(9) PRIMARY KEY NOT NULL AUTO_INCREMENT, action enum('block','allow') NOT NULL DEFAULT 'block', cardid mediumint(9) NOT NULL DEFAULT '0', type enum('adv','net','dom','com','tim') DEFAULT NULL, prio mediumint(9) NOT NULL DEFAULT '0', net_ipfrom varchar(45) DEFAULT NULL, net_ipto varchar(45) DEFAULT NULL, dom_domain varchar(255) DEFAULT NULL, adv_term text, com_netnum bigint(20) unsigned DEFAULT NULL, com_hddnum bigint(20) unsigned DEFAULT NULL, tim_start datetime DEFAULT NULL, tim_end datetime DEFAULT NULL, tim_recur tinyint(4) DEFAULT NULL, tim_dailystart smallint(6) DEFAULT NULL, tim_dailyend smallint(6) DEFAULT NULL);
CREATE TABLE IF NOT EXISTS activedirectorydomains (domain varchar(255) PRIMARY KEY NOT NULL, server varchar(255) NOT NULL, admingroups text, quota mediumint, recheckinterval mediumint, recheckuser varchar(255), recheckpass varchar(255));
CREATE TABLE IF NOT EXISTS cards (cardid mediumint(8) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT, cardname varchar(255) DEFAULT NULL, enckey blob, status enum('ok','tb','sd','dd') DEFAULT 'ok', volumeserial bigint(20) unsigned UNIQUE KEY NOT NULL DEFAULT '0', resetkey bigint(20) unsigned NOT NULL DEFAULT '0', passhashhash blob, passhashhash512 tinyblob, owneruserid mediumint(9) NOT NULL, softwarever int);
CREATE TABLE IF NOT EXISTS certificaterequests (certificaterequestid mediumint NOT NULL PRIMARY KEY AUTO_INCREMENT, cardid mediumint NOT NULL, status enum('p','a','r'), lastupdated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, publickey blob, machinename varchar(40) NOT NULL, username varchar(40) NOT NULL, driveserial bigint(20) UNSIGNED NOT NULL, macaddr bigint(20) UNSIGNED, processorid varchar(255) NOT NULL, os varchar(255) NOT NULL, biosid varchar(255) NOT NULL);
CREATE TABLE IF NOT EXISTS currentlogins (cookieid varchar(40) UNIQUE KEY NOT NULL, userid mediumint(9) NOT NULL, created datetime NOT NULL, lastused datetime NOT NULL);
CREATE TABLE IF NOT EXISTS emailalerts (alertid mediumint(8) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT, cardid mediumint(8) unsigned NOT NULL DEFAULT '0', userid mediumint(8) unsigned NOT NULL DEFAULT '0', email varchar(40) NOT NULL DEFAULT '');
CREATE TABLE IF NOT EXISTS unlocks (unlockid mediumint(8) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT, cardid mediumint(8) unsigned NOT NULL DEFAULT '0', time datetime NOT NULL DEFAULT '0000-00-00 00:00:00', usingcertificate enum('y','n') NOT NULL DEFAULT 'n', machinename varchar(255) DEFAULT NULL, username varchar(255) DEFAULT NULL, internalip varchar(40) DEFAULT NULL, externalip varchar(40) DEFAULT NULL, driveserial varchar(40) DEFAULT NULL, macaddr bigint(20) unsigned DEFAULT NULL, success enum('y','n') DEFAULT NULL, processorid varchar(255) DEFAULT NULL, os varchar(255) DEFAULT NULL);
CREATE TABLE IF NOT EXISTS users (email varchar(255) UNIQUE KEY NOT NULL DEFAULT '', domain varchar(255) DEFAULT NULL, enabled enum('y','n') NOT NULL DEFAULT 'y', passhash varchar(40) DEFAULT NULL, lastlogon datetime DEFAULT NULL, cookieid varchar(40) DEFAULT NULL, userid mediumint(8) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT, allowpwreset enum('y','n') NOT NULL DEFAULT 'y', passhash512 varbinary(64) DEFAULT NULL, quota mediumint(9) NOT NULL DEFAULT '0', authmask bigint(20) NOT NULL DEFAULT '0');
CREATE TABLE IF NOT EXISTS userstocards (userid mediumint(8) unsigned NOT NULL DEFAULT '0', cardid mediumint(8) unsigned NOT NULL DEFAULT '0', authmask bigint(9) NOT NULL DEFAULT '0');

Contents

Frequently Asked Questions

Installation

Using Conseal Server

Conseal Server Concepts