1-- Maintainer and author: Vlatko Kosturjak <kost at linux dot hr>
2--
3-- Usage:
4-- db2 create database snort
5-- db2 connect to snort
6-- db2 -tvf create_db2
7--
8-- This program is free software; you can redistribute it and/or modify
9-- it under the terms of the GNU General Public License Version 2 as
10-- published by the Free Software Foundation.  You may not use, modify or
11-- distribute this program under any other version of the GNU General
12-- Public License.
13--
14-- This program is distributed in the hope that it will be useful,
15-- but WITHOUT ANY WARRANTY; without even the implied warranty of
16-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
17-- GNU General Public License for more details.
18--
19-- You should have received a copy of the GNU General Public License
20-- along with this program; if not, write to the Free Software
21-- Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
22
23CREATE TABLE schema ( vseq        INT     NOT NULL,
24                      ctime       TIMESTAMP NOT NULL,
25                      PRIMARY KEY (vseq));
26INSERT INTO schema  (vseq, ctime) VALUES (107, current timestamp);
27
28CREATE TABLE signature ( sig_id       INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
29                         sig_name     VARCHAR(255) NOT NULL,
30                         sig_class_id BIGINT,
31                         sig_priority BIGINT,
32                         sig_rev      BIGINT,
33                         sig_sid      BIGINT,
34			 sig_gid      BIGINT,
35                         PRIMARY KEY (sig_id));
36CREATE INDEX sig_name_idx ON signature (sig_name);
37CREATE INDEX sig_class_idx ON signature (sig_class_id);
38
39CREATE TABLE sig_reference (sig_id  INT  NOT NULL,
40                            ref_seq INT  NOT NULL,
41                            ref_id  INT  NOT NULL,
42                            PRIMARY KEY(sig_id, ref_seq));
43
44CREATE TABLE reference (  ref_id        INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
45                          ref_system_id INT NOT NULL,
46                          ref_tag       VARCHAR(300) NOT NULL,
47                          PRIMARY KEY (ref_id));
48
49CREATE TABLE reference_system ( ref_system_id   INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
50                                ref_system_name VARCHAR(20),
51                                PRIMARY KEY (ref_system_id));
52
53CREATE TABLE sig_class ( sig_class_id        INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
54                         sig_class_name      VARCHAR(60) NOT NULL,
55                         PRIMARY KEY (sig_class_id) );
56CREATE INDEX sig_class_id_idx ON sig_class (sig_class_id);
57CREATE INDEX sig_class_name_idx ON sig_class (sig_class_name);
58
59CREATE TABLE event  ( sid 	  INT NOT NULL,
60                      cid 	  BIGINT NOT NULL,
61                      signature   INT NOT NULL,
62                      timestamp   timestamp NOT NULL,
63                      PRIMARY KEY (sid,cid));
64CREATE INDEX signature_idx ON event (signature);
65CREATE INDEX timestamp_idx ON event (timestamp);
66
67-- store info about the sensor supplying data
68CREATE TABLE sensor ( sid	  INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
69                      hostname    VARCHAR(300),
70                      interface   VARCHAR(300),
71                      filter	  VARCHAR(300),
72                      detail	  SMALLINT,
73                      encoding	  SMALLINT,
74                      last_cid    BIGINT NOT NULL,
75                      PRIMARY KEY (sid));
76
77-- All of the fields of an ip header
78CREATE TABLE iphdr  ( sid 	  INT NOT NULL,
79                      cid 	  BIGINT NOT NULL,
80                      ip_src      BIGINT NOT NULL,
81                      ip_dst      BIGINT NOT NULL,
82                      ip_ver      SMALLINT,
83                      ip_hlen     SMALLINT,
84                      ip_tos  	  SMALLINT,
85                      ip_len 	  INT,
86                      ip_id    	  INT,
87                      ip_flags    SMALLINT,
88                      ip_off      INT,
89                      ip_ttl   	  SMALLINT,
90                      ip_proto 	  SMALLINT NOT NULL,
91                      ip_csum 	  INT,
92                      PRIMARY KEY (sid,cid));
93CREATE INDEX ip_src_idx ON iphdr (ip_src);
94CREATE INDEX ip_dst_idx ON iphdr (ip_dst);
95
96-- All of the fields of a tcp header
97CREATE TABLE tcphdr(  sid 	  INT NOT NULL,
98                      cid 	  BIGINT NOT NULL,
99                      tcp_sport   INT NOT NULL,
100                      tcp_dport   INT NOT NULL,
101                      tcp_seq     BIGINT,
102                      tcp_ack     BIGINT,
103                      tcp_off     SMALLINT,
104                      tcp_res     SMALLINT,
105                      tcp_flags   SMALLINT NOT NULL,
106                      tcp_win     INT,
107                      tcp_csum    INT,
108                      tcp_urp     INT,
109                      PRIMARY KEY (sid,cid));
110CREATE INDEX tcp_sport_idx ON tcphdr (tcp_sport);
111CREATE INDEX tcp_dport_idx ON tcphdr (tcp_dport);
112CREATE INDEX tcp_flags_idx ON tcphdr (tcp_flags);
113
114-- All of the fields of a udp header
115CREATE TABLE udphdr(  sid 	  INT NOT NULL,
116                      cid 	  BIGINT NOT NULL,
117                      udp_sport   INT NOT NULL,
118                      udp_dport   INT NOT NULL,
119                      udp_len     INT,
120                      udp_csum    INT,
121                      PRIMARY KEY (sid,cid));
122CREATE INDEX udp_sport_idx ON udphdr (udp_sport);
123CREATE INDEX udp_dport_idx ON udphdr (udp_dport);
124
125-- All of the fields of an icmp header
126CREATE TABLE icmphdr( sid 	  INT NOT NULL,
127                      cid 	  BIGINT NOT NULL,
128                      icmp_type   SMALLINT NOT NULL,
129                      icmp_code   SMALLINT NOT NULL,
130                      icmp_csum   INT,
131                      icmp_id     INT,
132                      icmp_seq    INT,
133                      PRIMARY KEY (sid,cid));
134CREATE INDEX icmp_type_idx ON icmphdr (icmp_type);
135
136-- Protocol options
137CREATE TABLE opt    ( sid         INT NOT NULL,
138                      cid         BIGINT NOT NULL,
139                      optid       SMALLINT NOT NULL,
140                      opt_proto   SMALLINT NOT NULL,
141                      opt_code    SMALLINT NOT NULL,
142                      opt_len     INT,
143                      opt_data    VARCHAR(300),
144                      PRIMARY KEY (sid,cid,optid));
145
146-- Packet payload
147CREATE TABLE data   ( sid          INT NOT NULL,
148                      cid          BIGINT NOT NULL,
149                      data_payload VARCHAR(300),
150                      PRIMARY KEY (sid,cid));
151
152-- encoding is a lookup table for storing encoding types
153CREATE TABLE encoding(encoding_type SMALLINT NOT NULL,
154                      encoding_text VARCHAR(300) NOT NULL,
155                      PRIMARY KEY (encoding_type));
156INSERT INTO encoding (encoding_type, encoding_text) VALUES (0, 'hex');
157INSERT INTO encoding (encoding_type, encoding_text) VALUES (1, 'base64');
158INSERT INTO encoding (encoding_type, encoding_text) VALUES (2, 'ascii');
159
160-- detail is a lookup table for storing different detail levels
161CREATE TABLE detail  (detail_type SMALLINT NOT NULL,
162                      detail_text VARCHAR(300) NOT NULL,
163                      PRIMARY KEY (detail_type));
164INSERT INTO detail (detail_type, detail_text) VALUES (0, 'fast');
165INSERT INTO detail (detail_type, detail_text) VALUES (1, 'full');
166
167-- be sure to also use the snortdb-extra tables if you want
168-- mappings for tcp flags, protocols, and ports
169