sample_audit_db.mysql revision 0f63005dc454e8131506be734dc0404f48c8578c
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` (
81d426f4e03ac36896437325e6f67f74657b7dd9Chris Drake `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `objectid` VARCHAR(56) NOT NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `activitydate` VARCHAR(29) NOT NULL COMMENT 'Date format: 2011-09-09T14:58:17.654+02:00' ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `eventname` VARCHAR(255) ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `transactionid` VARCHAR(255) NOT NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `userid` VARCHAR(255) ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `trackingids` TEXT,
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) ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `request_protocol` VARCHAR(255) NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `request_operation` VARCHAR(255) NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `request_detail` TEXT NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `http_request_secure` VARCHAR(255) NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `http_request_method` VARCHAR(255) NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `http_request_path` VARCHAR(255) NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `http_request_queryparameters` TEXT NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `http_request_headers` TEXT NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `http_request_cookies` TEXT NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `http_response_headers` TEXT NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `response_status` VARCHAR(255) NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `response_statuscode` VARCHAR(255) NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `response_elapsedtime` VARCHAR(255) NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `response_elapsedtimeunits` VARCHAR(255) NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `roles` TEXT NULL ,
81d426f4e03ac36896437325e6f67f74657b7dd9Chris Drake INDEX `idx_auditrecon_objectid` (`objectid` ASC),
81d426f4e03ac36896437325e6f67f74657b7dd9Chris Drake INDEX `idx_auditrecon_activitydate` (`activitydate` ASC),
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay INDEX `idx_auditaccess_status` (`response_status` ASC) )
7dcb2f62e25d05f2afeb4e79f10102350d7c2c7bBrendan MmillerENGINE = InnoDB;
7dcb2f62e25d05f2afeb4e79f10102350d7c2c7bBrendan Mmiller-- -----------------------------------------------------
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch-- Table `auditauthentication`
7dcb2f62e25d05f2afeb4e79f10102350d7c2c7bBrendan Mmiller-- -----------------------------------------------------
548846fe158900a483ca91f47603c6bb6fde9b47Jon BranchCREATE TABLE IF NOT EXISTS `auditauthentication` (
81d426f4e03ac36896437325e6f67f74657b7dd9Chris Drake `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `objectid` VARCHAR(56) NOT NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `transactionid` VARCHAR(255) NOT NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `activitydate` VARCHAR(29) NOT 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 ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `entries` TEXT ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `trackingids` TEXT,
81d426f4e03ac36896437325e6f67f74657b7dd9Chris Drake INDEX `idx_auditrecon_objectid` (`objectid` ASC),
81d426f4e03ac36896437325e6f67f74657b7dd9Chris Drake INDEX `idx_auditrecon_activitydate` (`activitydate` ASC)
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch ENGINE = InnoDB;
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch-- -----------------------------------------------------
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch-- Table `auditactivity`
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch-- -----------------------------------------------------
548846fe158900a483ca91f47603c6bb6fde9b47Jon BranchCREATE TABLE IF NOT EXISTS `auditactivity` (
81d426f4e03ac36896437325e6f67f74657b7dd9Chris Drake `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `objectid` VARCHAR(56) NOT NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `activitydate` VARCHAR(29) NOT NULL COMMENT 'Date format: 2011-09-09T14:58:17.654+02:00' ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `eventname` VARCHAR(255) NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `transactionid` VARCHAR(255) NOT NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `userid` VARCHAR(255) NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `trackingids` MEDIUMTEXT,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `runas` VARCHAR(255) NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `activityobjectid` VARCHAR(255) NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `operation` VARCHAR(255) NULL ,
7dcb2f62e25d05f2afeb4e79f10102350d7c2c7bBrendan Mmiller `subjectbefore` MEDIUMTEXT NULL ,
7dcb2f62e25d05f2afeb4e79f10102350d7c2c7bBrendan Mmiller `subjectafter` MEDIUMTEXT NULL ,
7dcb2f62e25d05f2afeb4e79f10102350d7c2c7bBrendan Mmiller `changedfields` VARCHAR(255) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `subjectrev` VARCHAR(255) NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `passwordchanged` VARCHAR(5) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `message` TEXT NULL,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `status` VARCHAR(20) ,
81d426f4e03ac36896437325e6f67f74657b7dd9Chris Drake INDEX `idx_auditrecon_objectid` (`objectid` ASC),
81d426f4e03ac36896437325e6f67f74657b7dd9Chris Drake INDEX `idx_auditrecon_activitydate` (`activitydate` ASC),
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` (
81d426f4e03ac36896437325e6f67f74657b7dd9Chris Drake `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `objectid` VARCHAR(56) NOT NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `transactionid` VARCHAR(255) NOT NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `activitydate` VARCHAR(29) NOT NULL COMMENT 'Date format: 2011-09-09T14:58:17.654+02:00' ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `eventname` VARCHAR(50) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `userid` VARCHAR(255) NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `trackingids` MEDIUMTEXT ,
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 ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `reconid` VARCHAR(56) NULL ,
81d426f4e03ac36896437325e6f67f74657b7dd9Chris Drake INDEX `idx_auditrecon_objectid` (`objectid` ASC),
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` (
81d426f4e03ac36896437325e6f67f74657b7dd9Chris Drake `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `objectid` VARCHAR(56) NOT NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `transactionid` VARCHAR(255) NOT NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `activitydate` VARCHAR(29) NOT NULL COMMENT 'Date format: 2011-09-09T14:58:17.654+02:00' ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `eventname` VARCHAR(50) NULL ,
548846fe158900a483ca91f47603c6bb6fde9b47Jon Branch `userid` VARCHAR(255) NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `trackingids` MEDIUMTEXT ,
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 ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `targetobjectid` VARCHAR(511) NULL ,
81d426f4e03ac36896437325e6f67f74657b7dd9Chris Drake INDEX `idx_auditrecon_objectid` (`objectid` ASC),
81d426f4e03ac36896437325e6f67f74657b7dd9Chris Drake INDEX `idx_auditrecon_activitydate` (`activitydate` ASC)
8013147372959d2435f1f8b0305057cfce308168Jason LemayENGINE = InnoDB;
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay-- -----------------------------------------------------
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay-- Table `auditconfig`
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay-- -----------------------------------------------------
0f63005dc454e8131506be734dc0404f48c8578cJason LemayCREATE TABLE IF NOT EXISTS `auditconfig` (
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `objectid` VARCHAR(56) NOT NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `activitydate` VARCHAR(29) NOT NULL COMMENT 'Date format: 2011-09-09T14:58:17.654+02:00' ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `eventname` VARCHAR(255) NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `transactionid` VARCHAR(255) NOT NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `userid` VARCHAR(255) NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `trackingids` MEDIUMTEXT,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `runas` VARCHAR(255) NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `configobjectid` VARCHAR(255) NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `operation` VARCHAR(255) NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `beforeObject` MEDIUMTEXT NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `afterObject` MEDIUMTEXT NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `changedfields` VARCHAR(255) NULL ,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay `rev` VARCHAR(255) NULL,
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay INDEX `idx_auditrecon_objectid` (`objectid` ASC),
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay INDEX `idx_auditrecon_activitydate` (`activitydate` ASC),
0f63005dc454e8131506be734dc0404f48c8578cJason Lemay INDEX `idx_auditactivity_transactionid` (`transactionid` ASC)
0f63005dc454e8131506be734dc0404f48c8578cJason LemayENGINE = InnoDB;
1c5b000d6aed5e39ab2ec8fdb8bb3ec5af7fe64dJake Feaselgrant all on audit.* to root@'%' IDENTIFIED BY 'password';