diff options
Diffstat (limited to 'dbstore')
| -rw-r--r-- | dbstore/sql/init.sql | 333 |
1 files changed, 234 insertions, 99 deletions
diff --git a/dbstore/sql/init.sql b/dbstore/sql/init.sql index a480a58..68daae4 100644 --- a/dbstore/sql/init.sql +++ b/dbstore/sql/init.sql @@ -7,19 +7,19 @@ PRAGMA user_version = 314159; -- create cpes table CREATE TABLE cpes ( -- cpe unique ID - cpe_id INTEGER PRIMARY KEY, + cpe_id INTEGER PRIMARY KEY, -- cpe uri - cpe_uri TEXT UNIQUE NOT NULL CHECK ( - LENGTH(cpe_uri) > 5 AND - cpe_uri LIKE 'cpe:/%' - ), + cpe_uri TEXT UNIQUE NOT NULL CHECK ( + LENGTH(cpe_uri) > 5 AND + cpe_uri LIKE 'cpe:/%' + ), -- cpe 2.3 formatting string - cpe23 TEXT UNIQUE NOT NULL CHECK ( - LENGTH(cpe23) > 28 AND - cpe23 LIKE 'cpe:2.3:%' - ) + cpe23 TEXT UNIQUE NOT NULL CHECK ( + LENGTH(cpe23) > 28 AND + cpe23 LIKE 'cpe:2.3:%' + ) ); -- create all fts table (cross join across all @@ -44,19 +44,19 @@ CREATE VIRTUAL TABLE cpe_fts_all USING fts5( -- create titles table CREATE TABLE cpe_titles ( -- title primary key - cpe_title_id INTEGER PRIMARY KEY, + cpe_title_id INTEGER PRIMARY KEY, -- external CPE id - cpe_id INT NOT NULL - REFERENCES cpes(cpe_id), + cpe_id INT NOT NULL + REFERENCES cpes(cpe_id), -- language code - lang TEXT NOT NULL - CHECK (LENGTH(lang) > 0), + lang TEXT NOT NULL + CHECK (LENGTH(lang) > 0), -- text value - val TEXT NOT NULL - CHECK (LENGTH(val) > 0), + val TEXT NOT NULL + CHECK (LENGTH(val) > 0), -- prevent duplicate titles of the same name UNIQUE (cpe_id, lang) @@ -115,21 +115,21 @@ CREATE TRIGGER tr_cpe_titles_delete_fts_titles BEFORE DELETE ON cpe_titles -- create refs table CREATE TABLE cpe_refs ( -- reference unique id - cpe_ref_id INTEGER PRIMARY KEY, + cpe_ref_id INTEGER PRIMARY KEY, -- external CPE id - cpe_id INT NOT NULL - REFERENCES cpes(cpe_id), + cpe_id INT NOT NULL + REFERENCES cpes(cpe_id), -- url - href TEXT NOT NULL CHECK ( - LENGTH(href) > 0 AND - (href LIKE 'http://%' OR href LIKE 'https://%') - ), + href TEXT NOT NULL CHECK ( + LENGTH(href) > 0 AND + (href LIKE 'http://%' OR href LIKE 'https://%') + ), -- text description - val TEXT NOT NULL - CHECK (LENGTH(val) > 0) + val TEXT NOT NULL + CHECK (LENGTH(val) > 0) ); -- create refs fts table @@ -196,22 +196,22 @@ CREATE TABLE cpe_matches ( cpe_match_id INTEGER PRIMARY KEY, -- modern CPE 2.3 formatted string - cpe23 TEXT UNIQUE NOT NULL CHECK ( - LENGTH(cpe23) > 28 AND - cpe23 LIKE 'cpe:2.3:%' - ), + cpe23 TEXT UNIQUE NOT NULL CHECK ( + LENGTH(cpe23) > 28 AND + cpe23 LIKE 'cpe:2.3:%' + ), -- old CPE 2.2 URI - cpe22_uri TEXT NOT NULL + cpe22_uri TEXT NOT NULL ); -- cpe match vulnerabilities CREATE TABLE cpe_match_vulnerables ( - cpe_match_id INTEGER PRIMARY KEY, + cpe_match_id INTEGER PRIMARY KEY, -- true if this match is vulnerable, and false -- otherwise - is_vulnerable BOOLEAN NOT NULL, + is_vulnerable BOOLEAN NOT NULL, -- add foreign key constraint FOREIGN KEY (cpe_match_id) @@ -222,7 +222,7 @@ CREATE TABLE cpe_match_vulnerables ( -- cpe match version minimums (e.g. version starts) -- CREATE TABLE cpe_match_version_mins ( - cpe_match_id INTEGER PRIMARY KEY, + cpe_match_id INTEGER PRIMARY KEY, -- valid values -- @@ -230,11 +230,11 @@ CREATE TABLE cpe_match_version_mins ( -- versionStartIncluding) -- * false: boundary excludes this version (e.g., it was specified by -- versionStartExcluding) - is_inclusive BOOLEAN NOT NULL, + is_inclusive BOOLEAN NOT NULL, -- minimum version - min_version TEXT NOT NULL - CHECK (LENGTH(min_version) > 0), + min_version TEXT NOT NULL + CHECK (LENGTH(min_version) > 0), -- add foreign key constraint FOREIGN KEY (cpe_match_id) @@ -245,7 +245,8 @@ CREATE TABLE cpe_match_version_mins ( -- cpe match version maximums (e.g. version ends) -- CREATE TABLE cpe_match_version_maxs ( - cpe_match_id INTEGER PRIMARY KEY, + -- cpe match unique id + cpe_match_id INTEGER PRIMARY KEY, -- valid values -- @@ -253,11 +254,11 @@ CREATE TABLE cpe_match_version_maxs ( -- versionEndIncluding) -- * false: boundary excludes this version (e.g., it was specified by -- versionEndExcluding) - is_inclusive BOOLEAN NOT NULL, + is_inclusive BOOLEAN NOT NULL, -- maximum version - max_version TEXT NOT NULL - CHECK (LENGTH(max_version) > 0), + max_version TEXT NOT NULL + CHECK (LENGTH(max_version) > 0), -- add foreign key constraint FOREIGN KEY (cpe_match_id) @@ -267,17 +268,17 @@ CREATE TABLE cpe_match_version_maxs ( -- map of matching CREATE TABLE cpe_match_cpes ( -- CPE match ID - cpe_match_id INTEGER NOT NULL - REFERENCES cpe_matches(cpe_match_id), + cpe_match_id INTEGER NOT NULL + REFERENCES cpe_matches(cpe_match_id), -- CPE ID - cpe_id INTEGER NOT NULL - REFERENCES cpes(cpe_id), + cpe_id INTEGER NOT NULL + REFERENCES cpes(cpe_id), -- nullable cpe22 URI -- normally we would want this in a separate table, but -- we don't care that much about this value - cpe22_uri TEXT, + cpe22_uri TEXT, PRIMARY KEY (cpe_match_id, cpe_id) ); @@ -287,18 +288,18 @@ CREATE TABLE cpe_match_cpes ( -- CREATE TABLE descriptions ( -- description unique ID - desc_id INTEGER PRIMARY KEY, + desc_id INTEGER PRIMARY KEY, -- description language - lang TEXT NOT NULL CHECK ( - -- FIXME: not sure about maximum value here - LENGTH(lang) BETWEEN 1 AND 10 - ), + lang TEXT NOT NULL CHECK ( + -- FIXME: not sure about maximum value here + LENGTH(lang) BETWEEN 1 AND 10 + ), -- description text - value TEXT NOT NULL CHECK ( - LENGTH(value) > 0 - ) + value TEXT NOT NULL CHECK ( + LENGTH(value) > 0 + ) ); -- @@ -306,31 +307,31 @@ CREATE TABLE descriptions ( -- CREATE TABLE feeds ( -- feed unique ID - feed_id INTEGER PRIMARY KEY, + feed_id INTEGER PRIMARY KEY, -- time that feed was added - created_at TIMESTAMP WITH TIME ZONE NOT NULL - DEFAULT CURRENT_TIMESTAMP, + created_at TIMESTAMP WITH TIME ZONE NOT NULL + DEFAULT CURRENT_TIMESTAMP, -- data timestamp - data_time TIMESTAMP WITH TIME ZONE NOT NULL + data_time TIMESTAMP WITH TIME ZONE NOT NULL ); -- -- CVE feed items. -- CREATE TABLE feed_items ( - item_id INTEGER PRIMARY KEY, + item_id INTEGER PRIMARY KEY, -- feed that this CVE belongs to - feed_id INTEGER NOT NULL - REFERENCES feeds(feed_id), + feed_id INTEGER NOT NULL + REFERENCES feeds(feed_id), -- published timestamp - published_at TIMESTAMP WITH TIME ZONE NOT NULL, + published_at TIMESTAMP WITH TIME ZONE NOT NULL, -- last modified timestamp - modified_at TIMESTAMP WITH TIME ZONE NOT NULL + modified_at TIMESTAMP WITH TIME ZONE NOT NULL ); -- create index @@ -344,20 +345,20 @@ CREATE INDEX in_feed_items_feed_id ON feed_items(feed_id); -- CREATE TABLE cves ( -- CVE unique ID - cve_id INTEGER PRIMARY KEY, + cve_id INTEGER PRIMARY KEY, -- feed item that this CVE belongs to - item_id INTEGER NOT NULL - REFERENCES feed_items(item_id), + item_id INTEGER NOT NULL + REFERENCES feed_items(item_id), -- NVD CVE ID (e.g. "CVE-YYYY-XXXX) - nvd_cve_id TEXT NOT NULL CHECK ( - LENGTH(nvd_cve_id) BETWEEN 9 AND 17 AND - nvd_cve_id LIKE 'CVE-%' - ), + nvd_cve_id TEXT NOT NULL CHECK ( + LENGTH(nvd_cve_id) BETWEEN 9 AND 17 AND + nvd_cve_id LIKE 'CVE-%' + ), -- assigner, or empty string - assigner TEXT NOT NULL DEFAULT '' + assigner TEXT NOT NULL DEFAULT '' ); -- create index @@ -368,12 +369,12 @@ CREATE INDEX in_cves_item_id ON cves(item_id); -- CREATE TABLE cve_descriptions ( -- CVE unique ID - cve_id INTEGER NOT NULL - REFERENCES cves(cve_id), + cve_id INTEGER NOT NULL + REFERENCES cves(cve_id), -- description unique ID - desc_id INTEGER NOT NULL - REFERENCES descriptions(desc_id), + desc_id INTEGER NOT NULL + REFERENCES descriptions(desc_id), PRIMARY KEY (cve_id, desc_id) ); @@ -386,11 +387,11 @@ CREATE INDEX in_cve_descriptions_cve_id ON cve_descriptions(cve_id); -- CREATE TABLE cve_problems ( -- problem type unique ID - pt_id INTEGER PRIMARY KEY, + pt_id INTEGER PRIMARY KEY, -- CVE that this problem type belongs to - cve_id INTEGER NOT NULL - REFERENCES cves(cve_id) + cve_id INTEGER NOT NULL + REFERENCES cves(cve_id) ); -- create index @@ -401,12 +402,12 @@ CREATE INDEX in_cve_problems_cve_id ON cve_problems(cve_id); -- CREATE TABLE cve_problem_descriptions ( -- problem type that this description belongs to - pt_id INTEGER NOT NULL - REFERENCES cve_problems(pt_id), + pt_id INTEGER NOT NULL + REFERENCES cve_problems(pt_id), -- description unique ID - desc_id INTEGER NOT NULL - REFERENCES descriptions(desc_id), + desc_id INTEGER NOT NULL + REFERENCES descriptions(desc_id), PRIMARY KEY (pt_id, desc_id) ); @@ -416,26 +417,26 @@ CREATE TABLE cve_problem_descriptions ( -- CREATE TABLE cve_refs ( -- cve reference unique ID - cve_ref_id INTEGER PRIMARY KEY, + cve_ref_id INTEGER PRIMARY KEY, -- CVE unique ID - cve_id INTEGER NOT NULL - REFERENCES cves(cve_id), + cve_id INTEGER NOT NULL + REFERENCES cves(cve_id), -- reference URL - url TEXT NOT NULL CHECK ( - -- FIXME: not sure about length here - LENGTH(url) BETWEEN 7 AND 1024 - ), + url TEXT NOT NULL CHECK ( + -- FIXME: not sure about length here + LENGTH(url) BETWEEN 7 AND 1024 + ), -- reference name - name TEXT NOT NULL CHECK ( - -- FIXME: not sure about length here - LENGTH(name) BETWEEN 1 AND 1024 - ), + name TEXT NOT NULL CHECK ( + -- FIXME: not sure about length here + LENGTH(name) BETWEEN 1 AND 1024 + ), -- reference name - source TEXT NOT NULL + source TEXT NOT NULL ); -- create index @@ -445,14 +446,14 @@ CREATE INDEX in_cve_refs_cve_id ON cve_refs(cve_id); -- cve reference tags -- CREATE TABLE cve_ref_tags ( - cve_ref_id INTEGER NOT NULL - REFERENCES cve_refs(cve_ref_id), + cve_ref_id INTEGER NOT NULL + REFERENCES cve_refs(cve_ref_id), -- tag - tag TEXT NOT NULL CHECK ( - -- FIXME: not sure about maximum length - LENGTH(tag) BETWEEN 1 AND 128 - ), + tag TEXT NOT NULL CHECK ( + -- FIXME: not sure about maximum length + LENGTH(tag) BETWEEN 1 AND 128 + ), -- prevent duplicate tags UNIQUE (cve_ref_id, tag) @@ -460,3 +461,137 @@ CREATE TABLE cve_ref_tags ( -- create index CREATE INDEX in_cve_ref_tags_cve_ref_id ON cve_ref_tags(cve_ref_id); + +-- +-- CVSS severities. +-- +CREATE TABLE severities ( + -- severity unique ID + severity_id INTEGER PRIMARY KEY, + + -- severity name + name TEXT UNIQUE NOT NULL CHECK ( + -- limit to know severities + name IN ('NONE', 'LOW', 'MEDIUM', 'HIGH', 'CRITICAL') + ), + + -- severity sort order, from low to high + sort SMALLINT UNIQUE NOT NULL +); + +-- add severities +INSERT INTO severities(severity_id, name, sort) VALUES + (1, 'NONE', 1), + (2, 'LOW', 2), + (3, 'MEDIUM', 3), + (4, 'HIGH', 4), + (5, 'CRITICAL', 5); + +-- +-- item cvss v2 impacts +-- +CREATE TABLE item_cvss_v2_impacts ( + -- feed item that this impact belongs to + item_id INTEGER NOT NULL + REFERENCES feed_items(item_id), + + -- cvss v2 version + -- FIXME: don't need this, check during import + -- v2_version TEXT NOT NULL CHECK ( + -- LENGTH(v2_version) > 0 + -- ), + + -- cvss v2 vector + v2_vector TEXT NOT NULL CHECK ( + -- TODO: enforce this + LENGTH(v2_vector) > 0 + ), + + -- cvss v2 base score + -- (NOTE: multiplied by 10 and stored as a tinyint) + v2_base_score TINYINT NOT NULL CHECK ( + v2_base_score BETWEEN 0 AND 100 + ), + + -- cvss v2 severity + v2_severity_id INTEGER NOT NULL + REFERENCES severeties(severity_id), + + -- cvss v2 impact score + -- (NOTE: multiplied by 10 and stored as a tinyint) + v2_impact_score TINYINT NOT NULL CHECK ( + v2_impact_score BETWEEN 0 AND 100 + ), + + -- cvss v2 exploitability score + -- (NOTE: multiplied by 10 and stored as a tinyint) + v2_expl_score TINYINT NOT NULL CHECK ( + v2_expl_score BETWEEN 0 AND 100 + ), + + -- cvss v2 insufficient info + v2_insufficient_info BOOLEAN NOT NULL, + + -- cvss v2 insufficient info + v2_obtain_all_priv BOOLEAN NOT NULL, + + -- cvss v2 obtain user privileges + v2_obtain_user_priv BOOLEAN NOT NULL, + + -- cvss v2 obtain other privileges + v2_obtain_other_priv BOOLEAN NOT NULL, + + -- cvss v2 user interaction required? + v2_ui_req BOOLEAN NOT NULL +); + +-- create indices +CREATE INDEX in_item_cvss_v2_impacts_item_id ON item_cvss_v2_impacts(item_id); +CREATE INDEX in_item_cvss_v2_impacts_v2_severity_id ON item_cvss_v2_impacts(v2_severity_id); + +-- +-- item cvss v3 impacts +-- +CREATE TABLE item_cvss_v3_impacts ( + -- feed item that this impact belongs to + item_id INTEGER NOT NULL + REFERENCES feed_items(item_id), + + -- FIXME: don't need this, check during import + -- v3_version TEXT NOT NULL CHECK ( + -- LENGTH(v3_version) > 0 + -- ), + + -- cvss v3 vector + v3_vector TEXT NOT NULL CHECK ( + -- TODO: enforce this + LENGTH(v3_vector) > 0 AND + v3_vector LIKE 'CVSS:3.1/%' + ), + + -- cvss v3 base score + -- (NOTE: multiplied by 10 and stored as a tinyint) + v3_base_score TINYINT NOT NULL CHECK ( + v3_base_score BETWEEN 0 AND 100 + ), + + -- cvss v3 base severity + v3_severity_id INTEGER NOT NULL + REFERENCES severeties(severity_id), + + -- cvss v3 exploitability score + -- (NOTE: multiplied by 10 and stored as a tinyint) + v3_expl_score TINYINT NOT NULL CHECK ( + v3_expl_score BETWEEN 0 AND 100 + ), + + -- cvss v3 impact score + -- (NOTE: multiplied by 10 and stored as a tinyint) + v3_impact_score TINYINT NOT NULL CHECK ( + v3_impact_score BETWEEN 0 AND 100 + ) +); + +-- create indices +CREATE INDEX in_item_cvss_v3_impacts_item_id ON item_cvss_v3_impacts(item_id); +CREATE INDEX in_item_cvss_v3_impacts_v3_severity_id ON item_cvss_v3_impacts(v3_severity_id); |