1-- Copyright (C) 2000-2002 Carnegie Mellon University 2-- 3-- Author(s): Chris Reid <Chris.Reid@CodeCraftConsultants.com> 4-- 5-- Based on the create_mysql file from: 6-- Jed Pickel <jpickel@cert.org>, <jed@pickel.net> 7-- Roman Danyliw <rdd@cert.org>, <roman@danyliw.com> 8-- Todd Schrubb <tls@cert.org> 9-- 10-- This program is free software; you can redistribute it and/or modify 11-- it under the terms of the GNU General Public License Version 2 as 12-- published by the Free Software Foundation. You may not use, modify or 13-- distribute this program under any other version of the GNU General 14-- Public License. 15-- 16-- This program is distributed in the hope that it will be useful, 17-- but WITHOUT ANY WARRANTY; without even the implied warranty of 18-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 19-- GNU General Public License for more details. 20-- 21-- You should have received a copy of the GNU General Public License 22-- along with this program; if not, write to the Free Software 23-- Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA. 24 25 26 27-- Note that Roman Danyliw graciously provides an Entity Relationship diagram 28-- for the Snort / ACID database schema. This ERD is available from the ACID 29-- website: 30-- 31-- http://www.cert.org/kb/acid/ 32-- 33-- In the comments below, all fields marked as "FK" (foreign key) should 34-- be interpreted as "implied" foreign key, not "enforced" foreign key. 35-- These comments are intended to be used to help a database administrator 36-- construct their own diagram showing relationships between tables. 37 38 39 40CREATE TABLE [schema] ( vseq NUMERIC(10,0) NOT NULL, 41 ctime DATETIME NOT NULL, 42 PRIMARY KEY (vseq)) 43INSERT INTO [schema] (vseq, ctime) VALUES ('107', GETDATE()) 44 45CREATE TABLE event ( sid NUMERIC(10,0) NOT NULL , -- FK to sensor.sid 46 cid NUMERIC(10,0) NOT NULL , 47 signature NUMERIC(10,0) NOT NULL , -- FK to signature.sig_id 48 timestamp DATETIME NOT NULL , 49 PRIMARY KEY (sid,cid)) 50CREATE INDEX IX_event_signature ON event(signature) 51CREATE INDEX IX_event_timestamp ON event(timestamp) 52 53CREATE TABLE signature ( sig_id NUMERIC(10,0) IDENTITY(1,1) NOT NULL , 54 sig_name VARCHAR(255) NOT NULL, 55 sig_class_id NUMERIC(10,0), -- FK to sig_class.sig_class_id 56 sig_priority NUMERIC(10,0), 57 sig_rev NUMERIC(10,0), 58 sig_sid NUMERIC(10,0), 59 sig_gid NUMERIC(10,0), 60 PRIMARY KEY (sig_id)) 61CREATE INDEX IX_signature_signame ON signature(sig_name) 62CREATE INDEX IX_signature_sigclassid ON signature(sig_class_id) 63 64CREATE TABLE sig_reference ( sig_id NUMERIC(10,0) NOT NULL, -- FK to signature.sig_id 65 ref_seq NUMERIC(10,0) NOT NULL, 66 ref_id NUMERIC(10,0) NOT NULL, -- FK to reference.ref_id 67 PRIMARY KEY(sig_id, ref_seq)) 68 69CREATE TABLE reference ( ref_id NUMERIC(10,0) IDENTITY(1,1) NOT NULL, 70 ref_system_id NUMERIC(10,0) NOT NULL, -- FK to reference_system.ref_system_id 71 ref_tag VARCHAR(8000) NOT NULL, 72 PRIMARY KEY (ref_id)) 73 74CREATE TABLE reference_system ( ref_system_id NUMERIC(10,0) IDENTITY(1,1) NOT NULL, 75 ref_system_name VARCHAR(20), 76 PRIMARY KEY (ref_system_id)) 77 78CREATE TABLE sig_class ( sig_class_id NUMERIC(10,0) IDENTITY(1,1) NOT NULL, 79 sig_class_name VARCHAR(60) NOT NULL, 80 PRIMARY KEY (sig_class_id)) 81CREATE INDEX IX_sigclass_sigclassid ON sig_class(sig_class_id) 82CREATE INDEX IX_sigclass_sigclassname ON sig_class(sig_class_name) 83 84 85-- store info about the sensor supplying data 86CREATE TABLE sensor ( sid NUMERIC(10,0) IDENTITY(1,1) NOT NULL , 87 hostname VARCHAR(100) , 88 interface VARCHAR(100) , 89 filter VARCHAR(100) , 90 detail INT , -- FK to detail.detail_type 91 encoding INT , -- FK to encoding.encoding_type 92 last_cid NUMERIC(10,0) NOT NULL, 93 PRIMARY KEY (sid)) 94 95-- All of the fields of an ip header 96CREATE TABLE iphdr ( sid NUMERIC(10,0) NOT NULL , -- FK to event.sid, event.cid 97 cid NUMERIC(10,0) NOT NULL , 98 ip_src NUMERIC(10,0) NOT NULL , 99 ip_dst NUMERIC(10,0) NOT NULL , 100 ip_ver TINYINT , 101 ip_hlen TINYINT , 102 ip_tos TINYINT , 103 ip_len INT , 104 ip_id INT , 105 ip_flags TINYINT , 106 ip_off INT , 107 ip_ttl TINYINT , 108 ip_proto TINYINT NOT NULL , 109 ip_csum INT , 110 PRIMARY KEY (sid,cid) ) 111CREATE INDEX IX_iphdr_ipsrc ON iphdr(ip_src) 112CREATE INDEX IX_iphdr_ipdst ON iphdr(ip_dst) 113 114-- All of the fields of a tcp header 115CREATE TABLE tcphdr( sid NUMERIC(10,0) NOT NULL , -- FK to event.sid, event.cid 116 cid NUMERIC(10,0) NOT NULL , 117 tcp_sport INT NOT NULL , 118 tcp_dport INT NOT NULL , 119 tcp_seq NUMERIC(10,0) , 120 tcp_ack NUMERIC(10,0) , 121 tcp_off TINYINT , 122 tcp_res TINYINT , 123 tcp_flags TINYINT NOT NULL , -- FK to protocols (see snortdb-extra) 124 tcp_win INT , 125 tcp_csum INT , 126 tcp_urp INT , 127 PRIMARY KEY (sid,cid)) 128CREATE INDEX IX_tcphdr_sport ON tcphdr(tcp_sport) 129CREATE INDEX IX_tcphdr_dport ON tcphdr(tcp_dport) 130CREATE INDEX IX_tcphdr_tcpflags ON tcphdr(tcp_flags) 131 132-- All of the fields of a udp header 133CREATE TABLE udphdr( sid NUMERIC(10,0) NOT NULL , -- FK to event.sid, event.cid 134 cid NUMERIC(10,0) NOT NULL , 135 udp_sport INT NOT NULL , 136 udp_dport INT NOT NULL , 137 udp_len INT , 138 udp_csum INT , 139 PRIMARY KEY (sid,cid)) 140CREATE INDEX IX_udphdr_sport ON udphdr(udp_sport) 141CREATE INDEX IX_udphdr_dport ON udphdr(udp_dport) 142 143-- All of the fields of an icmp header 144CREATE TABLE icmphdr( sid NUMERIC(10,0) NOT NULL , -- FK to event.sid, event.cid 145 cid NUMERIC(10,0) NOT NULL , 146 icmp_type TINYINT NOT NULL , 147 icmp_code TINYINT NOT NULL , 148 icmp_csum INT , 149 icmp_id INT , 150 icmp_seq INT , 151 PRIMARY KEY (sid,cid)) 152CREATE INDEX IX_icmphdr_icmptype ON icmphdr(icmp_type) 153 154-- Protocol options 155CREATE TABLE opt ( sid NUMERIC(10,0) NOT NULL , -- FK to iphdr.sid, iphdr.cid 156 cid NUMERIC(10,0) NOT NULL , -- or to tcphdr.sid, tcphdr.cid 157 optid NUMERIC(10,0) NOT NULL , 158 opt_proto TINYINT NOT NULL , 159 opt_code TINYINT NOT NULL , 160 opt_len INT , 161 opt_data VARCHAR(8000) , 162 PRIMARY KEY (sid,cid,optid)) 163 164-- Packet payload 165CREATE TABLE data ( sid NUMERIC(10,0) NOT NULL , -- FK to event.sid, event.cid 166 cid NUMERIC(10,0) NOT NULL , 167 data_payload VARCHAR(8000) , 168 PRIMARY KEY (sid,cid)) 169 170-- encoding is a lookup table for storing encoding types 171CREATE TABLE encoding(encoding_type TINYINT NOT NULL , 172 encoding_text VARCHAR(50) NOT NULL , 173 PRIMARY KEY (encoding_type)) 174INSERT INTO encoding (encoding_type, encoding_text) VALUES (0, 'hex') 175INSERT INTO encoding (encoding_type, encoding_text) VALUES (1, 'base64') 176INSERT INTO encoding (encoding_type, encoding_text) VALUES (2, 'ascii') 177 178-- detail is a lookup table for storing different detail levels 179CREATE TABLE detail (detail_type TINYINT NOT NULL , 180 detail_text VARCHAR(50) NOT NULL , 181 PRIMARY KEY (detail_type)) 182INSERT INTO detail (detail_type, detail_text) VALUES (0, 'fast') 183INSERT INTO detail (detail_type, detail_text) VALUES (1, 'full') 184 185-- be sure to also use the snortdb-extra tables if you want 186-- mappings for tcp flags, protocols, and ports 187 188 189grant select, insert on [schema] to public 190grant select, insert on signature to public 191grant select, insert on sig_reference to public 192grant select, insert on reference to public 193grant select, insert on reference_system to public 194grant select, insert on sig_class to public 195grant select, insert on data to public 196grant select, insert on detail to public 197grant select, insert on encoding to public 198grant select, insert on event to public 199grant select, insert on icmphdr to public 200grant select, insert on iphdr to public 201grant select, insert on opt to public 202grant select, insert on sensor to public 203grant select, insert on tcphdr to public 204grant select, insert on udphdr to public 205 206