3 Managing Access and Other Settings

Topics

3.1 Managing User Accounts and Access

Topics

3.1.1 About Oracle Audit Vault and Database Firewall Auditor Accounts and Passwords

There are two types of auditor accounts in Oracle Audit Vault and Database Firewall:

  • Super Auditor:

    • Creates user accounts for super auditors and auditors

    • Has auditor access to all secured targets and secured target groups

    • Grants auditor access to secured targets or secured target groups to auditors

  • Auditor: Has access to specific secured targets or secured target groups granted by a super auditor

Passwords for these accounts need not be unique; however, Oracle recommends that passwords:

  • Have at least one uppercase alphabetic, one alphabetic, one numeric, and one special character (plus sign, comma, period, or underscore).

  • Be between 8 and 30 characters long.

  • Be composed of the following characters:

    • Lowercase letters: a-z.

    • Uppercase letters: A-Z.

    • Digits: 0-9.

    • Punctuation marks: comma (,), period (.), plus sign (+), colon(:), and underscore (_).

  • Not be the same as the user name.

  • Not be an Oracle reserved word.

  • Not be an obvious word (such as welcome, account, database, and user).

  • Not contain any repeating characters.

3.1.2 Creating Auditor Accounts

Super auditors can create both super auditor and auditor user accounts.

To create an auditor account in Oracle Audit Vault and Database Firewall:

  1. Log in to the Audit Vault Server console as a super auditor.
  2. Click the Settings tab.

    The Manage Auditors page appears by default, and displays existing users and the secured targets and/or groups to which they have access.

  3. Click Create.
  4. Enter the User Name and Password, and then re-type the password in the appropriate fields.

    Note:

    Oracle Audit Vault and Database Firewall does not accept user names with quotation marks, such as "jsmith".

  5. In the Type drop-down list, select Auditor or Super Auditor.
  6. Click Save.

    The new user is listed in the Manage Auditors page.

    See Also:

3.1.3 Viewing the Status of Auditor User Accounts

As a super auditor, you can view the status of auditor accounts by clicking the Settings tab. The Manage Auditors page lists all auditor and super auditor accounts, their status, and password expiry dates.

3.1.4 Managing User Access to Secured Targets or Groups

Topics

3.1.4.1 About Managing User Access

Super auditors have access to all secured targets and secured target groups, and can grant access to specific targets and groups to auditors.

You can control access to secured targets or groups in two ways:

  • Modify a secured target or group to grant or revoke access for one or more users.

  • Modify a user account to grant or revoke access to one or more secured targets or groups.

3.1.4.2 Controlling Access by User

To control which secured targets or groups are accessible by a user:

  1. Log in to the Audit Vault Server console as a super auditor.
  2. Click the Settings tab, then click Manage Auditors.

    The Manage Auditors page displays existing users and the secured targets or groups to which they have access.

  3. Click the name of the user account that you want to modify.

    The Modify Auditor page appears.

  4. In the Targets and Groups section, select the secured targets or secured target groups to which you want to grant or revoke access for this user.
  5. Click Grant Access or Revoke Access.

    A check mark indicates access granted. An "x" indicates access revoked.

  6. If necessary, repeat steps 4 and 5.
  7. Click Save.

3.1.4.3 Controlling Access by Secured Target or Group

To control which users have access to a secured target or group:

  1. Log in to the Audit Vault Server console as a super auditor.
  2. Click the Settings tab, and then click Manage Access Rights.
  3. Click the name of the secured target or secured target group for which you want to define access rights.

    The Modify Access page for this secured target or group appears, listing user access rights to this secured target or group. Super auditors have access by default.

  4. In the Modify Access page, select the users for which you want to grant or revoke access to this secured target or group.
  5. Click Grant Access or Revoke Access.

    A check mark indicates access granted. An X indicates that access revoked.

  6. If necessary, repeat steps 4 and 5.
  7. Click Save.

3.1.5 Changing a User Account Type

You can change an auditor account type from auditor to super auditor, or vice versa. Note that if you change a user's account type from auditor to super auditor, that user will have access to all secured targets and secured target groups.

To change a user account type in Oracle Audit Vault and Database Firewall:

  1. Log in to the Audit Vault Server console as a super auditor.

  2. Click the Settings tab.

    The Manage Auditors page appears by default, and displays existing users and the secured targets or groups to which they have access.

  3. Click the name of the user account you want to change.

  4. In the Modify Auditor page, in the Type section, click Change.

  5. In the Type drop-down list, select the new auditor type.

  6. If you changed the type from Super Auditor to Auditor, grant or revoke access to any secured targets or groups as necessary for this user:

    1. Select the secured targets or groups to which you want to grant or revoke access.

    2. Click Grant Access or Revoke Access.

      A check mark indicates access granted. An X indicates that access revoked.

    3. Repeat steps a and b if necessary.

  7. Click Save.

See Also:

Logging in to the Audit Vault Server Console

3.1.6 Unlocking a User Account

An Oracle Audit Vault and Database Firewall auditor account is locked after a number of failed login attempts. A super auditor can unlock user accounts.
  1. Log in to the Audit Vault Server console as a super auditor.
  2. Click the Settings tab.

    The Manage Auditors page appears by default, and displays existing users.

  3. Click the name of the user account you want to unlock.
  4. In the Modify Auditor page, click Unlock.

3.1.7 Deleting an Auditor Account

As a super auditor, you can delete any auditor account except the last super auditor.

To delete an auditor user account:

  1. Log in to the Audit Vault Server console as a super auditor.
  2. Click the Settings tab.

    The Manage Auditors page appears by default, and displays existing users and the secured targets or groups to which they have access.

  3. Select the users you want to delete, and then click Delete.

3.1.8 Changing the Auditor Password

When your Oracle Audit Vault and Database Firewall password expires, you will be prompted to create a new one. However, you can change your password at any time.

Changing your own Password

To change your Oracle Audit Vault and Database Firewall password:

  1. Log in to the Audit Vault Server console as an auditor.

  2. Click the Settings tab, and then under Security, click Change Password.

  3. Enter your Current Password, and then enter your New Password twice.

  4. Click Save.

Changing the Password of Another Auditor

If you are a super auditor, you can change the password of an auditor.

To change the password of an auditor:

  1. Log in to the Audit Vault Server console as a super auditor.

  2. Click the Settings tab, and then under Security, click Manage Auditors. (It should be selected by default.)

  3. In the Manage Auditors page, click the name of the auditor.

  4. In the Change Password section, fill the New Password and Re-enter New Password fields.

  5. Click Save.

See Also:

3.2 Creating Templates and Distribution Lists for Email Notifications

Topics

3.2.1 About Email Notifications and Templates

You can configure Oracle AVDF alerts to trigger an email when an alert is raised or a report is generated. For example, you can create an alert that is triggered every time a connection is made by an application shared schema account outside of the application (for example, APPS or SYSADM). When the user tries to log in, Oracle AVDF sends an email to two administrators warning them about misuse of the application account.

To accomplish this, you must create an email distribution list that defines who will receive the email, and then create an email template that contains a message. You select the template to be used for email notification when you define the alert rule.

3.2.2 Creating or Modifying an Email Distribution List

You can create an email distribution list for a specific notification purpose, that is, a list of email addresses that will receive a notification. You can specify a distribution list when notifying other users about alerts or reports.

To create or modify a distribution list:

  1. Log in to the Audit Vault Server console as an auditor.

    Note:

    • An auditor can create, modify, and delete email distribution lists that were initially created by the same auditor. This is applicable in case of upgrade to Oracle Audit Vault and Database Firewall 12.2.0.8.0 and later.

    • Email distribution lists that were created prior to upgrade of Oracle Audit Vault and Database Firewall 12.2.0.8.0, can be modified or deleted by a super auditor.

  2. Select the Settings tab.
  3. From the Notifications menu, click Distribution Lists.

    The Distribution Lists page is displayed, showing existing lists, which you can modify or delete.

  4. Click Create to add a new list, or click a list name to modify it, and then define the list as follows:

    • Name - Enter a name for the distribution list.

    • Description - (Optional) Enter a description of this list.

    • To - Enter the email addresses, separated by commas, that appear on the To line of notifications using this list.

    • CC - (Optional) Enter the email addresses, separated by commas, that appear on the CC line of notifications using this list.

  5. Click Save.

    The new list appears in the Distribution Lists page. From there, you can modify or delete distribution lists as necessary.

    See Also:

    Logging in to the Audit Vault Server Console

3.2.3 Creating or Modifying an Email Template

An email template enables you to specify the content of an email notification that is triggered by an alert or a report being generated.

To create or modify an email template:

  1. Log in to the Audit Vault Server console as an auditor.

    Note:

    • An auditor can create, modify, and delete email templates that were initially created by the same auditor. This is applicable in case of upgrade to Oracle Audit Vault and Database Firewall 12.2.0.8.0 and later.

    • Email templates that were created prior to upgrade of Oracle Audit Vault and Database Firewall 12.2.0.8.0, can be modified or deleted by a super auditor.

  2. Click the Settings tab.

  3. From the Notifications menu on the left, click Email Templates.

    The Email Templates page displays a list of existing email templates, which you can modify or delete. Some of these templates are predefined.

  4. Click Create to create a new template, or click the name of an existing template to modify it.

  5. Select the template Type:

    • Alert: Creates an email template used for alert notifications.

    • Report Attachment: Creates an email template used for report notifications, and attaches a PDF of the report to the email.

    • Report Notification: Creates an email template used for report notifications, but does not attach the PDF file of the report.

  6. Enter or select the desired values for Name, Description, and Format of this email template.

  7. Use the available tags on the right as building blocks for the Subject and Body of the email.

    The available tags depend on the type of notification. Table 3-1 and Table 3-2 explain the tags in detail.

    You can either click the tag name to transfer it to the template, or copy and paste the tag name to appear in either the Subject or Body of the template.

    For example, using these tags, you create this template:

    • For Subject, you enter Report: #AlertName#, #DateCreated#

    • For Body, you enter The #ReportName# is ready for review at #URL#.

    Then the following email notification may be generated:

    • Subject: System Privileges Report, May 26, 2015, 3:15:06 PM

    • Body: The System Privileges Report is ready for review at http://mau.example.com/console/f?p=7700:4:3525486105242281::NO::P4_REPORT_ID:36

  8. If you had selected Alert Notification Template in the earlier step, then in the Event Information section, select the audit events that you want included in the notification.

    The Event Information section does not appear if you had selected Report Attached Template or Report Notification Template.

  9. Click Save.

    After you create a new template, it is listed in the Notification Templates page. From there, you can modify or delete templates as necessary.

Table 3-1 lists the available tags for alert notification templates.

Table 3-1 Tags Available for Alert Notification Email Templates

Alert Tag Name Description

#AlertBody#

A special tag that is used as a shortcut to include all the available tags in the email

#AlertID#

The ID of the alert

#AlertName#

Name of the alert

#AlertTime#

Time the event causing the alert was created

#AlertSeverity#

Severity of the alert (Critical or Warning)

#AlertStatus#

Status of the Alert (for example, New, Open, or Closed)

#Description#

Description of the alert

#URL#

URL of the alert

Table 3-2 lists the available tags for report notification templates.

Table 3-2 Tags Available for Report Attachment or Notification Email Templates

Report Tag Name Description

#ReportName#

Name of the report

#DateCreated#

Date and time the report was generated

#ReportCategory#

Report Category name, such as "Access Reports"

#URL#

URL link to the report (for Report Notification templates)

See Also:

Logging in to the Audit Vault Server Console

3.3 Creating Non-Interactive Report Templates

This section contains information to create, modify, and use existing PDF or XLS report templates.

Prerequisites

  • BI Publisher Desktop is installed on Microsoft Windows host.

  • User is able to log in to Audit Vault Server through console.

  • Information pertaining to the AVSYS schema holding audit data is available.

Topics

3.3.1 Creating Non-Interactive Report Template

This section contains the required steps to create new Non-Interactive or PDF/ XLS Report, using existing RTF and XML reports.

To create a report template:

  1. Log in to the Audit Vault Server console as auditor.
  2. Click Reports tab and then click on PDF/XLS Reports under CUSTOM REPORTS.
    Result:

    The Uploaded Reports tab displays all the configured reports.

  3. Select any of the existing Report XML and Report RTF file to use as a template.
  4. Click on the icon against the selected report under the Download Report Template column.
  5. Save the report to your local drive with a new name.
  6. To preview changes in the RTF file requires sample data. Write a new Report SQL Query referring to the existing SQL in sample report XML file.
  7. The above SQL Query output is generated from SQL Developer and is exported into XML format. It is not compatible with RTF files. To generate data in RTF required XML format, use the DBMS_XMLGEN.GETXML () function. This is a built in function of Oracle Database.
  8. To generate XML data, use the SQL query string as a parameter to dbms_xmlgen.getxml() function.
    Result:

    It returns XML data as output.

    The below SQL example is for reference only.

    SELECT DBMS_XMLGEN.GETXML (‘YOUR REPORT SQL QUERY WITH PARAMETERS’) xml_data FROM dual;

    Example:

    SELECT DBMS_XMLGEN.GETXML('SELECT TO_CHAR(event_time, ''DS TS'') AS event_time, 
event_name, 
target_object, 
event_status, 
user_name,
client_ip,
client_program,
secured_target_name, 
COUNT(*) OVER () AS totalrowcount,
COUNT(secured_target_name) OVER(PARTITION BY secured_target_name) AS securerowcount 
FROM avsys.event_log elog 
WHERE ROWNUM <= 3000
AND ( event_time BETWEEN ''19-DEC-13 09.35.02.570000000 AM'' AND ''20-DEC-13 09.35.02.570000000 AM'' )
AND secured_target_id IN(SELECT secured_target_id FROM avsys.secured_target   
                        WHERE ( 
                                (secured_target_name_vc=UPPER(''MSSQLKVM5'') 
                                  OR 
                                  secured_target_name_vc LIKE UPPER(''MSSQLKVM5''||''_DELETED%'')
                                ) 
                                  OR
                                  UPPER(''MSSQLKVM5'')=''ALL''
                              )
                            )
ORDER BY secured_target_name, elog.event_time') xml 
from dual;

    Note:

    To generate SQL query string, use additional single quote inside this function for character identifier as escape character.

    For example:

    1. For DS TS date and timestamp formatting, apply single quote (') as escape character.

    2. For event_time timestamp parameter provide value as ''19-DEC-13 09.35.02.570000000 AM''.

      Note:

      Insert two single quotation marks for defining parameters.

    3. For database_name parameter provide value as ''MSSQL_ST''.

    4. Numeric values can be provided as is. Provide value for ROW_LIMIT parameter as 3000 or 20000 (any numerical value). Similarly make changes to other strings and parameters in the SQL query using single quotes.

  9. Copy the query output from SQL Developer tool (or any other tool).
  10. Paste it into notepad and save this file as XML.
  11. There is another option to use SPOOL command to generate XML file. See Generating XML Data File Using SPOOL Command for complete information. Load the generated XML file.
  12. Open the RTF template or sample report downloaded earlier using Microsoft Word.
  13. Click on BI Publisher tab on the top right corner.
  14. Click on Load XML and navigate to the generated XML and load it.
    Result:

    The following message is displayed:

    Data loaded successfully.

  15. Make the necessary changes to the report.
  16. If the file is in RTF format, then continue with the next step. Else, skip the remaining steps as they are relevant only for RTF files. Use Microsoft Word to edit the RTF file.
  17. Change the existing report name.
  18. Change report parameters like REPORT PERIOD, RUN BY, and REPORT RECORD LIMIT if required.
  19. Change the report parameter label if required.

    For example:

    Change the label RUN BY, you can change it directly to RUN BY USER.

  20. Change the report parameter value if required. This is the SQL query column name.

    For example:

    To change the TIME_FROM value double click on TIME_FROM. Or right click on it to access BI Publisher, then select Properties, and Advanced tab. To change <?TIME_FROM?> to data XML column name and the XML tag name for this column is TIME1, so your tag will be <?TIME1?>.

  21. To change existing chart double click on it and change VALUES, AGGREEGATION, LABELS, TYPE, and STYLE parameters. In case the chart is not required, then delete it.
  22. Change data table labels in the report if required. If the data table columns are different, the change the label and values as mentioned in earlier steps. To add additional columns, right click on the table, select Insert, and then select Insert columns to the Right. Similarly the columns can be merged and deleted.
  23. Change report header name if required.
  24. Choose to display secured target level count and level count.
  25. Retain the Time Zone and Date in footer section as they are common to all the reports.
  26. Click on the PDF or Excel icon in the tab to verify the changes.
  27. In case all the changes meet the requirements, then save the RTF file.

    Note:

    In the generated PDF report, data for parameters is not be displayed in header section. The parameters data is sourced from application runtime.

  28. This RTF report file can be uploaded along with XML report file for verification.
  29. Create the XML file.

    The following are the different tags in XML report file:

    1. Parameter: Add or change input report parameters in this tag if new report parameters are different.

    2. DATA: Contains the following tags or headers:

      • Column 1: Data Tag

      • Column 2: Description

      • AUDIT_SUBREPORT: Displays parameter values on RTF files in the header section. These change as per the new report parameters.

      • Time zone: Displays time zone information and is common for all the reports. This need not be changed.

      • TLQR: Contains report SQL and column mappings which should map with RTF column values. In this section, you need to paste your new report SQL query and column alias name mapping in XML column and values tags.

  30. This XML report file can be uploaded along with the RTF file generated earlier.

    Note:

    RTF and XML file names must be same.

  31. Navigate to the uploaded reports section in Audit Vault Server console and click Upload.
  32. Provide updated RTF and unchanged report definition taken from earlier steps.
  33. Verify the report in the Generated Report section of the Audit Vault application.
  34. In case the report is not generated, then check the status in Setting tab and select the job.

3.3.2 Modifying Non-Interactive Report Template

This section contains steps to modify or make cosmetic changes to Audit Vault reports.

To modify a report template:

  1. Log in to the Audit Vault Server console as auditor.
  2. Click Reports.
    Result:

    Uploaded Reports tab displays all the configured reports.

  3. Download Report XML and Report RTF file for the specific report.
  4. To preview changes in the RTF file, requires sample data. Copy the query data from the XML file which is similar to the following. Select the text mentioned below: 
    to_char(event_time, 'DS TS') as event_time
        client_ip,
        user_name,
        osuser_name,
        client_program,
        secured_target_name,
        error_code,
        error_message,
        decode
        {
                audit_trail_id,
                null, 'Network',
                'Audit Trail'
        }       as event_source
from
        avsys.event.log
  5. The query output generated from SQL Developer and exported into XML format is not compatible with RTF files.
  6. To generate XML data, use the dbms_xmlgen.getxml() function. This is a built in function of Oracle Database.
  7. Pass SQL query string as a parameter to dbms_xmlgen.getxml() function.
    Result:

    It returns XML data with sample output mentioned below.

    SELECT DBMS_XMLGEN.GETXML('SELECT TO_CHAR(event_time, ''DS TS'') AS event_time, 
event_name, 
target_object, 
event_status, 
user_name,
client_ip,
client_program,
secured_target_name, 
COUNT(*) OVER () AS totalrowcount,
COUNT(secured_target_name) OVER(PARTITION BY secured_target_name) AS securerowcount 
FROM avsys.event_log elog 
WHERE ROWNUM <= 3000
AND ( event_time BETWEEN ''19-DEC-13 09.35.02.570000000 AM'' AND ''20-DEC-13 09.35.02.570000000 AM'' )
AND secured_target_id IN(SELECT secured_target_id FROM avsys.secured_target   
                        WHERE ( 
                                (secured_target_name_vc=UPPER(''MSSQLKVM5'') 
                                  OR 
                                  secured_target_name_vc LIKE UPPER(''MSSQLKVM5''||''_DELETED%'')
                                ) 
                                  OR

                                  UPPER(''MSSQLKVM5'')=''ALL''
                              )
                            )
ORDER BY secured_target_name, elog.event_time') xml 
from dual;

    Note:

    To generate SQL query string, use additional single quote inside this function for character identifier as escape character.

    For example:

    1. For ''DS TS'' date and timestamp formatting, apply single quote (') as escape character.

    2. For event_time timestamp parameter provide value as ''19-DEC-13 09.35.02.570000000 AM''.

      Note:

      Insert two single quotation marks for defining parameters.

    3. For database_name parameter provide value as ''MSSQL_ST''.

  8. The above SQL query generates data in XML format, which can be uploaded in BI publisher template (RTF).
  9. Copy the query output from SQL Developer tool (or any other tool).
  10. Paste it into notepad and save this file as XML.

    Note:

    There is another option to use SPOOL command to generate XML file. See Generating XML Data File Using SPOOL Command for complete information. Load the generated XML file.

3.3.3 Generating XML Data File Using SPOOL Command

This section contains the necessary steps to generate XML from SQLPLUS using SPOOL command.

To generate an XML file using SPOOL command:

  1. Take the SQL query used to generate data in XML format.

    For example:

    SELECT DBMS_XMLGEN.GETXML('SELECT TO_CHAR(event_time, ''DS TS'') AS event_time, 
event_name, 
target_object, 
event_status, 
user_name,
client_ip,
client_program,
secured_target_name, 
COUNT(*) OVER () AS totalrowcount,
COUNT(secured_target_name) OVER(PARTITION BY secured_target_name) AS securerowcount 
FROM avsys.event_log elog 
WHERE ROWNUM <= 3000
AND ( event_time BETWEEN ''19-DEC-13 09.35.02.570000000 AM'' AND ''20-DEC-13 09.35.02.570000000 AM'' )
AND secured_target_id IN(SELECT secured_target_id FROM avsys.secured_target   
                        WHERE ( 
                                (secured_target_name_vc=UPPER(''MSSQLKVM5'') 
                                  OR 
                                  secured_target_name_vc LIKE UPPER(''MSSQLKVM5''||''_DELETED%'')
                                ) 
                                  OR

                                  UPPER(''MSSQLKVM5'')=''ALL''
                              )
                            )
ORDER BY secured_target_name, elog.event_time') xml 
from dual;
  2. Connect to the Audit Vault Server Database as avsys user.
  3. Execute the command:

    spool <path of the xml file>/<name of the xml file>.xml

  4. Run the SQL query from the earlier step.
  5. Execute the following command to turn off generating the XML data file further:

    spool off

  6. Check the XML file generated in the location defined earlier. Remove unwanted strings and retain only the data.
  7. Save it.
  8. Open the RTF template downloaded earlier.
  9. Click on BI Publisher tab on the top right corner.
  10. Click on Load XML.
  11. Navigate to the location of the generated XML file.
  12. Load it.
    Result:

    The following message is displayed:

    Data loaded successfully.

  13. Make the necessary changes.
  14. To verify the change, click on the PDF or Excel icon in the tab.
  15. If all the changes are complete as expected, save the RTF file.

    Note:

    In the generated PDF report, data for parameters is not displayed in the Header. These parameters and data is captured during application runtime.

  16. Navigate to the uploaded reports section in Audit Vault Server console and click Upload.

    Note:

    RTF and XML file names must be same.

  17. Provide updated RTF and unchanged report definition taken from earlier steps.
  18. Verify the report on the server.

3.3.4 Generating Reports Using RTF And XML Sample Templates

This section contains the necessary steps to generate reports using RTF and XML sample templates.

To generate report using sample template:

  1. Use the existing XML and RTF report files.
  2. Save them with a new report name.
  3. To preview changes to the RTF file, sample data is required. Write a new Report SQL Query.
  4. The above SQL Query output is generated from SQL Developer and is exported into XML format. It is not compatible with RTF files. To generate data in required RTF XML format, use the DBMS_XMLGEN.GETXML () function. This is a built in function of Oracle Database.
  5. Provide SQL query string as a parameter to dbms_xmlgen.getxml() function. Execute:

    SELECT DBMS_XMLGEN.GETXML (‘YOUR REPORT SQL QUERY WITH PARAMETERS’) xml_data

    FROM dual;

    Result:

    It returns the following example XML data as output:

    SELECT DBMS_XMLGEN.GETXML('SELECT TO_CHAR(event_time, ''DS TS'') AS event_time, 
event_name, 
target_object, 
event_status, 
user_name,
client_ip,
client_program,
secured_target_name, 
COUNT(*) OVER () AS totalrowcount,
COUNT(secured_target_name) OVER(PARTITION BY secured_target_name) AS securerowcount 
FROM avsys.event_log elog 
WHERE ROWNUM <= 3000
AND ( event_time BETWEEN ''19-DEC-13 09.35.02.570000000 AM'' AND ''20-DEC-13 09.35.02.570000000 AM'' )
AND secured_target_id IN(SELECT secured_target_id FROM avsys.secured_target   
                        WHERE ( 
                                (secured_target_name_vc=UPPER(''MSSQLKVM5'') 
                                  OR 
                                  secured_target_name_vc LIKE UPPER(''MSSQLKVM5''||''_DELETED%'')
                                ) 
                                  OR
                                  UPPER(''MSSQLKVM5'')=''ALL''

                              )
                            )
ORDER BY secured_target_name, elog.event_time') xml 
from dual;

    Note:

    To generate SQL query string, use additional single quote inside this function for character identifier as escape character.

    For example:

    1. For ''DS TS'' date and timestamp formatting, apply single quote (') as escape character.

    2. For event_time timestamp parameter provide value as ''19-DEC-13 09.35.02.570000000 AM''.

      Note:

      Insert two single quotation marks for defining parameters.

    3. For database_name parameter provide value as ''MSSQL_ST''.

    4. Numeric values can be provided as is. Provide value for ROW_LIMIT parameter as 3000 or 20000 (any numerical value).

    5. Apply additional single quote (') for string and date parameters, if they are present in SQL query.

  6. Copy the query output from SQL Developer tool (or any other tool).
  7. Paste it into notepad and save this file as XML.
  8. There is another option to use SPOOL command to generate XML file. See Generating XML Data File Using SPOOL Command for complete information. Load the XML data file.
    Result:

    The following message is displayed:

    Data loaded successfully.

  9. Make the changes to the RTF file as required. Change the report header name.
  10. Change the report parameters like Label and Values if required.

    For example:

    To change the label use option like RUN BY.

  11. To change the TIME value double click on one of the TIME fields. Or right click on it to access BI Publisher, then select Properties, and then Advanced tab. In the Advanced tab, add column reference value in <?ColumnName?> format. This column name is a reference of SQL Query output column name.
  12. To change the Report Chart go to BI Publisher tab, and click on CHART. Add chart as per your requirement by providing uploaded XML data as parameters.
  13. In the Report Data Table, go to BI Publisher tab, and click on TABLE WIZARD. Select the columns to be displayed in the table.
  14. Change the report header of the second page.
  15. In the secured target group level and total, choose aggregation at secured target level and total count at report level. Execute:

    count(*) over () as totalrowcount,

    count(secured_target_name) over(partition by secured_target_name) as securerowcount

  16. Keep same columns alias so that they can be referred in the report.
  17. Retain the Time Zone and Date in footer section as they are common to all the reports.
  18. Click on the PDF or Excel icon in the tab to verify the changes.
  19. In case all the changes meet the requirements, then save the RTF file.

    Note:

    In the generated PDF report, data for parameters is not be displayed in header section. The parameters data is sourced from application runtime.

  20. This RTF report file can be uploaded along with XML report file for verification.
  21. Create report XML using an existing template. Follow and use the comments existing in the template and modify accordingly. This is the report XML which is used to upload along with RTF file generated earlier.
  22. Navigate to the uploaded reports section in Audit Vault Server console and click Upload.

    Note:

    RTF and XML file names must be same.

  23. Provide updated RTF and unchanged report definition taken from earlier steps.
  24. Verify the report in the Generated Report section of the Audit Vault application.
  25. In case the report is not generated, then check the status in Setting tab and select the job.

3.4 Creating Alert Syslog Templates

Oracle Audit Vault and Database Firewall provides a default template for Oracle Audit Vault and Database Firewall alerts sent to syslog. If you do not want to use the default template, you can create your own alert syslog templates, and select one to use as a default instead. Using your own template lets you add more information to alert syslog messages.

To create an alert syslog template:

  1. Log in to the Audit Vault Server console as an auditor.
  2. Click the Settings tab, and then in the Notifications menu, click Alert Syslog Templates.
  3. Click Create.
  4. In the Create Alert Syslog Template page, enter a Name for the new template, and optionally, a Description.
  5. Select the Event Information that you want to include in syslog alerts from Oracle Audit Vault and Database Firewall.

    The alert syslog message will be formatted as a list of event records containing all fields you select in the template. The short event name (shown in parentheses) will be used.

    If you select Include "Error Message (EM)" as part of the syslog payload, then this option lengthens the syslog message so that some data may be truncated.

  6. If you want to make this the default template, then select Save as default template.

    The default alert syslog template is used for all Oracle Audit Vault and Database Firewall alert syslog messages.

  7. Click Save.

3.5 Viewing Enforcement Point and Audit Trail Status

Topics

3.5.1 Viewing Enforcement Point Status

To view enforcement points configured for all your secured target databases:

  1. Log into the Audit Vault Server console as an auditor.
  2. Click the Settings tab or the Secured Targets tab.
  3. From the Quick Links menu, click Enforcement Points.

    See Also:

3.5.2 Viewing Audit Trail Status

To view a list of audit trails collected for all your secured targets:

  1. Log into the Audit Vault Server console as an auditor.
  2. Click the Settings tab or the Secured Targets tab.
  3. From the Quick Links menu, click Audit Trails.

    Audit trails for all your secured target are listed in a table showing the trail, its status, the secured target name and type, and the host from which the trail was collected, the trail location and type.

  4. Optionally, click a column title to sort by that column.

    See Also:

3.6 Monitoring Jobs

You can see the status of various jobs that run on the Audit Vault Server, such as report generation, and user entitlement or audit policy retrieval from secured targets.

To see the status of jobs in the Audit Vault Server:

  1. Log in to the Audit Vault Server as an Auditor.
  2. Click any of these tabs: Secured Targets, Reports, Policy, or Settings.
  3. In the Quick Links menu on the left, click Jobs.

    A list of jobs is displayed, showing the job type, ID, timestamp, status, and associated user name. To see details for an individual job, click the icon to the left of that job. See Figure 3-1 below.

Figure 3-1 Jobs Page


Description of Figure 3-1 follows
Description of "Figure 3-1 Jobs Page"

See Also:

Logging in to the Audit Vault Server Console