B Oracle Database Firewall Database Schema

This appendix contains :

About the Oracle Database Firewall Schema
Summary Tables
Log Forensic Tables
Stored Procedure and User Role Audit Tables

About the Oracle Database Firewall Schema

The Oracle Database Firewall tables are stored in the SECURELOG schema. This schema contains a set of logically related tables, which are described in this appendix.

Summary Tables

This section contains:

About the Summary Tables
applied_baselines Table
database_user_addresses Table
database_users Table
dictionary Table
protected_database_addresses Table
protected_databases Table
sources Table
summary_clusters Table
summary_records Table
summary_sessions Table
summary_statement_attributes Table
traffic_events Table
traffic_summaries View
Relationship Diagram of the Summary Tables

About the Summary Tables

The summary tables store general information about the data that is being monitored, such as the names of the users logging in, the monitored databases, user sessions, database traffic, events, and so on.

applied_baselines Table

Table B-1 provides the name of the policy that is currently used for while traffic is being captured.

Table B-1 applied_baselines Table

Column	Datatype	NULL	Description
`baseline_id`	`INTEGER`	`NOT NULL`	Unique ID of this record
`name`	`VARCHAR2(1024)`	`NOT NULL`	Name of the baseline (also available from `traffic_summaries` view (Table B-13))
`database_id`	`INTEGER`	`NOT NULL`	ID of the protected database in the `protected_databases` table (Table B-6)

database_user_addresses Table

Table B-2 provides IP addresses that are expected to be used by a user who has accessed the protected database.

Table B-2 database_user_addresses Table

Column	Datatype	NULL	Description
`address_id`	`INTEGER`	`NOT NULL`	ID of the address record in this table
`user_id`	`INTEGER`	`NULL`	ID of the database user in the `database_users` table (Table B-3)
`address`	`VARCHAR2(30)`	`NULL`	Expected IP address of the user (possible one of many)

database_users Table

Table B-3 records each user who has accessed a protected database.

Table B-3 database_users Table

Column	Datatype	NULL	Description
`user_id`	`INTEGER`	`NOT NULL`	ID of the user record in this table
`user_name`	`VARCHAR2(255)`	`NULL`	Name of the user who started the session (also available from `traffic_summaries` view Table B-13)
`database_id`	`INTEGER`	`NULL`	ID of the protected database in the `protected_databases` table (Table B-6)
`terminated_at`	`TIMESTAMP`	`NULL`	Time that the session was ended
`is_admin`	`CHAR (ONLY:'0','1')`	`NOT NULL`	Set to 1 if the user is an administrator of the database
`is_predefined`	`CHAR (ONLY:'0','1')`	`NOT NULL`	Set to 1 if the user is a predefined user of the database (that is, automatically created when the `protected_databases` table (Table B-6) was created)

dictionary Table

Table B-4 provides a set of mappings from coded values to meaningful text. This table is useful in generating understandable text in a report, without hard-coding the values into the report query. The tables throughout this appendix use values listed in this table. For example, several tables have a column entitled cluster_type. The values possible for the cluster_type column are listed in the dictionary table.

Table B-4 dictionary Table

Column Datatype NULL Description

Column	Datatype	NULL	Description
`category`	`VARCHAR2(30)`	`NOT NULL`	Name of item. Possible values: `dialect` (database product) `event_action` `log_action` `logging_code` `threat_severity` `cluster_type` `doa_object_type` `doa_object_subtype` `doa_object_class` `doa_edit_type` `database_event_type` `record_type` `response_status` `log_cause` `event_action_code` `session_data_origin`
`value`	`INTEGER`	`NOT NULL`	Value of the item listed in the `category` column. For example, if the category is `dialect` and the database product listed is Oracle, then the value listed here is 2.
`name`	`VARCHAR2(100)`	`NULL`	Description of value listed in the `value` column. For example, if the `category` is `dialect` and the value is `2`, then the name listed here is `Oracle`.

category

VARCHAR2(30)

NOT NULL

Name of item. Possible values:

dialect (database product)
event_action
log_action
logging_code
threat_severity
cluster_type
doa_object_type
doa_object_subtype
doa_object_class
doa_edit_type
database_event_type
record_type
response_status
log_cause
event_action_code
session_data_origin

value

INTEGER

NOT NULL

Value of the item listed in the category column. For example, if the category is dialect and the database product listed is Oracle, then the value listed here is 2.

name

VARCHAR2(100)

NULL

Description of value listed in the value column. For example, if the category is dialect and the value is 2, then the name listed here is Oracle.

protected_database_addresses Table

Table B-5 provides address and port details for each protected database.

Table B-5 protected_database_addresses Table

Column	Datatype	NULL	Description
`address_id`	`INTEGER`	`NOT NULL`	Unique ID of this record
`address`	`VARCHAR2(255)`	`NOT NULL`	IP address of the protected database
`port_number`	`INTEGER`	`NOT NULL`	Port number used to access the protected database
`database_id`	`INTEGER`	`NULL`	ID of the protected database in the `protected_databases` table (Table B-6)

protected_databases Table

Table B-6 provides details about each protected database.

Table B-6 protected_databases Table

Column	Datatype	NULL	Description
`database_id`	`INTEGER`	`NOT NULL`	Unique ID of the database
`name`	`VARCHAR2(250)`	`NOT NULL`	Name of the database
`dialect`	`SMALLINT`	`NOT NULL`	SQL dialect: `1` - Microsoft SQL Server `2` - Oracle `5` - Sybase `7` - IBM DB2 (Linux, UNIX, Windows)
`description`	`VARCHAR2(250)`	`NULL`	Description of the database
`is_sox_database`	`CHAR (ONLY:'0','1')`	`NOT NULL`	Set to 1 if SOX reports can be produced from the database (also available from `traffic_summaries` view (Table B-13))
`is_pci_database`	`CHAR (ONLY:'0','1')`	`NOT NULL`	Set to 1 if PCI reports can be produced from the database (also available from `traffic_summaries` view (Table B-13))
`is_dpa_database`	`CHAR (ONLY:'0','1')`	`NOT NULL`	Set to 1 if DPA reports can be produced from the database (also available from `traffic_summaries` view (Table B-13))
`is_glba_database`	`CHAR (ONLY:'0','1')`	`NOT NULL`	Set to 1 if GLBA reports can be produced from the database (also available from `traffic_summaries` view (Table B-13))
`is_hipaa_database`	`CHAR (ONLY:'0','1')`	`NOT NULL`	Set to 1 if HIPAA reports can be produced from the database (also available from `traffic_summaries` view (Table B-13))

sources Table

Table B-7 provides details about the source of an event: either the relevant hardware (or virtual) device for events that apply system-wide, or the monitoring enforcement point for events detected by monitoring.

Table B-7 sources Table

Column	Datatype	NULL	Description
`id`	`INTEGER`	`NOT NULL`	Unique ID of the source of the event, which can be an appliance (physical server), in the case of syslog messages, or the enforcement point (in the case of database traffic related events, or enforcement point related syslog messages).
`time`	`TIMESTAMP`	`NOT NULL`	Time that the source of the event was added to the database
`name`	`VARCHAR2(30)`	`NOT NULL`	Short name of the source of the event
`is_hardware`	`CHAR (ONLY:'0','1')`	`NULL`	Set to 1 if the source of the event is a syslog source other than an enforcement point

summary_clusters Table

Table B-8 provides information about individual clusters for the purpose of reporting. As well as providing a unique identity to each cluster, this table provides an example statement that would appear in the cluster.

Table B-8 summary_clusters Table

Column	Datatype	NULL	Description
`cluster_id`	`INTEGER`	`NOT NULL`	Source ID of the binary data
`grammar_version`	`INTEGER`	`NOT NULL`	Grammar-specific version number
`dialect`	`SMALLINT`	`NOT NULL`	SQL dialect: `1 - Microsoft SQL Server` `2 - Oracle`
`cluster_type`	`SMALLINT`	`NOT NULL`	Type of statements included in the cluster: 0 - Composite 1 - Data manipulation 2 - Data definition 3 - Data control `4` - Procedural statement `5` - Data manipulation `6` - Transaction `7` - Transaction composite `8` - Invalid See also `traffic_summaries` view (Table B-13)
`representation`	`CLOB`	`NOT NULL`	String representation of cluster (path). Not used
`cluster_example`	`CLOB`	`NULL`	An example of a statement in the cluster (also available from `traffic_summaries` view (Table B-13)

summary_records Table

Table B-9 provides primary information collected in the traffic log, as an hourly summary of the count of occurrences of each cluster.

Table B-9 summary_records Table

Column	Datatype	NULL	Description
`summary_id`	`INTEGER`	`NOT NULL`	Unique ID of the summary record
`session_id`	`INTEGER`	`NOT NULL`	ID of the session in the `summary_sessions` table
`attribute_set_id`	`INTEGER`	`NOT NULL`	Set with session attributes
`cluster_id`	`INTEGER`	`NOT NULL`	ID of the cluster in the `summary_clusters` table (Table B-9); also available from `traffic_summaries` view (Table B-13)
`attribute_id`	`INTEGER`	`NULL`	ID of the attribute in the `summary_statement_attributes` table (Table B-11).
`grammar_version`	`INTEGER`	`NOT NULL`	Grammar-specific version number
`response_status`	`INTEGER`	`NOT NULL`	Response code of the statement for database response monitoring: 0 - Undefined 1 - Unknown 2 - Login success 3 - Login fail `4` - Statement success `5` - Statement fail See also `traffic_summaries` view (Table B-13)
`time`	`TIMESTAMP`	`NOT NULL`	Time that the SQL statement was captured by the Database Firewall (also available from `traffic_summaries` view (Table B-13))
`threat_severity`	`INTEGER`	`NOT NULL`	Threat severity of the statements: `0` - Unassigned `1` - Insignificant `2` - Minor `3` - Moderate `4` - Major `5` - Catastrophic See also `traffic_summaries` view (Table B-13)
`logging_code`	`SMALLINT`	`NOT NULL`	Logging level of the statements: `0` - Unassigned `1` - Never `2` - Sample `3` - Always `4` - Once `5` - Unique See also `traffic_summaries` view (Table B-13)
`action_code`	`SMALLINT`	`NOT NULL`	Action level of the statements: `1` - Known blocked `2` - Known warned `3` - Unseen blocked `4` - Unseen warned See also `traffic_summaries` view (Table B-13)
`statement_count`	`INTEGER`	`NULL`	Number of statements that have the same characteristics, as listed above (also available from `traffic_summaries` view (Table B-13))

summary_sessions Table

A new session is started when a client application successfully logs into a database. This session lasts for as long as the connection exists (often, until the application logs out from the database, although the session may terminate in a less controlled way). Sessions are associated with a client application, and with the authentication or identification information (primarily the user name) used to establish the connection. A simple client application creates a single session, or in some cases, one session after another. More complex applications may have many simultaneously active sessions.

Note:

This table does not record each session. Instead, a new record is added when one or more of the fields (source_id, client, user_id, baseline_id, and dialect_version) is different from an existing record.

Table B-10 provides details about each database session, typically identified by the source address of the session.

Table B-10 summary_sessions Table

Column	Datatype	NULL	Description
`session_id`	`INTEGER`	`NOT NULL`	Unique ID of the database session
`source_id`	`INTEGER`	`NULL`	ID of the sources in the `sources` table (also available from `traffic_summaries` view Table B-13)
`client`	`VARCHAR2(30)`	`NULL`	IP address of the database client (also available from `traffic_summaries` view Table B-13)
`user_id`	`INTEGER`	`NULL`	ID of the user who started the session (Table B-3)
`baseline_id`	`INTEGER`	`NULL`	ID of the policy in the `applied_baselines` table (Table B-1)
`dialect_version`	`VARCHAR2(20)`	`NULL`	Not used
`application_name`	`VARCHAR2(255)`	`NULL`	Name of the client program used in this session
`os_user_name`	`VARCHAR2(255)`	`NULL`	Operating system user name

summary_statement_attributes Table

Table B-11 stores statement attribute values that can be summarized from the summary_records table.

Table B-11 summary_statement_attributes Table

Column	Datatype	NULL	Description
`id`	`INTEGER`	`NULL`	ID of each record in this table
`hash`	`INTEGER`	`NULL`	Result of the CRC32 (Cyclic Redundancy Check) of the concatenation of all attribute values. This is for internal use only to enable fast searching.
`f5_response_code`	`VARCHAR2(4000)`	`NULL`	HTTP response code Note: This and the remaining fields in this table are relevant to the F5 BIG-IP ASM integration only. See Chapter 11, "Using Oracle Database Firewall with BIG-IP ASM," for more information.
`f5_method`	`VARCHAR2(4000)`	`NULL`	HTTP request method
`f5_protocol`	`VARCHAR2(4000)`	`NULL`	Request protocol
`f5_uri`	`VARCHAR2(4000)`	`NULL`	Requested resource
`f5_ip`	`VARCHAR2(15)`	`NULL`	Web client IP address
`f5_web_application_name`	`VARCHAR2(64)`	`NULL`	Web application name
`f5_unit_hostname`	`VARCHAR2(64)`	`NULL`	Name of the WAF box
`f5_management_ip_address`	`VARCHAR2(15)`	`NULL`	IP address of the WAF management interface
`f5_policy_name`	`VARCHAR2(128)`	`NULL`	WAF policy name
`f5_x_forwarded_for_header_value`	`VARCHAR2(4000)`	`NULL`	List of IP addresses provided by `X-FORWARDED-FOR` field in HTTP request
`f5_request_blocked`	`CHAR (ONLY:'0','1')`	`NULL`	Set to 1 if the http request was blocked
`f5_web_username`	`VARCHAR2(128)`	`NULL`	Name of the Web user
`f5_referer`	`VARCHAR2(4000)`	`NULL`	HTTP referrer
`f5_web_host`	`VARCHAR2(256)`	`NULL`	Web application server name
`f5_user_agent`	`VARCHAR2(1024)`	`NULL`	IHTTP user agent
`f5_cardinal_ip`	`VARCHAR2(15)`	`NULL`	IP address of the client that initiated the HTTP request. It is either the client IP address of the HTTP connection over which the request was issued, or if the HTTP header record "X-FORWARDED-FOR" is present, then it's value is used.
`f5_primary_violation`	`VARCHAR2(64)`	`NULL`	Violation from `attr_f5_violations` that have the highest priority
`f5_match_result`	`VARCHAR2(4000)`	`NULL`	One of the following: `1 - PolicyConflict` `2 - PolicyConfirmed` `3 - WAFBlocked` `4 - NoMatchDataMasked` `5 - NoMatch`

traffic_events Table

Table B-12 provides details about events that have been transmitted, or potentially transmitted, over syslog by the enforcement point. These events ares primarily alerts from monitoring.

Table B-12 traffic_events Table

Column	Datatype	NULL	Description
`event_id`	`INTEGER`	`NOT NULL`	Unique ID of the traffic event
`source_id`	`INTEGER`	`NOT NULL`	ID of the source event in the `sources` table "sources Table")
`time`	`TIMESTAMP`	`NOT NULL`	Time of the event
`cluster_id`	`INTEGER`	`NULL`	ID of the cluster
`action`	`INTEGER`	`NULL`	Action level of the cluster that the statement belongs to: `0` - Unassigned `1` - Block `2` - Warn `3` - Pass
`threat_severity`	`INTEGER`	`NULL`	Threat severity of the cluster: `0` - Unassigned `1` - Insignificant `2` - Minor `3` - Moderate `4` - Major `5` - Catastrophic
`log_level`	`INTEGER`	`NULL`	Logging level of the cluster: `0` - Unassigned `1` - Never `2` - Sample `3` - Always `4` - Once `5` - Unique
`db_client`	`VARCHAR2(30)`	`NULL`	IP address of the database client that sent the statement
`db_server`	`VARCHAR2(30)`	`NULL`	IP address of the database server
`db_user`	`VARCHAR2(255)`	`NULL`	Name of the database user who sent the statement
`web_user`	`VARCHAR2(255)`	`NULL`	Name of the Web user (available only in WAF alerts)
`web_application`	`VARCHAR2(255)`	`NULL`	Web application name (available only in WAF alerts)
`http_protocol`	`VARCHAR2(255)`	`NULL`	Request protocol (available only in WAF alerts): `http` `https`
`http_method`	`VARCHAR2(255)`	`NULL`	HTTP request method (available only in WAF alerts). For example, `POST`, `GET`, and so on
`http_uri`	`VARCHAR2(255)`	`NULL`	Requested resource (available only in WAF alerts). For example: `faq.asp`
`http_query_string`	`VARCHAR2(2048)`	`NULL`	Request. for example, `GET parameters: action=save`
`http_referrer`	`VARCHAR2(255)`	`NULL`	HTTP referrer (available only in WAF alerts). For example: `http:`//192.0.2.220
`http_host`	`VARCHAR2(255)`	`NULL`	Web application server name (available only in WAF alerts). Can be an IP address (for example, 192.0.2.220)
`http_user_agent`	`VARCHAR2(255)`	`NULL`	HTTP user agent (available only in WAF alerts). For example, `Mozilla/4.0 (compatible; MSIE 7.0;` `Windows NT 5.1`
`http_response_code`	`VARCHAR2(30)`	`NULL`	HTTP response code (available only in WAF alerts)
`http_request`	`VARCHAR2(2048)`	`NULL`	Full HTTP request. Includes `POST` data (available only in WAF alerts)
`waf_host_name`	`VARCHAR2(255)`	`NULL`	Name of the WAF appliance (available only in WAF alerts)
`waf_management_ip`	`VARCHAR2(30)`	`NULL`	IP address of the WAF management interface (available only in WAF alerts)
`waf_policy_name`	`VARCHAR2(255)`	`NULL`	WAF policy name (available only in WAF alerts)
`waf_policy_date`	`TIMESTAMP`	`NULL`	The time when the WAF policy was applied (available only in WAF alerts)
`waf_support_id`	`VARCHAR2(255)`	`NULL`	Unique ID of the event in the WAF system (available only in WAF alerts)
`waf_request_blocked`	`VARCHAR2(30)`	`NULL`	`TRUE` if the http request was blocked (available only in WAF alerts)
`waf_session_cookies`	`VARCHAR2(512)`	`NULL`	Contains all the session cookies sent with the HTTP request (available only in WAF alerts)
`primary_violation`	`VARCHAR2(255)`	`NULL`	Violation with the highest priority (available only in WAF alerts)
`cardinal_ip`	`VARCHAR2(30)`	`NULL`	IP address of attacker
`match_result`	`INTEGER`	`NULL`	One of the following (which are available only in WAF alerts): `1` - Confirmed `2` - Not confirmed `3` - Not proven
`statement_id`	`INTEGER`	`NULL`	Unique ID of the statement (sequential number)
`statement`	`VARCHAR2(2048)`	`NULL`	SQL statement string (may be truncated)

traffic_summaries View

Table B-13 provides a view to data in other tables.

Table B-13 traffic_summaries View

Column	Datatype	NULL	Description
`db_name`	`VARCHAR2(250)`	`NOT NULL`	Name of the protected database: `Microsoft SQL Server` `Oracle` `Sybase ASE` `Sybase SQL Anywhere` `DB2/LUW` (Linux, UNIX, Windows)
`dialect`	`SMALLINT`	`NOT NULL`	SQL dialect used by the database: `1` - Microsoft SQL Server `2` - Oracle `5` - Sybase ASE `6` - Sybase SQL Anywhere `8` - DB2/LUW (Linux, UNIX, Windows)
`is_sox_database`	`CHAR (ONLY:'0','1')`	`NOT NULL`	Set to 1 if SOX reports can be produced from the database (from `protected_databases` (Table B-6))
`is_pci_database`	`CHAR (ONLY:'0','1')`	`NOT NULL`	Set to 1 if PCI reports can be produced from the database (from `protected_databases` (Table B-6))
`is_dpa_database`	`CHAR (ONLY:'0','1')`	`NOT NULL`	Set to 1 if DPA reports can be produced from the database (from `protected_databases` (Table B-6))
`is_glba_database`	`CHAR (ONLY:'0','1')`	`NOT NULL`	Set to 1 if GLBA reports can be produced from the database (from `protected_databases` (Table B-6))
`is_hipaa_database`	`CHAR (ONLY:'0','1')`	`NOT NULL`	Set to 1 if HIPAA reports can be produced from the database (from `protected_databases` (Table B-6))
`baseline_name`	`VARCHAR2(1024)`	`NOT NULL`	Name of the baseline
`grammar_version`	`INTEGER`	`NOT NULL`	Grammar version
`client`	`VARCHAR2(30)`	`NULL`	IP address of the database client
`user_name`	`VARCHAR2(255)`	`NULL`	Name of the database user
`dialect_version`	`VARCHAR2(20)`	`NULL`	Not used
`source_id`	`INTEGER`	`NULL`	ID of the source in the `sources` table (Table B-7)
`application_name`	`VARCHAR2(255)`	`NULL`	The application name used as a client software in this session
`os_user_name`	`VARCHAR2(255)`	`NULL`	Operating system user name
`cluster_example`	`CLOB`	`NULL`	An example statement in the cluster
`time`	`TIMESTAMP`	`NOT NULL`	Time of the statement count
`cluster_id`	`INTEGER`	`NOT NULL`	ID of the cluster (from `summary_records` table (Table B-9))
`threat_severity`	`INTEGER`	`NOT NULL`	Threat severity of the statements: `0` - Unassigned `1` - Insignificant `2` - Minor `3` - Moderate `4` - Major `5` - Catastrophic
`logging_code`	`SMALLINT`	`NOT NULL`	Logging level of the statements: `0` - Unassigned `1` - Never `2` - Sample `3` - Always `4` - Once `5` - Unique
`action_code`	`SMALLINT`	`NOT NULL`	Action level of the statements: `1` - Known blocked `2` - Known warned `3` - Unseen blocked `4` - Unseen warned
`cluster_type`	`SMALLINT`	`NOT NULL`	Type of statements included in the cluster: 0 - Composite 1 - Data manipulation 2 - Data definition 3 - Data control `4` - Procedural statement `5` - Data manipulation `6` - Transaction `7` - Transaction composite `8` - Invalid
`response_status`	`INTEGER`	`NOT NULL`	Response code of the statement: 0 - Undefined 1 - Unknown 2 - Login success 3 - Login fail `4` - Statement success `5` - Statement fail
`statement_count`	`INTEGER`	`NULL`	Number of SQL statements that have the same characteristics, as listed in this table

Relationship Diagram of the Summary Tables

Figure B-1 illustrates the relationships between the summary database tables.

Figure B-1 Relationship Diagram of the Summary Tables

Description of "Figure B-1 Relationship Diagram of the Summary Tables"

Log Forensic Tables

This section contains:

About the Forensic Tables
traffic_log_queries Table
traffic_log_query_results Table

About the Forensic Tables

The forensic tables contain information about all the SQL statements that Oracle Database Firewall logs. Because the amount of data can be large, Oracle Database Firewall enables you to query the log files through the Administration Console. It stores these log files in the two tables described in this section, traffic_log_queries and traffic_log_query_results.

To search through the log files:

Log in to the standalone Database Firewall or Management Server Administration Console.

See "Logging in to the Administration Console" for more information.
Select the Reporting tab.
In the Traffic Log menu, select either Search Log or Log Search Results.

See Oracle Database Firewall Security Management Guide for more information about accessing the traffic log.

In addition to these two tables, for each search, Oracle Database Firewall creates a new table. This table is derived from traffic_log_query_results and has a name in form of traffic_log_query_results_id where id is the identifier of the search. This table is deleted when the entry in the traffic_log_queries table for a given search is deleted.

traffic_log_queries Table

Table B-14 provides the properties of each log search, such as the period that the log search covers and the filter settings. Each use of Search Log in the reporting page of the Administration Console adds a new row to the table. See Table B-15 for more information about the meaning of each field.

Table B-14 traffic_log_queries Table

Column	Datatype	NULL	Description
`id`	`INTEGER`	`NOT NULL`	Unique ID for the query
`title`	`VARCHAR2(100)`	`NULL`	The title of the report
`started_at`	`TIMESTAMP`	`NULL`	The time when the query was started
`finished_at`	`TIMESTAMP`	`NULL`	The time when the query was finished
`aborted_at`	`TIMESTAMP`	`NULL`	The time when the user cancelled the query
`deleted_at`	`TIMESTAMP`	`NULL`	The time when the query was deleted
`results_table`	`VARCHAR2(30)`	`NULL`	The name of the results table: `traffic_log_query_results_id`, where `id` is an integer that specifies the ID of the search (see Table B-15)
`time_from`	`TIMESTAMP`	`NOT NULL`	The begin of the data time range to be extracted (filtering)
`time_to`	`TIMESTAMP`	`NOT NULL`	The end of the data time range to be extracted (filtering)
`real_time_from`	`INTERVAL DAY(3) TO SECOND(2)`	`NULL`	The beginning of the time range relative to end of the time range
`real_time_from`	`INTERVAL DAY(3) TO SECOND(2)`	`NULL`	The ending of the time range relative to arbitrary time
`filter`	`CLOB`	`NULL`	Filter to reduce the number of entries
`records_limit`	`INTEGER`	`NULL`	Specifies the maximum number of records that should be extracted (filtering) (`NULL` is not recommended.)
`records_count`	`INTEGER`	`NULL`	Records how many records were found and displayed in the Administration Console
`searched_files`	`INTEGER`	`NULL`	Records how many files have already been searched and displayed in the Administration Console
`total_files`	`INTEGER`	`NULL`	Records how many files are be searched and displayed in the Administration Console
`status`	`VARCHAR2(255)`	`NULL`	Status for the search

traffic_log_query_results Table

Table B-15 provides a template for traffic_log_query_results_id, where id is an integer that specifies the ID of the search. The table stores the results of a log search and contains one row for each SQL statement retrieved. The table traffic_log_query_results_id is created automatically when a log search is performed, and is deleted when the user deletes the log search.

Table B-15 traffic_log_query_results Table

Column	Datatype	NULL	Description
`id`	`INTEGER`	`NOT NULL`	ID of this result set
`query_id`	`INTEGER`	`NULL`	ID of the query used to define this result set
`logfile_id`	`INTEGER`	`NULL`	ID of the log file containing this particular statement.
`statement_id`	`INTEGER`	`NULL`	ID of the statement or event
`record_type`	`SMALLINT`	`NOT NULL`	Type of the record: `1` - Statement record `2` - Session record
`source_name`	`VARCHAR2(30)`	`NULL`	Name of the device that collected the log file
`origin`	`VARCHAR2(255)`	`NULL`	Origin of the record. Can be one of the following: `dbfw` `f5` `dbfw,f5`
`protected_database`	`VARCHAR2(250)`	`NULL`	Name of the protected database
`database_dialect`	`SMALLINT`	`NOT NULL`	The database type (dialect) of the protected database: `1` - Microsoft SQL Server `2` - Oracle `5` - Sybase `7` - IBM DB2 (Linux, UNIX, Windows)
`user_name`	`VARCHAR2(255)`	`NULL`	Database user name associated with the statement
`user_name_origin`	`SMALLINT`	`NULL`	Origin of the database user name: `0` - Undefined 1 - Generated `2` - Network `3` - DB query
`raw_user_name`	`VARCHAR2(255)`	`NULL`	The database user name that was used at the time Oracle Database Firewall applied the statement policy
`session_seq`	`INTEGER`	`NULL`	Internal session sequence number
`client_ip`	`VARCHAR2(30)`	`NULL`	IP address of the database client that sent the statement
`client_port`	`INTEGER`	`NULL`	Port number of the database client that sent the statement
`server_ip`	`VARCHAR2(30)`	`NULL`	IP address of the database server that received the statement
`server_port`	`INTEGER`	`NULL`	Port number of the database server that received the statement
`baseline`	`VARCHAR2(1024)`	`NULL`	Name of the policy used when the statement was recorded
`traffic_source`	`VARCHAR2(30)`	`NULL`	Source of the traffic: `network` `local agent`
`dialect_version`	`VARCHAR2(20)`	`NULL`	Internal version or revision of the grammar implementation
`statement`	`CLOB`	`NOT NULL`	Statement text captured by the system
`time`	`TIMESTAMP`	`NOT NULL`	Time when the statement was captured by the system
`threat_severity`	`INTEGER`	`NOT NULL`	Threat severity of the statement: `0` - Unassigned `1` - Insignificant `2` - Minor `3` - Moderate `4` - Major `5` - Catastrophic
`logging_code`	`SMALLINT`	`NOT NULL`	Logging level of the statement: `0` - Unassigned `1` - Never `2` - Sample `3` - Always `4` - Once `5` - Unique
`log_cause`	`SMALLINT`	`NOT NULL`	Reason for logging the statement: 0 - Undefined 1 - Cluster 2 - Novelty 3 - Baseline anomaly `4` - Invalid SQL `5` - WAF `6` - Login `7` - Logout
`action_code`	`SMALLINT`	`NOT NULL`	Action level of the statement: `1` - Known blocked `2` - Known warned `3` - Unseen blocked `4` - Unseen warned
`event_action_code`	`SMALLINT`	`NULL`	Alert criterion that caused the statement to be logged: 0 - Undefined 1 - No alert 2 - Always alert 3 - Alert on success `4` - Alert on failure `5` - Block
`freq_code`	`INTEGER`	`NOT NULL`	Number of the statements that this record represents. In `log all` mode, the value is 1, because each statement is logged separately. In `log unique` mode, the value is 1, because only the first occurrence with the unique combination of user name, IP address and cluster is logged. In `log sample` mode, the value can be greater than 1.
`cluster_id`	`INTEGER`	`NOT NULL`	Global ID of the cluster associated with the statement
`cluster_type`	`INTEGER`	`NOT NULL`	Type or class of statement included in the cluster: 0 - Composite 1 - Data manipulation 2 - Data definition 3 - Data control `4` - Procedural statement `5` - Data manipulation `6` - Transaction `7` - Transaction composite `8` - Invalid
`grammar_version`	`INTEGER`	`NOT NULL`	Grammar-specific version number
`response_code`	`INTEGER`	`NULL`	Response code returned by the database server
`response_text`	`CLOB`	`NULL`	Error message returned by a database query failure
`response_detailed_status`	`CLOB`	`NULL`	Detailed text of the response returned by the database server
`failure_count`	`INTEGER`	`NULL`	Number of subsequent failed statements
`response_time`	`TIMESTAMP`	`NULL`	Time that Oracle Database Firewall captures the statement response
`response_status`	`INTEGER`	`NULL`	Status of the response: 0 - Undefined 1 - Unknown 2 - Login success 3 - Login fail `4` - Statement success `5` - Statement fail
`transaction_time`	`DOUBLE PRECISION`	`NULL`	Difference between response time and request time
`application_name`	`VARCHAR2(255)`	`NULL`	Name of the client application connected to the database
`application_name_origin`	`SMALLINT`	`NULL`	Origin of the application: `0` - Undefined 1 - Generated `2` - Network `3` - DB query
`os_user_name`	`VARCHAR2(255)`	`NULL`	Operating system user name that executed the statement
`os_user_name_origin`	`SMALLINT`	`NULL`	Origin of the operating system user: `0` - Undefined 1 - Generated `2` - Network `3` - DB query
`attr_f5_request`	`CLOB`	`NULL`	Full HTTP request, including POST data Note: This and the remaining fields in this table are relevant to the F5 BIG-IP ASM integration only. See Appendix 11, "Using Oracle Database Firewall with BIG-IP ASM," for more information.
`attr_f5_headers`	`CLOB`	`NULL`	HTTP request
`attr_f5_response_code`	`CLOB`	`NULL`	HTTP response code
`attr_f5_method`	`CLOB`	`NULL`	HTTP request method
`attr_f5_protocol`	`CLOB`	`NULL`	Request protocol
`attr_f5_uri`	`CLOB`	`NULL`	Requested resource
`attr_f5_query_string`	`CLOB`	`NULL`	Part of the URL containing request parameters sent using the `GET` method
`attr_f5_ip`	`VARCHAR2(15)`	`NULL`	Web client IP address
`attr_f5_web_application_name`	`VARCHAR2(64)`	`NULL`	Web application name
`attr_f5_violations`	`CLOB`	`NULL`	Identified WAF violations
`attr_f5_unit_hostname`	`VARCHAR2(64)`	`NULL`	Name of the WAF box
`attr_f5_management_ip_address`	`VARCHAR2(15)`	`NULL`	IP address of the WAF management interface
`attr_f5_policy_name`	`VARCHAR2(128)`	`NULL`	WAF policy name
`attr_f5_policy_apply_date`	`TIMESTAMP`	`NULL`	The time when the policy was applied
`attr_f5_x_forwarded_for_header_value`	`CLOB`	`NULL`	List of IP addresses provided by `X-FORWARDED-FOR` field in HTTP request.
`attr_f5_support_id`	`VARCHAR2(20)`	`NULL`	Unique ID of the event in the WAF system
`attr_f5_request_blocked`	`CHAR (ONLY:'0','1')`	`NULL`	Set to 1 if the HTTP request was blocked
`attr_f5_web_username`	`VARCHAR2(128)`	`NULL`	Name of the Web user
`attr_f5_authentication_method`	`CLOB`	`NULL`	Session authentication method
`attr_f5_referer`	`CLOB`	`NULL`	HTTP referrer
`attr_f5_web_host`	`VARCHAR2(256)`	`NULL`	Web application server name
`attr_f5_user_agent`	`VARCHAR2(1024)`	`NULL`	HTTP user agent
`attr_f5_cardinal_ip`	`VARCHAR2(15)`	`NULL`	IP address derived from `attr_f5_ip` and `attr_f5_x_forwarded_for_header_value`
`attr_f5_primary_violation`	`VARCHAR2(64)`	`NULL`	Violation from `attr_f5_violations` that have the highest priority
`attr_f5_session_cookies`	`CLOB`	`NULL`	Contains all the session cookies sent with the HTTP request
`attr_f5_match_result`	`CLOB`		Match result may be one of: `PolicyConflict` `PolicyConfirmed` `WAFBlocked` `NoMatchDataMasked` `NoMatch`
`attr_f5_match_tokens`	`CLOB`	`NULL`	List of matched tokens

Stored Procedure and User Role Audit Tables

This section contains:

About the Stored Procedure and User Role Audit Tables
doa_approved_edits Table
doa_approved_objects Table
doa_edit_comments Table
doa_edits Table
doa_pending_approvals Table
doa_tag_definitions Table

About the Stored Procedure and User Role Audit Tables

The database object auditing tables contain information about the stored procedures and user roles collected by the stored procedure auditing and user role auditing functions.

To find reports that describe the information captured in the Stored Procedure and User Role Audit tables:

Log in to the standalone Database Firewall or Management Server Administration Console.

See "Logging in to the Administration Console" for more information.
Select the Reporting tab.
Do one of the following:
- For stored procedure auditing, select from the Stored Procedure Auditing menu.
  
  For more information, see Oracle Database Firewall Security Management Guide.
- For user role auditing, select from the User Role Auditing menu.
  
  For more information, see Oracle Database Firewall Security Management Guide.

doa_approved_edits Table

Table B-16 provides details about each set of changes to an object (stored procedure or user role) that have been approved. This information is used for user role auditing and stored procedure auditing.

Table B-16 doa_approved_edits Table

Column	Datatype	NULL	Description
`id`	`INTEGER`	`NOT NULL`	Unique ID of the monitoring point auditing this object
`source_id`	`INTEGER`	`NOT NULL`	ID of the enforcement point monitoring the object
`database_id`	`INTEGER`	`NULL`	ID of the protected database
`object_type`	`INTEGER`	`NOT NULL`	Object type: `0` - Undefined `1` - Stored procedure `2` - User
`object_subtype`	`INTEGER`	`NOT NULL`	Object subtype: `0` - Undefined `1` - Procedure `2` - Function `3` - Trigger `4` - Package `5` - Package body `6` - Java source `7` - Extended procedure `8` - Scalar function `9` - Inline table function `10` - Replication filter `11` - Table function
`object_class`	`INTEGER`	`NOT NULL`	Object class: `0` - Undefined `1` - System `2` - User
`name`	`VARCHAR2(1024)`	`NOT NULL`	Name of the object
`tags`	`VARCHAR2(2048)`	`NULL`	Tags associated with the object
`changes_summary`	`VARCHAR2(255)`	`NULL`	Contains a summary of the set of changes approved (for example: `3 modifications`). This is a copy of the value from `doa_pending_approvals` (Table B-20).
`changed_by`	`VARCHAR2(2048)`	`NULL`	Comma-separated list of the names of the database users who were responsible for the modifications (copy of the value from `doa_pending_approvals` (Table B-20
`last_changed_at`	`VARCHAR2(2048)`	`NULL`	The date and time when the object was changed
`detected_at`	`TIMESTAMP`	`NOT NULL`	The date and time when the change was detected on the Management Server
`approved_by`	`VARCHAR2(255)`	`NOT NULL`	The name of the Administration Console user who approved the set of changes
`approved_at`	`TIMESTAMP`	`NOT NULL`	The date and time when the changes were approved
`user_comment`	`CLOB`	`NOT NULL`	Comment added by the user when the changes were approved

doa_approved_objects Table

Table B-17 provides a summary of the last set of changes to an object (stored procedure or user role) that have been approved. This information is used for user role auditing and stored procedure auditing.

Table B-17 doa_approved_objects Table

Column	Datatype	NULL	Description
`id`	`INTEGER`	`NOT NULL`	Unique ID of the object
`source_id`	`INTEGER`	`NOT NULL`	ID of the enforcement point monitoring the object
`database_id`	`INTEGER`	`NULL`	ID of the protected database
`object_type`	`INTEGER`	`NOT NULL`	Object type: `0` - Undefined `1` - Stored procedure `2` - User
`object_subtype`	`INTEGER`	`NOT NULL`	Object subtype: `0` - Undefined `1` - Procedure `2` - Function `3` - Trigger `4` - Package `5` - Package body `6` - Java source `7` - Extended procedure `8` - Scalar function `9` - Inline table function `10` - Replication filter `11` - Table function
`object_class`	`INTEGER`	`NOT NULL`	Object class: `0` - Undefined `1` - System `2` - User
`name`	`VARCHAR2(1024)`	`NOT NULL`	Name of the object
`tags`	`VARCHAR2(2048)`	`NULL`	Tags associated with the object
`signature`	`VARCHAR2(40)`	`NULL`	Hash of the object (base64) (signature change means object change)
`changes_summary`	`VARCHAR2(255)`	`NULL`	Summary of the changes
`changed_by`	`VARCHAR2(2048)`	`NULL`	Database users who modified the object
`changed_at`	`TIMESTAMP`	`NULL`	Time when the object was changed
`approved_by`	`VARCHAR2(255)`	`NOT NULL`	The name of the Administration Console user who approved the last set of changes
`approved_at`	`TIMESTAMP`	`NOT NULL`	The date and time when the changes were approved
`content`	`CLOB`	`NULL`	Current approved content of the object

doa_edit_comments Table

Table B-18 provides details about each comment added when approving changes. This information is used for user role auditing and stored procedure auditing.

Table B-18 doa_edit_comments Table

Column	Datatype	NULL	Description
`id`	`INTEGER`	`NOT NULL`	Unique ID of the comment
`approval_id`	`INTEGER`	`NOT NULL`	ID of the pending approval in the `doa_pending_approvals` table (Table B-20)
`user_comment`	`CLOB`	`NOT NULL`	The comment text
`created_by`	`VARCHAR2(255)`	`NOT NULL`	The name of the Administration Console user who added the comment
`created_at`	`TIMESTAMP`	`NOT NULL`	The date and time that the comment was created

doa_edits Table

Table B-19 provide details about all approved objects. This information is used for stored procedure auditing and user role auditing.

Table B-19 doa_edits Table

Column	Datatype	NULL	Description
`id`	`INTEGER`	`NOT NULL`	Unique ID of the object (stored procedure or user role)
`approval_id`	`INTEGER`	`NOT NULL`	ID of the pending approval in the `doa_pending_approvals` table (Table B-20)
`signature`	`VARCHAR2(40)`	`NULL`	The hash of the object (signature change means object change)
`content`	`CLOB`	`NULL`	New content of the object
`edit_type`	`SMALLINT`	`NOT NULL`	Type of change: `1` - New `2` - Modify `3` - Delete
`changed_by`	`VARCHAR2(255)`	`NULL`	Name of the database user who modified the object
`changed_at`	`TIMESTAMP`	`NULL`	The date and time when the object was changed
`detected_at`	`TIMESTAMP`	`NULL`	The date and time when the change was detected on the Management Server

doa_pending_approvals Table

Table B-20 provides a summary of the changes to an object (stored procedure or user role) that are pending approval. This information is used for User Role Auditing and Stored Procedure Auditing.

Table B-20 doa_pending_approvals Table

Column	Datatype	NULL	Description
`id`	`INTEGER`	`NOT NULL`	Unique ID of the object
`source_id`	`INTEGER`	`NOT NULL`	ID of the enforcement point monitoring the object
`database_id`	`INTEGER`	`NULL`	ID of the protected database
`object_type`	`INTEGER`	`NOT NULL`	Object type: `0` - Undefined `1` - Stored procedure `2` - User
`object_subtype`	`INTEGER`	`NOT NULL`	Object subtype: `0` - Undefined `1` - Procedure `2` - Function `3` - Trigger `4` - Package `5` - Package body `6` - Java source `7` - Extended procedure `8` - Scalar function `9` - Inline table function `10` - Replication filter `11` - Table function
`object_class`	`INTEGER`	`NOT NULL`	Object class: `0` - Undefined `1` - System `2` - User
`name`	`VARCHAR2(1024)`	`NOT NULL`	Name of the object
`tags`	`VARCHAR2(2048)`	`NULL`	Tags associated with the object
`is_declined`	`CHAR (ONLY:'0','1')`	`NULL`	Set to 1 if the change must not be approved in bulk operation (default is `FALSE`)
`is_updated`	`CHAR (ONLY:'0','1')`	`NULL`	Set to 1 if the change has been updated by the Management Server after being previously declined. (To decline this pending approval, set `is_declined` to `1` and `is_updated` to `0`.)
`changed_by`	`VARCHAR2(2048)`	`NULL`	Comma-separated list of the names of the database users who have modified the object since the previous approval
`last_changed_at`	`TIMESTAMP`	`NULL`	The date and time of the last change to the object
`last_signature`	`VARCHAR2(40)`	`NULL`	The hash of the object (signature change means object change)
`last_edit_type`	`SMALLINT`	`NOT NULL`	Last type of change: `0` - Undefined `1` - New `2` - Modify `3` - Delete `4` - Unchanged
`edit_cnt_new`	`INTEGER`	`NULL`	Specifies the number of new changes of type `new`
`edit_cnt_modify`	`INTEGER`	`NULL`	Specifies the number of new changes of type `modify`
`edit_cnt_delete`	`INTEGER`	`NULL`	Specifies the number of new changes of type `delete`
`changes_summary`	`VARCHAR2(255)`	`NULL`	Contains a summary of the above changes (for example,`3 modifications)`
`updated_at`	`TIMESTAMP`	`NOT NULL`	Specifies the date and time that the record was last updated in the Database Firewall

doa_tag_definitions Table

Table B-21 contains the definitions of tags that may be applied to stored procedures or user roles.

Table B-21 doa_tag_definitions Table

Column	Datatype	NULL	Description
`id`	`INTEGER`	`NOT NULL`	Unique ID for the definition
`object_type`	`INTEGER`	`NOT NULL`	Type of the object that will have the tag applied: `0` - Undefined `1` - Stored procedure `2` - User
`dialect`	`SMALLINT`	`NOT NULL`	Database type (dialect) of the database: `1` - Microsoft SQL Server `2` - Oracle `5` - Sybase ASE `7` - IBM DB2 (Linux, UNIX, Windows)
`key`	`VARCHAR2(255)`	`NOT NULL`	ID of the tag definition. It should be unique within type and dialect
`pattern`	`VARCHAR2(2048)`	`NULL`	Regular expression that will be matched against the object content to apply the tag
`tag`	`VARCHAR2(255)`	`NULL`	Tag that will be applied
`is_enabled`	`CHAR (ONLY:'0','1')`	`NOT NULL`	Set to 1 if the tag definition can be used
`is_predefined`	`CHAR (ONLY:'0','1')`	`NOT NULL`	Set to 1 if the tag definition was supplied with the product