Re: Proposed schema for the backend database for Nessus

I've been working on some modifications to Javier's proposal for a database 
schema, and I thought I'd share with the group what I've done so far.  

I've posted to a tarball  
(http://mywebpages.comcast.net/earnoth/proposed_nessus_db_schema.tgz)
with a bunch of stuff:
proposed_nessus_db_schema.v1.dia - Javier's original diagram drawn with Dia
proposed_nessus_db_schema.v1.mysql - Javier's original design for MySQL
proposed_nessus_db_schema.v1.pgsql - Javier's original design for PostgreSQL
proposed_nessus_db_schema.v1.png - Image of Javier's original diagram in Dia 
proposed_nessus_db_schema.v2.dia - Diagram with my changes, drawn with Dia
proposed_nessus_db_schema.v2.mysql -Design with my changes for MySQL
proposed_nessus_db_schema.v2.pgsql - Design with my changes for PostgreSQL
proposed_nessus_db_schema.v2.png - Image diagram with my changes in Dia

In case you've never heard of Dia, it's a Visio-like diagraming program.  You 
can find it here - http://www.lysator.liu.se/~alla/dia/home.html.    The 
drawings I made were with the UML drawing tools (Class & Constraint, 
specifically).

The sql statements (*pgsql, *mysql) were created with a script I wrote called 
Dia2SQLpy.  The script takes the Dia UML diagrams and produces either a mysql 
or postgresql create statement.  
You can find it's homepage here - 
http://honeypotdiary.org/earnoth/html/modules.php?name=Content&pa=showpage&pid=16
If you visit, please be patient, it will take a while for the page to load.

Here's the list of changes I made, and my reasoning:
================================================================================
Deleted ExecutionID column from Vulnerability table.
Deleted fk relationship Vulnerability_ExecutedPlugins_ExecutionID.
Created PluginID column in Vulnerability table.
Created fk relationship Vulnerability_NessusPlugins_PluginID.
--------------------------------------------------------------------------------
A plugin will address a given vulnerability.  ExecutedPlugins relates plugins 
to sessions.  Vulnerabilities are related to plugins regardless of scans 
performed.

================================================================================
Deleted PluginCVE table, added CVE column to Vulnerability table.
--------------------------------------------------------------------------------
A CVE names a vulnerability, not a Plugin.  Plugins may reference 
vulnerabilities through the CVE number, but CVE's are not published against 
Nessus plugins.
A CVE entry only accounts for a single vulnerability.  As such, each 
vulnerability listed in the Vulnerability table should only need one entry.

================================================================================
Deleted BID, added BugtraqID column to Vulnerability table.
--------------------------------------------------------------------------------
Same reasoning as CVE

================================================================================
Deleted IsFalsePositive column from Vulnerability table.
Added IsFalsePositive column to ExecutedPlugins table.
--------------------------------------------------------------------------------
In keeping with the model that differentiates a vulnerability from the 
execution iof a plugin n a session, a false positive is an aspect of a scan, 
not the vulnerability.

================================================================================
Deleted DetectedService_NessusPlugin_PluginID fk relationship.
Deleted Services_ExecutedPlugins_ServiceID fk relationship.
Reversed Services_DetectedService_ServiceID fk relationship.
Deleted ServiceID column from ExecutedPlugins table.
Deleted PluginID column from DetecteService table.
Created ServiceID column to NessusPlugin table.
Created NessusPlugin_Services_ServiceID fk relationship.
--------------------------------------------------------------------------------
A plugin will only probe a vulnerability in a single service.  As such, 
linking each NessusPlugin row will have an associated row in the Services 
table, regardless of any scan run with the plugin (ExecutedPlugins).

================================================================================
Created NessusVersion column in Session table.
--------------------------------------------------------------------------------
It might be useful to know what version of Nessus was used in a given scan

================================================================================
Added FoundVulnerable column to ExecutedPlugins table.
Deleted PluginID column from Vulnerability table.
Added VulnerabilityID column to NessusPlugin table.
Added Description column to Vulnerability table.
Deleted Vulnerability_NessusPlugin_PluginID fk reference.
Created NessusPlugin_Vulnerability_VulnerabilityID fk reference.
--------------------------------------------------------------------------------
Furthering the seperation of the Vulnerability, Plugin, and scan sessions 
concepts.

--------------------------------------------------------------------------------
Other thoughts:

There may be an issue with the NessusPlugin table.  Due to the nature of all 
the columns specified there, each one demands a large varchar() datatype.  
However, having too large of a max row size can be problematic.  However, the 
plugin is a discrete conceptual unit, so the columns all make sense to be in 
that table.  

Should ExecutedPlugins have timestamps as it does now?  That is, does nessus 
record when each plugin is stopped & started?  If not, then the StartTime & 
EndTime should probably be removed from ExecutedPlugins.
--------------------------------------------------------------------------------

Any comments on these thoughts would be very welcome.  

-- 
Eric I. Arnoth    CISSP (http://www.isc2.org)        
[email protected]                
http://mywebpages.comcast.net/earnoth
¤ø,¸¸,ø¤º°*°º¤ø,¸¸,ø¤ø,¸¸,ø¤º°*°º¤ø,¸¸,ø¤ø,¸¸,ø¤º°*°º¤ø,¸¸,ø¤ø,¸¸,ø¤º°*°º¤ø,¸¸,ø


On Thursday 16 January 2003 03:59, Javier Fernandez-Sanguino wrote:
> Alex Zimin wrote:
> >>You will see there is no such thing as a "report" in this schema. Other
> >>proposals (from front-ends) include directly the report information into
> >> the database. I'm not doing this since it is already redundant. If you
> >>check carefully, a report on a given session is just all the
> >>vulnerabilities that have been detected in a given session. I.e.
> >>
> >>SELECT Nessus-plugin.Description, Vulnerability.Type,
> >>Vulnerability.AdditionalInformation from Vulnerability, ExecutedPlugins,
> >> NessusPlugin where Vulnerability.ExecutionID =
> >>ExecutedPlugins.ExecutionID AND
> >>ExecutedPlugins.PluginID=NessusPlugin.PluginID AND
> >>ExecutedPlugins.SessionID="A GIVEN SESSION"
> >>
> >>Does this make sense at all?  :-)
> >
> >It will not be too difficult to generate a report based on provided
> >tables, however I want to notice one thing.
> >About month ago Renaud did a clean up of the plugins and several plugins
> >were removed, due to the same functionality included in different plugins
> >
> >If plugin table would not contain outdated plugin data, it will not be
> >possible to generate proper report.
>
> There's no reason why the Nessus-plugin table would not be able to
> contain outdated data. As long as pluginIDs are not reused whenever they
> are removed (are they?) you can have all the information there. Since
> you create this information with your first Nessus installation and
> update it regularly you are going to have always both the old plugins
> and the (updated) new ones...
>
> >Should we add a table which would list substituded plugins in case old
> >plugins would be replaced with a new plugins, with better functionality?
> >
> >Table structure will be very simple
> >
> >PluginID (original pluginID)
> >SubstPluginID (new PluginID)
> >SubstitutionDate (optional)
>
> That's not a bad idea, it would make report generation more complex,
> though.
>
> >In this case report will be generated and will display the description of
> >the substituted plugin.
>
> But that description might not be proper or valid. Take for example that
> the 'default_accounts' plugin (10328) is substituted by the hydra plugin
> (10909) which checks, not only telnet but many other services (not that
> it's going to happen). Even if the end functionality is the same, if you
> are not providing a proper username/password file for hydra then you
> might not detect (i.e. bruteforce) and account that the previous plugin
> did detect (it provides an 'accounts.txt' file which includes some
> common user/password combinations).
>
> So, not only would the output information differ the results as well as
> the references (CAN-1999-0502 for 10328) might even do too..
>
> >Or maybe even just add SubsPluginID and Date to the Nessus-Plugins table,
> >but in this case we may have too much garbage in that table.
>
> Yes. I guess this "feature" is not going to be used all the time, just
> in some specific locations.
>
> >Does it make sense?
>
> Yes it does and you've brought up a valid issue. I'm not sure if Renaud
> has reused plugin-ids after removing them for whatever reason (Renaud?).
>
> Regards
>
> Javi
>
> PS: If someone can provide comparison of plugins from the different
> Nessus releases I would be more than grateful (and the 'nessus-extract'
> tool would be useful here, hint, hint...)