Single-sign-on and self-serve account management.
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
 
 
 
 

442 lines
16 KiB

-- MySQL Script generated by MySQL Workbench
-- Mon 11 Feb 2019 04:07:57 PM MST
-- Model: New Model Version: 1.0
-- MySQL Workbench Forward Engineering
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
-- -----------------------------------------------------
-- Schema accounthub
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Table `acctstatus`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `acctstatus` (
`statusid` INT NOT NULL AUTO_INCREMENT,
`statuscode` VARCHAR(45) NOT NULL,
PRIMARY KEY (`statusid`),
UNIQUE INDEX `statusid_UNIQUE` (`statusid` ASC),
UNIQUE INDEX `statuscode_UNIQUE` (`statuscode` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `accttypes`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `accttypes` (
`typeid` INT NOT NULL AUTO_INCREMENT,
`typecode` VARCHAR(45) NOT NULL,
PRIMARY KEY (`typeid`),
UNIQUE INDEX `typeid_UNIQUE` (`typeid` ASC),
UNIQUE INDEX `typecode_UNIQUE` (`typecode` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `accounts`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `accounts` (
`uid` INT NOT NULL AUTO_INCREMENT,
`username` VARCHAR(190) NOT NULL,
`password` VARCHAR(255) NULL,
`realname` VARCHAR(255) NOT NULL,
`email` VARCHAR(255) NULL DEFAULT 'NOEMAIL@EXAMPLE.COM',
`authsecret` VARCHAR(100) NULL,
`pin` VARCHAR(10) NULL,
`phone1` VARCHAR(45) NOT NULL,
`phone2` VARCHAR(45) NOT NULL,
`acctstatus` INT NOT NULL DEFAULT 0,
`accttype` INT NOT NULL,
`deleted` TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`uid`, `username`),
UNIQUE INDEX `userid_UNIQUE` (`uid` ASC),
UNIQUE INDEX `username_UNIQUE` (`username` ASC),
INDEX `fk_accounts_acctstatus_idx` (`acctstatus` ASC),
INDEX `email_index` (`email` ASC),
INDEX `fk_accounts_accttypes1_idx` (`accttype` ASC),
CONSTRAINT `fk_accounts_acctstatus`
FOREIGN KEY (`acctstatus`)
REFERENCES `acctstatus` (`statusid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_accounts_accttypes1`
FOREIGN KEY (`accttype`)
REFERENCES `accttypes` (`typeid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `apikeys`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `apikeys` (
`key` VARCHAR(60) NOT NULL,
`notes` TEXT NULL,
`type` VARCHAR(45) NOT NULL DEFAULT 'FULL',
PRIMARY KEY (`key`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `groups`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `groups` (
`groupid` INT NOT NULL AUTO_INCREMENT,
`groupname` VARCHAR(45) NOT NULL,
PRIMARY KEY (`groupid`),
UNIQUE INDEX `groupid_UNIQUE` (`groupid` ASC),
UNIQUE INDEX `groupname_UNIQUE` (`groupname` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `assigned_groups`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `assigned_groups` (
`groupid` INT NOT NULL,
`uid` INT NOT NULL,
PRIMARY KEY (`groupid`, `uid`),
INDEX `fk_groups_has_accounts_accounts1_idx` (`uid` ASC),
INDEX `fk_groups_has_accounts_groups1_idx` (`groupid` ASC),
CONSTRAINT `fk_groups_has_accounts_groups1`
FOREIGN KEY (`groupid`)
REFERENCES `groups` (`groupid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_groups_has_accounts_accounts1`
FOREIGN KEY (`uid`)
REFERENCES `accounts` (`uid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `managers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `managers` (
`managerid` INT NOT NULL,
`employeeid` INT NOT NULL,
PRIMARY KEY (`managerid`, `employeeid`),
INDEX `fk_managers_accounts2_idx` (`employeeid` ASC),
CONSTRAINT `fk_managers_accounts1`
FOREIGN KEY (`managerid`)
REFERENCES `accounts` (`uid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_managers_accounts2`
FOREIGN KEY (`employeeid`)
REFERENCES `accounts` (`uid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `logtypes`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `logtypes` (
`logtype` INT NOT NULL,
`typename` VARCHAR(45) NULL,
PRIMARY KEY (`logtype`),
UNIQUE INDEX `logtype_UNIQUE` (`logtype` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `authlog`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `authlog` (
`logid` INT NOT NULL AUTO_INCREMENT,
`logtime` DATETIME NOT NULL,
`logtype` INT NOT NULL,
`uid` INT NULL,
`ip` VARCHAR(45) NULL,
`otherdata` VARCHAR(255) NULL,
PRIMARY KEY (`logid`),
UNIQUE INDEX `logid_UNIQUE` (`logid` ASC),
INDEX `fk_authlog_logtypes1_idx` (`logtype` ASC),
INDEX `fk_authlog_accounts1_idx` (`uid` ASC),
CONSTRAINT `fk_authlog_logtypes1`
FOREIGN KEY (`logtype`)
REFERENCES `logtypes` (`logtype`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_authlog_accounts1`
FOREIGN KEY (`uid`)
REFERENCES `accounts` (`uid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `permissions`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `permissions` (
`permid` INT NOT NULL AUTO_INCREMENT,
`permcode` VARCHAR(45) NOT NULL,
`perminfo` VARCHAR(200) NULL,
PRIMARY KEY (`permid`),
UNIQUE INDEX `permid_UNIQUE` (`permid` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `assigned_permissions`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `assigned_permissions` (
`uid` INT NOT NULL,
`permid` INT NOT NULL,
PRIMARY KEY (`uid`, `permid`),
INDEX `fk_permissions_has_accounts_accounts1_idx` (`uid` ASC),
INDEX `fk_permissions_has_accounts_permissions1_idx` (`permid` ASC),
CONSTRAINT `fk_permissions_has_accounts_permissions1`
FOREIGN KEY (`permid`)
REFERENCES `permissions` (`permid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_permissions_has_accounts_accounts1`
FOREIGN KEY (`uid`)
REFERENCES `accounts` (`uid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mobile_codes`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mobile_codes` (
`codeid` INT NOT NULL AUTO_INCREMENT,
`uid` INT NOT NULL,
`code` VARCHAR(45) NOT NULL DEFAULT '',
`description` VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY (`codeid`),
UNIQUE INDEX `codeid_UNIQUE` (`codeid` ASC),
INDEX `fk_mobile_codes_accounts1_idx` (`uid` ASC),
UNIQUE INDEX `code_UNIQUE` (`code` ASC),
CONSTRAINT `fk_mobile_codes_accounts1`
FOREIGN KEY (`uid`)
REFERENCES `accounts` (`uid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `rate_limit`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `rate_limit` (
`ipaddr` VARCHAR(45) NOT NULL,
`lastaction` DATETIME NULL,
PRIMARY KEY (`ipaddr`))
ENGINE = MEMORY;
-- -----------------------------------------------------
-- Table `onetimekeys`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `onetimekeys` (
`key` VARCHAR(10) NOT NULL,
`uid` INT NOT NULL,
`expires` DATETIME NOT NULL,
INDEX `fk_onetimekeys_accounts1_idx` (`uid` ASC),
PRIMARY KEY (`key`),
UNIQUE INDEX `key_UNIQUE` (`key` ASC),
CONSTRAINT `fk_onetimekeys_accounts1`
FOREIGN KEY (`uid`)
REFERENCES `accounts` (`uid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `notifications`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `notifications` (
`notificationid` INT NOT NULL AUTO_INCREMENT,
`uid` INT NOT NULL,
`timestamp` DATETIME NOT NULL,
`title` VARCHAR(255) NOT NULL,
`content` TINYTEXT NOT NULL,
`url` VARCHAR(255) NOT NULL,
`seen` TINYINT(1) NOT NULL DEFAULT 0,
`sensitive` TINYINT(1) NOT NULL,
`appid` VARCHAR(255) NULL,
PRIMARY KEY (`notificationid`, `uid`),
UNIQUE INDEX `notificationid_UNIQUE` (`notificationid` ASC),
INDEX `fk_notifications_accounts1_idx` (`uid` ASC),
CONSTRAINT `fk_notifications_accounts1`
FOREIGN KEY (`uid`)
REFERENCES `accounts` (`uid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `userkeytypes`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `userkeytypes` (
`typeid` INT NOT NULL,
`typename` VARCHAR(45) NOT NULL,
PRIMARY KEY (`typeid`, `typename`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `userkeys`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `userkeys` (
`uid` INT NOT NULL,
`key` VARCHAR(100) NOT NULL,
`created` DATETIME NULL,
`typeid` INT NOT NULL,
PRIMARY KEY (`uid`),
INDEX `fk_userkeys_userkeytypes1_idx` (`typeid` ASC),
CONSTRAINT `fk_userkeys_accounts1`
FOREIGN KEY (`uid`)
REFERENCES `accounts` (`uid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_userkeys_userkeytypes1`
FOREIGN KEY (`typeid`)
REFERENCES `userkeytypes` (`typeid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `userloginkeys`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `userloginkeys` (
`id` INT NOT NULL AUTO_INCREMENT,
`key` VARCHAR(255) NOT NULL,
`expires` DATETIME NULL,
`uid` INT NULL,
`appname` VARCHAR(255) NOT NULL,
`appicon` TINYTEXT NULL,
PRIMARY KEY (`id`, `key`),
UNIQUE INDEX `id_UNIQUE` (`id` ASC),
UNIQUE INDEX `key_UNIQUE` (`key` ASC),
INDEX `fk_userloginkeys_accounts1_idx` (`uid` ASC),
CONSTRAINT `fk_userloginkeys_accounts1`
FOREIGN KEY (`uid`)
REFERENCES `accounts` (`uid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `apppasswords`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `apppasswords` (
`passid` INT NOT NULL AUTO_INCREMENT,
`hash` VARCHAR(255) NOT NULL,
`uid` INT NOT NULL,
`description` VARCHAR(255) NOT NULL,
PRIMARY KEY (`passid`, `uid`),
UNIQUE INDEX `passid_UNIQUE` (`passid` ASC),
INDEX `fk_apppasswords_accounts1_idx` (`uid` ASC),
CONSTRAINT `fk_apppasswords_accounts1`
FOREIGN KEY (`uid`)
REFERENCES `accounts` (`uid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
-- -----------------------------------------------------
-- Data for table `acctstatus`
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO `acctstatus` (`statusid`, `statuscode`) VALUES (1, 'NORMAL');
INSERT INTO `acctstatus` (`statusid`, `statuscode`) VALUES (2, 'LOCKED_OR_DISABLED');
INSERT INTO `acctstatus` (`statusid`, `statuscode`) VALUES (3, 'CHANGE_PASSWORD');
INSERT INTO `acctstatus` (`statusid`, `statuscode`) VALUES (4, 'TERMINATED');
INSERT INTO `acctstatus` (`statusid`, `statuscode`) VALUES (5, 'ALERT_ON_ACCESS');
COMMIT;
-- -----------------------------------------------------
-- Data for table `accttypes`
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO `accttypes` (`typeid`, `typecode`) VALUES (1, 'LOCAL');
INSERT INTO `accttypes` (`typeid`, `typecode`) VALUES (2, 'EXTERNAL');
COMMIT;
-- -----------------------------------------------------
-- Data for table `logtypes`
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO `logtypes` (`logtype`, `typename`) VALUES (1, 'PORTAL_LOGIN_OK');
INSERT INTO `logtypes` (`logtype`, `typename`) VALUES (2, 'PORTAL_LOGIN_FAILED');
INSERT INTO `logtypes` (`logtype`, `typename`) VALUES (3, 'PASSWORD_CHANGED');
INSERT INTO `logtypes` (`logtype`, `typename`) VALUES (4, 'API_LOGIN_OK');
INSERT INTO `logtypes` (`logtype`, `typename`) VALUES (5, 'API_LOGIN_FAILED');
INSERT INTO `logtypes` (`logtype`, `typename`) VALUES (6, 'PORTAL_BAD_AUTHCODE');
INSERT INTO `logtypes` (`logtype`, `typename`) VALUES (7, 'API_BAD_AUTHCODE');
INSERT INTO `logtypes` (`logtype`, `typename`) VALUES (8, 'BAD_CAPTCHA');
INSERT INTO `logtypes` (`logtype`, `typename`) VALUES (9, '2FA_ADDED');
INSERT INTO `logtypes` (`logtype`, `typename`) VALUES (10, '2FA_REMOVED');
INSERT INTO `logtypes` (`logtype`, `typename`) VALUES (11, 'PORTAL_LOGOUT');
INSERT INTO `logtypes` (`logtype`, `typename`) VALUES (12, 'API_AUTH_OK');
INSERT INTO `logtypes` (`logtype`, `typename`) VALUES (13, 'API_AUTH_FAILED');
INSERT INTO `logtypes` (`logtype`, `typename`) VALUES (14, 'API_BAD_KEY');
INSERT INTO `logtypes` (`logtype`, `typename`) VALUES (15, 'LOG_CLEARED');
INSERT INTO `logtypes` (`logtype`, `typename`) VALUES (16, 'USER_REMOVED');
INSERT INTO `logtypes` (`logtype`, `typename`) VALUES (17, 'USER_ADDED');
INSERT INTO `logtypes` (`logtype`, `typename`) VALUES (18, 'USER_EDITED');
INSERT INTO `logtypes` (`logtype`, `typename`) VALUES (19, 'MOBILE_LOGIN_OK');
INSERT INTO `logtypes` (`logtype`, `typename`) VALUES (20, 'MOBILE_LOGIN_FAILED');
INSERT INTO `logtypes` (`logtype`, `typename`) VALUES (21, 'MOBILE_BAD_KEY');
COMMIT;
-- -----------------------------------------------------
-- Data for table `permissions`
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO `permissions` (`permid`, `permcode`, `perminfo`) VALUES (1, 'ADMIN', 'System administrator');
INSERT INTO `permissions` (`permid`, `permcode`, `perminfo`) VALUES (100, 'INV_VIEW', 'Access inventory system');
INSERT INTO `permissions` (`permid`, `permcode`, `perminfo`) VALUES (101, 'INV_EDIT', 'Edit inventory system');
INSERT INTO `permissions` (`permid`, `permcode`, `perminfo`) VALUES (200, 'TASKFLOOR', 'Access TaskFloor');
INSERT INTO `permissions` (`permid`, `permcode`, `perminfo`) VALUES (300, 'QWIKCLOCK', 'Access QwikClock and punch in/out');
INSERT INTO `permissions` (`permid`, `permcode`, `perminfo`) VALUES (301, 'QWIKCLOCK_MANAGE', 'Edit punches and other data for managed users');
INSERT INTO `permissions` (`permid`, `permcode`, `perminfo`) VALUES (302, 'QWIKCLOCK_EDITSELF', 'Edit own punches and other data');
INSERT INTO `permissions` (`permid`, `permcode`, `perminfo`) VALUES (303, 'QWIKCLOCK_ADMIN', 'Add and edit shifts and other data for all users');
INSERT INTO `permissions` (`permid`, `permcode`, `perminfo`) VALUES (400, 'SITEWRITER', 'Manage and edit websites, messages, and analytics');
INSERT INTO `permissions` (`permid`, `permcode`, `perminfo`) VALUES (401, 'SITEWRITER_CONTACT', 'Manage messages sent via website contact forms');
INSERT INTO `permissions` (`permid`, `permcode`, `perminfo`) VALUES (402, 'SITEWRITER_ANALYTICS', 'View website analytics');
INSERT INTO `permissions` (`permid`, `permcode`, `perminfo`) VALUES (403, 'SITEWRITER_EDIT', 'Edit website content');
INSERT INTO `permissions` (`permid`, `permcode`, `perminfo`) VALUES (404, 'SITEWRITER_FILES', 'Manage and upload files');
COMMIT;
-- -----------------------------------------------------
-- Data for table `userkeytypes`
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO `userkeytypes` (`typeid`, `typename`) VALUES (1, 'RSSAtomFeed');
INSERT INTO `userkeytypes` (`typeid`, `typename`) VALUES (2, 'Other');
COMMIT;