1. Auditor's Guide
  2. Creating Alerts

10 Creating Alerts

Learn about creating alerts.

10.1 About Alerts

You should understand how alerts work in general and how to define useful alerts.

10.1.1 Overview

Alerts can be used for targets and third-party plug-ins.

You can create and configure alerts on events for targets, and for third-party plug-ins that have been developed using the Oracle Audit Vault and Database Firewall SDK. These events may be collected by the Audit Vault Agent or the Database Firewall. Alerts are independent of audit policies or firewall policies.

Alerts are rule-based. That is, if the rule definition is matched (for example, User A fails to log in to Client Host B after three tries), then an alert is raised. An alert can be applied to multiple targets, such as four Oracle databases. The alert rule can include more than one event and the event comes from different targets. For example, User A failed to log in to target X and User A also failed to log in to target Y.

You can specify an alert severity. Also, if a target is monitored by a Database Firewall, you can configure alerts based on audit records sent by the firewall, in addition to the alerts specified in the firewall policy.

When you configure an alert, you can set up an email to be automatically sent to a user, such as a security officer, or to a distribution list. You can also configure templates to be used for email alert notification.

Alerts are raised when the audit data reaches the Audit Vault Server, not when the event that raises the alert occurs. The time lag between when the event occurs and when the alert is raised depends on several factors, including how frequently the audit records are collected. The timestamp of an alert event indicates the time that the event occurred (for example, the time that User A tries to log in). The timestamp for the alert indicates when the alert was raised.

Alerts have a retention policy of three months online and zero months in archive.

Related Topics

10.1.2 Defining Useful Alerts

A good way to define meaningful alerts is to first browse activity reports in Oracle Audit Vault and Database Firewall.

Activity reports contain a variety of audit and network event data, so browsing them can help you determine the key fields in audit records that are of special interest to you. These audit record fields are columns in the activity reports.

Looking at the report columns of interest, and the values in those columns, is a useful starting point for creating an alert that focuses on the audit events on which you want to be alerted. You can then create an alert with a condition (a rule) that defines the specific audit record field(s) and values that will trigger the alert.

For example, suppose you want to be alerted on schema changes to certain database objects. You can start by browsing the Database Schema activity report.

In this report, you can see the various database target objects, users, client program names, and other data associated with schema change audit events captured by Oracle Audit Vault and Database Firewall. From here, you can decide which target objects you want to alert on. You can then narrow down the alert to specific users, client programs, etc.

Related Topics

10.2 Creating Alerts and Writing Alert Conditions

Learn about creating alerts and writing alert conditions.

10.2.1 Creating or Modifying an Alert

You create custom alerts or use a predefined alert.

When you create an alert in Oracle Audit Vault and Database Firewall, you define the conditions that will trigger the alert, and specify the type of notification that will be sent, and to whom. For example, you could create an alert that is raised each time User X tries to modify Table Y, which will notify administrator Z, using a specific email notification template. Oracle Audit Vault and Database Firewall has a preconfigured alert that is triggered based on alert settings in your Database Firewall policy. The alerts you create are for audit and other events not associated with Database Firewall.

Tip:

Oracle recommends creating an alert policy with email notifications to monitor the AVREPORTUSER, AVSAUDIT, and ORDS_PUBLIC_USER users.
Create an alert policy with email notification with the following condition:
upper(:EVENT_STATUS)='FAILURE' and upper(:EVENT)='LOGON' and (upper(:USER)='AVREPORTUSER' or upper(:USER)='AVSAUDIT' or upper(:USER)='ORDS_PUBLIC_USER')

If you receive an alert you should check the event details and take action to prevent further login attempts for the AVREPORTUSER, AVSAUDIT, and ORDS_PUBLIC_USER users.

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

  2. Click on Policies tab.

  3. From the left navigation menu, select Alert Policies.

  4. To view or modify the definition for an existing alert, click its name in the Alert Name field.

  5. To create a new alert definition click Create.

  6. Enter the Alert policy name.

  7. Specify the information in the following fields:

    • Alert description: Optionally, enter a description for this alert.

    • Target type: Select a target type. For example, Oracle Database.

    • Severity: Select Warning or Critical.

    • Condition: Enter a Boolean condition that must be met for this alert to be triggered.

      You can click any of the Condition - Available Fields listed on the right to enter them as part of the alert condition. These fields are the permissible audit or network event fields you can use to build your condition in the following format:

      :condition_field operator expression

      You can use any valid SQL WHERE clause with the available fields, making sure to include a colon (:) before that field. For example, your condition may be:

      upper(:EVENT_STATUS)='FAILURE'

      Caution:

      Starting in Oracle AVDF 20.11 the following attributes have been changed:

      Starting in Oracle AVDF 20.11, users can add filters in the UI interactive report provided on the create alert policy and copy them in an alert condition. Underneath the Condition field, users can choose either Copy condition from examples, Copy condition from alert policies, Create condition using report, or Create condition using global sets (Oracle AVDF 20.13 and later).

      For creating a condition from a global set, only one global set can be specified in a condition clause but additional global set conditions can be appended with AND or OR. The following table shows which alert fields will be checked depending on the selected global set.
      Global Set Alert Field
      IP Address Set CLIENT_IP
      OS User Set OSUSER
      Client Program Set CLIENT_PROGRAM
      Database User Set USER
      Privileged User Set USER
      Sensitive Object Set OBJECT
      Related Topics
  8. Optionally, in the threshold condition area, specify the following information:

    • Threshold (number): Enter the number of times the alert condition should be met before the alert is raised.

    • Duration (in minutes): If you entered a threshold value that is more than 1, enter the length of time (in minutes) that this alert condition should be evaluated to meet that threshold value. For example if you enter a threshold of 3 and duration of 5, then the condition must be met 3 times in 5 minutes to raise an alert.

    • Group By (field): Select a field from the list to group events by this column for this alert.

  9. Optionally, in the Configure email notification area, specify the following information:

    1. Enable email notification:When email notifications are disabled, there is no other information needed. When email notifications are enabled, specify the following information:

      • Email template: Select a notification template to use for this alert. Starting in Oracle AVDF 20.11, users can associate only one notification template per alert policy. On an upgrade to 20.11, the alert notification template for an existing alert policy will be set to the default alert template.

        See Creating Templates and Distribution Lists for Email Notifications for more detailed information.

      • To: Enter email addresses to receive notifications by writing out the address, followed by the Enter key. Once an email address has been entered, then you can continue writing additional email addresses. In addition to email addresses, you can enter distribution lists directly into this field, followed by the Enter key. If you would like to create a distribution list, click the plus button and add the required information. Enter the Name and Email addresses desired for the list. You can also set this as your default distribution list for email notifications. Any distribution list that you have previously set as default will automatically populate this field.

      • Cc: Enter email addresses to be copied on notifications by writing out the address, followed by the Enter key. Once an email address has been entered, then you can continue writing additional email addresses. In addition to email addresses, you can enter distribution lists directly into this field, followed by the Enter key. If you would like to create a distribution list, click the plus button and add the required information. Enter the Name and Email addresses desired for the list.

  10. Click Save.

    The new alert appears in the Alert Policies page.

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

  2. Click on Policies tab.

  3. From the left navigation menu, select Alert Policies.

  4. To view or modify the definition for an existing alert, click its name in the Alert Name field.

  5. To create a new alert definition click Create.

  6. Enter the Alert Name.

  7. Specify the information in the following fields:

    • Type: Select a target type. For example, Oracle Database.

    • Severity: Select Warning or Critical.

    • Threshold (times): Enter the number of times the alert condition should be met before the alert is raised.

    • Duration (min): If you entered a threshold value that is more than 1, enter the length of time (in minutes) that this alert condition should be evaluated to meet that threshold value. For example if you enter a threshold of 3 and duration of 5, then the condition must be met 3 times in 5 minutes to raise an alert.

    • Group By (Field): Select a field from the list to group events by this column for this alert.

    • Description: Optionally, enter a description for this alert.

    • Condition: Enter a Boolean condition that must be met for this alert to be triggered.

      You can click any of the Condition - Available Fields listed on the right to enter them as part of the alert condition. These fields are the permissible audit or network event fields you can use to build your condition in the following format:

      :condition_field operator expression

      You can use any valid SQL WHERE clause with the available fields, making sure to include a colon (:) before that field. For example, your condition may be:

      upper(:EVENT_STATUS)='FAILURE'

  8. Optionally, in the Notification area, specify the following information:

    1. Template: Select a notification template to use for this alert.

    2. Distribution List: Select an email distribution list that will be notified about this alert.

    3. To: Enter email addresses, separated by commas, to receive notifications.

    4. Cc: Enter email addresses, separated by commas, to be copied on notifications.

    5. Click Add to List to record the email recipients that you entered in the To and Cc fields.

  9. Click Save.

    The new alert appears in the Alert Policies page.

You can monitor alert activity from the dashboard on the Audit Vault Server console Home page.

Related Topics

10.2.1.1 Command Class to Command Mappings for Alert Policies and Reports

Starting in Oracle AVDF 20.11, Database Firewall and Alert policies no longer utilize command classes. Instead, users are able to create policies based on specific commands such as INSERT, UPDATE, or DELETE. This table can help you identify which commands are a part of which command class.

Command Class Commands for Oracle Commands for SQL Server Commands for MySQL Commands for DB2 LUW Commands for Sybase ASE
DCL ADMINISTER, ALTER, CHANGE, COMPRESS, ENCRYPT, GRANT, INVALID, LOGIN, ORADEBUG, REVOKE, SET, STOP ALTER, DENY, GRANT, LOGIN, REVOKE, SET, USE, VALIDATE BINLOG, DROP, FLUSH, GRANT, INSTALL, KEYCACHE, KILL, LOAD, RESET, REVOKE, SET, UNINSTALL, USE GRANT, REVOKE, SET, TRANSFER EXECUTE, GRANT, KILL, LOAD, LOCK, MOUNT, REVOKE, SET, TRANSFER, USE, VALIDATE
DDL ALTER, ANALYZE, ASSOCIATE, AUDIT, COMMENT, CREATE, DISASSOCIATE, DROP, NOAUDIT, RENAME, TRUNCATE ADD, ALTER, CREATE, DISABLE, DROP, ENABLE, RECONFIGURE, TRUNCATE, USE ALTER, CHECK, CHECKSUM, CREATE, DROP, PARTITION, RENAME, REPLACE, TRUNCATE ALLOCATE, ALTER, COMMENT, CREATE, DROP, RENAME, TRUNCATE ALTER, CREATE, DEALLOCATE, DROP, TRUNCATE
DML DELETE, DROP, EXECUTE, EXPLAIN, INSERT, MERGE, RETRIEVE, UPDATE, WRITE BACKUP, DELETE, INSERT, MERGE, RESTORE, UPDATE, WRITE ANALYZE, DELETE, GET, INSERT, LOAD, OPTIMIZE, REPAIR, UPDATE DELETE, EXPLAIN, INSERT, MERGE, REFRESH, UPDATE DELETE, DUMP, EXECUTE, INPUT, INSERT, MERGE, QUIESCE, REFRESH, REMOVE, UNMOUNT, UPDATE, WRITE
Logon LOGIN LOGIN LOGIN LOGIN LOGIN
Logoff LOGOUT LOGOUT LOGOUT LOGOUT LOGOUT
Procedural EXECUTE, EXIT, LOCK CHECKPOINT, DEALLOCATE, END, EXECUTE, GET, KILL, LOAD, MOVE, PRINT, RECEIVE, REVERT, SEND, SLOWDOWN, STOP CHANGE, DEALLOCATE, EXECUTE, PREPARE, RESIGNAL, SET, SIGNAL, START, STOP ASSOCIATE, AUDIT, CONNECT, DECLARE, DISCONNECT, EXECUTE, FLUSH, FREE, GET, LOCK, PREPARE, RELEASE, RESIGNAL, SIGNAL CHECKPOINT, CLEAR, CONFIGURE, CONNECT, DISCONNECT, EXECUTE, EXIT, OUTPUT, PREPARE, PRINT, PUBLISH, QUIT, RECONFIGURE, START, STOP
Select SELECT READ, SELECT SELECT DESCRIBE, SELECT, VALUES SELECT
Transaction COMMIT, ROLLBACK, SAVEPOINT, SET, TRANSACTION BEGIN, COMMIT, ROLLBACK, SAVE, SET COMMIT, END, LOCK, PREPARE, RECOVER, RELEASE, ROLLBACK, SAVEPOINT, START, UNLOCK COMMIT, ROLLBACK, SAVEPOINT COMMIT, ROLLBACK, SAVE, START
10.2.1.2 Session or Statement to Command Mappings for Alert Policies and Reports

Starting in Oracle AVDF 20.11, Alert policies no longer utilize session or statement classes. Instead, users are able to create policies based on specific commands such as INSERT, UPDATE, or DELETE. This table can help you identify which commands are a part of which statement class.

Statement Class Commands for Oracle Commands for SQL Server Commands for MySQL Commands for DB2 LUW Commands for Sybase ASE
DCL ADMINISTER KEY MANAGEMENT, ALTER SESSION, ALTER SYSTEM, CHANGE PASSWORD, COMPRESSED, ENCRYPTED, GRANT OBJECT, GRANT ROLE, INVALID OPERATION, LOGIN, ORADEBUG, REVOKE OBJECT, REVOKE ROLE, SET ROLE, SHUTDOWN ALTER AUTHORIZATION, DBCC, DENY, GRANT, LOGIN, REVOKE, SET, SETUSER, USE DATABASE BINLOG, FLUSH, GRANT, INSTALL, KEYCACHE, KILL, LOAD INDEX, PURGE, RESET, REVOKE, SET ROLE, UNINSTALL, USE GRANT, REVOKE, SET, TRANSFER DBCC ADDTEMPDB, DBCC CHECKALLOC, DBCC CHECKCATALOG, DBCC CHECKDB, DBCC CHECKINDEX, DBCC CHECKSTORAGE, DBCC CHECKTABLE, DBCC CHECKVERIFY, DBCC COMPLETE XACT, DBCC DBREPAIR, DBCC ENGINE, DBCC FIX TEXT, DBCC FORGET XACT, DBCC INDEXALLOC, DBCC MONITOR, DBCC NODETRACEOFF, DBCC NODETRACEON, DBCC PRAVAILABLETEMPDBS, DBCC QUORUM, DBCC REBUILD TEXT, DBCC REINDEX, DBCC SERVERLIMITS, DBCC SET SCOPE, DBCC SHRINKDB SETUP, DBCC STACKUSED, DBCC TABLEALLOC, DBCC TEXTALLOC, DBCC TRACEOFF, DBCC TRACEON, DBCC TUNE, DBCC UPGRADE OBJECT, DBCC ZAPDEFRAGINFO, GRANT, KILL, LOAD DATABASE, LOAD TRANSACTION, LOCK TABLE, MOUNT DATABASE, REVOKE, SET, SETUSER, SYSTEM, TRANSFER TABLE, USE
DDL ALTER ANALYTIC VIEW, ALTER ATTRIBUTE DIMENSION, ALTER AUDIT POLICY, ALTER CLUSTER, ALTER DATABASE, ALTER DATABASE DICTIONARY, ALTER DATABASE LINK, ALTER DIMENSION, ALTER DISKGROUP, ALTER FLASHBACK ARCHIVE, ALTER FUNCTION, ALTER HIERARCHY, ALTER INDEX, ALTER INDEXTYPE, ALTER INMEMORY JOIN GROUP, ALTER JAVA, ALTER LIBRARY, ALTER LOCKDOWN PROFILE, ALTER MATERIALIZED VIEW, ALTER MATERIALIZED VIEW LOG, ALTER MATERIALIZED ZONEMAP, ALTER OPERATOR, ALTER OUTLINE, ALTER PACKAGE, ALTER PLUGGABLE DATABASE, ALTER PROCEDURE, ALTER PROFILE, ALTER RESOURCE COST, ALTER ROLE, ALTER ROLLBACK SEGMENT, ALTER SEQUENCE, ALTER SYNONYM, ALTER TABLE, ALTER TABLESPACE, ALTER TRIGGER, ALTER TYPE, ALTER USER, ALTER VIEW, ANALYZE, ASSOCIATE, AUDIT, AUDIT CONTEXT, AUDIT POLICY, COMMENT, CREATE ANALYTIC VIEW, CREATE ATTRIBUTE DIMENSION, CREATE AUDIT POLICY, CREATE CLUSTER, CREATE CONTEXT, CREATE CONTROLFILE, CREATE DATABASE, CREATE DATABASE LINK, CREATE DIMENSION, CREATE DIRECTORY, CREATE DISKGROUP, CREATE EDITION, CREATE FLASHBACK ARCHIVE, CREATE FUNCTION, CREATE HIERARCHY, CREATE INDEX, CREATE INDEXTYPE, CREATE INMEMORY JOIN GROUP, CREATE JAVA, CREATE LIBRARY, CREATE LOCKDOWN PROFILE, CREATE MATERIALIZED VIEW, CREATE MATERIALIZED VIEW LOG, CREATE MATERIALIZED ZONEMAP, CREATE OPERATOR, CREATE OUTLINE, CREATE PACKAGE, CREATE PACKAGE BODY, CREATE PFILE, CREATE PLUGGABLE DATABASE, CREATE PROCEDURE, CREATE PROFILE, CREATE RESTORE POINT, CREATE ROLE, CREATE ROLLBACK, CREATE SCHEMA, CREATE SEQUENCE, CREATE SPFILE, CREATE SYNONYM, CREATE TABLE, CREATE TABLESPACE, CREATE TRIGGER, CREATE TYPE, CREATE TYPE BODY, CREATE USER, CREATE VIEW, DISASSOCIATE, DROP ANALYTIC VIEW, DROP ATTRIBUTE DIMENSION, DROP AUDIT POLICY, DROP CLUSTER, DROP CONTEXT, DROP DATABASE, DROP DATABASE LINK, DROP DIMENSION, DROP DIRECTORY, DROP DISKGROUP, DROP EDITION, DROP FLASHBACK, DROP FUNCTION, DROP HIERARCHY, DROP INDEX, DROP INDEXTYPE, DROP INMEMORY JOIN GROUP, DROP JAVA, DROP LIBRARY, DROP LOCKDOWN PROFILE, DROP MATERIALIZED VIEW, DROP MATERIALIZED VIEW LOG, DROP MATERIALIZED ZONEMAP, DROP OPERATOR, DROP OUTLINE, DROP PACKAGE, DROP PLUGGABLE DATABASE, DROP PROCEDURE, DROP PROFILE, DROP RESTORE, DROP ROLE, DROP ROLLBACK, DROP SEQUENCE, DROP SYNONYM, DROP TABLE, DROP TABLESPACE, DROP TRIGGER, DROP TYPE, DROP TYPE BODY, DROP USER, DROP VIEW, NOAUDIT, NOAUDIT CONTEXT, NOAUDIT POLICY, RENAME, TRUNCATE CLUSTER, TRUNCATE TABLE ADD SIGNATURE, ALTER APPLICATION, ALTER ASSEMBLY, ALTER ASYMMETRIC KEY, ALTER AVAILABILITY GROUP, ALTER BROKER PRIORITY, ALTER CERTIFICATE, ALTER COLUMN ENCRYPTION KEY, ALTER CREDENTIAL, ALTER CRYPTOGRAPHIC PROVIDER, ALTER DATABASE, ALTER DATABASE AUDIT, ALTER DATABASE KEY, ALTER DATABASE SCOPED CONFIGURATION, ALTER DATABASE SCOPED CREDENTIAL, ALTER ENDPOINT, ALTER EVENT SESSION, ALTER EXTERNAL DATA SOURCE, ALTER EXTERNAL LANGUAGE, ALTER EXTERNAL LIBRARY, ALTER EXTERNAL RESOURCE POOL, ALTER FEDERATION, ALTER FULLTEXT, ALTER FUNCTION, ALTER INDEX, ALTER LOGIN, ALTER MASTER KEY, ALTER MESSAGE TYPE, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME, ALTER PROCEDURE, ALTER QUEUE, ALTER REMOTE, ALTER RESOURCE, ALTER ROLE, ALTER ROUTE, ALTER SCHEMA, ALTER SEARCH PROPERTY LIST, ALTER SECURITY POLICY, ALTER SEQUENCE, ALTER SERVER, ALTER SERVER CONFIGURATION, ALTER SERVER ROLE, ALTER SERVICE, ALTER SERVICE MASTER KEY, ALTER SYMMETRIC, ALTER TABLE, ALTER TRIGGER, ALTER USER, ALTER VIEW, ALTER WORKLOAD GROUP, ALTER XML, CREATE AGGREGATE, CREATE APPLICATION, CREATE ASSEMBLY, CREATE ASYMMETRIC KEY, CREATE AVAILABILITY GROUP, CREATE BROKER PRIORITY, CREATE CERTIFICATE, CREATE COLUMN ENCRYPTION KEY, CREATE COLUMN MASTER KEY, CREATE COLUMNSTORE INDEX, CREATE CONTRACT, CREATE CREDENTIAL, CREATE CRYPTOGRAPHIC PROVIDER, CREATE DATABASE, CREATE DATABASE AUDIT, CREATE DATABASE KEY, CREATE DATABASE SCOPED CREDENTIAL, CREATE DEFAULT, CREATE DIAGNOSTICS SESSION, CREATE ENDPOINT, CREATE EVENT NOTIFICATION, CREATE EVENT SESSION, CREATE EXTERNAL DATA SOURCE, CREATE EXTERNAL FILE FORMAT, CREATE EXTERNAL LANGUAGE, CREATE EXTERNAL LIBRARY, CREATE EXTERNAL RESOURCE POOL, CREATE EXTERNAL TABLE, CREATE FEDERATION, CREATE FULLTEXT, CREATE FUNCTION, CREATE INDEX, CREATE LOGIN, CREATE MASTER KEY, CREATE MESSAGE TYPE, CREATE PARTITION, CREATE PROCEDURE, CREATE QUEUE, CREATE REMOTE, CREATE RESOURCE, CREATE ROLE, CREATE ROUTE, CREATE RULE, CREATE SCHEMA, CREATE SEARCH PROPERTY LIST, CREATE SECURITY POLICY, CREATE SEQUENCE, CREATE SERVER, CREATE SERVER ROLE, CREATE SERVICE, CREATE STATISTICS, CREATE SYMMETRIC KEY, CREATE SYNONYM, CREATE TABLE, CREATE TRIGGER, CREATE TYPE, CREATE USER, CREATE VIEW, CREATE WORKLOAD GROUP, CREATE XML, DISABLE TRIGGER, DROP AGGREGATE, DROP APPLICATION, DROP ASSEMBLY, DROP ASYMMETRIC, DROP AVAILABILITY GROUP, DROP BROKER PRIORITY, DROP CERTIFICATE, DROP COLUMN ENCRYPTION KEY, DROP COLUMN MASTER KEY, DROP CONTRACT, DROP CREDENTIAL, DROP CRYPTOGRAPHIC PROVIDER, DROP DATABASE, DROP DATABASE AUDIT, DROP DATABASE KEY, DROP DATABASE SCOPED CREDENTIAL, DROP DEFAULT, DROP DIAGNOSTICS SESSION, DROP ENDPOINT, DROP EVENT NOTIFICATION, DROP EVENT SESSION, DROP EXTERNAL DATA SOURCE, DROP EXTERNAL FILE FORMAT, DROP EXTERNAL LANGUAGE, DROP EXTERNAL LIBRARY, DROP EXTERNAL TABLE, DROP FEDERATION, DROP FULLTEXT CATALOG, DROP FULLTEXT INDEX, DROP FULLTEXT STOPLIST, DROP FUNCTION, DROP INDEX, DROP LOGIN, DROP MASTER KEY, DROP MESSAGE, DROP PARTITION FUNCTION, DROP PARTITION SCHEME, DROP PROCEDURE, DROP QUEUE, DROP REMOTE, DROP RESOURCE, DROP ROLE, DROP ROUTE, DROP RULE, DROP SCHEMA, DROP SEARCH PROPERTY LIST, DROP SECURITY POLICY, DROP SEQUENCE, DROP SERVER, DROP SERVER ROLE, DROP SERVICE, DROP SIGNATURE, DROP STATISTICS, DROP SYMMETRIC, DROP SYNONYM, DROP TABLE, DROP TRIGGER, DROP TYPE, DROP USER, DROP VIEW, DROP WORKLOAD GROUP, DROP XML, ENABLE TRIGGER, RECONFIGURE, TRUNCATE, USE FEDERATION ALTER DATABASE, ALTER EVENT, ALTER FUNCTION, ALTER INSTANCE, ALTER LOGFILE, ALTER PROCEDURE, ALTER SERVER, ALTER TABLE, ALTER TABLESPACE, ALTER USER, ALTER VIEW, CHECK, CHECKSUM, CREATE DATABASE, CREATE EVENT, CREATE FUNCTION, CREATE INDEX, CREATE LOGFILE, CREATE PROCEDURE, CREATE ROLE, CREATE SERVER, CREATE TABLE, CREATE TABLESPACE, CREATE TRIGGER, CREATE USER, CREATE USER FUNCTION, CREATE VIEW, DROP DATABASE, DROP EVENT, DROP FUNCTION, DROP INDEX, DROP PROCEDURE, DROP ROLE, DROP SERVER, DROP TABLE, DROP TABLESPACE, DROP TRIGGER, DROP USER, DROP VIEW, PARTITION, RENAME TABLE, RENAME TABLES, RENAME USER, REPLACE, TRUNCATE TABLE ALLOCATE, ALTER AUDIT POLICY, ALTER BUFFERPOOL, ALTER DATABASE, ALTER EVENT, ALTER FUNCTION, ALTER HISTOGRAM, ALTER INDEX, ALTER MASK, ALTER METHOD, ALTER MODULE, ALTER NICKNAME, ALTER NODEGROUP, ALTER PACKAGE, ALTER PERMISSION, ALTER PROCEDURE, ALTER SCHEMA, ALTER SECURITY, ALTER SEQUENCE, ALTER SERVER, ALTER SERVICE, ALTER SPECIFIC PROCEDURE, ALTER STOGROUP, ALTER TABLE, ALTER TABLESPACE, ALTER THRESHOLD, ALTER TRIGGER, ALTER TRUSTED CONTEXT, ALTER TYPE, ALTER USAGE LIST, ALTER USER, ALTER VIEW, ALTER WORK, ALTER WORKLOAD, ALTER WRAPPER, ALTER XSROBJECT, COMMENT, CREATE ALIAS, CREATE AUDIT, CREATE BUFFERPOOL, CREATE DATABASE, CREATE DATABASE PARTITION GROUP, CREATE EVENT MONITOR, CREATE FUNCTION, CREATE GLOBAL TEMPORARY TABLE, CREATE HISTOGRAM, CREATE INDEX, CREATE MASK, CREATE METHOD, CREATE MODULE, CREATE NICKNAME, CREATE NODEGROUP, CREATE PERMISSION, CREATE PROCEDURE, CREATE ROLE, CREATE SCHEMA, CREATE SECURITY LABEL, CREATE SECURITY POLICY, CREATE SEQUENCE, CREATE SERVER, CREATE SERVICE, CREATE SPECIFIC METHOD, CREATE STOGROUP, CREATE SYNONYM, CREATE TABLE, CREATE TABLESPACE, CREATE THRESHOLD, CREATE TRANSFORM, CREATE TRIGGER, CREATE TRUSTED CONTEXT, CREATE TYPE, CREATE USAGE LIST, CREATE USER, CREATE VARIABLE, CREATE VIEW, CREATE WORK, CREATE WORKLOAD, CREATE WRAPPER, DROP ALIAS, DROP AUDIT POLICY, DROP BUFFERPOOL, DROP DATABASE PARTITION GROUP, DROP EVENT MONITOR, DROP FUNCTION MAPPING, DROP HISTOGRAM, DROP INDEX, DROP INDEX EXTENSION, DROP MASK, DROP METHOD, DROP MODULE, DROP NICKNAME, DROP NODEGROUP, DROP PACKAGE, DROP PERMISSION, DROP PROCEDURE, DROP ROLE, DROP SCHEMA, DROP SECURITY LABEL, DROP SECURITY POLICY, DROP SEQUENCE, DROP SERVER, DROP SPECIFIC PROCEDURE, DROP STOGROUP, DROP TABLE, DROP TABLESPACE(S), DROP THRESHOLD, DROP TRANSFORM(S), DROP TRIGGER, DROP TRUSTED CONTEXT, DROP TYPE, DROP USAGE LIST, DROP USER, DROP VARIABLE, DROP VIEW, DROP WORK, DROP WORKLOAD, DROP WRAPPER, DROP XSROBJECT, RENAME INDEX, RENAME STOGROUP, RENAME TABLESPACE, TRUNCATE ALTER ALL, ALTER DATABASE, ALTER DEFAULT, ALTER ENCRYPTION KEY, ALTER FUNCTION, ALTER INDEX, ALTER LOGIN, ALTER MATERIALIZED VIEW, ALTER PRECOMPUTED RESULT SET, ALTER PROCEDURE, ALTER ROLE, ALTER RULE, ALTER TABLE, ALTER THREAD POOL, ALTER TYPE, ALTER VIEW, CREATE ARCHIVE DATABASE, CREATE DATABASE, CREATE DEFAULT, CREATE ENCRYPTION KEY, CREATE EXISTING TABLE, CREATE FUNCTION, CREATE INDEX, CREATE LOGIN, CREATE MATERIALIZED VIEW, CREATE PLAN, CREATE PRECOMPUTED RESULT SET, CREATE PROCEDURE, CREATE PROXY TABLE, CREATE ROLE, CREATE RULE, CREATE SCHEMA, CREATE SERVICE, CREATE TABLE, CREATE THREAD POOL, CREATE TRIGGER, CREATE VIEW, DEALLOCATE CURSOR, DEALLOCATE LOCATOR, DROP DATABASE, DROP DEFAULT, DROP ENCRYPTION KEY, DROP FUNC, DROP FUNCTION, DROP INDEX, DROP LOGIN, DROP LOGIN PROFILE, DROP MATERIALIZED VIEW, DROP PRECOMPUTED RESULT SET, DROP PROC, DROP PROCEDURE, DROP ROLE, DROP RULE, DROP SERVICE, DROP TABLE, DROP THREAD POOL, DROP TRIGGER, DROP VIEW, TRUNCATE LOB, TRUNCATE MATERIALIZED VIEW, TRUNCATE PRECOMPUTED RESULT SET, TRUNCATE TABLE
DML DELETE, EXECUTE CURSOR, EXPLAIN PLAN, FLASHBACK DATABASE, FLASHBACK TABLE, INSERT, LOB WRITE, MERGE, PURGE DBA RECYCLEBIN, PURGE INDEX, PURGE RECYCLEBIN, PURGE TABLE, PURGE TABLESPACE, UPDATE BACKUP, DELETE, INSERT, INSERT BULK, MERGE, RESTORE, RESTORE DATABASE, UPDATE, UPDATE STATISTICS, UPDATETEXT, WRITETEXT ANALYZE, DELETE, GET DIAGNOSTICS, INSERT, LOAD DATA, LOAD XML, OPTIMIZE, REPAIR, UPDATE DELETE, EXPLAIN, INSERT, MERGE, REFRESH TABLE, UPDATE DELETE, DUMP CONFIGURATION, DUMP DATABASE, DUMP TRANSACTION, EXECUTE CURSOR, INPUT, INSERT, MERGE, QUIESCE DATABASE, REFRESH PRECOMPUTED RESULT SET, REMOVE JAVA CLASS, REMOVE JAVA JAR, REMOVE JAVA PACKAGE, REORG COMPACT, REORG DEFRAG, REORG FORWARDED ROWS, REORG REBUILD, REORG RECLAIM SPACE, UNMOUNT DATABASE, UPDATE, WRITETEXT
Logon LOGIN ATTEMPTED, LOGIN ATTEMPTED AND SUCCEDED, LOGIN FAILED LOGIN ATTEMPTED, LOGIN ATTEMPTED AND SUCCEDED, LOGIN FAILED LOGIN ATTEMPTED, LOGIN ATTEMPTED AND SUCCEDED, LOGIN FAILED LOGIN ATTEMPTED, LOGIN ATTEMPTED AND SUCCEDED, LOGIN FAILED LOGIN ATTEMPTED, LOGIN ATTEMPTED AND SUCCEDED, LOGIN FAILED
Logoff LOGOUT LOGOUT LOGOUT LOGOUT LOGOUT
Procedural ASSIGNMENT, BEGIN, CALL ODBC, CASE, CLOSE, CONTINUE, DECLARE, EXEC, EXECUTE, EXECUTE IMMEDIATE, EXIT, FETCH, FOR, FORALL, FUNCTION, GOTO, IF, LOCK TABLE, LOOP, NULL, OPEN, OPEN FOR, PIPE, PLSQL BLOCK, PRAGMA AUTONOMOUS TRANSACTION, PROCEDURE, RAISE, RETURN, WHILE BEGIN, BREAK, CALL, CHECKPOINT, CLOSE, CONTINUE, DEALLOCATE, DECLARE, END CONVERSATION, EXEC, EXECUTE, EXECUTE PROCEDURE, FETCH, GET CONVERSATION GROUP, GOTO, IF, KILL, LOAD, MOVE CONVERSATION, NULL, OPEN, PRINT, RAISEERROR, RECEIVE, RETURN, REVERT, RPC, SEND, SHUTDOWN, SLOWDOWN, THROW, WAIT FOR, WHILE BEGIN, CALL, CASE, CHANGE, CLOSE, DEALLOCATE, DECLARE CONDITION, DECLARE CURSOR, DECLARE HANDLER, DECLARE VAR, DO, EXECUTE, FETCH, IF, ITERATE, LEAVE, LOOP, PREPARE, REPEAT, RESIGNAL, RETURN, SET VARIABLE, SIGNAL, START SLAVE, STOP SLAVE, WHILE ASSIGNMENT, ASSOCIATE, AUDIT, BEGIN, CALL, CASE, CLOSE, CONNECT, DECLARE CURSOR, DECLARE GLOBAL TEMPORARY TABLE, DISCONNECT, EXECUTE, FETCH, FLUSH, FOR, FREE, GET, GOTO, IF, ITERATE, LEAVE, LOCK, LOOP, NULL, OPEN, PREPARE, RELEASE, REPEAT, RESIGNAL, RETURN, SIGNAL, WHILE BEGIN, BREAK, CALL PROCEDURE, CALL SYSTEM PROCEDURE, CHECKPOINT, CLEAR, CLOSE, CONFIGURE, CONNECT, CONTINUE, DECLARE, DECLARE CURSOR, DISCONNECT, DISK, EXECUTE PROCEDURE, EXIT, FETCH, GO, GOTO, IF, ONLINE DATABASE, OPEN, OUTPUT, PARAMETERS, PREPARE DATABASE, PREPARE TRANSACTION, PRINT, QUIT, RAISERROR, RECONFIGURE, RETURN, RPC, SHUTDOWN, START LOGGING, STOP LOGGING, WAITFOR, WHILE
Select DESCRIBE, LOB READ, SELECT READTEXT, SELECT DESCRIBE, EXPLAIN, HANDLER CLOSE, HANDLER OPEN, HANDLER READ, HELP, SELECT, SHOW DESCRIBE, SELECT, VALUES READ, READTEXT, SELECT
Transaction COMMIT, ROLLBACK, SAVEPOINT, SET CONSTRAINT, SET TRANSACTION, TRANSACTION BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, ROLLBACK WORK, SAVE TRANSACTION, SET TRANSACTION BEGIN WORK, COMMIT, LOCK, RELEASE SAVEPOINT, ROLLBACK, SAVEPOINT, START TRANSACTION, UNLOCK, XA COMMIT, XA END, XA PREPARE, XA RECOVER, XA ROLLBACK, XA START COMMIT, ROLLBACK, SAVEPOINT BEGIN TRANSACTION, COMMIT, ROLLBACK, SAVE TRANSACTION

10.2.2 Writing Alert Conditions

Learn how to define alert conditions.

10.2.2.1 About Alert Conditions

Learn about alert conditions.

The Alert Condition is the where clause of a select statement. In the Condition field of the Create Alert page, you can construct a Boolean condition that evaluates audit events. When the Boolean condition evaluates to TRUE, then Oracle Audit Vault and Database Firewall raises the alert, and notifies any specified users. As a general guideline, try to keep your alert conditions simple. Overly complex conditions can slow the Audit Vault Server database performance.

10.2.2.2 Writing an Alert Condition

Learn how to write an alert condition.

Syntax of Alert Conditions

The syntax for an alert condition is:

:condition_field operator expression

For example:

:event_status='FAILURE' and upper(:event_name)=upper('LOGON')

An alert condition is a WHERE clause in a SELECT statement, with an added colon (:) before the fields. For example, the above condition looks like the WHERE clause in this SELECT statement: 

SELECT user_name, event_status, event_name from avsys.event_log
   WHERE event_status='FAILURE' and upper(event_name)=upper('LOGON');

The WHERE clause above captures events in the avsys.event_log table where the event was LOGON and the event status was FAILURE. Converting this WHERE clause to an alert condition will cause that alert to be triggered whenever there are failed logons. You can specify in the alert how many failed logons within a specified period of time trigger the alert.

Rules for Writing Alert Conditions

Table 10-1 lists the rules for writing alert conditions and gives some examples.

Table 10-1 Rules for Writing Alert Conditions

Use the available audit record fields The Create Alert page has a list of fields you can copy and use to build the alert condition. See .

Use any legal SQL function

You can use any legal SQL function, including user-defined functions. However, you cannot use sub-query statements. For example, you can use:

  • upper()

  • lower()

  • to_char()

Use any legal SQL operator

For example, you can use:

  • not

  • like

  • <

  • >

  • in

  • and

  • null

When using operators, follow these guidelines:

  • Remember that Oracle Audit Vault and Database Firewall evaluates an alert condition for each incoming audit record.

  • You cannot use nested queries (for example, not in SELECT...) in the condition.

Use wildcards

You can use the following wildcards:

  • % (to match zero or more characters)

  • _ (to match exactly one character)

Group components of a condition

You can group components within the condition by using parentheses. For example: 

(((A > B) and (B > C)) or C > D)

Example 1

You want to be alerted whenever there are three failed logon attempts on Oracle Database targets within a five-minute period.

To write a condition for this alert, you can copy EVENT_STATUS and EVENT_NAME from the available fields list, and use them to write this condition:

upper(:EVENT_STATUS)='FAILURE' and upper(:EVENT_NAME)='LOGON'

Tip: Set the threshold to 3 (3 times) and duration to 5 (less than 5 minutes) with this condition. You can look up audit event names and attributes in Oracle Database Audit Events.

Example 2

You want to monitor application shared schema accounts that are being used outside the database. An example of this scenario is when the database user is APPS and the client identifier is set to NULL.

To write a condition for this alert, you can copy the EVENT_NAME and USER_NAME fields from the available fields list, and use them to write this condition:

:EVENT_NAME='LOGON' and :USER_NAME='apps' and :CLIENT_IP=NULL

This condition says, "Raise an alert if any ex-employee tries to log in to the database."

Tip: You can look up audit event names and attributes in Oracle Database Audit Events.

Alert for Example 1 (mentioned above) in the Audit Vault Server Console

This alert says: "Alert me whenever there are three failed logon attempts on Oracle Database targets within a five-minute period."

The alert Condition uses two of the Condition - Available Fields on the right side of the Create Alert page.

If this alert is raised, its Severity will be set to Warning. An email will also be sent to the user avdf_auditor@samplecompany.com, using the Alert Notification Template.

In reports, instances of this alert will be grouped by client application ID.

Available Audit Record Fields for use in Alert Conditions

Table 10-2 describes the available audit record fields you can use in alert conditions.

Important: These fields must be preceded by a colon (:) when used in the condition (for example :USER_NAME).

Table 10-2 Available Fields for Alert Conditions

Condition Field Description

ACTION_TAKEN

(Firewall Alerts) Action taken by the Database Firewall, for example: BLOCK, WARN, or PASS

COLLECTION_TIME

The time Oracle Audit Vault and Database Firewall raised the alert

CLIENT_HOST

The host name of the client application that was the source of the event causing the alert

CLIENT_ID

The ID of the client application that was the source of the event causing the alert

CLIENT_IP

The IP address of the client application that was the source of the event causing the alert

CLUSTER_TYPE

Note:

Can only be used prior to AVDF 20.11

(Firewall Alerts) The cluster type of the SQL statement causing the alert. Values may be:

Data Manipulation
Data Definition
Data Control
Procedural
Transaction
Composite
Composite with Transaction

COMMAND_CLASS

Starting in Oracle AVDF 20.11, the COMMAND_CLASS attribute can't use DML, DDL, or DCL. Instead, you must use specific commands such as INSERT, UPDATE, or DELETE. You will need to modify your existing alert policies to accommodate these changes. See Statement Class to Command Mappings for Database Firewall Policies for information on what commands to use.

The Oracle Audit Vault and Database Firewall command class.

Tip: You can look up audit event names and attributes in Oracle Database Audit Events.

ERROR_CODE

The target's error code

ERROR_MESSAGE

The target's error message

EVENT

The target's audit event name.

Tip: You can look up audit event names and attributes in Oracle Database Audit Events.

EVENT_STATUS

Status of the event: Success or Failure

EVENT_TIME

The time that the event occurred

LOCATION

Describes where the audit trail is located. Valid values are:

Audit File
Audit Table
Transaction Log
Event Log
Syslog
Network
Custom

NETWORK_CONNECTION

Description of the connection between the target database and the database client, in the following format:

client_ip:client_port,database_ip:database_port

For example:

198.51.100.1:5760,203.0.113.1:1521

POLICY_NAME

The name of the Database Firewall policy or audit policy that generated this event.

For Oracle AVDF 20.3 and later: In case of audit data collected by the Agent, the policy name contains the audit policies that caused the current event.

REPOSITORY_NAME

The name of the Container Database

ROW_COUNT

The number of rows returned by a SELECT DML query.

Note: To fetch the row count, create a Database Object rule in a Database Firewall policy on the target. See Database Object Rule for more information.

OSUSER

Name of the target's OS user

TARGET_CLASS

Targets fall into these classes:

Database
OS
Directory Service
Filesystem

TARGET

Name of the target in Oracle Audit Vault and Database Firewall.

OBJECT

Name of the object on the target, for example, a table name, file name, or a directory name. Must be in upper case, for example, ALERT_TABLE.

OBJECT_OWNER

Owner of the object on the target

OBJECT_TYPE

The object type on the target, for example, TABLE, or DIRECTORY

TERMINAL

The Unix terminal that was the source of the event causing the alert (for example, /dev/1)

THREAT_SEVERITY

(Firewall Alerts) The threat severity of the SQL statement triggering the alert, as defined in a Database Firewall policy. Values may be: Minimal, Minor, Moderate, Major, or Critical.

USER

User name of the target user

AUDIT_TYPE

Oracle AVDF 20.3 and later

Audit types for Oracle Database target:

  • Standard
  • FineGrainedAudit
  • XS
  • Database Vault
  • Label Security
  • RMAN_AUDIT
  • Datapump
  • Direct path API

APPLICATION_CONTEXT

Oracle AVDF 20.3 and later

Application context information.

DATABASE_NAME

Oracle AVDF 20.4 and later

The name of the DB2 database that contains the audit records.

INSTANCE_NAME

Oracle AVDF 20.4 and later

The name of the instance which hosts the DB2 database.

RULE_NAME

Oracle AVDF 20.5 and later

The name of the rule defined by the user in Database Firewall policy.

See Also:

10.2.3 Disabling, Enabling, or Deleting Alerts

Learn how to enable, disable, or delete alerts.

You can disable an alert while keeping the alert definition in case you wish to enable this alert again in the future.

To disable or enable alerts:

  1. Log into the Audit Vault Server console as an auditor.
  2. Click on Policies tab.
  3. From the left navigation menu, click Alert Policies. The alerts list is displayed on the main page.
  4. Select the check box(es) to the left of the Alert Name column for the specific alerts. Click the Disable, Enable, or Delete button to perform that action on all selected alerts.

10.3 Monitoring Alerts

Oracle AVDF raises an alert when data matches an alert rule condition in a single audit record, or matches multiple events with its duration and threshold setting.

Auditors can view recently raised alerts in the dashboard on the Audit Vault Server console's Home page. Alerts are grouped by the time that the alerts are raised, and by the severity level of the alert (warning or critical). Clicking on the circle marker available on the line chart will redirect you to Alert Reports under the Alerts tab.

You can also schedule alert reports from the Audit Vault Server Reports tab.

See Also:

10.4 Responding to an Alert

After you have created alerts and when they are generated, you or other auditors can respond to them.

You can change the alert status (for example, closing it), or notify other users of the alert.
  1. Log in to the Audit Vault Server console as an auditor.
  2. Click on Alerts tab.
    A table of alerts can be seen. The table contains information regarding:
    • Alert ID
    • Alert Status
    • Alert Policy
    • Target
    • User
    • Event
    • Object
    • Alert Severity
    • Event Time
  3. You can filter the list of visible alerts by clicking the Action drop-down at the top of the table and select Filter.
    1. Select the column to filter by from the Column drop-down.
    2. Select the an operator from the Operator drop-down.
    3. Enter in an appropriate value in the Expression field if applicable.
  4. Select the check box in the left column to select a specific alert and perform any of the following actions:
    1. Click the Notify button, to notify another auditor of the alert. In the Manual Alert Notification page, select the template type for the notification. Select a distribution list and/or enter email addresses in the To or Cc fields. Separate multiple email addresses with a comma. Click the Add to List button to compile the listing, and then click the Notify button to send the notification.
    2. From the Set Alert Status list, select , Open or Closedto set the alert status, and then click the Apply button. When an alert is first generated, it is set to New.
    3. Click the Alert ID of an alert to get additional details of the alert on the report.

10.5 Creating Custom Alert Status Values

You can create alert status values to assign to an alert during the lifetime of the alert.

Oracle Audit Vault and Database Firewall provides two status values: New and Closed prior to Oracle AVDF 20.8 and Open and Closed starting in Oracle AVDF 20.8. You can create additional ones to suit your needs, such as Pending.
  1. Log in to the Audit Vault Server console as an auditor.
  2. Click on Alerts tab.
  3. From the left navigation menu, click on Manage Alert Status.

    In this page there are two tabs: Custom Alert Status and Pre-configured Alert Status. From here you can edit or delete existing alert status values.

  4. To create a new alert status, click Create.
  5. In the Create Alert Status Value dialog, enter the following settings:

    • Status Value: Enter a name for the status value (for example, Pending).

    • Description: Optionally, enter a description for the status value.

  6. Click Save.

    The new alert status appears in the Manage Alert Status page.

    See Also:

10.6 Forwarding Alerts to Syslog

In addition to seeing alerts in reports, and receiving them in alert notifications, you can forward all alert messages to syslog.

As a prerequisite to forwarding alerts to syslog, the Oracle Audit Vault and Database Firewall administrator must configure syslog destinations in the Audit Vault Server, and select Alert as a syslog category. See the Oracle Audit Vault and Database Firewall Administrator's Guide for instructions.
  1. Log in to the Audit Vault Server console as a super auditor.
  2. Click on Policies tab.
  3. Click on Alert Policies tab in the left navigation menu.
  4. Click Forward Alerts to Syslog button. The button only appears if the Syslog connector is set up by the Oracle AVDF administrator.

    All defined alerts are forwarded to Syslog.

Example 10-1 Oracle Audit Vault and Database Firewall Syslog Alert Message Format

Oracle Audit Vault and Database Firewall alerts appear in syslog in a format similar to the following:

[AVDFAlert@111 AN="alert_name" ASE="alert_severity" URL="auditor_console_URL_for_alert" AT="alert_generated_time" TN="secured_target" UN="username" AD="alert_description"]

The UN and TN parameters may list zero or more users or targets related to this alert.

Example:

Apr 16 23:22:31 avs08002707d652 logger: [AVDFAlert@111 AN="w_1" ASE="Warning" URL=https://192.0.2.10/console/f?p=7700... AT="2014-04-16T22:55:30.462332Z" TN="cpc_itself" UN="JDOE" AD=" "]

See Also:

Logging in to the Audit Vault Server Console