[Snort-devel] Ideas about DB logging ; Oracle 8 outer join li mitations

Kreimendahl, Chad J Chad.Kreimendahl at ...1167...
Wed Apr 17 09:24:02 EDT 2002

(Since it's along my old lines of db thoughts, I thought I'd include the
devel list)

Actually, it wouldn't be that dificult to merge from another database.   If
you knew what the current sequence value was, you could add an int to the
cid value and make it one more than that... And then increment your sequence
to the last value you merged.   This would greatly increase performance for
constraint checking (primary key (cid) instead of (sid,cid))  and would make
joins slightly faster (pk -> pk joins are fast enough as it is, but one less
column would help).  We actually copy our sensor table to all our
databases... Not efficient, but it's a good workaround to the sid probo.

So you're saying, you want to see the entire rule somewhere in the schema?
I built a fairly massive configuration tool for snort that I integrated with
the monitoring part, so that anyone could see the rule (as it is configed
for the sid to which it's assigned).  Another option would be to link to
snort.org's sig-id database (we do that as well).

Most of the changes I'd like to make to spo_database  revolve around
rewriting to vastly improve performance of logging to Oracle (and possibly
several others).  I think it would be well advised to separate the db
specific stuff into separate files, and use only the common stuff within
spo_database.... Possibly spo_database_<dbtype>.  With a standard structure
for passing info from db into db_<dbtype>, you could easily support several
different sets of functionality within each of the databases, and thus
optimize the output greatly based on those.


-----Original Message-----
From: Imran William Smith [mailto:iwsmith at ...1111...] 
Sent: Tuesday, April 16, 2002 8:32 PM
To: Kreimendahl, Chad J; roman at ...49...
Subject: Re: [Snort-devel] Ideas about DB logging ; Oracle 8 outer join

Good news about 9i and outer joins, and glad the strange
8i and before (+)= syntax has gone away.  Sybase also had similar syntax, *=
, but the * went on the opposite side as the (+), for added confusion!

About the sequences and the select(max), I can see no good reason why the
cid has to be unique but only per sensor. I.e. current schema will have
sid=1,cid=1, but could also have sid=2, cid=1.  Why not just have cid to be
totally unique? It would make merging of data from 2 existing sensor
databases even harder, but the way the signatures work (sig_id grows 
from 1, for the first signature detected), this is pretty impossible anyway,
because two sensor databases would both use differing sig_id's for the same
signature (but the same sig_sid, which seems to be put on the schema only
for informational purposes, rather than any relational use).

On a further note about the schema, there's nowhere in signature that the
original text of the signature (from *.rules) is inserted. So although a
signature has been triggered, without manually going to snort.rules file in
a text editor, it's not always obvious WHY that rule has been triggered.  If
the original text was held by snort, and inserted when a new signature
record was inserted, a frontend could pull this and view the signature


----- Original Message ----- 
From: "Kreimendahl, Chad J" <Chad.Kreimendahl at ...1167...>
To: "'Imran William Smith'" <iwsmith at ...1111...>; <roman at ...49...>
Sent: Tuesday, April 16, 2002 10:36 PM
Subject: RE: [Snort-devel] Ideas about DB logging ; Oracle 8 outer join

I think most of what we're saying leads to possibly building database
centric output plugins (possibly?)... To most effeciently maximize
performance.... Anyway:

Even though that select max is terrible from a performance perspective, it
could also benefit (in the short term) from placeholders and binding.... I'm
a big fan of using sequences wherever possible, but does this mean we'd have
to create a unique sequence for each sensor?  Not sure I mind a CREATE
SEQUENCE command in snort, but I worry about most DBAs at companies wanting
to allow the interface user to have CREATE SEQ access... 

I thought Oracle 8 supported the standard for outer joins...  I know for
certain that Oracle 9i does.   I'm using them extensively in our web IDS
tool.  Though, that may explain the problems my ex-group across the hall was
having with INNER JOINS.... I had sent them a query that worked on a
duplicate of their database I had... (I was on 9i, they were on 8), and it
wasn't returning the data in the same way...

The max outer joins may also be fixed in 9i... (I've attached a query at
bottom, and it works perfectamundo on 9i)

As a little side note, if there's any hardware/software sort of combo you
want to test on, we have it all, or can get it all... 

                        TO_CHAR(event.timestamp, '<date format from prefs>')
                FROM event
                        LEFT JOIN signature ON (signature.sig_id =
                        LEFT JOIN iphdr ON ((iphdr.sid = event.sid) AND
(iphdr.cid = event.cid))
                        event.timestamp >= (SYSDATE-(<number of hours>/24))
                ORDER BY event.timestamp desc

-----Original Message-----
From: Imran William Smith [mailto:iwsmith at ...1111...] 
Sent: Monday, April 15, 2002 8:01 PM
To: Kreimendahl, Chad J; roman at ...49...
Subject: Re: [Snort-devel] Ideas about DB logging ; Oracle 8 outer join

I noticed the snort does a 'select max cid where sid=x'
every time it does an insert, to get the next cid.  That could also be done
much more efficiently with a sequence (but RDBMS specific), which would
presumably give good speedup, especially over a network connected database
(currently, a read before every write - ugh!).

I am having problems adding Oracle 8 support for Acid.
Most of the porting is fine, but the snort table design implies extensive
use of outer joins:

The main table is event.  but there may be additional information rows for
signature, iphdr, tcphdr, udphdr, icmphdr, data.  This is classic case where
an outer join is useful to pick up the 'might have' data.

Unfortunately Oracle 8 does not support ANSI92 standard
for outer joins - it uses

where inner.column (+)= outer.column

instead of the 

from inner
left join outer on (inner.column = outer.column)

OK, that can be fixed.  But worse yet, Oracle 8 supports 
an outer join to a MAXIMUM of 1 outer table in a single
query.  Extensive rewrites called for.  Oracle 9 claims
full ANSI99 outer join compliance, so this problem should
go away.  But I am searching for an Oracle 9 machine
in the organisation to test this.  Maybe one of you guys has Oracle 9?
Sample query (generated by Acid):

SELECT event.sid as sid, event.cid as cid, signature, timestamp, ip_src, 
ip_dst, ip_proto, tcp_sport as layer4_sport, tcp_dport
as layer4_dport, sig_name, sig_priority, sig_class_id
FROM event , signature, tcphdr, iphdr
WHERE event.signature = signature.sig_id
AND event.sid (+)= iphdr.sid
AND event.cid (+)= iphdr.cid
AND event.sid (+)= tcphdr.sid
AND event.cid (+)= tcphdr.cid
AND event.sid = 1
AND ip_proto = 6

To which Oracle 8 replies:

ORA-01417: a table may be outer joined to at most one other table

Any ideas guys?  


Imran William Smith

More information about the Snort-devel mailing list