Database Reference
In-Depth Information
Then we can select the syslog out as follows:
select * from sys.ext_table_auditlog where sql_text like '%GRANT%';
Jan 1 01:15:59 aelab2-5 Oracle Audit[4736]: ACTION : 'grant dba to scott' DATABASE USER: '/'
PRIVILEGE : SYSDBA CLIENT USER: oracle CLIENT TERMINAL: pts/1 STATUS: 0
A caveat to reliably using external tables is that the fields contained within syslog messages can change, and
therefore there maybe be some corruption of read output caused by columns drifting. This means that the best way to
search through syslog can be from the OS itself. Note that if you want to perform relational operations on OS syslog, it
can be done easily at the OS anyway using
awk
. Please see
http://matt.might.net/articles/sql-in-the-shell/
for
examples. However, the most common security activity for syslog is to
grep
for a known string.
Basic OS commands for searching the audit trail are as follows:
Grep through the files for a string:
for file in */*/*.gz; do gzcat "$file"| egrep -i 'grant sysdba to'; done
Directory printout:
find . -print | sed -e 's;[^/]*/;|____;g;s;____|; |;g'
Find a specific server from root of the dated directories:
find . -print | egrep sscllinuxora1
Print out the filename that contains string “dba_registry”
for file in app*gz ; do gzcat $file | sed -e 's/^/'$file':/' | grep dba_registry; done
Solaris bash search recursively:
find . -name filename -print | xargs grep 'stringtosearchfor' | sort -u
Tree command for Solaris:
find . -type d -print | sed -e 's;[^/]*/;|____;g;s;____|; |;g'
Ongoing Maintenance
The Unix SA may wish to remove the new Oracle syslog entries from flooding a pre-existing
*.info
wide syslog entry,
which has already been pointing to the local OS from
syslog.conf,
by using the very useful
facility.
none
to “minus
out” Oracle syslog from that local, pre-existing syslog audit trail. This will avoid the potentially high-volume Oracle
syslog that is being sent to the network from also filling the local disk due to a legacy syslog setting (
*.info
), which is
pointing locally and inadvertently catching the Oracle syslog. For example, the legacy local
syslog.conf
entry could
look like the following, and may have been in place for many years:
*.info; /localpath/pre-existing_OS_syslog_and_new_orasyslog.log
