[Snort-users] Draft: Database layout

Mike Andersen mike at ...207...
Wed Aug 2 14:56:43 EDT 2000


Here is a draft that we can use as a basis for further discussions about
the layout of the database.  Note that I've not done any changes on
indexes or other tables than the four that contains header information.
I have not added any fields for payload either (except icmp_data).

 * Which data type is best for storing icmp_data?

 * What is the best way to represent TCP flags in the database?
   Personally I believe that it's easier for both human and machine to
   use the suggested method.

Hmm... does anyone know any good tools that I can use to draw a database 
diagram?  And runs under Linux? 


#-----------------------------------------------------------------------
# Copyright (C) 2000 Carnegie Mellon University
#
# Author(s): Jed Pickel <jed at ...153...>, Todd Schrubb <tls at ...241...>
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
#----------------------------------------------------------------------

##
## Changes  from the original:
##	Added:
##		- tstamp	Timestamp (automaticly updated in MySQL)
##		- ip_ver	The IP version	      
##		- ip_ihl	Internet Header length
##		- ip_len	Total Length (of datagram)
##		- ip_flags	Various control Flags
##		- ip_chks	Header Checksum
##		- ip_opt	Options
##
CREATE TABLE iphdr  ( sid 	  INT 	    UNSIGNED NOT NULL,
                      cid 	  INT 	    UNSIGNED NOT NULL,
		      tstamp	  TIMESTAMP(14),
		      ip_ver      TINYINT   UNSIGNED,		# 4
		      ip_ihl	  TINYINT   UNSIGNED,		# 4
		      ip_tos  	  TINYINT   UNSIGNED,		# 8
		      ip_len 	  SMALLINT  UNSIGNED,		# 16
		      ip_id    	  SMALLINT  UNSIGNED,		# 16
		      ip_flags    TINYINT   UNSIGNED,		# 3
		      ip_off      SMALLINT  UNSIGNED,		# 13
		      ip_ttl   	  TINYINT   UNSIGNED,		# 8
                      ip_proto 	  TINYINT   UNSIGNED,		# 8
		      ip_chks	  SMALLINT  UNSIGNED,		# 16
                      ip_src  	  INT  	    UNSIGNED NOT NULL,	# 32
                      ip_dst  	  INT       UNSIGNED NOT NULL,	# 32
		      ip_opt	  MEDIUMINT UNSIGNED,		# 24
                      PRIMARY KEY (sid,cid),
                      INDEX   sip (ip_src0,ip_src1,ip_src2,ip_src3),
                      INDEX   dip (ip_dst0,ip_dst1,ip_dst2,ip_dst3));

##
## Changes from the original:
##	The names starts with tcp instead of th
##
##	Added:
##		- tcp_seq	Sequence Number
##		- tcp_aseq	Acknowledgment Number
##		- tcp_doff	Data Offset
##		- tcp_res	Reserved bits
##		- tcp_urg	
##		- tcp_ack
##		- tcp_psh
##		- tcp_syn
##		- tcp_fin
##		- tcp_chks	Header Checksum
##		- tcp_opt	Options
##
##   	Question:
##		Which format is better for flags, true/fals or all bits
##		represented in one TINYINT?      
##
CREATE TABLE tcphdr(  sid 	  INT 	    UNSIGNED NOT NULL,
                      cid 	  INT 	    UNSIGNED NOT NULL,
		      tcp_sport   SMALLINT  UNSIGNED NOT NULL,	# 16
		      tcp_dport   SMALLINT  UNSIGNED NOT NULL,	# 16
		      tcp_seq	  INT       UNSIGNED,		# 32
		      tcp_ack     INT       UNSIGNED,		# 32
		      tcp_doff    TINYINT   UNSIGNED,		# 4
		      tcp_res     TINYINT   UNSIGNED,		# 6
		      tcp_urg	  TINYINT   UNSIGNED,		# 1
		      tcp_ack     TINYINT   UNSIGNED,		# 1
		      tcp_psh     TINYINT   UNSIGNED,		# 1
		      tcp_syn	  TINYINT   UNSIGNED,		# 1
		      tcp_fin	  TINYINT   UNSIGNED,		# 1
		      tcp_win  	  SMALLINT  UNSIGNED,		# 16
		      tcp_chks	  SMALLINT  UNSIGNED, 		# 16
                      tcp_urp     SMALLINT  UNSIGNED,		# 16
		      tcp_opt	  MEDIUMINT UNSIGNED,		# 24
                      PRIMARY KEY (sid,cid),			
                      INDEX       sport (th_sport),
                      INDEX       dport (th_dport),
                      INDEX       flags (th_flags));


##
## Changes from the original:
##	The names start with udp instead of uh
##	
##	Added:
##		- udp_chks 	Checksum
##
CREATE TABLE udphdr(  sid 	  INT 	   UNSIGNED NOT NULL,
                      cid 	  INT 	   UNSIGNED NOT NULL,
                      udp_sport   SMALLINT UNSIGNED NOT NULL,	# 16
                      udp_dport   SMALLINT UNSIGNED NOT NULL,	# 16
                      udp_len     SMALLINT UNSIGNED,		# 16
		      udp_chks	  SMALLINT UNSIGNED,   		# 16
                      PRIMARY KEY (sid,cid),			
                      INDEX       sport (udp_sport),
                      INDEX       dport (udp_dport));



##
## Changes from the original:
##	The names starts with icmp
##
##	Added:
##		- icmp_chks	Checksum
##		- icmp_div	Used depending on icmp_type
##		- icmp_data	IP header + 64 bits of data	
##
CREATE TABLE icmphdr( sid 	  INT 	    UNSIGNED NOT NULL,
                      cid 	  INT  	    UNSIGNED NOT NULL,
                      icmp_type   TINYINT   UNSIGNED NOT NULL,	# 8
                      icmp_code	  TINYINT   UNSIGNED NOT NULL,	# 8
		      icmp_chks   SMALLINT  UNSIGNED,		# 16
		      icmp_div	  INT       UNSIGNED,		# 32
		      icmp_data   ?         UNSIGNED,		# 256
                      PRIMARY KEY (sid,cid),
                      INDEX       type (type));

##----------------------------------------------------------------------
##
## Unchanged
##
CREATE TABLE event  ( sid 	  INT 	   UNSIGNED NOT NULL,
                      cid 	  INT 	   UNSIGNED NOT NULL,
                      signature   VARCHAR  (255)    NOT NULL,
                      timestamp 	   DATETIME NOT NULL,
                      PRIMARY KEY (sid,cid),
                      INDEX       sig (signature(10)),
                      INDEX       time (timestamp));


CREATE TABLE sensor ( sid	  INT 	   UNSIGNED NOT NULL AUTO_INCREMENT,
                      hostname    TEXT,
                      interface   TEXT,
                      filter	  TEXT,
                      PRIMARY KEY (sid));




mike
-- 
Science is to computer science as hydrodynamics is to plumbing.





More information about the Snort-users mailing list