Search My Oracle Blog

Custom Search

03 December, 2015

Auditing DBMS_STATS usage

(I will be returning to the Tracing series .... but a quick diversion because I had received a request for assistance on auditing DBMS_STATS usage)

First, I setup auditing

SQL> alter system set audit_trail='DB_EXTENDED' scope=SPFILE;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size      2260088 bytes
Variable Size    750781320 bytes
Database Buffers   310378496 bytes
Redo Buffers      5517312 bytes
Database mounted.
Database opened.
SQL> audit execute on sys.dbms_stats;

Audit succeeded.

SQL> 

Next, I run a DBMS_STATS call and check the audit trail for it.

SQL> connect hemant/hemant
Connected.
SQL> create table obj_all_list as select * from all_objects;

Table created.

SQL> execute dbms_stats.gather_table_stats('','OBJ_ALL_LIST',method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

SQL> execute dbms_stats.lock_table_stats('','OBJ_ALL_LIST');

PL/SQL procedure successfully completed.

SQL> 

Now, I check the Audit Trail.

SQL> connect / as sysdba
Connected.
SQL> set pages600
SQL> select to_char(timestamp,'DD-MON HH24:MI:SS'), username, userhost, sql_text
  2  from dba_audit_object
  3  where obj_name = 'DBMS_STATS'
  4  and timestamp > trunc(sysdate)
  5  order by timestamp;

TO_CHAR(TIMESTAMP,'DD-MO USERNAME
------------------------ ------------------------------
USERHOST
--------------------------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
03-DEC 22:58:35   HEMANT
ora11204
BEGIN dbms_stats.gather_table_stats('','OBJ_ALL_LIST',method_opt=>'FOR ALL COLUM
NS SIZE 1'); END;


03-DEC 22:58:50   HEMANT
ora11204
BEGIN dbms_stats.lock_table_stats('','OBJ_ALL_LIST'); END;



SQL> 

Note : Execution of DBMS_STATS by SYS would not be audited in the database audit trail table.  As you can see below :

SQL> execute dbms_stats.unlock_table_stats('HEMANT','OBJ_ALL_LIST');

PL/SQL procedure successfully completed.

SQL> select to_char(timestamp,'DD-MON HH24:MI:SS'), username, userhost, sql_text
  2  from dba_audit_object
  3  where obj_name = 'DBMS_STATS'
  4  and timestamp > trunc(sysdate)
  5  order by timestamp;

TO_CHAR(TIMESTAMP,'DD-MO USERNAME
------------------------ ------------------------------
USERHOST
--------------------------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
03-DEC 22:58:35   HEMANT
ora11204
BEGIN dbms_stats.gather_table_stats('','OBJ_ALL_LIST',method_opt=>'FOR ALL COLUM
NS SIZE 1'); END;


03-DEC 22:58:50   HEMANT
ora11204
BEGIN dbms_stats.lock_table_stats('','OBJ_ALL_LIST'); END;



SQL> 

I would need AUDIT_SYS_OPERATIONS and AUDIT_FILE_DEST to capture audit of actions by SYS.

SQL> show parameter audit

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest        string  /u01/app/oracle/admin/orcl/adu
       mp
audit_sys_operations       boolean  FALSE
audit_syslog_level       string
audit_trail        string  DB_EXTENDED
SQL>

.
.
.

No comments:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016