Security Auditing

Security auditing allows a user with the maintain_audit privilege (for example, the system administrator, the DBA, or a separate security administrator) to control the recording of all or specified classes of security events for the entire Ingres installation. Selected classes of events, such as use of database procedures or access to tables, can be recorded in the security audit log file for later analysis. Criteria can be selected that apply across an entire class of installation objects or targeted at a single object.

You obtain the maximum benefits of security auditing by focusing the amount of audit information produced by the system. This both reduces the impact of security auditing in terms of resource consumption, and makes it easier to look through the resulting information for possible security infringements. The coarse and fine selection criteria can be used together to create a suitable security-auditing environment that meets the needs of any security administrator.

Security Audit Log Configuration

A security audit log is created by the system administrator as part of the installation configuration. For details, see the chapter "Configuring Ingres" in the System Administrator Guide.

For information about the following parameters, see the Auditing Page, Security Component (Configuration Manager window) in online help:

• audit_mechanism
• log_page_size
• max_log_size
• on_error
• on_switch_log
• security_auditing

At this stage, although the security-auditing facility is ready to produce auditing information, the default installation auditing state produces no output to the security auditing log file. Auditing is enabled through the security audit statements, described next.

Security Audit Statements

Security audit levels are enabled and disabled with the enable security_audit and disable security_audit statements. To use these statements, you must have the maintain_audit privilege and be working in a session connected to the iidbdb database.

These statements allow you to specify the types of security events you want to audit using keywords, such as

• database—to control logging of database access
• procedure—to control logging of procedure access
• all—to control logging of all possible security events

The enable security_audit statement enables logging the specified event types, and the disable security_audit statement disables logging the specified event types. The events specified using these statements are known as the default events, which is a term that comes up when specifying auditing levels for users, profiles, and roles, as described in the next section.

For complete syntax for these statements and a complete list of keywords, see the SQL Reference Guide.

Security Audit Levels for Users and Roles

Security audit levels can also be specified for individual users (directly or through a profile), as well as for roles (once again, you must have the maintain_audit privilege to do this). You can specify the security audit level whenever you create or modify the user, profile, or role. For more information on working with users, profiles, and roles, see the chapter "Authorizing User Access."

By default, users are audited for default events (as specified by the security audit statements). However, you can specify that a user be audited for all events and even that the query text associated with the user's queries be audited.

Important! Because query text auditing is quite detailed and takes up a lot of space in the security log file, it must be explicitly enabled at the user level and using enable security_audit query_text. Otherwise, no query text auditing can take place.

By default, roles are audited according to the settings for the individuals using the role. However, because a role can give a user privileges the user does not otherwise have, you can specify that anyone who uses a role be audited for all events while using the role, regardless of that user's audit state.

Note: Default auditing levels (as well as other default user and role attributes) are determined by the default profile. If the default profile is modified, the defaults stated in this section do not apply.

Security Changes Taking Effect

The security status for a user session is determined at the time of initial connection. Thereafter, during the session:

• If the auditing level of a user, profile, or role is changed from default auditing to auditing all events, or vice versa, the change in status can apply only to new sessions connecting after the change has been made.
• All other security-auditing related changes take effect immediately.

Access to the Security Audit Log

Access to the security audit log is established through registering the security audit log file as a virtual table. After it has been successfully registered, the security audit log file can be queried as any other table.

Register the Security Audit Log File

To access the security audit log file contents with SQL query statements, you must first register the audit log file as a virtual table using the register table statement with the dbms=sxa clause. The register table statement, when used to register the security audit log file, requires the auditor privilege.

For example, the following statements make a subset of the security audit log file sal1.log available through the table sal1:

register table sal1(
    database      char(24) not null,
    audittime     date  not null,
    user_name     char(24) not null,
    auditstatus   char(1)  not null,
    auditevent    char(24) not null,
    objecttype    char(24) not null,
    objectname    char(24) not null,
    description   char(80) not null
    )
  as import from 'sal1.log'
  with dbms = sxa;

After the security audit log is registered, any user with the auditor privilege can perform queries on the registered virtual table to view its contents. For example, to obtain all events by the user spy against the database securedb, query the table created in the previous example as follows:

select audittime, auditstatus, auditevent,
    objecttype, objectname, description
  from sal1
  where database = 'securedb' and user_name = 'spy'
  order by audittime;

The result of the query might be similar to the following:

audittime auditstatus auditevent objecttype objectname descrpt 01Jan2004

01:00  N   SELECT       TABLE       salaries    Attempt to
                        access a
                        TABLE 01-Jan-2004

Afterwards, when the table is no longer needed, a user with the auditor privilege can use the remove table statement, specifying the name of the virtual table created using register table.

For the complete syntax and details (including specifications for the security log audit file format) for the register table and remove table statements, see the SQL Reference Guide. To display information on registered objects, see the help register statement.

Obtain the Current Audit File Name

To obtain the name of the current security audit log fileuse the dbmsinfo function, as follows:

dbmsinfo('security_audit_log')

The function returns only the file name itself, not the full file specification.

You must have the auditor privilege to use this call.

Alternatively, you can access the current file through the iiaudit system catalog, held in the iidbdb system database.