Auditing sys administrative users

By using standard auditing, operations performed against database objects by sys or users with sysdba and sysoper privileges are not audited. Only details about logon including the terminal and the date are audited by mandatory auditing. This recipe will show you how to enable the audit for sys users.

Getting ready

All steps will be performed on the HACKDB database.

How to do it...

  1. In a separate terminal open /var/log/oracle_audit.log with the tail –f command. From a second terminal connect as sysdba and issue a count against the hr.employees table:
    SQL> conn / as sysdba  
Connected.
SQL> select count(*) from hr.employees;
  COUNT(*)
----------
       107
  2. If you now look at /var/opt/oracle_audit.log you will see that nothing was recorded.
  3. Connect as sysdba and modify audit_sys_operation to true as follows:
    SQL> alter system  set audit_sys_operations=true scope=spfile;
  4. Bounce the database.
  5. Connect as sysdba and reissue the count against hr.employees:
    SQL> conn / as sysdba  
Connected.
SQL> select count(*) from hr.employees;
  COUNT(*)
----------
       107
  6. Now if you look in /var/log/oracle_audit.log you should see that the previous operation was audited this time:
    Sep 16 23:34:41 nodeorcl1 Oracle Audit[3492]: LENGTH : '186' ACTION :[33] 'select count(*) from hr.employees' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/0' STATUS:[1] '0' DBID:[10] '2310990645'

How it works...

The audit trails for users with sysdba and sysoper roles once enabled are always generated externally using operating system files in a location specified by audit_file_dest or the default locations (ORACLE_BASE/admin/DB_UNIQUE_NAME/adump or $ORACLE_HOME/rdbms/audit) regardless of the audit_trail parameter setting.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.221.163.13