1 2#DROP USER 'dcuser'; 3#DROP database dependencycheck; 4 5CREATE database dependencycheck; 6USE dependencycheck; 7 8DROP TABLE IF EXISTS software; 9DROP TABLE IF EXISTS cpeEntry; 10DROP TABLE IF EXISTS reference; 11DROP TABLE IF EXISTS vulnerability; 12DROP TABLE IF EXISTS properties; 13 14CREATE TABLE properties (id varchar(50) PRIMARY KEY, value varchar(500)); 15 16CREATE TABLE vulnerability (id int auto_increment PRIMARY KEY, cve VARCHAR(20) UNIQUE, 17 description VARCHAR(8000), cwe VARCHAR(10), cvssScore DECIMAL(3,1), cvssAccessVector VARCHAR(20), 18 cvssAccessComplexity VARCHAR(20), cvssAuthentication VARCHAR(20), cvssConfidentialityImpact VARCHAR(20), 19 cvssIntegrityImpact VARCHAR(20), cvssAvailabilityImpact VARCHAR(20)); 20 21CREATE TABLE reference (cveid INT, name VARCHAR(1000), url VARCHAR(1000), source VARCHAR(255), 22 CONSTRAINT fkReference FOREIGN KEY (cveid) REFERENCES vulnerability(id) ON DELETE CASCADE); 23 24CREATE TABLE cpeEntry (id INT auto_increment PRIMARY KEY, cpe VARCHAR(250), vendor VARCHAR(255), product VARCHAR(255)); 25 26CREATE TABLE software (cveid INT, cpeEntryId INT, previousVersion VARCHAR(50) 27 , CONSTRAINT fkSoftwareCve FOREIGN KEY (cveid) REFERENCES vulnerability(id) ON DELETE CASCADE 28 , CONSTRAINT fkSoftwareCpeProduct FOREIGN KEY (cpeEntryId) REFERENCES cpeEntry(id) 29 , PRIMARY KEY (cveid, cpeEntryId)); 30 31CREATE INDEX idxVulnerability ON vulnerability(cve); 32CREATE INDEX idxReference ON reference(cveid); 33CREATE INDEX idxCpe ON cpeEntry(cpe); 34CREATE INDEX idxCpeEntry ON cpeEntry(vendor, product); 35CREATE INDEX idxSoftwareCve ON software(cveid); 36CREATE INDEX idxSoftwareCpe ON software(cpeEntryId); 37 38INSERT INTO properties(id,value) VALUES ('version','2.9'); 39 40CREATE USER 'dcuser' IDENTIFIED BY 'DC-Pass1337!'; 41GRANT SELECT, INSERT, DELETE, UPDATE ON dependencycheck.* TO 'dcuser'; 42 43 44DROP PROCEDURE IF EXISTS save_property; 45 46DELIMITER // 47CREATE PROCEDURE save_property 48(IN prop varchar(50), IN val varchar(500)) 49BEGIN 50INSERT INTO properties (`id`, `value`) VALUES (prop, val) 51 ON DUPLICATE KEY UPDATE `value`=val; 52END // 53DELIMITER ; 54 55GRANT EXECUTE ON PROCEDURE dependencycheck.save_property TO 'dcuser'; 56 57UPDATE properties SET value='3.0' WHERE ID='version'; 58