A SCAP Database Model

1. A SCAP Database Model All of SCAP data in a Relational Database Ken.Lassesen@lumension.com

2. A mini-system to learn SCAP • A SCAP database with all of the SCAP data in it • Examples (with source code) • Automatic updates utility • Automatic import of client data • Detection of equivalent OVAL elements • Schema-change tolerant DB design • SCAP data formats (schema) are constantly change • Authors can lag in updating • Tools and data formats may be out of sync

3. XML and Databases • Xml is excellent for distribution, poor for analysis and reporting (databases are better) • XML is single computer/thread centric (no sharing) • DB is data set centric with concurrent access • Exposes XML data as regular columns / rows • Reporting staff do not need to learn XML

4. A simple logical database model

5. What we will cover • Importing SCAP Xml files • CVE - Vulnerability • CVSS – Scoring • OVAL -- Assessment • CPE – Product • XCCDF – Checklist • CCE – Configuration • Importing OVAL result files from clients • Querying OVAL elements for equivalency • Reduce client test load by 25+%

6. Examples of importing / exporting data • Import of OVAL results into the database • Files are dropped into a folder • Utility will pick up and insert into the database automatically • Export of equivalent OVAL element • Eliminate redundant and duplicate data • OVAL comes from multiple sources • Duplications must be expected and handled

7. SCAP Import Utility • Two mode of operation: • Drop the Xml files in a folder • Edit DataFiles.Xml to point to locations you want checked for changes. • Files are uploaded into DB and processed automatically sqlXml = new SqlXml(new XmlTextReader(fileName)); db = new ImportDataBase(); cmd = db.StoredProcedure("ImportScapXmlFile"); cmd.Parameters.AddWithValue("FileName", fi.Name); cmd.Parameters.AddWithValue("ScapXml", sqlXml); cmd.ExecuteNonQuery();

8. Common Vulnerability Enumeration (CVE) http://nvd.nist.gov/ • The physical data is simple – 2 columns • The logical data can consist of many columns and additional rows. • Calculated columns generates the logical columns. • Cross Apply generates the logical rows

9. Example of Computed Columns • XQuery is used to convert XML into Columns • SELECT xml.value('(/*/@CVSS_score)[1]','float') as CVSS_Score • Proper design tolerates change of schema • Note: cve/1.2 and no namespace above

10. Example of Logical Rows • All of the data can be extracted into computed views • We get multiple logical rows from one physical row. • Tolerates typical evolution of schema Create View vCVE_VendorProduct As select CVEID, item.value('./@name','varchar(100)') as ProductName, item.value('./@vendor','varchar(100)') as Vendor from CVECross Apply Xml.nodes('//*[@name and @vendor]') as prod(item)

11. More logical rows examples

12. Common Vulnerability Scoring System (CVSS) • All CVEs come with a generic NIST determined CVSS score and vector • Organizations may wish to determine their own score and vectors. • Computed column can automatically replace NIST value with your own.

13. Open Vulnerability and Assessment Language (OVAL) • Physical table slightly more complex • OvalID (could be computed) • Element Version (could be computed) • SchemaVersion is the version of OVAL • Comes from header of imported file

14. OVAL Logical Columns and Logical Rows • Over 70 different logical record layouts SELECT localname, count(*) as [Count] from OVAL group by localname SELECT namespaceuri, count(*) as [Count] from OVAL group by namespaceuri

15. Some OVAL Logical Rows

16. Data is often hierarchical in the XML • Extract of hierarchy is easy • definition  test  state • Object var • State var

17. Common Platform Enumeration (CPE) • CPE has a 2 column physical structure <cpe-item name="cpe:///0verkill:0verkill"> <title>0verkill 0verkill</title> </cpe-item>

18. Putting the parts together • OVAL-CVE (CVSS) -CPE

19. Extensible Configuration Checklist Description Format (XCCDF) • No Global Identifiers results in more columns • One table is for identification only.

20. XCCDF computed and cross-apply

21. Putting SCAP into play • Identifying devices/computers… • Recording automated results • Recording manual results

22. Device / Host Identification • OVAL’s system_info node provides the pattern (included with all OVAL results) • Physical Table is just two columns

23. Device information • OVAL interpreters returns a <system_info> • Contains sufficient information to uniquely identify the device 99.9% of the time

24. Device computed columns and logical rows

25. OVAL Results • Uploaded just like SCAP files • Put into a folder and utility will upload • Complete history is kept (purge as needed) • UploadID points to source document

26. Using the database to improve performance • OVAL data is coming from multiple authors • Often the same element content is seen with many different ids • Some sources have 20% duplicate internally • If we can identify duplicate contents and eliminate it, then • Size of the OVAL file to download is reduced • Number of tests and executions on the client is reduced and less work (i.e. CPU usage, memory) on the client. • Utility identifies duplicate content

27. This is just a start … • We have • Viewed how SCAP elements are related • An elegant but simple design for a SCAP database • Supports reporting across multiple devices • Runs on free software (SQLExpress 2005) • Will support up to 4 gigs (with Express) for each of: • Device history data • SCAP import data history • Current State data of devices • Automated import of SCAP data • Automated import of Device result files

28. Where do you get this stuff • Source: • http://oval.lassesen.com/Nist2007/ • Database: (Free – no expiry) • Microsoft SQL Server Management Studio Express • Microsoft SQL Server 2005 Express Edition with Advanced Services Service Pack 2 • Allows full text search of XML