[Secureideas-base-devel] DB Schema

[Jeff D. <jd...@ac...>]

I thought I would get involved with the schema changes. I took a look at the
schema Axton created and I think it is a good start, but I have some
comments...

1. Why force the use of InnoDB? This is slower than MyISAM. Also why use
foreign keys? We really don't care much about referential integrity because
we never modify the data. We only insert, select and delete it. Also MyISAM
doesn't support foreign keys. :)

2. Why have snort_option_ipv4 table? Why not just put the value of the ip
option? i.e If you have a loose source route, just put 133. You don't need
to put Copy/Class/Number. If you knew the Value was 123, you know that the
Copy is 1, the Class is 0 and the Number is 3.

To me you have this expanded table that really doesn't service much purpose
except additional details, but to insert an event, you have to know which
s_option_ipv4_code.snort_option_ipv4 to insert into event_option_ipv4. If
you want to keep this data fine, but make it an extra table to look up the
value for the IP option with the details and not required to insert an
event. 

If you would like to keep it there, I would move the s_option_ipv4_value to
s_option_ipv4_code and use that to insert into your event_option_ipv4 table.
Then again, this table is only used for querying and could be considered
extra from the core Snort schema.

3. snort_option_tcp: it looks like s_option_tcp_code is really the Kind tcp
option per this page: http://www.iana.org/assignments/tcp-parameters if that
is the case, this table is really extra also. 

4. These extra tables are good for querying, but do they need to be a part
of the core Snort DB schema. And do you really need to know these details
when inserting an event? These types of tables were always part of the
extension Snort schema that had things like protocols, services and flags...
maybe these should be part of that and include it with snort (it once was
some time ago). If people want just the core schema, they have it, otherwise
they can add all the extra fluff.

5. What is a sub Generator in the snort_generator table? I am looking at the
Generators file for snort 2.7 and don't see a sub generator nor have ever
heard of one before. 

5. The priority was removed from the signature. You can set the priority per
signature as well as per classification. This should really be set only on
the signature and not in the class. If you make this a TINYINT, it will only
take 1 byte per signature. This is the way it is currently.. except it is an
INT (4 bytes) which is a waste of 3 bytes per signature IMHO.

6. event_alert table: still unsure what s_generator_alertid is and why it is
here. You have a generator which should be enough.

I hope these comments continue with the dialog that was started several
months ago. I would be happy to help with cleaning this up, optimizing it
and working on a snort database plug-in.

Cheers,
Jeff