This blog contains experience gained over the years of implementing (and de-implementing) large scale IT applications/software.

New SAP ASE Audit Logging Destination in 16.0.4

Let’s face it, auditing in SAP ASE 16 is difficult to configure due to the requirement to produce your own stored procedure and correctly size the rotating table setup with multiple database segments for each of the multiple audit tables. Once configured, you then had the realisation that to obtain the records, you needed to extract them from the database somehow, and then the problem of who does this task, what privileges they need, should they themselves be audited etc etc.

Good news! With the introduction of ASE 16.0 SP04, configuring auditing in the SAP ASE database just got dramatically easier and way more useable!

Introducing “audit trail type”

In previous versions of the SAP ASE database, database audit records were stored only in the sybsecurity database in a specific set of tables that you had to size and create and rotate yourself (or with a stored procedure).

Once the records were in the database, it was then up to you to define how and when those records would be analysed.
Depending on whether your SIEM tool supports direct ODBC/JDBC access to SAP ASE or not, would depend on how complex the extraction process would be.

In SP04 a new parameter was introduced called “audit trail type” where you can now set the audit store to be “syslog”.

When setting the store to be “syslog”, the audit records are pushed out to the Linux syslogd daemon (or rsyslogd or syslog-ng) and written to the O/S defined location according to the configuration of syslogd:

Each audit record gets a tag/program name of “SAP_ASE_AUDIT”, which means you can define a custom syslogd log file to hold the records, and also then specify a custom rotation should you wish.
Your syslogd logs may already be pulled into your SIEM tools, in which case you will simply need to classify and store those records for analysis.

With the new parameter set to “syslog” and the audit records being stored as file(s) on the file system, you will need to ensure that the file system has adequate space and establish a comfortable file retention (logrotate) configuration to ensure that audit records do not cause the file system to fill (preventing persistence of additional audit records).

Of course, should you enjoy torture, you can always go ahead and continue to use the database to store the audit records. Simply setting the new parameter “audit trail type” to “table”, will store the audit records in the database just like the previous versions of ASE.

Useful Links

What’s new in ASE 16.0.4

Parameter: audit trail type

Oracle AUD$ TIMESTAMP# from 9i to 10gR2

You select TIMESTAMP# from AUD$ in an Oracle 10gR2 or 11g database, but you seem to be missing the most recent audit information.
Your DB was upgraded at some point from 9i or earlier.

SQL> select min(TIMESTAMP#) from aud$;

SQL> select max(TIMESTAMP#) from aud$;

When you select from the DBA_AUDIT_TRAIL view, you see all the records:

SQL> select max(timestamp) from dba_audit_trail;
26-APR-12 AM

The reason is that the TIMESTAMP# column in AUD$ was made obsolete in, but it still contains the old data.
The old data was migrated into the NTIMESTAMP# column but it will be missing the additional sub-second timings and the timezone:

SQL> select min(NTIMESTAMP#) from aud$;
04-MAR-04 PM

You should use the NTIMESTAMP# column in AUD$ to see the later audit records post 10g upgrade.
See MYOS note 427296.1 for more detail on this.

The DBA_AUDIT_TRAIL view uses the new NTIMESTAMP# column:

SQL> select max(timestamp) from dba_audit_trail ;
26-APR-12 AM

SQL> select min(timestamp) from dba_audit_trail;
04-MAR-04 PM

Disabling Change or Deactivation of SAP Audit Logging SM19 – SAL

To disable changing or deactivation of the SAP Audit Logging (SAL) settings via transaction SM19, you can remove the authorisation object S_ADMI_FCD activity AUDA.

This would prevent “normal” BASIS administrators from being capable of changing or disabling audit logging, but permit you to provide an emergency user with this capability (you will need to change SAL settings in SM19 at some point!).