In our database there is turned on auditing on some operations and audit records go to OS.
AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }
DB Auditing is enabled. Audit records will be written to the
SYS.AUD$ table.
OS Auditing is enabled. Audit records will be written to an
audit trail in the operating system.
db,extended As db, but the SQL_BIND and SQL_TEXT columns are also populated.
NONE Auditing is disabled (default).
xml- Auditing is enabled, with all audit records stored
as XML format OS files.
xml,extended As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
TRUE This value is supported for backward-compatibility
with versions of Oracle;it is equivalent to the DB value.
FALSE This value is supported for backward-compatibility
with versions of Oracle;it is equivalent to the NONE value.
SQL> SHOW PARAMETER AUDIT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /opt/app/oracle/admin/oravr/adump
audit_sys_operations boolean TRUE
audit_syslog_level string LOCAL7.INFO
audit_trail string OS
SQL>
1. Connect to a database instance as sysdba user
SQL> connect / as sysdba
2. Set audit trail to OS
SQL> alter system set audit_trail=OS;
3. Enable auditing for system users
SQL> alter system set audit_sys_operations=TRUE;
4. Set rsyslog facility and severity(needs database restart)
SQL> alter system set audit_syslog_level=local7.info scope=spfile sid='*';
5. Restart database
SQL> shutdown immediate;
SQL> startup;
6. Edit rsyslog.conf file
#Saving oracle database audit records
local7.info /opt/syslog/oracle_audit.log
--Optional
#Send oracle database audit trail to remote rsyslog server
local5.info @192.168.2.54
7. Restart rsyslog service
# service rsyslog restart
Shutting down system logger: [ OK ]
Starting system logger: [ OK ]
8. It is better to limit the size for audit log, or it may fill the space:
# vi /etc/logrotate.d/oracle.audit
#Created by MariK
/opt/syslog/oracle_audit.log {
rotate 3
compress
missingok
notifempty
size 40G
postrotate
service rsyslog restart
endscript
}
To check the syntax run :
# logrotate /etc/logrotate.d/oracle.audit
Example:
oravr1.oravr.in:/home/oracle> tailf /opt/syslog/oracle_audit.log
Jul 22 06:02:41 oravr1 Oracle Audit[22070]: LENGTH: "287" SESSIONID:[9] "162326064" ENTRYID:[4] "8009" STATEMENT:[5] "52545" USERID:[6] "DBSNMP" USERHOST:[32] "oravr1.oravr.in" TERMINAL:[7] "unknown" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$NAME:[8] "PROFILE$" OS$USERID:[6] "oracle" DBID:[10] "4040881803"
Jul 22 06:02:41 oravr1 Oracle Audit[22070]: LENGTH: "288" SESSIONID:[9] "162326064" ENTRYID:[4] "8010" STATEMENT:[5] "52545" USERID:[6] "DBSNMP" USERHOST:[32] "oravr1.oravr.in" TERMINAL:[7] "unknown" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$NAME:[9] "DBA_USERS" OS$USERID:[6] "oracle" DBID:[10] "4040881803"
Jul 22 06:02:41 oravr1 Oracle Audit[22070]: LENGTH: "283" SESSIONID:[9] "162326064" ENTRYID:[4] "8011" STATEMENT:[5] "52545" USERID:[6] "DBSNMP" USERHOST:[32] "oravr1.oravr.in" TERMINAL:[7] "unknown" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$NAME:[4] "DUAL" OS$USERID:[6] "oracle" DBID:[10] "4040881803"
Jul 22 06:02:41 oravr1 Oracle Audit[22070]: LENGTH: "302" SESSIONID:[9] "162326064" ENTRYID:[4] "8012" STATEMENT:[5] "52570" USERID:[6] "DBSNMP" USERHOST:[32] "oravr1.oravr.in" TERMINAL:[7] "unknown" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$NAME:[4] "OBJ$" OS$USERID:[6] "oracle" DBID:[10] "4040881803" PRIV$USED:[3] "237"
Jul 22 06:02:41 oravr1 Oracle Audit[22070]: LENGTH: "284" SESSIONID:[9] "162326064" ENTRYID:[4] "8013" STATEMENT:[5] "52570" USERID:[6] "DBSNMP" USERHOST:[32] "oravr1.oravr.in" TERMINAL:[7] "unknown" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$NAME:[5] "USER$" OS$USERID:[6] "oracle" DBID:[10] "4040881803"
Jul 22 06:02:41 oravr1 Oracle Audit[22070]: LENGTH: "283" SESSIONID:[9] "162326064" ENTRYID:[4] "8014" STATEMENT:[5] "52570" USERID:[6] "DBSNMP" USERHOST:[32] "oravr1.oravr.in" TERMINAL:[7] "unknown" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$NAME:[4] "OBJ$" OS$USERID:[6] "oracle" DBID:[10] "4040881803"
Jul 22 06:02:41 oravr1 Oracle Audit[22070]: LENGTH: "284" SESSIONID:[9] "162326064" ENTRYID:[4] "8015" STATEMENT:[5] "52570" USERID:[6] "DBSNMP" USERHOST:[32] "oravr1.oravr.in" TERMINAL:[7] "unknown" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$NAME:[5] "USER$" OS$USERID:[6] "oracle" DBID:[10] "4040881803"
Jul 22 06:02:41 oravr1 Oracle Audit[22070]: LENGTH: "300" SESSIONID:[9] "162326064" ENTRYID:[4] "8016" STATEMENT:[5] "52570" USERID:[6] "DBSNMP" USERHOST:[32] "oravr1.oravr.in" TERMINAL:[7] "unknown" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$NAME:[20] "_CURRENT_EDITION_OBJ" OS$USERID:[6] "oracle" DBID:[10] "4040881803"
Jul 22 06:02:41 oravr1 Oracle Audit[22070]: LENGTH: "284" SESSIONID:[9] "162326064" ENTRYID:[4] "8017" STATEMENT:[5] "52570" USERID:[6] "DBSNMP" USERHOST:[32] "oravr1.oravr.in" TERMINAL:[7] "unknown" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$NAME:[5] "USER$" OS$USERID:[6] "oracle" DBID:[10] "4040881803"
Jul 22 06:02:41 oravr1 Oracle Audit[22070]: LENGTH: "290" SESSIONID:[9] "162326064" ENTRYID:[4] "8018" STATEMENT:[5] "52570" USERID:[6] "DBSNMP" USERHOST:[32] "oravr1.oravr.in" TERMINAL:[7] "unknown" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$NAME:[10] "_BASE_USER" OS$USERID:[6] "oracle" DBID:[10] "4040881803"
