Creating SQL Reports From PFCLScan

We have been asked if it is possible to take the results of a PFCLScan audit and instead of producing a nice HTML or MS Word or PDF report could the results be loaded into a vulnerability SQL database that the customer uses. Yes, of course, this is possible. The reporting language in PFCLScan has been designed to allow simple quick reports to be created. The reporting language is a simple text based template language. You can create any report that is text based. First create a template text file that is designed how you want your report to look for layout and style. Then decide where you want the data to be added from PFCLScan. This data can come from:

• The tool itself (version, name etc)
• From the scan (time of scan, length of scan, number of errors etc)
• From the project and policies (project name, target, policy names, check names, types etc)
• From the results of the scan of course (both detailed results at the individual error level and summary results at the check or policy level)

The reporting language is great for creating nice reports. You can, for instance create an MS Word document with a style based on what you want to see with layouts, tables, logos and more. Then simply save the file as HTML in MS Word and then open the HTML file in a text editor and add PFCLReport language variables where you want to have the data and output. We wanted report creation to be at the simple level of office tools so that as many people as possible can easily create their own reports without being programmers.

We also do not obfuscate or encrypt the shipped templates; these are just text files (.tem) that you can read and edit or change if you wish to see how we did it.

Finally, we also included a Word Processor that can save files in PDF, Word 97-2003 and current .docx file formats and a programmer’s editor. The fact we have these two tools in the PFCLScan interface also means that you can develop reports completely in PFCLScan.

The purpose of this demo though is to see how we can use PFCLScan to generate SQL so that the details of the cracked passwords in our sample scan can be loaded directly into an Oracle database for analysis. First let us decide on the data to be stored. In our example this is going to be:

• Project ID to distinguish cracked passwords from different databases
• Username
• 10g Password
• 11g Password
• Mode of cracking
• 10g Attempts (number of guesses of the password)
• 11g Attempts (number of guesses of the 11g password)
• Account status

To enable us to insert the data we need a table creation script first (note we have dispensed with tablespaces, sizing, indexes etc for this example, you will want to consider this):

Now let us install the table into the database in preparation for the data from the report:

The preparation is complete so now first let us create a simple PFCLScan project and use the Password Cracker template:

Now we will set up the connection details to our sample 12cR1 database and test them:

OK, that works so now we can run the project and see what passwords are cracked:

Now, normally we could run a report that creates a nice HTML report that we have the option to save as an MS Word document or save as a PDF for distribution. Because we want to save the cracked password data to a database (in this example we use the same database; that is just for ease of this example and would not be the case in a real scenario as we would not advise storing the cracked passwords into the database that was analysed. This is just an example for the technique).

Now open up the "Reports tab" and choose "Create Report" and in the programmers editor presented we will write the report template. Here is the editor before we start:

Now let us write the report template and save the template to the reports directory using the File menu in the programmers editor:

The filename is now visible. Now we can run a report and use our new template and test if it works. Choose the "Run Report" button on the "Home tab" and follow the wizard, instead of choosing a template from the list in the second screen, click next and locate the new report template just created with the "Browse" button, then choose SQL and text and give the report a name. The final screen in the wizard looks like this:

When the report has run it can now be opened:

Cancel this window if you would prefer to open the output file in PFCLScan else click "Open" and the file will open in the text editor you have associated with Internet Explorer. We chose to cancel and then expanded the project tree at the left of the window to show reports listed under "Fix Scripts". Simply click on the report and the generated SQL file will open in the programmers editor. You can see that we generated an SQL file to insert the data to our database:

We can now run this file in SQL*Plus and insert the data into the database. We must make a note of the filename of course first:

The script that was generated also commits the data as we added a commit statement at the end of the template. We can now work with this data in the database if we wish:

These results could be scanned from many databases and comparisons then made against all databases using SQL. The simple SQL above looks for the worst case scenario, a password set to the username (in UPPER) and account status is open.

Of course using an Oracle database to summarise scan data is fine and we have customers who want to do this (actually with a mysql database) as they already correlate their scanning with other tools into a database. PFCLScan is well equipped to integrate with existing systems as you can see it can easily generate SQL. This SQL could also be fix scripts or audit settings.

We can also summarise across multiple projects where the same scans have been performed in PFCLScan itself without using a database; we will discuss how you could do this in a later blog post

What would be nice is to avoid all the manual steps of running the scan, running the report to generate the SQL file and then running that file against the database to insert the data. PFCLScan can be easily configured to automate processes such as this. We can specify multiple tasks such as running a scan, running a report and executing SQL all as one step; this is the automation feature of PFCLScan. We can also run projects from the command line so that PFCLScan can easily be integrated with other tools.

You may have noticed that the SQL table is "awkward" as we specified all fields as VARCHAR2 where some are clearly numbers. This is because we took the simplest approach in this example to generate the SQL Insert in one line in the report template. We also have another fine grained approach where we can specify each column of results where the SQL must be specified in the policy. Watch out for an example of this in an upcoming blog post.

In the next blog post we will show you how to automate the scan, generation of SQL and execution of that SQL for this same example and discuss how automation can be taken much further in PFCLScan to allow PFCLScan to be more powerful for you and also to allow easy integration with other tools.