Extract Oracle Audit Records Script

Today I'll share with you one of my scripts I'm using to easily retrieve the audit records of an oracle database user.

Note: Auditing should be enabled on the database or the script will return no rows.
              To enable auditing please check this article:
              http://www.oracle-base.com/articles/10g/auditing-10gr2.php

Download the script from this link:

https://www.dropbox.com/s/24ht510venaogwn/audit_details.sh?dl=0

Once you run this script it will let you choose the database you want to retrieve data from (in case that you have multiple running databases on the server), then it will ask you to enter the username, and lastly will ask you to enter the number of days back you want to retrieve audit data or enter a specific date.

This script is very easy to use, it has been tested on Linux and SUN environments.

DISCLAIMER: THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS".

Here is the script code, in case the download link is not working with you:

	# ##############################################################################################
	# This script shows AUDIT records for DB User.
	# To be run by ORACLE user
	# # # #
	# Author: Mahmmoud ADEL # # # # ###
	# Created: 25-04-2013 # # # # #
	#
	# Modified: 07-03-2019 Allow the user to control the display of LOGIN/LOGOFF data.
	# 10-03-2019 Added the option of excluding specific audit action from the report.
	# ##############################################################################################
	# ###########################
	# Listing Available Instances:
	# ###########################
	echo
	echo "=================================================================="
	echo "This Script Retreives AUDIT data for a user if auditing is enabled."
	echo "=================================================================="
	echo
	sleep 1
	# #######################################
	# Excluded INSTANCES:
	# #######################################
	# Here you can mention the instances the script will IGNORE and will NOT run against:
	# Use pipe "|" as a separator between each instance name.
	# e.g. Excluding: -MGMTDB, ASM instances:
	EXL_DB="\-MGMTDB|ASM" #Excluded INSTANCES [Will not get reported offline].
	# Count Instance Numbers:
	INS_COUNT=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|wc -l )
	# Exit if No DBs are running:
	if [ $INS_COUNT -eq 0 ]
	then
	echo No Database Running !
	exit
	fi
	# If there is ONLY one DB set it as default without prompt for selection:
	if [ $INS_COUNT -eq 1 ]
	then
	export ORACLE_SID=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )
	# If there is more than one DB ASK the user to select:
	elif [ $INS_COUNT -gt 1 ]
	then
	echo
	echo "Select the Instance You Want To Run this script Against:[Enter the number]"
	echo "-------------------------------------------------------"
	select DB_ID in $( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )
	do
	if [ -z "${REPLY##[0-9]*}" ]
	then
	export ORACLE_SID=$DB_ID
	echo Selected Instance:
	echo
	echo "********"
	echo $DB_ID
	echo "********"
	echo
	break
	else
	export ORACLE_SID=${REPLY}
	break
	fi
	done
	fi
	# Exit if the user selected a Non Listed Number:
	if [ -z "${ORACLE_SID}" ]
	then
	echo "You've Entered An INVALID ORACLE_SID"
	exit
	fi
	# #########################
	# Getting ORACLE_HOME
	# #########################
	ORA_USER=`ps -ef|grep ${ORACLE_SID}|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk '{print $1}'|tail -1`
	USR_ORA_HOME=`grep ${ORA_USER} /etc/passwd| cut -f6 -d ':'|tail -1`
	# SETTING ORATAB:
	if [ -f /etc/oratab ]
	then
	ORATAB=/etc/oratab
	export ORATAB
	## If OS is Solaris:
	elif [ -f /var/opt/oracle/oratab ]
	then
	ORATAB=/var/opt/oracle/oratab
	export ORATAB
	fi
	# ATTEMPT1: Get ORACLE_HOME using pwdx command:
	PMON_PID=`pgrep -lf _pmon_${ORACLE_SID}|awk '{print $1}'`
	export PMON_PID
	ORACLE_HOME=`pwdx ${PMON_PID}|awk '{print $NF}'|sed -e 's/\/dbs//g'`
	export ORACLE_HOME
	#echo "ORACLE_HOME from PWDX is ${ORACLE_HOME}"
	# ATTEMPT2: If ORACLE_HOME not found get it from oratab file:
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	## If OS is Linux:
	if [ -f /etc/oratab ]
	then
	ORATAB=/etc/oratab
	ORACLE_HOME=`grep -v '^\#' $ORATAB | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
	export ORACLE_HOME
	## If OS is Solaris:
	elif [ -f /var/opt/oracle/oratab ]
	then
	ORATAB=/var/opt/oracle/oratab
	ORACLE_HOME=`grep -v '^\#' $ORATAB | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
	export ORACLE_HOME
	fi
	#echo "ORACLE_HOME from oratab is ${ORACLE_HOME}"
	fi
	# ATTEMPT3: If ORACLE_HOME is still not found, search for the environment variable: [Less accurate]
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	ORACLE_HOME=`env|grep -i ORACLE_HOME|sed -e 's/ORACLE_HOME=//g'`
	export ORACLE_HOME
	#echo "ORACLE_HOME from environment is ${ORACLE_HOME}"
	fi
	# ATTEMPT4: If ORACLE_HOME is not found in the environment search user's profile: [Less accurate]
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	ORACLE_HOME=`grep -h 'ORACLE_HOME=\/' $USR_ORA_HOME/.bash_profile $USR_ORA_HOME/.*profile | perl -lpe'$_ = reverse' |cut -f1 -d'=' | perl -lpe'$_ = reverse'|tail -1`
	export ORACLE_HOME
	#echo "ORACLE_HOME from User Profile is ${ORACLE_HOME}"
	fi
	# ATTEMPT5: If ORACLE_HOME is still not found, search for orapipe: [Least accurate]
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	ORACLE_HOME=`locate -i orapipe|head -1|sed -e 's/\/bin\/orapipe//g'`
	export ORACLE_HOME
	#echo "ORACLE_HOME from orapipe search is ${ORACLE_HOME}"
	fi
	# TERMINATE: If all above attempts failed to get ORACLE_HOME location, EXIT the script:
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	echo "Please export ORACLE_HOME variable in your .bash_profile file under oracle user home directory in order to get this script to run properly"
	echo "e.g."
	echo "export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1"
	exit
	fi
	# ########################################
	# Exit if the user is not the Oracle Owner:
	# ########################################
	CURR_USER=`whoami`
	if [ ${ORA_USER} != ${CURR_USER} ]; then
	echo ""
	echo "You're Running This Sctipt with User: \"${CURR_USER}\" !!!"
	echo "Please Run This Script With The Right OS User: \"${ORA_USER}\""
	echo "Script Terminated!"
	exit
	fi
	# #########################
	# SQLPLUS Section:
	# #########################
	# PROMPT FOR VARIABLES:
	# ####################
	echo
	echo "Enter The USERNAME you want to retrieve its Audit Data: [Blank Value means ALL Users]"
	echo "======================================================"
	while read DB_USERNAME
	do
	case $DB_USERNAME in
	# NO VALUE PROVIDED:
	"") USERNAME_COND="";break ;;
	#*) USERNAME_COND="USERNAME=upper('${DB_USERNAME}') or OS_USERNAME='${DB_USERNAME}' AND";break ;;
	*) USERNAME_COND="USERNAME=upper('${DB_USERNAME}') AND";break ;;
	esac
	done
	echo
	echo "Do you want to include LOGIN/LOGOFF information: [Y|N Default [N]]"
	echo "==============================================="
	while read LOGININFO
	do
	case ${LOGININFO} in
	# NO VALUE PROVIDED:
	""|N|n|NO|no|No) export EXCLUDELOGINDATA="AND ACTION_NAME not like 'LOGO%' AND";break ;;
	Y|y|YES|yes|Yes) export EXCLUDELOGINDATA="";break ;;
	*) echo "Please enter a VALID answer [Y|N]" ;;
	esac
	done
	echo
	echo "Do you want to EXCLUDE a specific Action from the list:"
	echo "======================================================"
	echo "[Blank means INCLUDE ALL Actions Or Provide One of These Action to exclude: SELECT, ALTER, DROP, CREATE, TRUNCATE, GRANT or REVOKE]"
	while read EXCLUDEDACTION
	do
	case ${EXCLUDEDACTION} in
	# NO VALUE PROVIDED:
	"") export EXCLUDEDACTION="null";break ;;
	*) export EXCLUDEDACTION;break ;;
	esac
	done
	echo
	echo "How [MANY DAYS BACK] you want to retrieve AUDIT data? [Default 1]"
	echo "====================================================="
	echo "OR: Enter A Specific DATE in this FORMAT [DD-MM-YYYY] e.g. 25-01-2011"
	echo "== ================================================================="
	while read NUM_DAYS
	do
	case $NUM_DAYS in
	# User PROVIDED a NON NUMERIC value:
	*[!0-9]*) echo;echo "Retreiving AUDIT data for User [${DB_USERNAME}] on [${NUM_DAYS}] ..."
	${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
	set linesize 157
	col OS_USERNAME for a15
	col DB_USERNAME for a15
	--col EXTENDED_TIMESTAMP for a36
	col DATE for a22
	col OWNER for a10
	col OBJ_NAME for a25
	col USERHOST for a21
	col ACTION_NAME for a25
	col ACTION_OWNER_OBJECT for a55
	--select extended_timestamp,OS_USERNAME,USERNAME DB_USERNAME,USERHOST,ACTION_NAME||' '||OWNER||' . '||OBJ_NAME ACTION_OWNER_OBJECT
	select to_char(extended_timestamp,'DD-Mon-YYYY HH24:MI:SS')"DATE",OS_USERNAME,USERNAME DB_USERNAME,USERHOST,ACTION_NAME||' '||OWNER||' . '||OBJ_NAME ACTION_OWNER_OBJECT
	from dba_audit_trail
	where ${USERNAME_COND}
	timestamp > SYSDATE-${NUM_DAYS} ${EXCLUDELOGINDATA}
	ACTION_NAME not like upper ('%${EXCLUDEDACTION}%')
	--AND TRUNC(extended_timestamp) = TO_DATE('${NUM_DAYS}','DD-MM-YYYY')
	order by EXTENDED_TIMESTAMP;
	PROMPT
	EOF
	exit
	break ;;
	# NO VALUE PROVIDED:
	"") export NUM_DAYS=1;echo;echo "Retreiving AUDIT data in the last 24 Hours ... [Please Wait]";break ;;
	# A NUMERIC VALUE PROVIDED:
	*) export NUM_DAYS;echo;echo "Retreiving AUDIT data in the last ${NUM_DAYS} Days ... [Please Wait]";break ;;
	esac
	done
	# Execution of SQL Statement:
	# ##########################
	${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
	set linesize 190 pages 1000
	col OS_USERNAME for a15
	col DB_USERNAME for a15
	--col EXTENDED_TIMESTAMP for a36
	col DATE for a22
	col OWNER for a10
	col OBJ_NAME for a25
	col USERHOST for a21
	col ACTION_NAME for a25
	col ACTION_OWNER_OBJECT for a80
	--select extended_timestamp,OS_USERNAME,USERNAME,USERHOST,ACTION_NAME||' '||OWNER||' . '||OBJ_NAME ACTION_OWNER_OBJECT
	select to_char(extended_timestamp,'DD-Mon-YYYY HH24:MI:SS')"DATE",OS_USERNAME,USERNAME DB_USERNAME,USERHOST,ACTION_NAME||' '||OWNER||' . '||OBJ_NAME ACTION_OWNER_OBJECT
	from dba_audit_trail
	where ${USERNAME_COND}
	timestamp > SYSDATE-${NUM_DAYS} ${EXCLUDELOGINDATA}
	ACTION_NAME not like upper ('%${EXCLUDEDACTION}%')
	order by EXTENDED_TIMESTAMP;
	PROMPT
	EOF
	# #############
	# END OF SCRIPT
	# #############
	# REPORT BUGS to: <mahmmoudadel@hotmail.com>.
	# DISCLAIMER: THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS".
	# DOWNLOAD THE LATEST VERSION OF DATABASE ADMINISTRATION BUNDLE FROM:
	# http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html

view raw audit_records hosted with ❤ by GitHub