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 INT UNSIGNED NOT NULL, 25 ctime DATETIME NOT NULL, 26 PRIMARY KEY (vseq)); 27INSERT INTO `schema` (vseq, ctime) VALUES ('107', now()); 28 29CREATE TABLE event ( sid INT UNSIGNED NOT NULL, 30 cid INT UNSIGNED NOT NULL, 31 signature INT UNSIGNED NOT NULL, 32 timestamp DATETIME NOT NULL, 33 PRIMARY KEY (sid,cid), 34 INDEX sig (signature), 35 INDEX time (timestamp)); 36 37CREATE TABLE signature ( sig_id INT UNSIGNED NOT NULL AUTO_INCREMENT, 38 sig_name VARCHAR(255) NOT NULL, 39 sig_class_id INT UNSIGNED NOT NULL, 40 sig_priority INT UNSIGNED, 41 sig_rev INT UNSIGNED, 42 sig_sid INT UNSIGNED, 43 sig_gid INT UNSIGNED, 44 PRIMARY KEY (sig_id), 45 INDEX sign_idx (sig_name(20)), 46 INDEX sig_class_id_idx (sig_class_id)); 47 48CREATE TABLE sig_reference (sig_id INT UNSIGNED NOT NULL, 49 ref_seq INT UNSIGNED NOT NULL, 50 ref_id INT UNSIGNED NOT NULL, 51 PRIMARY KEY(sig_id, ref_seq)); 52 53CREATE TABLE reference ( ref_id INT UNSIGNED NOT NULL AUTO_INCREMENT, 54 ref_system_id INT UNSIGNED NOT NULL, 55 ref_tag TEXT NOT NULL, 56 PRIMARY KEY (ref_id)); 57 58CREATE TABLE reference_system ( ref_system_id INT UNSIGNED NOT NULL AUTO_INCREMENT, 59 ref_system_name VARCHAR(20), 60 PRIMARY KEY (ref_system_id)); 61 62CREATE TABLE sig_class ( sig_class_id INT UNSIGNED NOT NULL AUTO_INCREMENT, 63 sig_class_name VARCHAR(60) NOT NULL, 64 PRIMARY KEY (sig_class_id), 65 INDEX (sig_class_id), 66 INDEX (sig_class_name)); 67 68# store info about the sensor supplying data 69CREATE TABLE sensor ( sid INT UNSIGNED NOT NULL AUTO_INCREMENT, 70 hostname TEXT, 71 interface TEXT, 72 filter TEXT, 73 detail TINYINT, 74 encoding TINYINT, 75 last_cid INT UNSIGNED NOT NULL, 76 PRIMARY KEY (sid)); 77 78# All of the fields of an ip header 79CREATE TABLE iphdr ( sid INT UNSIGNED NOT NULL, 80 cid INT UNSIGNED NOT NULL, 81 ip_src INT UNSIGNED NOT NULL, 82 ip_dst INT UNSIGNED NOT NULL, 83 ip_ver TINYINT UNSIGNED, 84 ip_hlen TINYINT UNSIGNED, 85 ip_tos TINYINT UNSIGNED, 86 ip_len SMALLINT UNSIGNED, 87 ip_id SMALLINT UNSIGNED, 88 ip_flags TINYINT UNSIGNED, 89 ip_off SMALLINT UNSIGNED, 90 ip_ttl TINYINT UNSIGNED, 91 ip_proto TINYINT UNSIGNED NOT NULL, 92 ip_csum SMALLINT UNSIGNED, 93 PRIMARY KEY (sid,cid), 94 INDEX ip_src (ip_src), 95 INDEX ip_dst (ip_dst)); 96 97# All of the fields of a tcp header 98CREATE TABLE tcphdr( sid INT UNSIGNED NOT NULL, 99 cid INT UNSIGNED NOT NULL, 100 tcp_sport SMALLINT UNSIGNED NOT NULL, 101 tcp_dport SMALLINT UNSIGNED NOT NULL, 102 tcp_seq INT UNSIGNED, 103 tcp_ack INT UNSIGNED, 104 tcp_off TINYINT UNSIGNED, 105 tcp_res TINYINT UNSIGNED, 106 tcp_flags TINYINT UNSIGNED NOT NULL, 107 tcp_win SMALLINT UNSIGNED, 108 tcp_csum SMALLINT UNSIGNED, 109 tcp_urp SMALLINT UNSIGNED, 110 PRIMARY KEY (sid,cid), 111 INDEX tcp_sport (tcp_sport), 112 INDEX tcp_dport (tcp_dport), 113 INDEX tcp_flags (tcp_flags)); 114 115# All of the fields of a udp header 116CREATE TABLE udphdr( sid INT UNSIGNED NOT NULL, 117 cid INT UNSIGNED NOT NULL, 118 udp_sport SMALLINT UNSIGNED NOT NULL, 119 udp_dport SMALLINT UNSIGNED NOT NULL, 120 udp_len SMALLINT UNSIGNED, 121 udp_csum SMALLINT UNSIGNED, 122 PRIMARY KEY (sid,cid), 123 INDEX udp_sport (udp_sport), 124 INDEX udp_dport (udp_dport)); 125 126# All of the fields of an icmp header 127CREATE TABLE icmphdr( sid INT UNSIGNED NOT NULL, 128 cid INT UNSIGNED NOT NULL, 129 icmp_type TINYINT UNSIGNED NOT NULL, 130 icmp_code TINYINT UNSIGNED NOT NULL, 131 icmp_csum SMALLINT UNSIGNED, 132 icmp_id SMALLINT UNSIGNED, 133 icmp_seq SMALLINT UNSIGNED, 134 PRIMARY KEY (sid,cid), 135 INDEX icmp_type (icmp_type)); 136 137# Protocol options 138CREATE TABLE opt ( sid INT UNSIGNED NOT NULL, 139 cid INT UNSIGNED NOT NULL, 140 optid INT UNSIGNED NOT NULL, 141 opt_proto TINYINT UNSIGNED NOT NULL, 142 opt_code TINYINT UNSIGNED NOT NULL, 143 opt_len SMALLINT, 144 opt_data TEXT, 145 PRIMARY KEY (sid,cid,optid)); 146 147# Packet payload 148CREATE TABLE data ( sid INT UNSIGNED NOT NULL, 149 cid INT UNSIGNED NOT NULL, 150 data_payload TEXT, 151 PRIMARY KEY (sid,cid)); 152 153# encoding is a lookup table for storing encoding types 154CREATE TABLE encoding(encoding_type TINYINT UNSIGNED NOT NULL, 155 encoding_text TEXT NOT NULL, 156 PRIMARY KEY (encoding_type)); 157INSERT INTO encoding (encoding_type, encoding_text) VALUES (0, 'hex'); 158INSERT INTO encoding (encoding_type, encoding_text) VALUES (1, 'base64'); 159INSERT INTO encoding (encoding_type, encoding_text) VALUES (2, 'ascii'); 160 161# detail is a lookup table for storing different detail levels 162CREATE TABLE detail (detail_type TINYINT UNSIGNED NOT NULL, 163 detail_text TEXT NOT NULL, 164 PRIMARY KEY (detail_type)); 165INSERT INTO detail (detail_type, detail_text) VALUES (0, 'fast'); 166INSERT INTO detail (detail_type, detail_text) VALUES (1, 'full'); 167 168# be sure to also use the snortdb-extra tables if you want 169# mappings for tcp flags, protocols, and ports 170