Tutorial: Monitoring changes to metadata

You can configure FortiDBto use your database’s auditing features to monitor for metadata changes and generate alerts based on the policies you specify. For example, you can configure FortiDB to generate alerts when database tables or columns are created, deleted, or modified. You can then use the alert information to generate a report.

This example configures FortiDB to monitor an Oracle database. Before you start the tutorial, ensure that the database has the required configuration. For details, see Oracle target database pre-configuration.

FortiDB can use several different methods to collect information from the monitoring process. The value of your database’s audit_trail parameter determines which collection method you use. For this example, because the value of audit_trail is db, extended, so the collection method is DB, EXTENDED.

For a description of other collection methods, see Configuring Oracle monitoring.

Create a target

A target specifies a database for to monitor.

User Name	`admin`
Password	`fortidb1!$`

All DAM tasks require the user to log in as admin.

In the navigation menu, go to Target Database Server > Targets.
On the Targets page, click Add.
On the General tab, enter the following information. For this example, the target is an Oracle database:

Name	`dam3target`
Type	Oracle
DB Host Name/IP	The IP address or name of the machine where the database is located (for example, `test_machine` or `172.30.12.112`)
Port	The number of the port the database uses; the default port is 1521
DB Name	The name of the database (for example, `orcl`)
User Name	The database user name
Password	The password for the database user
DB Activity Monitoring	Select Allow.

To verify that the connection parameters are correct, click Test Connection.

The message "Success" is displayed at the top of the page.

Click Save.

The dam3target item is displayed in the list of targets.

Configure an alert policy for metadata

In the navigation menu, click DB Activity Monitoring > Monitoring Management.

Your target database is listed on the Target Monitoring Management page.

Click dam3target (the name of the target you created).
On the General tab, confirm that the following default Audit Configuration values are selected:

Collection Method	DB, EXTENDED
Polling Frequency	60

To test the collection method, click Test.

The message "Success" is displayed the top of the page.

Click the Alert Policies tab.
Locate the policy item Tables, which has a Type value of (metadata policy icon), and then select by selecting its check box.
Click Enable.

Under Status, a green icon with an arrow is displayed.

Start monitoring

To start monitoring the database, click the General tab, and then click Start Monitoring.

Monitor Status displays Starting and then Running.

If the message "NEED_RECONFIGURE" is displayed, click the Alert Policies tab, and then click the Reconfigure* button.

View alerts generated by the policy and export them as a report

Using a database client-side application, execute several SQL statements that generate alerts.

For example, execute the following SQL statements:

create table table1 (column1 int, column2 char);

drop table table1;

To view alerts, click DB Activity Monitoring > Security Alerts.
In the Security Alerts list, click an item to display its details under Alert Details (below the list).

To hide the alert details, beside Alert Details, click the triangle icon.

To change the alert status from "Unacknowledged" to "Acknowledged", do the following:
1. Select the check box(es) of the alerts to change, and then select "Acknowledged" in the Status dropdown list.
2. Click Apply.

The color of the status icon changes.

To create a customized report, click Report > User-Defined DAM Reports, and then select Add.
On the General tab, for Name, enter a name for the report. Optionally, for Description, enter a short description for the report.
Click the Table View tab.
In the Available Columns list, select columns to include in the report, and then click >> (right arrows) to add the selected columns to the Columns in Report list.
Click Save.
On the User-Defined Alert Reports page, in the list of reports, select the report you just created, and then click Run.
After FortiDB has run the report, beside the report name, click [+] (plus sign).

A list of items with names created from the report name and run times is displayed.

Click a run report item to view the report.
To export the report, click one of the following file format icons:
- PDF
- TXT (tab-delimited)
- XLS (Excel)
- CSV (comma-separated values)

Your browser prompts you to download a file of the specified format.

Name	`dam3target`
Type	Oracle
DB Host Name/IP	The IP address or name of the machine where the database is located (for example, `test_machine` or `172.30.12.112`)
Port	The number of the port the database uses; the default port is 1521
DB Name	The name of the database (for example, `orcl`)
User Name	The database user name
Password	The password for the database user
DB Activity Monitoring	Select Allow.

To verify that the connection parameters are correct, click Test Connection.

The message "Success" is displayed at the top of the page.

Click Save.

The dam3target item is displayed in the list of targets.

Configure an alert policy for metadata

In the navigation menu, click DB Activity Monitoring > Monitoring Management.

Your target database is listed on the Target Monitoring Management page.

Click dam3target (the name of the target you created).

On the General tab, confirm that the following default Audit Configuration values are selected:

Collection Method	DB, EXTENDED
Polling Frequency	60

To test the collection method, click Test.

The message "Success" is displayed the top of the page.

Click the Alert Policies tab.

Locate the policy item Tables, which has a Type value of

(metadata policy icon), and then select by selecting its check box.

Click Enable.

Under Status, a green icon with an arrow is displayed.

Start monitoring

To start monitoring the database, click the General tab, and then click Start Monitoring.

Monitor Status displays Starting and then Running.

If the message "NEED_RECONFIGURE" is displayed, click the Alert Policies tab, and then click the Reconfigure* button.

View alerts generated by the policy and export them as a report

Using a database client-side application, execute several SQL statements that generate alerts.

For example, execute the following SQL statements:

create table table1 (column1 int, column2 char);

drop table table1;

To view alerts, click DB Activity Monitoring > Security Alerts.

In the Security Alerts list, click an item to display its details under Alert Details (below the list).

To hide the alert details, beside Alert Details, click the triangle icon.

To change the alert status from "Unacknowledged" to "Acknowledged", do the following:

Select the check box(es) of the alerts to change, and then select "Acknowledged" in the Status dropdown list.
Click Apply.

The color of the status icon changes.

To create a customized report, click Report > User-Defined DAM Reports, and then select Add.

On the General tab, for Name, enter a name for the report. Optionally, for Description, enter a short description for the report.

Click the Table View tab.

In the Available Columns list, select columns to include in the report, and then click >> (right arrows) to add the selected columns to the Columns in Report list.

Click Save.

On the User-Defined Alert Reports page, in the list of reports, select the report you just created, and then click Run.

After FortiDB has run the report, beside the report name, click [+] (plus sign).

A list of items with names created from the report name and run times is displayed.

Click a run report item to view the report.

To export the report, click one of the following file format icons:

PDF
TXT (tab-delimited)
XLS (Excel)
CSV (comma-separated values)

Your browser prompts you to download a file of the specified format.

Handbook (HTML)

Tutorial: Monitoring changes to metadata