Thread: [Secureideas-base-devel] DB Schema
Brought to you by:
secureideas,
sinukas
From: Jeff D. <jd...@ac...> - 2007-08-31 21:37:09
|
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 |
From: Sean M. <sam...@us...> - 2007-08-31 22:13:18
|
Jeff this looks great I am pouring over the DB schema that Axton sent also, Im not really a DB guy so I am really slow reading this, your comments are helping me see things. I am also trying to layout some visio drawings showing the table interconnect in the schema and the required and extra function tables. Sean On Aug 31, 2007, at 5:37 PM, Jeff Dell wrote: > 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 > > > > > > ---------------------------------------------------------------------- > --- > This SF.net email is sponsored by: Splunk Inc. > Still grepping through log files to find problems? Stop. > Now Search log events and configuration files using AJAX and a > browser. > Download your FREE copy of Splunk now >> http://get.splunk.com/ > _______________________________________________ > Secureideas-base-devel mailing list > Sec...@li... > https://lists.sourceforge.net/lists/listinfo/secureideas-base-devel > |
From: Michael S. <ms...@ma...> - 2007-08-31 22:15:05
|
On Fri, Aug 31, 2007 at 05:37:10PM -0400, you wrote: >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. :) I'd rather not cripple the schema to support one particular database. The current schema is heavily biased toward historic mysql limitations, which make it hard to get good performance on other db's. >4. These extra tables are good for querying, but do they need to be a part >of the core Snort DB schema. Yes. It would be nice if the core db schema supported enough that there could be more commonality (fewer extensions) for various products that build on that schema--which would allow more interoperability between those projects. |
From: Jeff D. <jd...@ac...> - 2007-09-01 00:05:36
|
>>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. :) >I'd rather not cripple the schema to support one particular database. >The current schema is heavily biased toward historic mysql limitations, >which make it hard to get good performance on other db's. Are you saying that removing support for foreign keys is crippling the schema? Not sure how that would cripple anything because integrity checking is really not an issue with Snort... we don't modify any tables. But this is just for MySQL. Other db's can have foreign keys and it wouldn't really affect how it inserts or selects. We could even leave the foreign keys in the schema.. just don't force InnoDB and let the user choose. If they would like to use a fast non-transactional database allow them. What suggestions would you have to get better performance with other db's? a lot has changed in MySQL since this the schema was originally written 5 years ago. So maybe those also exist in MySQL. I can think of one feature, partitioning, that would help performance with a lot of events. But that can be done at the application and not necessarily should be in the schema. Cheers, Jeff |
From: Michael S. <ms...@ma...> - 2007-09-01 00:36:20
|
On Fri, Aug 31, 2007 at 08:05:37PM -0400, Jeff Dell wrote: >Are you saying that removing support for foreign keys is crippling the >schema? Yes. If the schema is created with explicit foreign keys, your DB can optimize certain types of queries better. (A good query planner can make assumptions about joins if it knows about the foreign key relationships.) >Not sure how that would cripple anything because integrity checking >is really not an issue with Snort.. we don't modify any tables. You're inserting into multiple tables. You might have an inserter crash. With an ACID compliant database your tables are still consistent in that case. With a non-ACID compliant database, you have an inconsistent database. On a non-trivial database it's more straightforward to keep the database consistent than to write routines to clean it up once it's corrupted. An example of where this is a factor is deletes--the current implementation has a horrible method of deleting in which an event is deleted from any table where it might appear. With a schema in which the relationships between the tables are explicit, you can just delete from one table with a single query expression and let the delete cascade to the related tables. This is *dramatically* faster than the current approach. I really don't want to start a database advocacy fight on this list, so I'll avoid saying any more on the topic other than reiterating that performance is not a reason for stripping functionality out of the database, *especially* without some benchmarks and use cases to support that suggestion. >What suggestions would you have to get better performance with other db's? I made a few a while back. Checking out the current schema proposal has been on my todo list for a while, it just hasn't happened lately. The diagrams or overview someone mentioned earlier would be helpful. Specifically, that means that I'm not speaking at all about your comments regarding option tables, because I don't recall them being in the schema and I'll have to look at them. Mike Stone |
From: Jeff D. <jd...@ac...> - 2007-09-01 00:49:48
|
I would agree with your comments... I wasn't thinking of an INSERT crashing.. I mainly deal with the client side. I would also agree that it is faster to delete events when using foreign keys... Personally I never had a problem with deleting from multiple tables.. but I could see how it is easier and would get better performance for a delete. When we nail down the basic schema... I will do some performance tests to see which one gets better performance for inserts and deletes. Cheers, Jeff -----Original Message----- From: Michael Stone [mailto:ms...@ma...] Sent: Friday, August 31, 2007 8:36 PM To: Jeff Dell Cc: sec...@li... Subject: Re: [Secureideas-base-devel] DB Schema On Fri, Aug 31, 2007 at 08:05:37PM -0400, Jeff Dell wrote: >Are you saying that removing support for foreign keys is crippling the >schema? Yes. If the schema is created with explicit foreign keys, your DB can optimize certain types of queries better. (A good query planner can make assumptions about joins if it knows about the foreign key relationships.) >Not sure how that would cripple anything because integrity checking >is really not an issue with Snort.. we don't modify any tables. You're inserting into multiple tables. You might have an inserter crash. With an ACID compliant database your tables are still consistent in that case. With a non-ACID compliant database, you have an inconsistent database. On a non-trivial database it's more straightforward to keep the database consistent than to write routines to clean it up once it's corrupted. An example of where this is a factor is deletes--the current implementation has a horrible method of deleting in which an event is deleted from any table where it might appear. With a schema in which the relationships between the tables are explicit, you can just delete from one table with a single query expression and let the delete cascade to the related tables. This is *dramatically* faster than the current approach. I really don't want to start a database advocacy fight on this list, so I'll avoid saying any more on the topic other than reiterating that performance is not a reason for stripping functionality out of the database, *especially* without some benchmarks and use cases to support that suggestion. >What suggestions would you have to get better performance with other db's? I made a few a while back. Checking out the current schema proposal has been on my todo list for a while, it just hasn't happened lately. The diagrams or overview someone mentioned earlier would be helpful. Specifically, that means that I'm not speaking at all about your comments regarding option tables, because I don't recall them being in the schema and I'll have to look at them. Mike Stone |
From: Jeff D. <jd...@ac...> - 2007-09-01 02:31:37
|
I was just looking into the database output plug-in and it currently supports transactional inserts so if it does crash during an insert, it will roll back regardless of foreign keys. Cheers, Jeff -----Original Message----- From: Michael Stone [mailto:ms...@ma...] Sent: Friday, August 31, 2007 8:36 PM To: Jeff Dell Cc: sec...@li... Subject: Re: [Secureideas-base-devel] DB Schema On Fri, Aug 31, 2007 at 08:05:37PM -0400, Jeff Dell wrote: >Are you saying that removing support for foreign keys is crippling the >schema? Yes. If the schema is created with explicit foreign keys, your DB can optimize certain types of queries better. (A good query planner can make assumptions about joins if it knows about the foreign key relationships.) >Not sure how that would cripple anything because integrity checking >is really not an issue with Snort.. we don't modify any tables. You're inserting into multiple tables. You might have an inserter crash. With an ACID compliant database your tables are still consistent in that case. With a non-ACID compliant database, you have an inconsistent database. On a non-trivial database it's more straightforward to keep the database consistent than to write routines to clean it up once it's corrupted. An example of where this is a factor is deletes--the current implementation has a horrible method of deleting in which an event is deleted from any table where it might appear. With a schema in which the relationships between the tables are explicit, you can just delete from one table with a single query expression and let the delete cascade to the related tables. This is *dramatically* faster than the current approach. I really don't want to start a database advocacy fight on this list, so I'll avoid saying any more on the topic other than reiterating that performance is not a reason for stripping functionality out of the database, *especially* without some benchmarks and use cases to support that suggestion. >What suggestions would you have to get better performance with other db's? I made a few a while back. Checking out the current schema proposal has been on my todo list for a while, it just hasn't happened lately. The diagrams or overview someone mentioned earlier would be helpful. Specifically, that means that I'm not speaking at all about your comments regarding option tables, because I don't recall them being in the schema and I'll have to look at them. Mike Stone |
From: Michael S. <ms...@ma...> - 2007-09-01 11:12:34
|
On Fri, Aug 31, 2007 at 10:31:42PM -0400, Jeff Dell wrote: >I was just looking into the database output plug-in and it currently >supports transactional inserts so if it does crash during an insert, it will >roll back regardless of foreign keys. Those are two seperate things. You said "integrity checking is not really an issue for snort", to which I replied with a use case for transaction support. (This is related to your suggestion to drop innodb in favor of myisam--which effectively kills transaction support--and has nothing to do with the foreign keys.) Mike Stone |
From: Axton <axt...@gm...> - 2007-12-20 18:58:17
|
The added benefits of referential integrity: - the ability to perform cascading operations (updates and deletes). This can greatly simplify the code base within Base for these types of operations. - the knowledge that the data is normalized; think chemical spill - normalization keeps the db as small as it can be; data is not redundantly stored in multiple tables - it provides the basis for knowledge about the db schema; think reverse engineering - locking comes into play with normalization, to different degrees depending on the db type InnoDB vs. MyISAM: - InnoDB supports PK/FK constraints (see above) - InnoDB supports row level locking Since the fast logging output methods do not hold up Snort from doing its thing, I do not see that the performance gains of using MyISAM vs. InnoDB provide much benefit. The extra tables that provide the english translations for various options are provided as a benefit for the presentation of data. They can be easily used by things like web applications that want to present both the raw and friendly data. I will have to do some more digging to answer the other questions. Axton Grams On Aug 31, 2007 4:37 PM, Jeff Dell <jd...@ac...> wrote: > 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 > > > > > > ------------------------------------------------------------------------- > This SF.net email is sponsored by: Splunk Inc. > Still grepping through log files to find problems? Stop. > Now Search log events and configuration files using AJAX and a browser. > Download your FREE copy of Splunk now >> http://get.splunk.com/ > _______________________________________________ > Secureideas-base-devel mailing list > Sec...@li... > https://lists.sourceforge.net/lists/listinfo/secureideas-base-devel > |
From: Jeff D. <jd...@ac...> - 2007-12-20 19:05:31
|
I understand the referential integrity and I would agree with it. Please disregard comment #1. The other comments I feel still hold true and would enjoy a conversation about them. Cheers, Jeff -----Original Message----- From: sec...@li... [mailto:sec...@li...] On Behalf Of Axton Sent: Thursday, December 20, 2007 1:58 PM To: sec...@li... Subject: Re: [Secureideas-base-devel] DB Schema The added benefits of referential integrity: - the ability to perform cascading operations (updates and deletes). This can greatly simplify the code base within Base for these types of operations. - the knowledge that the data is normalized; think chemical spill - normalization keeps the db as small as it can be; data is not redundantly stored in multiple tables - it provides the basis for knowledge about the db schema; think reverse engineering - locking comes into play with normalization, to different degrees depending on the db type InnoDB vs. MyISAM: - InnoDB supports PK/FK constraints (see above) - InnoDB supports row level locking Since the fast logging output methods do not hold up Snort from doing its thing, I do not see that the performance gains of using MyISAM vs. InnoDB provide much benefit. The extra tables that provide the english translations for various options are provided as a benefit for the presentation of data. They can be easily used by things like web applications that want to present both the raw and friendly data. I will have to do some more digging to answer the other questions. Axton Grams On Aug 31, 2007 4:37 PM, Jeff Dell <jd...@ac...> wrote: > 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 > > > > > > ------------------------------------------------------------------------- > This SF.net email is sponsored by: Splunk Inc. > Still grepping through log files to find problems? Stop. > Now Search log events and configuration files using AJAX and a browser. > Download your FREE copy of Splunk now >> http://get.splunk.com/ > _______________________________________________ > Secureideas-base-devel mailing list > Sec...@li... > https://lists.sourceforge.net/lists/listinfo/secureideas-base-devel > ------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ Secureideas-base-devel mailing list Sec...@li... https://lists.sourceforge.net/lists/listinfo/secureideas-base-devel |
From: Axton <axt...@gm...> - 2007-12-21 03:06:14
|
On Dec 20, 2007 2:07 PM, Jeff Dell <jd...@ac...> wrote: > I understand the referential integrity and I would agree with it. Please > disregard comment #1. The other comments I feel still hold true and would > enjoy a conversation about them. > > Cheers, > > Jeff > > -----Original Message----- > From: sec...@li... > [mailto:sec...@li...] On Behalf Of > Axton > Sent: Thursday, December 20, 2007 1:58 PM > To: sec...@li... > Subject: Re: [Secureideas-base-devel] DB Schema > > > The added benefits of referential integrity: > - the ability to perform cascading operations (updates and deletes). > This can greatly simplify the code base within Base for these types of > operations. > - the knowledge that the data is normalized; think chemical spill > - normalization keeps the db as small as it can be; data is not > redundantly stored in multiple tables > - it provides the basis for knowledge about the db schema; think > reverse engineering > - locking comes into play with normalization, to different degrees > depending on the db type > > InnoDB vs. MyISAM: > - InnoDB supports PK/FK constraints (see above) > - InnoDB supports row level locking > > Since the fast logging output methods do not hold up Snort from doing > its thing, I do not see that the performance gains of using MyISAM vs. > InnoDB provide much benefit. > > The extra tables that provide the english translations for various > options are provided as a benefit for the presentation of data. They > can be easily used by things like web applications that want to > present both the raw and friendly data. > > I will have to do some more digging to answer the other questions. > > Axton Grams > > On Aug 31, 2007 4:37 PM, Jeff Dell <jd...@ac...> wrote: > > 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 I have an old ERD here: http://arswiki.org/base/erd/ To answer you specific questions: 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. :) Axton: Skipped. 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. Axton: This is really only provided as reference information for applications that use the schema. The idea here is that decoded data is stored in the database so that applications can simply reference the data to get friendly names instead of having to reproduce this decoding independantly. It basically prevents each app that uses the data from having to reinvent the wheel in the event such data is needed. I a bit of a db purist, so I tend to throw everything, including the kitchen sink, into the 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. Axton: Yep, all the snort_option_% tables are there for the same purpose. 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. Axton: This probably does not need to be part of the core snort schema. In my eyes, the core snort schema is available simply for logging purposes. Anything above and beyond that is an extension. The question now becomes, is there any harm in including it, and are any challenges presented by not including it. I do not see any harm in including the snort_option_% tables, but they are not necessary for logging. As for adding the tables snort_option_% tables at a later time, it may or may not be difficult to define the referential integrity to these additional tables depending on how much data is in the database and what database you are using. Pgsql, Mysql, oracle, sql server all look to be capable of handling 'alter table' statements that allow the definition of foreign key constraints at a later time. 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. Axton: The best I can guess is that I pulled it from snort's generators.h. As an example: #define GENERATOR_SPP_HTTP_DECODE 102 #define HTTP_DECODE_UNICODE_ATTACK 1 #define HTTP_DECODE_CGINULL_ATTACK 2 #define HTTP_DECODE_LARGE_METHOD 3 #define HTTP_DECODE_MISSING_URI 4 #define HTTP_DECODE_DOUBLE_ENC 5 #define HTTP_DECODE_ILLEGAL_HEX 6 #define HTTP_DECODE_OVERLONG_CHAR 7 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. Axton: I probably matched the size of the priority to that of snort. While it may be a waste of 3 bytes at this time, it may save some real headaches later. From snort's signature.h: /* struct for rule classification */ typedef struct _ClassType { char *type; /* classification type */ int id; /* classification id */ char *name; /* "pretty" classification name */ int priority; /* priority */ struct _ClassType *next; } ClassType; 6. event_alert table: still unsure what s_generator_alertid is and why it is here. You have a generator which should be enough. Axton: see number 5 above. Axton Grams |
From: Jason <sec...@br...> - 2007-12-21 03:14:26
|
> 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. > > Axton: The best I can guess is that I pulled it from snort's > generators.h. As an example: > > #define GENERATOR_SPP_HTTP_DECODE 102 > #define HTTP_DECODE_UNICODE_ATTACK 1 > #define HTTP_DECODE_CGINULL_ATTACK 2 > #define HTTP_DECODE_LARGE_METHOD 3 > #define HTTP_DECODE_MISSING_URI 4 > #define HTTP_DECODE_DOUBLE_ENC 5 > #define HTTP_DECODE_ILLEGAL_HEX 6 > #define HTTP_DECODE_OVERLONG_CHAR 7 Those are SIDS You would have an event 102:1 for HTTP_DECODE_UNICODE_ATTACK |
From: Jeff D. <jd...@ac...> - 2007-12-21 13:03:09
|
2. I understand the details being in the database and I will talk about that in a bit... but I am talking about the requirement to do a select to find the option_ipv4_code.snort_option_ipv4 to use in the insert of an event. If you are going to use this as a reference table.. use it for that and not require a select to insert an event. 3,4. I hear the argument and I am open either way with storing reference tables in the database. I think it is a great idea to have reference tables, but historically it was included as a different schema file. This way it gives the end user the option to use them or not.. Personally I don't care one way or another, but other people might, so I mentioned it. 5a,6. Hmm.. I have never seen this before. But I am all for giving the end user as much information as possible. If this can be pushed down into the database that would be cool. 5b. I am really not talking about the size as much as the location of the priority. The Priority should be stored per signature and not as you moved it to the classification. This is because you can set the priority per signature also. Cheers, Jeff -----Original Message----- From: Axton [mailto:axt...@gm...] Sent: Thursday, December 20, 2007 10:06 PM To: Jeff Dell; sec...@li... Subject: Re: [Secureideas-base-devel] DB Schema On Dec 20, 2007 2:07 PM, Jeff Dell <jd...@ac...> wrote: > I understand the referential integrity and I would agree with it. Please > disregard comment #1. The other comments I feel still hold true and would > enjoy a conversation about them. > > Cheers, > > Jeff > > -----Original Message----- > From: sec...@li... > [mailto:sec...@li...] On Behalf Of > Axton > Sent: Thursday, December 20, 2007 1:58 PM > To: sec...@li... > Subject: Re: [Secureideas-base-devel] DB Schema > > > The added benefits of referential integrity: > - the ability to perform cascading operations (updates and deletes). > This can greatly simplify the code base within Base for these types of > operations. > - the knowledge that the data is normalized; think chemical spill > - normalization keeps the db as small as it can be; data is not > redundantly stored in multiple tables > - it provides the basis for knowledge about the db schema; think > reverse engineering > - locking comes into play with normalization, to different degrees > depending on the db type > > InnoDB vs. MyISAM: > - InnoDB supports PK/FK constraints (see above) > - InnoDB supports row level locking > > Since the fast logging output methods do not hold up Snort from doing > its thing, I do not see that the performance gains of using MyISAM vs. > InnoDB provide much benefit. > > The extra tables that provide the english translations for various > options are provided as a benefit for the presentation of data. They > can be easily used by things like web applications that want to > present both the raw and friendly data. > > I will have to do some more digging to answer the other questions. > > Axton Grams > > On Aug 31, 2007 4:37 PM, Jeff Dell <jd...@ac...> wrote: > > 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 I have an old ERD here: http://arswiki.org/base/erd/ To answer you specific questions: 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. :) Axton: Skipped. 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. Axton: This is really only provided as reference information for applications that use the schema. The idea here is that decoded data is stored in the database so that applications can simply reference the data to get friendly names instead of having to reproduce this decoding independantly. It basically prevents each app that uses the data from having to reinvent the wheel in the event such data is needed. I a bit of a db purist, so I tend to throw everything, including the kitchen sink, into the 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. Axton: Yep, all the snort_option_% tables are there for the same purpose. 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. Axton: This probably does not need to be part of the core snort schema. In my eyes, the core snort schema is available simply for logging purposes. Anything above and beyond that is an extension. The question now becomes, is there any harm in including it, and are any challenges presented by not including it. I do not see any harm in including the snort_option_% tables, but they are not necessary for logging. As for adding the tables snort_option_% tables at a later time, it may or may not be difficult to define the referential integrity to these additional tables depending on how much data is in the database and what database you are using. Pgsql, Mysql, oracle, sql server all look to be capable of handling 'alter table' statements that allow the definition of foreign key constraints at a later time. 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. Axton: The best I can guess is that I pulled it from snort's generators.h. As an example: #define GENERATOR_SPP_HTTP_DECODE 102 #define HTTP_DECODE_UNICODE_ATTACK 1 #define HTTP_DECODE_CGINULL_ATTACK 2 #define HTTP_DECODE_LARGE_METHOD 3 #define HTTP_DECODE_MISSING_URI 4 #define HTTP_DECODE_DOUBLE_ENC 5 #define HTTP_DECODE_ILLEGAL_HEX 6 #define HTTP_DECODE_OVERLONG_CHAR 7 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. Axton: I probably matched the size of the priority to that of snort. While it may be a waste of 3 bytes at this time, it may save some real headaches later. From snort's signature.h: /* struct for rule classification */ typedef struct _ClassType { char *type; /* classification type */ int id; /* classification id */ char *name; /* "pretty" classification name */ int priority; /* priority */ struct _ClassType *next; } ClassType; 6. event_alert table: still unsure what s_generator_alertid is and why it is here. You have a generator which should be enough. Axton: see number 5 above. Axton Grams |
From: Jason <sec...@br...> - 2007-12-21 13:43:48
|
Jeff Dell wrote: > 2. I understand the details being in the database and I will talk about that > in a bit... but I am talking about the requirement to do a select to find > the option_ipv4_code.snort_option_ipv4 to use in the insert of an event. If > you are going to use this as a reference table.. use it for that and not > require a select to insert an event. I'm good with putting all sorts of metadata in the database where ever possible. This should be static data and as such you can query it once and store it for the run, there should not be a need to run a select for every insert, only on start to load metadata. > > 3,4. I hear the argument and I am open either way with storing reference > tables in the database. I think it is a great idea to have reference tables, > but historically it was included as a different schema file. This way it > gives the end user the option to use them or not.. Personally I don't care > one way or another, but other people might, so I mentioned it. I can see both sides but I think that the benefit outweighs any cost in this case. > > 5a,6. Hmm.. I have never seen this before. But I am all for giving the end > user as much information as possible. If this can be pushed down into the > database that would be cool. > > 5b. I am really not talking about the size as much as the location of the > priority. The Priority should be stored per signature and not as you moved > it to the classification. This is because you can set the priority per > signature also. Agreed. Especially since the priority may be changed outside of a signature. I've introduced the concept of handlers and qualifiers in SnortUnified.pm and this use case is one I specifically had in mind. If an event fires and correlates with some other available data that indicated the priority should be raised, the handlers would do this for you. As an aside, please do not do any work with snort proper writing to a database. I suggest wither using Barnyard or SnortUnified.pm. The future of outputs in snort will be limited to very fast non blocking operations and a database write does not quite qualify. |
From: Jeff D. <jd...@ac...> - 2007-12-21 13:57:24
|
>> 2. I understand the details being in the database and I will talk about that >> in a bit... but I am talking about the requirement to do a select to find >> the option_ipv4_code.snort_option_ipv4 to use in the insert of an event. If >> you are going to use this as a reference table.. use it for that and not >> require a select to insert an event. >I'm good with putting all sorts of metadata in the database where ever >possible. This should be static data and as such you can query it once >and store it for the run, there should not be a need to run a select for >every insert, only on start to load metadata. But wouldn't it be best to not have to load meta data. These values can be inserted into the table without the need to load metadata and there would be no additional overhead. The schema just needs to be changed slightly to store s_option_ipv4_code which is the actual option code instead of s_option_ipv4_value which is the key value in the reference table. Cheers, Jeff |
From: Jason <sec...@br...> - 2007-12-21 17:04:16
|
Jeff Dell wrote: >>> 2. I understand the details being in the database and I will talk about > that >>> in a bit... but I am talking about the requirement to do a select to find >>> the option_ipv4_code.snort_option_ipv4 to use in the insert of an event. > If >>> you are going to use this as a reference table.. use it for that and not >>> require a select to insert an event. > >> I'm good with putting all sorts of metadata in the database where ever >> possible. This should be static data and as such you can query it once >> and store it for the run, there should not be a need to run a select for >> every insert, only on start to load metadata. > > But wouldn't it be best to not have to load meta data. These values can be > inserted into the table without the need to load metadata and there would be > no additional overhead. The schema just needs to be changed slightly to > store s_option_ipv4_code which is the actual option code instead of > s_option_ipv4_value which is the key value in the reference table. > > Cheers, > Jeff > > yes excepting that this places the burden on the applications to do the work. Why make the app have to do it if it can be normalized across all apps simply by using the meta data? IMHO, as much data as is reasonably normalized in the DB is a key desirable element of any new schema. |
From: Jeff D. <jd...@ac...> - 2007-12-21 17:20:55
|
>Jeff Dell wrote: >>>> 2. I understand the details being in the database and I will talk about >> that >>>> in a bit... but I am talking about the requirement to do a select to find >>>> the option_ipv4_code.snort_option_ipv4 to use in the insert of an event. >> If >>>> you are going to use this as a reference table.. use it for that and not >>>> require a select to insert an event. >> >>> I'm good with putting all sorts of metadata in the database where ever >>> possible. This should be static data and as such you can query it once >>> and store it for the run, there should not be a need to run a select for >>> every insert, only on start to load metadata. >> >> But wouldn't it be best to not have to load meta data. These values can be >> inserted into the table without the need to load metadata and there would be >> no additional overhead. The schema just needs to be changed slightly to >> store s_option_ipv4_code which is the actual option code instead of >> s_option_ipv4_value which is the key value in the reference table. >> >> Cheers, >> Jeff >> >> > >yes excepting that this places the burden on the applications to do the >work. Why make the app have to do it if it can be normalized across all >apps simply by using the meta data? >IMHO, as much data as is reasonably normalized in the DB is a key >desirable element of any new schema. I don't think I was very clear.. I don't mean to remove the meta data table. I am saying that currently with the way the schema is designed you have to store the meta data in memory to do an INSERT. But this is unnecessary... If you make a minor change to the schema, you don't have to worry about storing meta data in memory to do inserts and you can still keep the meta data table in the schema so applications can utilize the additional details it can provide when performing a SELECT. Cheers, Jeff |