Oracle Secrets Database

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Dec 2 15:34:49 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SHOW PARAMETER audit syslog level

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/POWER/ad
ump
audit_syslog_level string
audit_sys_operations boolean FALSE
audit_trail string NONE

SQL> SELECT value FROM v$parameter WHERE name='audit syslog level';

no rows selected

SQL>

Yet, when executing CONNECT / AS SYSDBA, the facility and level logged in /var/adm/messages
on Solaris is “user.notice”:
Feb 21 11:45:52 dbserver Oracle Audit[27742]: [ID 441842 user.notice]
ACTION : 'CONNECT'
Feb 21 11:45:52 dbserver DATABASE USER: '/'
Feb 21 11:45:52 dbserver PRIVILEGE : SYSDBA
Feb 21 11:45:52 dbserver CLIENT USER: oracle
Feb 21 11:45:52 dbserver CLIENT TERMINAL: pts/3
Feb 21 11:45:52 dbserver STATUS: 0
If an SPFILE is used, the full setting is available by querying V$SPPARAMETER:

SQL> SELECT value FROM v$spparameter WHERE name='audit syslog level';
VALUE
-----------
user.notice

Auditing Non-Privileged Users
Of course, you may also direct audit records pertaining to non-privileged users to the system log by setting

AUDIT TRAIL=OS in addition to AUDIT SYSLOG LEVEL. Non-privileged users cannot delete audit trails logging their actions. The search for perpetrators with queries against auditing views, such as DBA AUDIT STATEMENT or DBA AUDIT OBJECT, is easier than searching the system log. For these reasons, keeping the audit trails of non-privileged users inside the database with

AUDIT TRAIL=DB is preferred. With the latter setting, audit trails are written to the table SYS.AUD$ and may be queried through the aforementioned data dictionary views. Setting AUDIT TRAIL=NONE switches off auditing of actions by non-privileged users.

SQL> AUDIT CONNECT BY appuser /* audit trail=os set */;
entries similar to the following are written to the syslog facility (example from Solaris):
Feb 21 11:41:14 dbserver Oracle Audit[27684]: [ID 930208 user.notice]
SESSIONID: "15" ENTRYID: "1" STATEMENT: "1" USERID: "APPUSER"
USERHOST: "dbserver" TERMINAL: "pts/3" ACTION: "100" RETURNCODE: "0"
COMMENT$TEXT: "Authenticated by: DATABASE" OS$USERID: "oracle"
PRIV$USED: 5
Another entry is added to /var/adm/messages when a database session ends:
Feb 21 11:44:41 dbserver Oracle Audit[27684]: [ID 162490 user.notice]
SESSIONID: "15" ENTRYID: "1" ACTION: "101" RETURNCODE: "0"
LOGOFF$PREAD: "1" LOGOFF$LREAD: "17" LOGOFF$LWRITE: "0" LOGOFF$DEAD:
"0" SESSIONCPU: "2"
Note that additional data provided on the actions LOGON (100) and LOGOFF (101) conforms
to the columns of the view DBA AUDIT SESSION. Translation from action numbers to action
names is done via the view AUDIT ACTIONS as in this example:
SQL> SELECT action, name FROM audit actions WHERE action IN (100,101)
ACTION NAME
------ ------
100 LOGON
101 LOGOFF

When AUDIT SYSLOG LEVEL=AUTH.INFO, AUDIT SYS OPERATIONS=FALSE and AUDIT TRAIL=NONE,
CONNECT, STARTUP, and SHUTDOWN are logged via syslog. With these settings, an instance shutdown on Solaris writes entries similar to the following to /var/adm/messages:

Feb 21 14:40:01 dbserver Oracle Audit[29036]:[ID 63719 auth.info] ACTION:'SHUTDOWN'
Feb 21 14:40:01 dbserver DATABASE USER: '/'
Feb 21 14:40:01 dbserver PRIVILEGE : SYSDBA
Feb 21 14:40:01 dbserver CLIENT USER: oracle
Feb 21 14:40:01 dbserver CLIENT TERMINAL: pts/3
Feb 21 14:40:01 dbserver STATUS: 0

When AUDIT SYSLOG LEVEL=AUTH.INFO, AUDIT SYS OPERATIONS=TRUE, and AUDIT TRAIL=NONE,
SQL and PL/SQL statements executed with SYSDBA or SYSOPER privileges are also logged via syslog. Dropping a user after connecting with / AS SYSDBA results in a syslog entry similar to the one shown here:

Feb 21 14:46:53 dbserver Oracle Audit[29170]: [ID 853627 auth.info]
ACTION : 'drop user appuser'
Feb 21 14:46:53 dbserver DATABASE USER: '/'
Feb 21 14:46:53 dbserver PRIVILEGE : SYSDBA
Feb 21 14:46:53 dbserver CLIENT USER: oracle
Feb 21 14:46:53 dbserver CLIENT TERMINAL: pts/3
Feb 21 14:46:53 dbserver STATUS: 0