[Openvas-discuss] SQL Schema Notes

[oday@fas.harvard.edu]

Just pulling from the source code here.  I will try to patch this code in soon
to trunk. Any objections to going forward with this schema for now?

Oliver


/*=====================================================================
/* Table: schema
/*=====================================================================
CREATE TABLE schema (
  vseq        INT      UNSIGNED NOT NULL,
  ctime       DATETIME NOT NULL,
  PRIMARY KEY (vseq)
);
INSERT INTO schema  (vseq, ctime) VALUES ('102', now());


/*=====================================================================
/* Table: vulnerability
/*=====================================================================
CREATE TABLE vulnerability (
  vulnerabilityid INT         UNSIGNED NOT NULL AUTO_INCREMENT,
  executionid     INT         UNSIGNED NOT NULL,
  hostid          INT         UNSIGNED NOT NULL,
  sessionid       INT         UNSIGNED NOT NULL,
  pluginid        INT         UNSIGNED NOT NULL,
  isfalsepositive TINYINT     UNSIGNED NOT NULL,
  risk            TINYINT     UNSIGNED NOT NULL,
  type            VARCHAR(20),
  info            TEXT,
  PRIMARY KEY (vulnerabilityid),
  INDEX       (executionid),
  INDEX       (hostid),
  INDEX       (sessionid),
  INDEX       (pluginid)
);


/*=====================================================================
/* Table: pluginbid
/*=====================================================================
CREATE TABLE pluginbid (
  pluginid INT         UNSIGNED NOT NULL,
  bid      VARCHAR(30) NOT NULL,
  INDEX (pluginid)
);


/*=====================================================================
/* Table: pluginsubstitute
/*=====================================================================
CREATE TABLE pluginsubstitute (
  pluginid         INT      UNSIGNED NOT NULL,
  subpluginid      INT      UNSIGNED NOT NULL,
  substitutiondate datetime NOT NULL,
  INDEX (pluginid),
  INDEX (subpluginid)
);

/*=====================================================================
/* Table: plugincve
/*=====================================================================
CREATE TABLE plugincve (
       pluginid INT         UNSIGNED NOT NULL,
       cve      VARCHAR(30) NOT NULL,
INDEX (pluginid)
);


/*=====================================================================
/* Table: plugin
/*=====================================================================
CREATE TABLE plugin (
  pluginid        INT          UNSIGNED NOT NULL AUTO_INCREMENT,
  name            VARCHAR(255) NOT NULL,
  family          VARCHAR(50),
  filename        VARCHAR(255),
  category        VARCHAR(50),
  type            VARCHAR(50),
  script_modified VARCHAR(255),
  source          VARCHAR(255),
  dependencies    VARCHAR(255),
  revision        VARCHAR(15),
  notes           TEXT,
  description     TEXT,
  solution        TEXT,
  summary         TEXT,
  risk_factor     TEXT,
PRIMARY KEY (pluginid)
);


/*=====================================================================
/* Table: protocol
/*=====================================================================
CREATE TABLE protocol (
  protocolid     INT         UNSIGNED NOT NULL AUTO_INCREMENT,
  protocolnumber INT         UNSIGNED NOT NULL,
  name           VARCHAR(30) NOT NULL,
  PRIMARY KEY (protocolid)
);


/*=====================================================================
/* Table: userlist
/*=====================================================================
CREATE TABLE userlist (
  userid INT          UNSIGNED NOT NULL AUTO_INCREMENT,
  name   VARCHAR(50)  NOT NULL,
  rules  VARCHAR(255) NOT NULL,
  hash   VARCHAR(255) NOT NULL,
  PRIMARY KEY (userid)
);

/*=====================================================================
/* Table: hostsession
/*=====================================================================
CREATE TABLE hostsession (
  hostid        INT          UNSIGNED NOT NULL,
  sessionid     INT          UNSIGNED NOT NULL,
  starttime     DATETIME,
  endtime       DATETIME,
  status        VARCHAR(20),
  preferences   VARCHAR(255) NOT NULL,
  configuration VARCHAR(255) NOT NULL,
  ipaddresses   VARCHAR(30)  NOT NULL,
  nessusversion VARCHAR(10)  NOT NULL,
  description   TEXT,
  INDEX (hostid),
  INDEX (sessionid)
);


/*=====================================================================
/* Table: usersession
/*=====================================================================
CREATE TABLE usersession (
  userid    INT         UNSIGNED NOT NULL,
  sessionid INT         UNSIGNED NOT NULL,
  location  VARCHAR(30),
  INDEX (userid),
  INDEX (sessionid)
);


/*=====================================================================
/* Table: knowservice
/*=====================================================================
CREATE TABLE knowservice (
  serviceid   INT          UNSIGNED NOT NULL AUTO_INCREMENT,
  protocolid  INT          UNSIGNED NOT NULL,
  portnumber  INT          UNSIGNED NOT NULL,
  servicename VARCHAR(255),
  PRIMARY KEY (serviceid),
  INDEX (protocolid)
);

/*=====================================================================
/* Table: executedplugin
/*=====================================================================
CREATE TABLE executedplugin (
  executionid    INT         UNSIGNED NOT NULL AUTO_INCREMENT,
  pluginid       INT         UNSIGNED NOT NULL,
  hostid         INT         UNSIGNED NOT NULL,
  sessionid      INT         UNSIGNED NOT NULL,
  serviceid      INT         UNSIGNED NOT NULL,
  completed      TINYINT     UNSIGNED NOT NULL,
  starttime      DATETIME    NOT NULL,
  endtime        DATETIME,
  pluginrevision VARCHAR(10),
  PRIMARY KEY (executionid),
  INDEX       (pluginid),
  INDEX       (hostid),
  INDEX       (sessionid),
  INDEX       (serviceid)
);


/*=====================================================================
/* Table: service
/*=====================================================================
CREATE TABLE service (
  pluginid  INT UNSIGNED NOT NULL,
  hostid    INT UNSIGNED NOT NULL,
  sessionid INT UNSIGNED NOT NULL,
  serviceid INT UNSIGNED NOT NULL,
  INDEX (pluginid),
  INDEX (hostid),
  INDEX (sessionid),
  INDEX (serviceid)
);


/*=====================================================================
/* Table: session
/*=====================================================================
CREATE TABLE session (
  sessionid     INT          UNSIGNED NOT NULL AUTO_INCREMENT,
  starttime     DATETIME,
  endtime       DATETIME,
  preferences   VARCHAR(255),
  ipaddresses   VARCHAR(30),
  description   VARCHAR(255),
  nessusversion VARCHAR(10),
  configuration TEXT,
  PRIMARY KEY (sessionid)
);


/*=====================================================================
/* Table: knowledgebase
/*=====================================================================
CREATE TABLE knowledgebase (
  knowledgeid  INT      UNSIGNED NOT NULL AUTO_INCREMENT,
  hostid       INT      UNSIGNED NOT NULL,
  sessionid    INT      UNSIGNED NOT NULL,
  kbkey        INT      UNSIGNED NOT NULL,
  value        TEXT     NOT NULL,
  creationtime DATETIME NOT NULL,
  PRIMARY KEY (knowledgeid),
  INDEX       (hostid),
  INDEX       (sessionid)
);


/*=====================================================================
/* Table: host
/*=====================================================================
CREATE TABLE host (
  hostid INT         UNSIGNED NOT NULL AUTO_INCREMENT,
  name   VARCHAR(30),
  ip     VARCHAR(16) NOT NULL,
  dns    VARCHAR(30),
  mac    VARCHAR(18),
  PRIMARY KEY (hostid),
  INDEX       (ip)
);