in Oracle

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>

.
.
.