Utilities User Guide FortiDB Version 3.2 www.fortinet.
FortiDB Utilities User Guide Version 3.2 December 19, 2008 15-32000-81369-20081219 © Copyright 2008 Fortinet, Inc. All rights reserved. No part of this publication including text, examples, diagrams or illustrations may be reproduced, transmitted, or translated in any form or by any means, electronic, mechanical, manual, optical or otherwise, for any purpose, without prior written permission of Fortinet, Inc.
Table of Contents Table of Contents FortiDB MA Utilities ................................................................................................. 3 Auto Discovery......................................................................................................... 4 DB2 .....................................................................................................................................6 MS-SQL ......................................................................................
Table of Contents Report Body Columns .................................................................................................44 Abnormal or Unauthorized Changes to Data Report (AUC) .............................................45 COBIT Objectives and Setup Requirements ..............................................................45 Report Body Columns .................................................................................................
FortiDB MA Utilities FortiDB MA Utilities FortiDB MA provides several utilities to help you use other modules: • Auto Discovery to ease the burden of manually setting up database connections • Connection Summary to show which database connections are Open or are Open and Running • Rule Chaining to trigger one rule based upon another • Report Manager for custom, offline reports FortiDB Version 3.
Auto Discovery Auto Discovery FortiDB MA provides the ability to search for, and establish connections to, databases on your network. Rather than manually entering all of the connection information, you can have FortiDB MA automatically discover it for you. Selecting Addresses for Auto-Discovery In order to use this feature: 4 1 Select the Database->New menu, and click the Auto Discovery button on the Create New Database Connection screen. Or you can just select Auto Discovery from the Main page.
Auto Discovery Selecting Non-Standard Ports for Auto-Discovery 5 Click the Begin Discovery button. Results from Auto-Discovery FortiDB Version 3.
DB2 Auto Discovery Discovered Database Information Populating Connection Form The process will automatically return: • Database Type and version • IP address (with port if applicable) • Database name/instance Once the Auto Discovery list is returned, you can create, by clicking the Add button on the Discovered Database Applications screen, the database connections you wish to assess or monitor. The additional required and recommended fields will need to be completed manually.
Auto Discovery MS-SQL • Destined for port 1434 Note: FortiDB MA sends a packet to port 1434, which MSSQL uses in order to return information about itself such as instance name, version, etc. (Even though this is an MSSQL-specific port number, FortiDB MA uses it for all Auto-Discoveryrelated transmissions.) • Originating from the port whose number is specified in the dss.udpport property in dssConfig.properties. FortiDB Version 3.
MS-SQL Connection Summary Connection Summary The Connection Summary utility allows you to see, by FortiDB MA module and in one place, a dashboard view of all of your database connections. Connection Summary Button Connection Summary Output 8 FortiDB Version 3.
Rule Chaining MS-SQL Rule Chaining The Rule Chaining module allows you to associate rules so that one, the source1 rule, can influence the execution of another, the target2 rule. Both rules are established with the same target database. Rule Chaining Setting Screen FortiDB MA offers two types of chained-rule pairs: • Rule pairs in which there are no parameters passed.
MS-SQL Rule Chaining Configuring a Rule Chain for a Specific Target Database Connection You can perform the following: • Choose the target database (the database you want to run the rules against) • Add item (new chain) • Delete item • View/Modify item (make changes to an existing chain) • Enable item (a chain does not have to be enabled when it is created) • Disable item Rule Chaining Setting Screen 10 FortiDB Version 3.
Rule Chaining Chaining with Parameterized User-Defined Rules After the database has been specified and you have clicked on [Add Item], you will be presented with the Create Rule Chaining Settings page. Here, you need to: • Name the Rule Chain • Select the policy you want to use as the Source Rule • Select the target rule (Chained Rule) you want to execute, once the first rule had been violated. • Specify whether you want the chain to run immediately upon source-rule violation or not.
Chaining with Parameterized User-Defined Rules Rule Chaining General PUDR Steps The general step for creating a chain that uses a PUDR are: 1 In UBM, define an Object, User, or Session policy that will be your Source Rule. 2 In UBM, define a PUDR that will be your Target Rule 3 In the Rule Chaining module, define a chain which associates the UBM policy and the PUDR. PUDR Process Parameterized User-Defined Rule Flow Diagram The PUDR process involves these steps.
Rule Chaining Chaining with Parameterized User-Defined Rules PUDR Eligible Rules Disabled Parameter Checkboxes If the chosen target rule cannot accept parameters, they will be grayed out. Validating the PUDR before Saving If one or more variables selected do not appear in the PUDR, FortiDB MA presents a warning message. FortiDB Version 3.
Chaining with Parameterized User-Defined Rules Rule Chaining Chaining the UBM Policy and PUDR Together Associating a Source Rule That Can Pass parameters with a PUDR Example of Chaining to a PL/SQL-based PUDR In this Oracle PL/SQL kill-session example, we: 1 Create a DB user, BAD_GUY, whose session we will monitor, in our Oracle target database. Item Setting for Session Policy 14 FortiDB Version 3.
Rule Chaining Chaining with Parameterized User-Defined Rules Policy Settings for Suspicious Login Time 2 Create a UBM Session Policy, our Source rule, in order to monitor BAD_GUY and generate an alert to trigger our Target rule, a PUDR. We will pass the Session ID from the Source to the Target rule. 3 Create a Target PUDR, in the UBM module, which will contain the following killsession code. That code, in turn, will accept our passed Session ID parameter (shown in red): FortiDB Version 3.
Chaining with Parameterized User-Defined Rules Rule Chaining DECLARE v_str VARCHAR2(80) := 'ALTER SYSTEM KILL SESSION '||chr(39); v_statementVARCHAR2(80); sesid NUMBER; serial NUMBER; usernameVARCHAR(50); osuser VARCHAR(50); machine VARCHAR(50); program VARCHAR(50); BEGIN SELECT sid, serial#,username,osuser,machine,program INTO sesid,serial,username,osuser,machine,program FROM v$session WHERE audsid =$sessionid; v_statement := v_str||sesid||','||serial||chr(39)||' IMMEDIATE'; EXECUTE IMMEDIATE v_statement
Rule Chaining Chaining with Parameterized User-Defined Rules Chained-Rule Alerts: (UBM Session Policy and PUDR) 5 Get an alert when the (the Session Policy) Source rule is violated. 6 Get another alert when the chained PUDR executes and, in this case kills the session of BAD_GUY. 7 And, in the Alert Details dialog, display DB user name, OS user name, machine name, and source-program name as shown above.
Chaining with Parameterized User-Defined Rules Rule Chaining SELECT username, osuser, terminal FROM v$session WHERE osuser = '$osusername' Multiple Source-Rule-Violation Behavior When using the Rule Chaining feature with PUDRs, you might expect a targetpolicy alert for each source-policy alert. However, unless there is a change in the passed parameter, there will be only one PUDR alert--despite multiple sourcepolicy alerts.
Rule Chaining Chaining with Parameterized User-Defined Rules In this case, the alert will be generated only for first object in the SELECT list; namely: vje.test. FortiDB Version 3.
Alert Report Manager Report Manager Report Manager In order to access the FortiDB MA Report Manager module, click on the Report Manager link on the left-side navigator on the main FortiDB MA screen.
Report Manager Alert Report Manager Setting a Timer-Based Schedule Deleting a Previously Set Timer Schedule You can delete a previously set Timer schedule by clicking on the Delete Timer button. Deleting a Timer Schedule Setting a Calendar-based Schedule For a Calendar-based Schedule: 1 Click on the [Add Schedule] button at the bottom of the Schedule Setting screen. 2 Specify the days and/or times you want.
Alert Report Manager Report Manager Setting a Calendar-Based Schedule Setting a Combined Schedule You can also specify a combined schedule which consists of both a timer- and a calendar-based schedule. Setting a Randomized Interval In order to make it difficult to predict your monitoring times, you may also set a reporting schedule that, while dependent on your chosen Interval value, won't run exactly that often.
Report Manager Alert Report Manager Reporting by Time The Alert Report Manager module generates reports based on alerts generated by the various other modules. ARM: Reporting by Time ARM: Reporting by Time: Calendar Pop-up In order to reduce the number of alerts on your report to only those you are interested in, you may now filter alerts based on time. Enabling Email Recipients Please see the FortiDB MA Administration Guide for a discussion of this topic.
Alert Report Manager Report Manager New Reports Menu In the New Reports page, fill in the necessary data information that you want to show in the report. New Report Setting Screen (top) 24 FortiDB Version 3.
Report Manager Alert Report Manager New Report Setting Screen (bottom) You may specify these parameters for your new report: • Report Name (name you choose; this is required) • ID (Alarm ID(s); each alarm1 has a unique ID) • Alert Status (handled, acknowledged, or not) • Alert Severity (Critical, Informational, etc.
Alert Report Manager Report Manager • Alert Generated Time (day or time interval that the alerts occurred) • Report Generate Schedule: • One Time Only (snapshot of current alerts typically used for archiving purposes) • Schedule (run according to the schedule specified in Set Defaults->Schedule Settings) • Report Format (Columns you want to appear and/or be used to sort your report): • File Format • Aggregate Violations checkbox (enables whether similar violations are put in a single Alert re
Report Manager Alert Report Manager Activating ARM In order to begin running scheduled reports, you should use the Reports->Status menu. Check the Yes checkbox and click the Save button. Status Menu Status Dialog Running and Analyzing Reports You may elect to see all reports, or just those created since a specified number of days have occurred, by using the View Reports dropdown. View Reports Dropdown List on Current Reports Screen FortiDB Version 3.
Alert Report Manager Report Manager Current Report Configuration In the row corresponding to your report of interest, you can choose which report version to preview via the Report History dropdown and you can specify reportspecific email recipients by clicking on the Email Receivers icon. Report Summary Action Choosing Summary Report Action By clicking the [Summary] Action button, you can get to a screen provides summary information for each alert.
Report Manager Alert Report Manager Report Detailed Action By clicking the [Detailed] Action] button, you can get to a screen provides detailed information for each alert. The Detailed Report gives specific information about each alert. The Id is a hyperlink that you can click on for more information. As was the case for the Summary Report information screen, you can also click on the Id for the alarm of interest and be taken to the Alert Details screen.
Custom Reports Report Manager Custom Reports Custom Reports Using the open-source JasperReports library1, the Quartz scheduling librar2y, the chart generating Kavachart libra3ry, and the open-source iReport design too4l, you can produce your own custom reports to complement those offered by the FortiDB MA Report Manager. As an example, FortiDB MA is shipping with an Alert Statistics Report and Template, produced by the above tools and libraries. Reports can be generated in PDF, HTML, or Excel format.
Report Manager Custom Reports You can select: • Time only schedule • Daily schedule • Weekly schedule • Monthly schedule Time-only Schedule Settings Daily Schedule Settings You can have your reports run on a daily basis at a certain time. FortiDB Version 3.
Custom Reports Report Manager Weekly Schedule Settings You can have your reports run on a weekly basis on day(s). Monthly Schedule Settings You can have your reports run on a monthly basis. Customer and Company Information You can have a custom logo and address (or other descriptive text) appear on each report.
Report Manager Custom Reports Company Information Dialog Note: The name of the file containing the logo cannot contain spaces. Report and Template Generation and Management Custom Reports Main Page From the Custom Reports main page, you can: • Add a report • Modify a report • Delete a report • Modify a report's template FortiDB Version 3.
Custom Reports Report Manager • Generate a Report Adding Reports To add a new report, take the following steps: 1 Click on the Custom Reports Manager link on the left-side navigator or select from the top bar menu, Reports -> Custom Reports Manager. 2 Click the Add Report button. The Add Report dialog displays. 3 Enter your report name and description. 4 Click the Add Report button.
Report Manager Custom Reports Modifying a Report Deleting Reports 1 Select the report you want to delete. 2 Click the Delete Report button. The confirmation window displays. 3 Click the OK. Deleting a Report FortiDB Version 3.
Custom Reports Report Manager Modifying Report Templates You can import your template (*.jrxml) file and save it in the internal reports database. You can also export the template from the internal reports database and store it as a (*.jrxml)) file on local file system. Templates Manager Page Click on the Manage Template(s) button on the Custom Reports Manager page in order to bring up the Templates Manager page, where you can add, modify, delete templates as well as set your default template.
Report Manager Custom Reports Templates Manager: Modifying a Template Page Generating Reports To generate a report, take the following steps: 1 From the Custom Reports Manager page, click the Generate Report button. 2 In the Template parameters page, select the template you want to use from the pull-down list. 3 To set parameter values to filter the report data, click the Settings button. You may limit the rows returned by: • Specifying a "like" or "not like" Column Name condition.
Custom Reports Report Manager Generated HTML Report Example Note: In order to export and save your report files in a tightly secured machine, you might need to change the Internet Option settings of the machine. You can change your Internet Option settings as follows: 38 1 Open Control Panel, and open Internet Options. 2 In the Internet Properties window, click the Security tab. 3 Select Trusted sites. 4 Click the Sites button. The Trusted sites dialog displays.
Report Manager Custom Reports Report History Report History Report History allows you to: • View a list of previously generated reports • Regenerate a particular report • Delete reports or your entire report history FortiDB Version 3.
Licensing and Administration Report Manager Licensing and Administration User Administration for Custom Reports and SOX Reports In order to enable a user to utilize the Custom Reports feature, select the Custom Reports radio button on the User Administration screen. Note: Selecting SOX Reports will automatically enable Custom Reports. The FortiDB MA license file excerpt shown above includes a license to use the Custom Reports and SOX Reports features.
Report Manager Licensing and Administration Property cr.reportDatabase Purpose Possible Values Defines the location of the FortiDB MA Custom Reports database jdbc\:postgresql\://localho st/reportdb cr.user Defines the user name for the FortiDB MA Custom Reports database cr.password Defines the encrypted password for the FortiDB MA Custom Reports database 1. Default1 jdbc\:oracle\:thin\:@192.1 68.5.12\:1521\:ipref fortidbma Initial value when FortiDB MA is installed.
SOX Compliance Reports Report Manager Description of Shipped Sample Report Alert Statstics Report Contains detailed information about alerts: • Database Connection name • Guarded item name • Application name • Policy type • Alert Severity • Alert Status, • Alert Description • Alert Timestamp. Report data is grouped by Database Connection name. Report statistics include: total alerts for database, and total records at the end of report.
Report Manager SOX Compliance Reports Reports and Acronyms This release includes these SOX reports: Report Name Acronym History of Privilege Changes Report HPC Abnormal or Unauthorized Changes to Data Report AUC Abnormal Use of Service Accounts Report AUS Abnormal Termination of Database Activity Report ATD End of Period Adjustments Report EPA Verification of Audit Settings Report VAS Acronym representing all SOX Compliance reports ALL Common Report Header Fields Here are the common report
History of Privilege Changes Report (HPC) SOX Report Specifics SOX Report Specifics This section lists the COBIT objectives and descriptions, the FortiDB MA module-setup requirements, and individual-column detail for each report in this release. History of Privilege Changes Report (HPC) HPC Report Sample COBIT Objectives and Setup Requirements Objective Number(s) AI2.4, DS3.5, DS5.3, DS5.
SOX Report Specifics Abnormal or Unauthorized Changes to Data Report (AUC) Abnormal or Unauthorized Changes to Data Report (AUC) AUC Report Sample COBIT Objectives and Setup Requirements Objective Number(s) FortiDB MA Module Setup Requirement Objective Description AI2.3 Unauthorized changes to data by non-application1 accounts are tracked and reviewed by IT Management on a quarterly basis.
Abnormal Use of Service Accounts Report (AUS) SOX Report Specifics Abnormal Use of Service Accounts Report (AUS) AUS Report Sample COBIT Objectives and Setup Requirements Objective Number(s) FortiDB MA Module Setup Requirement Objective Description DS5.3 Database transactions from unauthorized sources are tracked and reviewed by IT Management on a weekly basis.
SOX Report Specifics Abnormal Termination of Database Activity Report (ATD) Abnormal Termination of Database Activity Report (ATD) ATD Report Sample COBIT Objectives and Setup Requirements Objective Number(s) FortiDB MA Module Setup Requirement Objective Description DS10.1 Routine transactions and processes between the application and the database are reviewed on a daily basis for successful completion by IT Management.
End of Period Adjustments Report (EPA) SOX Report Specifics Description Column Error Code The proprietary error code generated by the originating application. End of Period Adjustments Report (EPA) EPA Report Sample COBIT Objectives and Setup Requirements Objective Number(s) AI2.3 Objective Description FortiDB MA Module Setup Requirement End of period adjustments to the general ledger are tracked and reviewed by Business Management on a monthly basis.
SOX Report Specifics Column End of Period Adjustments Report (EPA) Description Object The name and owner of the database object that was directly manipulated by the flagged activity Time Stamp The exact time the flagged activity was conducted. Terminal Name The terminal IP address or name. Origin Application The name, or other identifier, for the originating application, if the activity originated from an external application or from an application server.
Verification of Audit Settings Report (VAS) SOX Report Specifics The resulting report period is July 24 until August 16, inclusive. Note: Since the time frame from August 3rd and beyond is a future time frame, there will be no data for it in the report. Verification of Audit Settings Report (VAS) VAS Report Sample COBIT Objectives and Setup Requirements Objective Number(s) DS3.5, DS5.5, DS13.
SOX Report Specifics Column Verification of Audit Settings Report (VAS) Description Object The name and owner of the database object that was directly manipulated by the flagged activity Time Stamp The exact time the flagged activity was conducted. Terminal Name The terminal IP address or name. Origin Application The name, or other identifier, for the originating application, if the activity originated from an external application or from an application server.
Verification of Audit Settings Report (VAS) 52 SOX Report Specifics FortiDB Version 3.
Index Index A R activate 20 Alert Behavior 17 Alert Report Manager 20 ARM 20 activating 27 Auto Discovery DB2 6 MS-SQL 6 Auto Discovery 4 Randomized Interval 22 Report Detailed 29 Report History 39 Report Manager 20 Report Result 37 Report Summary 28 Rule Chaining Parameterized User-Defined Rules 11 PL/SQL-based PUDR 14 Rule Chaining 9 C Calendar-based Schedule 21 compliance 20 Connection Summary 8 Custom Report Properties 40 Custom Reports 30 D DB2 6 dssConfig.
Index 54 FortiDB Version 3.
