1-- Copyright (C) 2000-2002 Carnegie Mellon University 2-- 3-- Maintainer: Roman Danyliw <rdd@cert.org>, <roman@danyliw.com> 4-- 5-- Original Author(s): Jed Pickel <jed@pickel.net> (2000-2001) 6-- Roman Danyliw <rdd@cert.org> 7-- Todd Schrubb <tls@cert.org> 8-- 9-- This program is free software; you can redistribute it and/or modify 10-- it under the terms of the GNU General Public License Version 2 as 11-- published by the Free Software Foundation. You may not use, modify or 12-- distribute this program under any other version of the GNU General 13-- Public License. 14-- 15-- This program is distributed in the hope that it will be useful, 16-- but WITHOUT ANY WARRANTY; without even the implied warranty of 17-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 18-- GNU General Public License for more details. 19-- 20-- You should have received a copy of the GNU General Public License 21-- along with this program; if not, write to the Free Software 22-- Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA. 23 24CREATE TABLE schema ( vseq INT4 NOT NULL, 25 ctime TIMESTAMP with time zone NOT NULL, 26 PRIMARY KEY (vseq)); 27INSERT INTO schema (vseq, ctime) VALUES ('107', now()); 28 29CREATE TABLE signature ( sig_id SERIAL NOT NULL, 30 sig_name TEXT NOT NULL, 31 sig_class_id INT8, 32 sig_priority INT8, 33 sig_rev INT8, 34 sig_sid INT8, 35 sig_gid INT8, 36 PRIMARY KEY (sig_id)); 37CREATE INDEX sig_name_idx ON signature (sig_name); 38CREATE INDEX sig_class_idx ON signature (sig_class_id); 39 40CREATE TABLE sig_reference (sig_id INT4 NOT NULL, 41 ref_seq INT4 NOT NULL, 42 ref_id INT4 NOT NULL, 43 PRIMARY KEY(sig_id, ref_seq)); 44 45CREATE TABLE reference ( ref_id SERIAL, 46 ref_system_id INT4 NOT NULL, 47 ref_tag TEXT NOT NULL, 48 PRIMARY KEY (ref_id)); 49 50CREATE TABLE reference_system ( ref_system_id SERIAL, 51 ref_system_name TEXT, 52 PRIMARY KEY (ref_system_id)); 53 54CREATE TABLE sig_class ( sig_class_id SERIAL, 55 sig_class_name TEXT NOT NULL, 56 PRIMARY KEY (sig_class_id) ); 57CREATE INDEX sig_class_name_idx ON sig_class (sig_class_name); 58 59CREATE TABLE event ( sid INT4 NOT NULL, 60 cid INT8 NOT NULL, 61 signature INT4 NOT NULL, 62 timestamp timestamp with time zone 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 SERIAL, 69 hostname TEXT, 70 interface TEXT, 71 filter TEXT, 72 detail INT2, 73 encoding INT2, 74 last_cid INT8 NOT NULL, 75 PRIMARY KEY (sid)); 76 77-- All of the fields of an ip header 78CREATE TABLE iphdr ( sid INT4 NOT NULL, 79 cid INT8 NOT NULL, 80 ip_src INT8 NOT NULL, 81 ip_dst INT8 NOT NULL, 82 ip_ver INT2, 83 ip_hlen INT2, 84 ip_tos INT2, 85 ip_len INT4, 86 ip_id INT4, 87 ip_flags INT2, 88 ip_off INT4, 89 ip_ttl INT2, 90 ip_proto INT2 NOT NULL, 91 ip_csum INT4, 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 INT4 NOT NULL, 98 cid INT8 NOT NULL, 99 tcp_sport INT4 NOT NULL, 100 tcp_dport INT4 NOT NULL, 101 tcp_seq INT8, 102 tcp_ack INT8, 103 tcp_off INT2, 104 tcp_res INT2, 105 tcp_flags INT2 NOT NULL, 106 tcp_win INT4, 107 tcp_csum INT4, 108 tcp_urp INT4, 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 INT4 NOT NULL, 116 cid INT8 NOT NULL, 117 udp_sport INT4 NOT NULL, 118 udp_dport INT4 NOT NULL, 119 udp_len INT4, 120 udp_csum INT4, 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 INT4 NOT NULL, 127 cid INT8 NOT NULL, 128 icmp_type INT2 NOT NULL, 129 icmp_code INT2 NOT NULL, 130 icmp_csum INT4, 131 icmp_id INT4, 132 icmp_seq INT4, 133 PRIMARY KEY (sid,cid)); 134CREATE INDEX icmp_type_idx ON icmphdr (icmp_type); 135 136-- Protocol options 137CREATE TABLE opt ( sid INT4 NOT NULL, 138 cid INT8 NOT NULL, 139 optid INT2 NOT NULL, 140 opt_proto INT2 NOT NULL, 141 opt_code INT2 NOT NULL, 142 opt_len INT4, 143 opt_data TEXT, 144 PRIMARY KEY (sid,cid,optid)); 145 146-- Packet payload 147CREATE TABLE data ( sid INT4 NOT NULL, 148 cid INT8 NOT NULL, 149 data_payload TEXT, 150 PRIMARY KEY (sid,cid)); 151 152-- encoding is a lookup table for storing encoding types 153CREATE TABLE encoding(encoding_type INT2 NOT NULL, 154 encoding_text TEXT 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 INT2 NOT NULL, 162 detail_text TEXT 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