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;

Oracle Database 11g Enterprise Edition Release - 64bit Production
PL/SQL Release - Production
CORE    Production
TNS for Linux: Version - Production
NLSRTL Version - 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

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%';

---------- ----------------------------------------------------------------------
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 by santosh varma on February 26, 2013 - 12:17

    Good post…..

  2. #2 by rupa on February 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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: