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