Security Auditing
Security auditing is the recording of all or specified classes of security events for the entire 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 to a single object or across an entire class of installation objects.
Security auditing is controlled by a user with the maintain_audit privilege.
Related Information
Audit Focus
The information in the audit log can quickly grow in volume. You can achieve maximum benefit of security auditing by focusing the audit information produced by the system.
Security auditing has an impact on resource consumption. Audit records are recorded in a shared buffer before being written to the audit page and then to the log file. The performance impact of security auditing should be tested thoroughly before implementation.
Focusing the audit information both reduces resource consumption and makes it easier to examine the logs 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.
How to Enable Security Auditing
By default, security auditing is disabled. You must enable security auditing by setting the security_auditing configuration parameter. In addition, you must specify the level of auditing using the ENABLE SECURITY_AUDIT statement.
To enable security auditing follow these steps:
1. In CBF, select Security, Configure, Auditing.
The Configure Security Auditing screen appears.
2. Scroll to security_auditing. Select Edit to toggle the setting to ON.
3. (Optional) Tab to the Audit log files and use the Edit function to change the location and names of the security audit log files.
4. Connect to the master database iidbdb as the installation owner or security administrator.
5. Issue statements similar to the following to enable the level of security auditing.
• To enable security auditing on all operations by all users, installation wide:
ENABLE SECURITY_AUDIT ALL
• To enable query text auditing by a specific user:
ALTER USER username WITH SECURITY_AUDIT=(QUERY_TEXT)
How to Verify Security Auditing Levels
You can verify security auditing levels by querying the appropriate system catalog.
To check that security auditing was enabled on all operations by all users, installation wide, follow these steps:
1. Log on as the installation owner.
2. Connect to master database iidbdb.
Query the system catalog iisecurity_state by issuing the following command:
SELECT STATE FROM iisecurity_state
WHERE NAME = 'All';
The value returned should be E (enabled).
To check that query text auditing was enabled by a specific user, follow these steps:
3. Log on as the installation owner.
4. Connect to master database iidbdb.
5. Query the system catalog iiusers by issuing the following command:
SELECT AUDIT_QUERY_TEXT FROM iiusers
WHERE USER_NAME = 'username';
The value returned should be Y.
Security Auditing Configuration Parameters
A security audit log file is created as part of the installation process. Audit records are recorded in a shared buffer before being written to the audit page and then to the log file. The auditing derived parameters will affect performance so concurrent performance testing is advised before implementing security auditing.
The security auditing configuration parameters are as follows (for more detail, see the online help for CBF or Configuration Manager):
audit_mechanism
Used for the auditing destination.
Default: INGRES
log_page_size
Specifies the page size of each audit log page
max_log_size
Specifies the maximum log size in kilobytes
on_error
Specifies the audit action to take on an error, either SHUTDOWN or STOPAUDIT
on_log_full
Specifies the audit action to take on “log full” condition: SHUTDOWN, STOPAUDIT, or SUSPEND
on_switch_log
Specifies the full path of the utility to execute when an audit log is full or before a new log is initialized
security_auditing
Specifies whether security auditing is ON or OFF
Audit log files
Specify the full path of each audit log
Security Audit Statements
Levels of security auditing are enabled and disabled with the SQL statements ENABLE SECURITY_AUDIT and DISABLE SECURITY_AUDIT.
To use these statements, you must have the maintain_audit privilege and be connected to the iidbdb database.
Keywords on these statements allow you to specify the types of security events you want to audit. For example:
• DATABASE—to control logging of database access
• PROCEDURE—to control logging of procedure access
• ALL—to control logging of all possible security events
The events specified using these statements are known as the default events, which is a term that applies when specifying auditing levels for users, profiles, and roles, as described in the next section.
Security Audit Levels for Users and Roles
Security audit levels can also be specified for individual users (directly or through a profile) and for roles (requires the maintain_audit privilege). You can specify the security audit level whenever you create or modify the user, profile, or role.
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 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.
Changes to Security Audit Status During a Session
The security status for a 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.
Registering 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 following statements, for example, 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;
The REGISTER TABLE statement, when used to register the security audit log file, requires the auditor privilege.
When the virtual 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.
To display information on registered objects, use the HELP REGISTER statement.
For the complete syntax (including specifications for the security log audit file format) for the REGISTER TABLE and REMOVE TABLE statements, see the SQL Language Guide.
Querying the Registered Virtual Table
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 sal1 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
01‑Jan‑2008 01:00 N SELECT TABLE salaries Attempt to
access a TABLE
01-Jan-2008
Obtain the Current Audit File Name
The dbmsinfo function can be used to find the name of the audit log file. You must have the auditor privilege to use this call.
To obtain the name of the current security audit log file
Issue the following statement:
dbmsinfo('security_audit_log')
The function returns the file name only, not the full file specification.
Alternatively, you can access the current file through the iiaudit system catalog, in the iidbdb system database.