Yet Another User Audit Log Solution for IQ (YAUALS for IQ)

[stefano_bolli]

In this document it is described an applicative and customizable solution to audit log all (or a configurable subset) succesfull logins, login attempts and logouts that occurs on a SAP IQ engine. It can be a lightweight and customizable alternative to the DB AUDIT OPTION natively provided with IQ engine.

It is useful if you need this audit log for specific purposes that requires a specific format not available using native features.

YAUALS is distributed using GNU General Public License v3 (see http://www.gnu.org/licenses/) and it is available as attachment to this document for download.

Objectives

The idea is to provide a customizable and lightweight alternative to the DB AUDIT OPTION natively provided with IQ engine.

Description

YAUALS idea is to capture, using conditioned events, all succesfull logins, login attempts and logouts that occurs on a SAP IQ engine.

Once this events are triggered they are recorded on a audit log file.

YAUALS was developed and tested on SAP IQ 16.0 SP08 engine installed on a Windows 2012 R2 machine.

If you want to use this script on linux you need to adapt the OS command executed by xp_cmdshell functions because in current version it uses DOS commands.

Implementation details

Table: DBA.AUDIT_PARAMETERS

It stores the configurable parameters for the solution.

Procedure: DBA.sp_audit_login

In case of successful login it logs keyword=LOGIN, timestamp, username, connection ID (CID), node details (IP, hostname, osuser, osversion, client specifications) details of the successful login session to audit file.

When a failed login attempt occurs it logs keyword=FAILED, timestamp, username.

The audit log file path and name are stored in AUDIT_PARAMETERS table.

Syntax

exec dba.sp_audit_login

Example output (on audit log file)

..

2015-08-13 00:11:27.877 #USER AUDIT - LOGIN# IQUSER=DEMOUSR;CID=5743;IP=192.168.100.34;HOST=DEMOHOST;OSUSER=windemouser;OS='Windows 2012R2';EXE='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe';PID=0x17c4;THREAD=0xdec;VERSION=16.0.0.808;API=ODBC;TIMEZONEADJUSTMENT=120

..

..

2015-08-13 00:11:28.605 #USER AUDIT - FAILED# IQUSER=FAKE_USER

..

Procedure: DBA.sp_audit_logout

In case of successul logout it logs keyword=LOGOUT, timestamp, username and connection ID (CID) details of the disconnected session to audit file.

The audit log file path and name are stored in AUDIT_PARAMETERS table.

Syntax

exec dba.sp_audit_logout

Example output (on audit log file)

..

2015-08-13 00:13:28.085 #USER AUDIT - LOGOUT# IQUSER=DEMOUSR;CID=5743

..

Procedure: DBA.sp_audit_file_rotate

It executes the audit log file rotation considering the retention configured in AUDIT_PARAMETERS table, parameter <'sp_audit', 'Retention_UserAudit_File'> expressed in days <0 to maintains. For example to maintain only last 2 days configure -2.

The audit log file path and name are stored in AUDIT_PARAMTERS table.

Syntax

exec sp_audit_file_rotate

Function: DBA.fx_audit_get_param

It is a utility function to retrieve parameters from DBA.AUDIT_PARAMETERS

Syntax

fx_audit_get_param(<APP>, <KEY>)

Example

select @pathAuditFile=fx_audit_get_param('sp_audit','Path_UserAudit_File')

select @nameAuditFile=fx_audit_get_param('sp_audit','Name_UserAudit_File')

select @dayToDelete=fx_audit_get_param('sp_audit','Retention_UserAudit_File')

Event: ev_AUD_Login_OK

It is a triggered event on successful connection event that simply call: sp_audit_login

Event: ev_AUD_Login_KO

It is a triggered event on failed connection event that simply call: sp_audit_login

Event: ev_AUD_Logout

It is a triggered event on successful logout event that simply call: sp_audit_logout

Event: ev_AUD_LogRotate

It is a scheduled event (1 run a day) that simply call: sp_audit_file_rotate

Future developments

1. Actually the audit log is written directly to file:
   1. an extension could provide a table (in IQ, or outside directly in audit log repository system) where write log.
   2. an extension could provide to write directly to windows/linux system log (ex. message @warningText type status to event log).
   3. an extension could provide a SNMP trap directly to audit log repository system.
   4. an extension could provide the selection, throught configuration, of the output method to use (also more than one) among the reported above.
2. Actually YAUALS audit events for all users:
   1. an extension could provide a set of configurable rules in order to record only specific user/roles/.. events.

Conclusions

Yet Another User Audit Log Solution for IQ (YAUALS for IQ) is a very lightweight and extensible tool for those who need a way to track user access to IQ engine. It doesn't require any additional license and it can be implemented in all current IQ versions.