aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--dbstore/sql/init.sql189
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;