System Administration Certification exam 919 for Informix 11.70 prep, Part 8: Security

Using auditing

Auditing creates a record of selected activities that users perform. Auditing is based on the notion of audit events and audit masks. Auditing needs to be configured and then set up on the Informix server. It can also be turned off when not needed.

Using audit events

Any database server activity that can potentially alter or reveal data or the auditing configuration is considered an event. You can use the database server secure-auditing facility to audit and keep a record of events either when they succeed or fail, or when the activity is attempted. Each audit event is identified by a four-letter event code. There are over 150 events that can be audited. Following are some common event codes:

• ACTB is access table.
• UPRW is update current row.
• STEX is set explain.
• RDRW is read row.
• DLRW is delete row.

Back to top

Using audit masks

Audit masks specify those events that the database server must audit. You can include any event in a mask. The masks are associated with user IDs so that specified actions that a user ID takes are recorded. Global masks _default, _require, and _exclude are specified for all users in the system. Table 4 lists four types of audit masks.

User masks

The global masks are always applied to user actions that are performed during a session in which auditing is turned on. Audit masks are applied in the following order:

1. An individual user mask or if none, the _default mask
2. The _require mask
3. The _exclude mask

When a user initiates access to a database, the database server checks whether an individual user mask exists with the same user name as the account that the user uses. If an individual user mask exists, the database server reads the audit instructions in it first and ignores the _default mask. If no individual user mask exists, the database server reads and applies the audit instructions in the _default mask to that user.

Template masks

You can create template audit masks to help set up auditing for situations that recur or for various types of users. Template mask names begin with an underscore (_).

Back to top

Using audit instructions

Masks and their events are called auditing instructions. You can select anything from minimal audit instructions, in which no events are audited, to maximum audit instructions, in which all security-relevant database server events are audited for all users. The onaudit utility is used for all audit-mask creation and maintenance.

Selective row-level auditing

With Informix 11.70, auditing can be configured so that row-level events of only selected tables are recorded in the audit trail. In previous releases, auditing was an all-or-nothing type of activity. Selective row-level auditing can compact audit records so that they are more manageable, which can potentially improve database server performance.

Informix 11.70 adds the new table level property AUDIT, which can be specified with the CREATE TABLE or ALTER TABLE commands, as shown in Listing 18. The AUDIT property controls the auditing on the specific table.

                    
CREATE TABLE {existing syntax} | with AUDIT;
ALTER TABLE {existing syntax} | add AUDIT;
ALTER TABLE {existing syntax} | drop AUDIT; 

The onaudit utility supports an option (the -R flag) that can be run to enable selective row-level auditing. You can start selective row-level auditing either when you initially start auditing your databases or when the auditing utility is already running.

Back to top

Understanding the audit process

When you turn on auditing, the database server generates audit records for every event that the auditing instructions specify. The database server stores the audit records in a file called an audit file. (The audit trail might consist of more than one audit file.)

Audit administrators must specify and maintain the audit configuration, which includes the following information:

• The audit mode
• How the database server behaves if it encounters an error when writing audit records to the audit trail
• For UNIX, the directory in which the audit trail is located
• For UNIX, the maximum size of an audit file before the database server automatically starts another audit file

Audit configuration

Complete the following steps to set the audit configuration.

1. Turn auditing on or off. Auditing is turned on or off via the onaudit utility.
2. Specify audit modes. ADTMODE in the ADTCFG file sets the type and level of auditing.
3. Use the ADTCFG file. Configuration parameters in the ADTCFG file specify the properties of the audit configuration. These configuration parameters are ADTERR, ADTMODE, ADTPATH, and ADTSIZE. The path name for the default ADTCFG file is $INFORMIXDIR/aaodir/adtcfg for UNIX and %INFORMIXDIR%\aaodir\adtcfg for Windows.
4. On UNIX, determine the properties of the audit files. Audit files are located in a directory you specify via the ADTPATH onconfiguration parameter. The naming convention used for audit files is dbservername.integer, where dbservername is the database server name as defined in your onconfig file, and integer is the next available integer after the number defined in the ADTLOG file.

Back to top

Setting up auditing in an Informix server

Setting up auditing in an Informix server requires the following high-level steps:

1. Use the onaudit utility to add audit events to audit masks. The command in Listing 19 shows how the Update Audit Mask and Delete Audit Mask audit events are added to the _default mask by their four-letter event codes.
   
   Listing 19. Adding events
   

   $ onaudit -m -u _default -e +UPAM,DRAM

   
   
2. Confirm your audit mask settings in Listing 20.
   
   Listing 20. Confirm audit mask settings
   

   $ onaudit -o -u _default _default - UPAM,DRAM

   
   
3. Specify a directory for the audit trail in UNIX. The database server stores audit files in a file system directory. You can specify the directory with the onaudit utility, as shown in Listing 21.
   
   Listing 21. Using the onaudit utility
   

   $ onaudit -p /work/audit

   
   
4. Check the audit configuration using the $ onaudit -c command. The configuration results are shown in Listing 22.
   
   Listing 22. Checking the audit configuration
   

   Onaudit -- Audit Subsystem Configuration Utility Current audit system configuration: ADTMODE = 0 ADTERR = 0 ADTPATH = /usr/informix/aaodir ADTSIZE = 50000 Audit file = 0 ADTROWS = 0

   
   
5. Set the audit level and turn on auditing. The AAO or DBSSO configures the level of auditing in the system. To start auditing all sessions, enter $ onaudit -l 1. Following are additional auditing-level options:

• 0 = auditing disabled
• 1 = auditing on, starts auditing for all sessions
• 3 = auditing on, audits DBSSO actions
• 5 = auditing on, audits database server administrator actions
• 7 = auditing on, audits DBSSO and database server administrator actions

Once auditing is started, the audit level is logged in the online.log, and the adt VP is started, as shown in Listing 23.

14:43:19 Dynamically added 1 adt VP
14:43:19 Audit Mode changed to 1 

54 4da833a8 0 1 IO Idle 9adt* adt vp 0 

Back to top

Configuring additional options with auditing

You can configure additional options with auditing.

Error mode

The database server can perform either of two functions if an error occurs when writing to the audit trail:

• Continue-error mode
• Two levels of severity of halt-error mode

Setting up selective row-level auditing

The command in Listing 24 continues auditing all tables that have the AUDIT flag and stops auditing all other tables.

 
                            
$ onaudit -R 1 

Options: 
0 = Auditing row-level events on all tables (0 is the default value) 
1 = Selective row-level auditing is enabled for tables set with the AUDIT flag. 
2 = Selective row-level auditing is enabled for tables set with the AUDIT flag. 
    The primary key, if an integer data type, is included in the audit records.

Back to top

Turning off auditing

Auditing is stopped by setting the audit level to 0, as in $ onaudit -l 0.

Back to top

Using the onshowaudit utility

The audit trail (or audit files) is accessed using the onshowaudit utility. You can use the $ onshowaudit command to extract information for a particular user, database server, or both, making it possible to isolate a particular subset of data from a potentially large audit trail. The database server does not audit the onshowaudit utility's execution. Listing 25 shows the results of the onshowaudit command.

 
                    
ONSHOWAUDIT Secure Audit Utility
INFORMIX-SQL Version 11.70.FC4
ONLN|2011-10-06 15:28:25.000|fido|11647|abc1170tcp|informix
|0:RDRW:stores_demo:106:2097230:309::
Program Over. 

The -f path option specifies the directory and file name of the audit files, as shown in Listing 26.

If -f is omitted from the command, onshowaudit searches for audit files in the ADTPATH directory specified in the default ADTCFG file. The audit directory and file name must conform to minimum security levels. The directory must be owned by user informix, belong to the AAO group, and must not allow public access (0770 permission). The files must have comparable permissions (0660 permission). The files must not be symbolic links to other locations. The directory can be a symbolic link. If the audit directory and files are not secure, the onshowaudit utility returns an error message and does not display the audit results.

 
                    
$ onshowaudit -I -f /work/aaodir/ol_lx_rama.7

ONSHOWAUDIT Secure Audit Utility
INFORMIX-SQL Version 11.70.FC4
ONLN|2011-10-06 15:28:25.000|fido|11647|abc1170tcp|informix
|0:RDRW:stores_demo:106:2097230:309:: 
Program Over. 

The command in Listing 27 shows only the records that pertain to usr1 in the audit log file /work/aaodir/ol_lx_rama.7.

onshowaudit -I -f /work/aaodir/ol_lx_rama.7 -u usr1 

The onshowaudit command can also prepare the extracted data with delimiters so it can be later loaded into a database, as shown in Listing 28.

 
                    
$ onshowaudit -I -l -f /work/aaodir/ol_lx_rama.7 

ONSHOWAUDIT Secure Audit Utility 
INFORMIX-SQL Version 11.70.FC4 
ONLN|2011-10-06 15:28:25.000|fido|11647|abc1170tcp|informix|0|RDRW|stores_demo
|106|||2097230|309|||| 
Program Over. 

4 of 8 | Previous | Next

Comments

Back to top

Table of contents

• Before you start
• Understanding connection security
• Using network encryption
• Using auditing
• Using discretionary access control
• Conclusion
• Resources
• About the authors

---

4 of 8 | Previous | Next

Next steps from IBM

• Try: Informix Ultimate Edition
• Buy: Informix Ultimate Edition

Dig deeper into Information Management on developerWorks

IBM SmartCloud trial. No charge.

Unleash the power of hybrid cloud computing today!

---

Public and private solutions in one trial.

Special offers