sample_audit_db.mysql revision 81db14effa88a707fad039b67f41454a99c5115b
7dcb2f62e25d05f2afeb4e79f10102350d7c2c7bBrendan MmillerCREATE DATABASE IF NOT EXISTS `audit` CHARACTER SET utf8 COLLATE utf8_bin;
7dcb2f62e25d05f2afeb4e79f10102350d7c2c7bBrendan Mmiller-- -----------------------------------------------------
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch-- Table `auditaccess`
7dcb2f62e25d05f2afeb4e79f10102350d7c2c7bBrendan Mmiller-- -----------------------------------------------------
548846fe158900a483ca91f47603c6bb6fde9b47Jon BranchCREATE TABLE IF NOT EXISTS `auditaccess` (
7dcb2f62e25d05f2afeb4e79f10102350d7c2c7bBrendan Mmiller `objectid` VARCHAR(38) NOT NULL ,
18f2985c5be44e17aff0b118ee2c6f18967fa150Brendan Mmiller `activity` VARCHAR(24) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `activitydate` VARCHAR(29) NULL COMMENT 'Date format: 2011-09-09T14:58:17.654+02:00' ,
81db14effa88a707fad039b67f41454a99c5115bJon Branch `transactionid` VARCHAR(56) NOT NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `eventname` VARCHAR(255) ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `server_ip` VARCHAR(40) ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `server_port` VARCHAR(5) ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `client_host` VARCHAR(255) ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `client_ip` VARCHAR(40) ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `client_port` VARCHAR(5) ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `userid` VARCHAR(255) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `principal` TEXT NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `roles` VARCHAR(1024) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `auth_component` VARCHAR(255) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `resource_uri` VARCHAR(255) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `resource_protocol` VARCHAR(10) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `resource_method` VARCHAR(10) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `resource_detail` VARCHAR(255) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `http_method` VARCHAR(10) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `http_path` VARCHAR(255) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `http_querystring` VARCHAR(255) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `http_headers` TEXT ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `status` VARCHAR(20) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `elapsedtime` VARCHAR(13) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch PRIMARY KEY (`objectid`),
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch INDEX `idx_auditaccess_status` (`status` ASC),
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch INDEX `idx_auditaccess_principal` (`principal`(28) ASC) )
7dcb2f62e25d05f2afeb4e79f10102350d7c2c7bBrendan MmillerENGINE = InnoDB;
7dcb2f62e25d05f2afeb4e79f10102350d7c2c7bBrendan Mmiller-- -----------------------------------------------------
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch-- Table `auditauthentication`
7dcb2f62e25d05f2afeb4e79f10102350d7c2c7bBrendan Mmiller-- -----------------------------------------------------
548846fe158900a483ca91f47603c6bb6fde9b47Jon BranchCREATE TABLE IF NOT EXISTS `auditauthentication` (
7dcb2f62e25d05f2afeb4e79f10102350d7c2c7bBrendan Mmiller `objectid` VARCHAR(38) NOT NULL ,
81db14effa88a707fad039b67f41454a99c5115bJon Branch `transactionid` VARCHAR(56) NOT NULL ,
7dcb2f62e25d05f2afeb4e79f10102350d7c2c7bBrendan Mmiller `activitydate` VARCHAR(29) NULL COMMENT 'Date format: 2011-09-09T14:58:17.654+02:00' ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `userid` VARCHAR(255) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `eventname` VARCHAR(50) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `result` VARCHAR(255) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `principals` TEXT ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `context` TEXT ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `sessionid` VARCHAR(255) ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `entries` TEXT ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch PRIMARY KEY (`objectid`)
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch ENGINE = InnoDB;
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch-- -----------------------------------------------------
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch-- Table `auditactivity`
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch-- -----------------------------------------------------
548846fe158900a483ca91f47603c6bb6fde9b47Jon BranchCREATE TABLE IF NOT EXISTS `auditactivity` (
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `objectid` VARCHAR(38) NOT NULL ,
7dcb2f62e25d05f2afeb4e79f10102350d7c2c7bBrendan Mmiller `activity` VARCHAR(24) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `activitydate` VARCHAR(29) NULL COMMENT 'Date format: 2011-09-09T14:58:17.654+02:00' ,
81db14effa88a707fad039b67f41454a99c5115bJon Branch `transactionid` VARCHAR(56) NOT NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `eventname` VARCHAR(255) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `userid` VARCHAR(255) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `runas` VARCHAR(255) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `resource_uri` VARCHAR(255) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `resource_protocol` VARCHAR(10) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `resource_method` VARCHAR(10) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `resource_detail` VARCHAR(255) NULL ,
7dcb2f62e25d05f2afeb4e79f10102350d7c2c7bBrendan Mmiller `subjectbefore` MEDIUMTEXT NULL ,
7dcb2f62e25d05f2afeb4e79f10102350d7c2c7bBrendan Mmiller `subjectafter` MEDIUMTEXT NULL ,
7dcb2f62e25d05f2afeb4e79f10102350d7c2c7bBrendan Mmiller `changedfields` VARCHAR(255) NULL ,
7dcb2f62e25d05f2afeb4e79f10102350d7c2c7bBrendan Mmiller `passwordchanged` VARCHAR(5) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `subjectrev` VARCHAR(255) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `message` TEXT NULL,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `activityobjectid` VARCHAR(255) ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `status` VARCHAR(20) ,
18f2985c5be44e17aff0b118ee2c6f18967fa150Brendan Mmiller PRIMARY KEY (`objectid`) ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch INDEX `idx_auditactivity_transactionid` (`transactionid` ASC)
7dcb2f62e25d05f2afeb4e79f10102350d7c2c7bBrendan MmillerENGINE = InnoDB;
7dcb2f62e25d05f2afeb4e79f10102350d7c2c7bBrendan Mmiller-- -----------------------------------------------------
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch-- Table `auditrecon`
7dcb2f62e25d05f2afeb4e79f10102350d7c2c7bBrendan Mmiller-- -----------------------------------------------------
548846fe158900a483ca91f47603c6bb6fde9b47Jon BranchCREATE TABLE IF NOT EXISTS `auditrecon` (
7dcb2f62e25d05f2afeb4e79f10102350d7c2c7bBrendan Mmiller `objectid` VARCHAR(38) NOT NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `transactionid` VARCHAR(56) NOT NULL ,
18f2985c5be44e17aff0b118ee2c6f18967fa150Brendan Mmiller `activitydate` VARCHAR(29) NULL COMMENT 'Date format: 2011-09-09T14:58:17.654+02:00' ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `eventname` VARCHAR(50) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `userid` VARCHAR(255) NULL ,
7dcb2f62e25d05f2afeb4e79f10102350d7c2c7bBrendan Mmiller `activity` VARCHAR(24) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `exceptiondetail` TEXT NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `linkqualifier` VARCHAR(255) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `mapping` VARCHAR(511) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `message` TEXT NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `messagedetail` MEDIUMTEXT NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `situation` VARCHAR(24) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `sourceobjectid` VARCHAR(511) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `status` VARCHAR(20) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `targetobjectid` VARCHAR(511) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `reconciling` VARCHAR(12) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `ambiguoustargetobjectids` MEDIUMTEXT NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `reconaction` VARCHAR(36) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `entrytype` VARCHAR(7) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `reconid` VARCHAR(56) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch PRIMARY KEY (`objectid`) ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch INDEX `idx_auditrecon_reconid` (`reconid` ASC),
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch INDEX `idx_auditrecon_targetobjectid` (`targetobjectid`(28) ASC),
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch INDEX `idx_auditrecon_sourceobjectid` (`sourceobjectid`(28) ASC),
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch INDEX `idx_auditrecon_activitydate` (`activitydate` ASC),
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch INDEX `idx_auditrecon_mapping` (`mapping`(255) ASC),
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch INDEX `idx_auditrecon_entrytype` (`entrytype` ASC),
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch INDEX `idx_auditrecon_situation` (`situation` ASC),
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch INDEX `idx_auditrecon_status` (`status` ASC) )
7dcb2f62e25d05f2afeb4e79f10102350d7c2c7bBrendan MmillerENGINE = InnoDB;
8013147372959d2435f1f8b0305057cfce308168Jason Lemay-- -----------------------------------------------------
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch-- Table `auditsync`
8013147372959d2435f1f8b0305057cfce308168Jason Lemay-- -----------------------------------------------------
548846fe158900a483ca91f47603c6bb6fde9b47Jon BranchCREATE TABLE IF NOT EXISTS `auditsync` (
8013147372959d2435f1f8b0305057cfce308168Jason Lemay `objectid` VARCHAR(38) NOT NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `transactionid` VARCHAR(56) NOT NULL ,
8013147372959d2435f1f8b0305057cfce308168Jason Lemay `activitydate` VARCHAR(29) NULL COMMENT 'Date format: 2011-09-09T14:58:17.654+02:00' ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `eventname` VARCHAR(50) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `userid` VARCHAR(255) NULL ,
8013147372959d2435f1f8b0305057cfce308168Jason Lemay `activity` VARCHAR(24) NULL ,
8013147372959d2435f1f8b0305057cfce308168Jason Lemay `exceptiondetail` TEXT NULL ,
685b771b5e91b170f26b3b326c1bf9efe4210424Alin Brici `linkqualifier` VARCHAR(255) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `mapping` VARCHAR(511) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `message` TEXT NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `messagedetail` MEDIUMTEXT NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `situation` VARCHAR(24) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `sourceobjectid` VARCHAR(511) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `status` VARCHAR(20) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `targetobjectid` VARCHAR(511) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch PRIMARY KEY (`objectid`)
8013147372959d2435f1f8b0305057cfce308168Jason LemayENGINE = InnoDB;
1c5b000d6aed5e39ab2ec8fdb8bb3ec5af7fe64dJake Feaselgrant all on audit.* to root@'%' IDENTIFIED BY 'password';