diff options
| -rw-r--r-- | dbstore/sql/init.sql | 189 |
1 files changed, 189 insertions, 0 deletions
diff --git a/dbstore/sql/init.sql b/dbstore/sql/init.sql index cf9d82a..662893e 100644 --- a/dbstore/sql/init.sql +++ b/dbstore/sql/init.sql @@ -770,3 +770,192 @@ CREATE TRIGGER tr_cve_fts_refresh_after_insert GROUP BY cve_ref_id ) g ON (g.cve_ref_id = f.cve_ref_id); END; + +-- +-- CISA Known Exploited Vulnerabilities (KEV) catalogs. +-- +CREATE TABLE cisa_catalogs ( + -- catalog unique ID + cat_id INTEGER PRIMARY KEY, + + -- catalog title + title TEXT NOT NULL CHECK ( + -- FIXME: not sure about max length here + LENGTH(title) BETWEEN 1 AND 256 + ), + + -- catalog version + version TEXT NOT NULL CHECK ( + LENGTH(version) = 10 AND + version LIKE '____.__.__' + ), + + -- catalog release date + released_at TIMESTAMP WITH TIME ZONE NOT NULL +); + +-- +-- CISA catalog vendors and projects. +-- +CREATE TABLE cisa_vendors ( + -- vendor/project unique ID + vendor_id INTEGER PRIMARY KEY, + + -- vendor/project name + name TEXT UNIQUE NOT NULL CHECK ( + -- FIXME: not sure about max length here + LENGTH(name) BETWEEN 1 AND 256 + ) +); + +-- +-- CISA catalog products. +-- +CREATE TABLE cisa_products ( + -- product unique ID + product_id INTEGER PRIMARY KEY, + + -- product name + name TEXT UNIQUE NOT NULL CHECK ( + -- FIXME: not sure about max length here + LENGTH(name) BETWEEN 1 AND 256 + ) +); + +-- +-- CISA KEV catalog vulnerabilities +-- +CREATE TABLE cisa_vulns ( + -- vulnerability unique ID + vuln_id INTEGER PRIMARY KEY, + + -- catalog ID + cat_id INTEGER NOT NULL + REFERENCES cisa_catalogs(cat_id), + + -- year of NVD CVE ID, (e.g. the "XXXX" in "CVE-XXXX-YYYY") + cve_year SMALLINT NOT NULL CHECK ( + cve_year BETWEEN 1999 AND 2126 + ), + + -- number of NVD CVE ID, (e.g. the "YYYY" in "CVE-XXXX-YYYY") + cve_num SMALLINT NOT NULL CHECK ( + -- there is no upper bound, but let's pick + -- something sane + cve_num BETWEEN 1 AND 33554432 + ), + + -- vendor/project ID + vendor_id INTEGER NOT NULL + REFERENCES cisa_vendors(vendor_id), + + -- product ID + product_id INTEGER NOT NULL + REFERENCES cisa_products(product_id), + + -- vulnerability name + name TEXT NOT NULL CHECK ( + -- FIXME: not sure about max length here + LENGTH(name) BETWEEN 1 AND 1024 + ), + + -- date that vulnerability was added to catalog + added_at DATE NOT NULL, + + -- short description of vulnerability + description TEXT NOT NULL CHECK ( + -- FIXME: not sure about max length here + LENGTH(description) BETWEEN 1 AND 2048 + ), + + -- required action + action TEXT NOT NULL CHECK ( + -- FIXME: not sure about max length here + LENGTH(action) BETWEEN 1 AND 2048 + ), + + -- date that required action is due + due_at DATE NOT NULL +); + +-- +-- CISA vulnerability CVE IDs (e.g. "CVE-YYYY-NNNN"). +-- +CREATE VIEW cisa_vuln_cve_ids AS + SELECT vuln_id, + printf('CVE-%04d-%04d', cve_year, cve_num) AS nvd_id + FROM cisa_vulns; + +-- +-- create CISA vulnerabilities FTS table +-- +-- this virtual table is populated and cleared by triggers on the +-- corresponding cve tables (see triggers below). +-- +CREATE VIRTUAL TABLE cisa_vulns_fts USING fts5( + -- parent vulnerability id + vuln_id UNINDEXED, + + -- NVD CVE ID (e.g. CVE-XXXX-YYYY) + cve_id, + + -- vulnerability vendor/project + vendor, + + -- vulnerability product + product, + + -- vulnerability name + name, + + -- vulnerability description + description, + + -- use porter stemming + tokenize = 'porter' +); + +-- +-- Dummy table to trigger cve_fts refreshes. +-- +CREATE TABLE cisa_vulns_fts_refresh ( + created_at TIMESTAMP WITH TIME ZONE NOT NULL + DEFAULT CURRENT_TIMESTAMP +); + +-- create before insert trigger on cve_fts_refresh +CREATE TRIGGER tr_cisa_vulns_fts_refresh_before_insert + BEFORE INSERT ON cisa_vulns_fts_refresh + FOR EACH ROW BEGIN + DELETE FROM cisa_vulns_fts; + END; + +-- +-- create insert trigger on cisa_vulns_fts_refresh +-- +CREATE TRIGGER tr_cisa_vulns_fts_refresh_after_insert + AFTER INSERT ON cisa_vulns_fts_refresh + FOR EACH ROW BEGIN + INSERT INTO cisa_vulns_fts( + vuln_id, + cve_id, + vendor, + product, + name, + description + ) + SELECT a.vuln_id, -- vuln ID + b.cve_id, -- NVD cve id + c.name, -- vendor name + d.name, -- product name + a.name, -- vuln name + a.description -- vuln description + + FROM cisa_vulns a + JOIN cve_vuln_cve_ids b + ON (b.vuln_id = a.vuln_id) + JOIN cisa_vendors c + ON (c.vendor_id = a.vendor_id) + JOIN cisa_products d + ON (c.product_id = a.product_id); + END; |