Auditing ALTER SYSTEM commands

A recent OTN Thread inspired this posting: How can we track down initialization parameter changes that someone has done with ALTER SYSTEM? One way would be to monitor the alert.log, but I think auditing should be most appropriate here. Preparing the playing field:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0    Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> show parameter audit_trail

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
audit_trail                 string     NONE

When you use DBCA to create an 11g Database, AUDIT_TRAIL will show as DB – I did the creation on the command line, therefore it is still NONE. When we want to see the particular ALTER SYSTEM command like in the demonstration, the value EXTENDED is additionally required.

SQL> alter system set audit_trail='DB','EXTENDED' scope=spfile;

System altered.

SQL> shutdown immediate

ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  313159680 bytes
Fixed Size            2227944 bytes
Variable Size          255852824 bytes
Database Buffers       50331648 bytes
Redo Buffers            4747264 bytes
Database mounted.
Database opened.
SQL> audit alter system by access;

Audit succeeded.

SQL> alter system set processes=250 scope=spfile;

System altered.

SQL> alter system set parallel_degree_policy=limited;

System altered.

SQL> alter system set result_cache_mode=force scope=memory;

System altered.

You may notice that I was still SYS when doing the ALTER SYSTEM commands. That is important here, because they are not audited, as we can see below. If you want auditing for SYS, this posting shows how to do that.

 
SQL> grant dba to adam identified by adam;

Grant succeeded.

SQL> connect adam/adam
Connected.

SQL> alter system set processes=200 scope=spfile;

System altered.

SQL> alter system set parallel_degree_policy=manual;

System altered.

SQL> alter system set result_cache_mode=manual scope=memory;

System altered.

SQL> select username,sql_text from dba_audit_trail where sql_text like 'alter system%';

USERNAME   SQL_TEXT
---------- ----------------------------------------------------------------------
ADAM       alter system set processes=200 scope=spfile
ADAM       alter system set parallel_degree_policy=manual
ADAM       alter system set result_cache_mode=manual scope=memory

Conclusion: ALTER SYSTEM commands can be audited very comfortable into the audit trail as long as you don’t do them as SYS. All actions of SYS can be audited also with a different approach. As always: Don’t believe it, test it 🙂

, ,

  1. #1 von santosh varma am Februar 26, 2013 - 12:17

    Good post…..

  2. #2 von rupa am Februar 28, 2017 - 11:14

    What if we need to log the statements made in a particular interval, time period this is dialog way of checking the status of alter system, automatically if we need to set up audit logs how can we achieve it ? Appreciate if you have a solution for it.

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s

Diese Seite verwendet Akismet, um Spam zu reduzieren. Erfahre, wie deine Kommentardaten verarbeitet werden..