1-- Copyright (C) 2000-2002 Carnegie Mellon University 2-- Portions Copyright (C) 2000 Mike Andersen <mike@src.no> 3-- Portions Copyright (C) 2001 Andrew Stubbs <andrews@stusoft.com> 4-- Portions Copyright (C) 2001 Jed Pickel <jed@pickel.net> 5-- 6-- Author(s): Mike Andersen <mike@src.no> 7-- Thomas Stenhaug <thomas@src.no> 8-- 9-- Maintainer: Roman Danyliw <rdd@cert.org>, <roman@danyliw.com> 10-- 11-- This program is free software; you can redistribute it and/or modify 12-- it under the terms of the GNU General Public License as published by 13-- the Free Software Foundation. You may not use, modify or distribute 14-- this program under any other version of the GNU General 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, 24-- MA 02111-1307, USA. 25-- 26-- 27-- This file was recently updated by Andrew Stubbs to fix some bugs 28-- and make the script more user friendly. 29-- 30-- Comments from Andrew <andrews@stusoft.com> on his update: 31-- 32-- There's a trigger in place of the AUTO_INCREMENT-option for the 33-- sensor.sid. I don't fully understand how the NUMBER-type conversion 34-- works at this point. 35-- 36-- Oracles DATE seems "bit" more picky on the format than MySQL. 37-- 38-- Rename it to : create_oracle.sql 39-- to run type : sqlplus user/password@db_instance @ create_oracle.sql 40-- 41-- The drop tables / sequences are a personal preference - remove if 42-- you wish the prompt merely echos the stuff after it - useful for 43-- figuring out where you are when its running 44 45prompt schema; 46drop table schema; 47 48CREATE TABLE schema ( vseq INT NOT NULL, 49 ctime VARCHAR2(24) NOT NULL, 50 PRIMARY KEY (vseq)); 51 52INSERT INTO schema (vseq, ctime) VALUES ('107', sysdate); 53 54prompt event; 55drop table event; 56CREATE TABLE event ( sid INT NOT NULL, 57 cid INT NOT NULL, 58 signature INT NOT NULL, 59 timestamp DATE NOT NULL, 60 PRIMARY KEY (sid,cid)); 61 62prompt signature; 63drop table signature; 64CREATE TABLE signature ( sig_id INT NOT NULL, 65 sig_name VARCHAR2(255), 66 sig_class_id INT, 67 sig_priority INT, 68 sig_rev INT, 69 sig_sid INT, 70 sig_gid INT, 71 PRIMARY KEY (sig_id)); 72 73-- 74-- auto-increment the signature.sig_id 75-- 76drop sequence seq_snort_signature_id ; 77CREATE SEQUENCE seq_snort_signature_id START WITH 1 INCREMENT BY 1; 78 79CREATE or replace TRIGGER tr_snort_signature_id 80 BEFORE INSERT ON signature 81 FOR EACH ROW 82 BEGIN 83 SELECT seq_snort_signature_id.nextval INTO :new.SIG_ID FROM 84dual; 85 END; 86/ 87prompt sig_reference; 88drop table sig_reference; 89CREATE TABLE sig_reference (sig_id INT NOT NULL, 90 ref_seq INT NOT NULL, 91 ref_id INT NOT NULL, 92 PRIMARY KEY(sig_id, ref_seq)); 93 94prompt reference; 95drop table reference; 96CREATE TABLE reference ( ref_id INT NOT NULL, 97 ref_system_id INT NOT NULL, 98 ref_tag VARCHAR2(100) NOT NULL, 99 PRIMARY KEY (ref_id)); 100-- 101-- auto-increment the reference.ref_id 102-- 103 104drop sequence seq_snort_reference_id; 105CREATE SEQUENCE seq_snort_reference_id START WITH 1 INCREMENT BY 1; 106 107CREATE or replace TRIGGER tr_snort_reference_id 108 BEFORE INSERT ON reference 109 FOR EACH ROW 110 BEGIN 111 SELECT seq_snort_reference_id.nextval INTO :new.REF_ID FROM 112dual; 113 END; 114/ 115 116prompt reference_system; 117drop table reference_system ; 118CREATE TABLE reference_system ( ref_system_id INT NOT NULL, 119 ref_system_name VARCHAR2(20), 120 PRIMARY KEY (ref_system_id)); 121 122drop sequence seq_snort_ref_system_id ; 123CREATE SEQUENCE seq_snort_ref_system_id START WITH 1 INCREMENT BY 1; 124CREATE or replace TRIGGER tr_snort_ref_system_id 125 BEFORE INSERT ON reference_system 126 FOR EACH ROW 127 BEGIN 128 SELECT seq_snort_ref_system_id.nextval INTO 129:new.REF_SYSTEM_ID FROM dual; 130 END; 131/ 132prompt sig_class; 133drop table sig_class; 134CREATE TABLE sig_class ( sig_class_id INT NOT NULL, 135 sig_class_name VARCHAR(60) NOT NULL, 136 PRIMARY KEY (sig_class_id)); 137 138drop sequence seq_snort_sig_class_id ; 139CREATE SEQUENCE seq_snort_sig_class_id START WITH 1 INCREMENT BY 1; 140CREATE or REPLACE TRIGGER tr_snort_sig_class_id 141 BEFORE INSERT ON sig_class 142 FOR EACH ROW 143 BEGIN 144 select seq_snort_sig_class_id.nextval into :new.sig_class_id from 145dual; 146 END; 147/ 148-- 149-- store info about the sensor supplying data 150-- 151prompt sensor; 152drop table sensor; 153CREATE TABLE sensor ( 154 sid INT NOT NULL, 155 hostname VARCHAR2(100), 156 interface VARCHAR2(100), 157 filter VARCHAR2(100), 158 detail INT, 159 encoding INT, 160 last_cid INT NOT NULL, 161 PRIMARY KEY (sid)); 162 163-- 164-- auto-increment the sensor.sid 165-- 166drop sequence seq_snort_sensor_id ; 167CREATE SEQUENCE seq_snort_sensor_id START WITH 1 INCREMENT BY 1; 168 169CREATE OR REPLACE TRIGGER tr_snort_sensor_id 170 BEFORE INSERT ON sensor 171 FOR EACH ROW 172 BEGIN 173 SELECT seq_snort_sensor_id.nextval INTO :new.SID FROM dual; 174 END; 175/ 176 177-- All of the fields of an ip header 178prompt iphdr; 179drop table iphdr; 180CREATE TABLE iphdr ( 181 sid INT NOT NULL, 182 cid INT NOT NULL, 183 ip_src INT NOT NULL, 184 ip_dst INT NOT NULL, 185 ip_ver INT, 186 ip_hlen INT, 187 ip_tos INT, 188 ip_len INT, 189 ip_id INT, 190 ip_flags INT, 191 ip_off INT, 192 ip_ttl INT, 193 ip_proto INT NOT NULL, 194 ip_csum INT, 195 PRIMARY KEY (sid,cid)); 196 197 198-- All of the fields of a tcp header 199prompt tcphdr; 200drop table tcphdr; 201CREATE TABLE tcphdr ( 202 sid INT NOT NULL, 203 cid INT NOT NULL, 204 tcp_sport INT NOT NULL, 205 tcp_dport INT NOT NULL, 206 tcp_seq INT, 207 tcp_ack INT, 208 tcp_off INT, 209 tcp_res INT, 210 tcp_flags INT NOT NULL, 211 tcp_win INT, 212 tcp_csum INT, 213 tcp_urp INT, 214 PRIMARY KEY (sid,cid)); 215 216 217-- All of the fields of a udp header 218prompt udphdr; 219drop table udphdr; 220CREATE TABLE udphdr ( 221 sid INT NOT NULL, 222 cid INT NOT NULL, 223 udp_sport INT NOT NULL, 224 udp_dport INT NOT NULL, 225 udp_len INT, 226 udp_csum INT, 227 PRIMARY KEY (sid,cid)); 228 229 230-- All of the fields of an icmp header 231prompt icmphdr; 232drop table icmphdr; 233CREATE TABLE icmphdr( 234 sid INT NOT NULL, 235 cid INT NOT NULL, 236 icmp_type INT NOT NULL, 237 icmp_code INT NOT NULL, 238 icmp_csum INT, 239 icmp_id INT, 240 icmp_seq INT, 241 PRIMARY KEY (sid,cid)); 242 243 244-- Protocol options 245prompt opt; 246drop table opt; 247CREATE TABLE opt ( 248 sid INT NOT NULL, 249 cid INT NOT NULL, 250 optid INT NOT NULL, 251 opt_proto INT NOT NULL, 252 opt_code INT NOT NULL, 253 opt_len INT, 254 opt_data BLOB, 255 PRIMARY KEY (sid,cid,optid)); 256 257 258-- Packet payload 259prompt data; 260drop table data; 261CREATE TABLE data ( 262 sid INT NOT NULL, 263 cid INT NOT NULL, 264 data_payload BLOB, 265 PRIMARY KEY (sid,cid)); 266 267 268-- encoding is a lookup table for storing encoding types 269prompt encoding 270drop table encoding; 271CREATE TABLE encoding ( 272 encoding_type INT NOT NULL, 273 encoding_text VARCHAR2(50) NOT NULL, 274 PRIMARY KEY (encoding_type)); 275 276INSERT INTO encoding (encoding_type, encoding_text) VALUES (0, 'hex'); 277INSERT INTO encoding (encoding_type, encoding_text) VALUES (1, 'base64'); 278INSERT INTO encoding (encoding_type, encoding_text) VALUES (2, 'ascii'); 279 280 281-- detail is a lookup table for storing different detail levels 282prompt detail; 283drop table detail; 284CREATE TABLE detail ( 285 detail_type INT NOT NULL, 286 detail_text VARCHAR2(50) NOT NULL, 287 PRIMARY KEY (detail_type)); 288 289INSERT INTO detail (detail_type, detail_text) VALUES (0, 'fast'); 290INSERT INTO detail (detail_type, detail_text) VALUES (1, 'full'); 291