aboutsummaryrefslogtreecommitdiff
path: root/dbstore/sql/init.sql
diff options
context:
space:
mode:
Diffstat (limited to 'dbstore/sql/init.sql')
-rw-r--r--dbstore/sql/init.sql333
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);
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-09 23:54:21 +0000