Database Reference
In-Depth Information
Searching the Audit Trail
Oracle syslog audit trail can be searched from the DB using SQL via an external table, which allows you to query
compressed syslog files through a directory (note
gzcat
here is an alias to
gunzip -c
). Execute the following
commands as
SYS
, or set up a separate schema owner if you prefer.
create or replace directory log_dir5 as '/tmp';
create or replace directory exec_dir as '/usr/bin';
drop table sys.ext_table_auditlog5;
create table sys.ext_table_auditlog5 (
ACTION Varchar2(4000),
CLIENTIDENTIFIER Varchar2(4000),
EXTENDED_TIMESTAMP Varchar2(4000),
GLOBAL_UID Varchar2(4000),
GRANTEE Varchar2(4000),
INSTANCE_NUMBER Varchar2(4000),
OBJECT_NAME Varchar2(4000),
OS_PRIVILEGE Varchar2(4000),
OS_PROCESS Varchar2(4000),
OS_USER Varchar2(4000),
OBJECT_SCHEMA Varchar2(4000),
PRIV_USED Varchar2(4000),
RETURNCODE Varchar2(4000),
SCN Varchar2(4000),
SES_ACTIONS Varchar2(4000),
SESSION_ID Varchar2(4000),
SQL_BIND Varchar2(4000),
SQL_TEXT Varchar2(4000),
AUTH_PRIVILEGES Varchar2(4000),
TERMINAL Varchar2(4000),
EXTENDED_TIMESTAMP2 Varchar2(4000),
OS_HOST Varchar2(4000),
DB_USER Varchar2(4000),
STATEMENTID Varchar2(4000),
ENTRYID Varchar2(4000)
)
organization external (
type oracle_loader
default directory log_dir5
access parameters (
records delimited by newline
PREPROCESSOR exec_dir:'gzcat'
BADFILE log_dir5: 'syslog.bad'
LOGFILE log_dir5: 'syslog_logfile.log'
fields terminated by ' ' optionally enclosed by '"'
missing field values are null
)
location ('ubrl.com.gz')
)
reject limit unlimited;
