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 :-)

About these ads

, ,

  1. #1 by santosh varma on February 26, 2013 - 12:17

    Good post…..

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

Follow

Get every new post delivered to your Inbox.

Join 2,802 other followers

%d bloggers like this: