Oracle 23c New Features for DBA'sHello,
We want to report general audit report daily from our databases who have made manual modifications to the database so it may be helpful for us to retrospect when needed.
How this report looks like, find useful?
User Schema Name Object INSERTS DELETES UPDATES ALTER GRANTS DROPS TRUNCATE -------------- -------------------- ----------------------- ------- ------- ------- ------- ------- ------- -------- insights SYSADM USERN 0 0 2 0 0 0 0 SYSADM RUNCTL_BDEP 0 0 1 0 0 0 0 SYSADM PS_SYS_DATA 0 0 1 0 0 0 0 SYSADM PS_PER 0 0 1 0 0 0 0 test1 SYSADM SS_MTHD 0 0 2 0 0 0 0 SYSADM SHOP_CART 0 1 0 0 0 0 0 SYSADM ITEM_HECS_ELEC 0 0 4 0 0 0 0 SYSADM USER_WISH_LIST_TBL 0 2 0 0 0 0 0
So here it is what we planned.
- We want to store the audit files in s3 for backup purposes.
- We want to store the audit files in xml format rather general oracle audit file format, in order to have a schema on read for our datalake.
- As its RDS , first we have to download all the files and then cleanse , remove unnecessary xml records and keep only and extract them as above report
- Purge xml audit files in RDS as such they consume lot of space in rds directories
Note:- By the way, you can use v$xml_audit_trail, but I find not useful and also I want to do other things like mailing, purging, storing to s3 etc.
Further, What you need before you run this scripts are as below
- An ec2 instance (ondemand) which having IAM role to read access to RDS and access to s3 bucket
- Install AWS cli and set your credentials or above IAM role is enough
- Oracle Client Installed for purging of files or you can manage different way
- Python 2.7 Installed and XML
Download Scripts from Here
Download_Audit_Files.sh does below.
- Download, cleanse the audit files
- Run analyze.py script to generate report above
- Send mail for that report
- Purge XML Files from Oracle RDS lesser than 1 day
cat Download_Audit_Files.sh
########################################
#Configuration
########################################
AUDITLOGDATE=`date +"%Y%m%d" --date="$2 days ago"`
AUDIT_DIR=/backups/washexports/audit
S3BUCKET=s3://databaselogs.geeksinsight.com/database/audit/$1/
DBUSERNAME=dbmanager
DBPASSWORD=password
SCRIPT_DIR=${SCRIPTS_DIR}
MAIL_RECEIPIENT=DBAUDIT mail@123.com,mail2@123.com
#######################################
# Export variables
#######################################
export AWS_DEFAULT_REGION=ap-southeast-2
export ORACLE_HOME=/opt/oracle/app/oracle/product/11.2.0/client_1
export PATH=/usr/local/rvm/gems/ruby-2.1.5/bin:/usr/local/rvm/gems/ruby-2.1.5@global/bin:/usr/local/rvm/rubies/ruby-2.1.5/bin:/usr/lib64/qt-3.3/bin:/opt/aws/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/opt/oracle/app/oracle/product/11.2.0/client_1/bin:/usr/local/rvm/bin:/root/bin
##############################################
# Download the xml files for given instance
##############################################
for var in `aws rds describe-db-log-files --db-instance-identifier $1 --filename-contains "xml" | grep LogFileName | sort -r | cut -d '"' -f4- |rev | cut -c 3- | rev | grep _$AUDITLOGDATE`
do
aws rds download-db-log-file-portion --db-instance-identifier $1 --log-file-name $var --max-items 99999999 --starting-token 0 --output text >> ${AUDIT_DIR}/${AUDITLOGDATE}_$1.xml
done
######################################
# Cleanse the Audit Files
######################################
#sed -i '/pscs/d' ${AUDIT_DIR}/${AUDITLOGDATE}_$1.xml
sed -i '/<\/Audit>/d' ${AUDIT_DIR}/${AUDITLOGDATE}_$1.xml
sed -i '/<\/AuditRecord>/d' ${AUDIT_DIR}/${AUDITLOGDATE}_$1.xml
sed -i '/xml/d' ${AUDIT_DIR}/${AUDITLOGDATE}_$1.xml
sed -i '/Version/d' ${AUDIT_DIR}/${AUDITLOGDATE}_$1.xml
sed -i -e 's/<\/DBID>/<\/DBID><\/AuditRecord>/g' ${AUDIT_DIR}/${AUDITLOGDATE}_$1.xml
vim -e ${AUDIT_DIR}/${AUDITLOGDATE}_$1.xml <<@@@
g/truncated/.-1,.d
wq
@@@
sed -i '1i\'"<root><Audit>" ${AUDIT_DIR}/${AUDITLOGDATE}_$1.xml
sed -i '$ a\'"</Audit></root>" ${AUDIT_DIR}/${AUDITLOGDATE}_$1.xml
##################################################################
# Generate report using analyze2.py python script
# YOu may need to further customize the script as per your needs
##################################################################
cd ${AUDIT_DIR}
${SCRIPTS_DIR}/audit/analyze2.py ${AUDITLOGDATE}_$1.xml >> ${SCRIPTS_DIR}/logs/Audit_Process_${AUDITLOGDATE}_$1.log
SIZE=`ls -ltr ${AUDIT_DIR}/${AUDITLOGDATE}_$1.xml | tr -s ' ' | cut -d ' ' -f 5`
##################################################################
#Send Email of the report
###################################################################
cat /scripts/db_reconfiguration/logs/Audit_Process_${AUDITLOGDATE}_$1.log | mailx -s "Campus Database Audit Log - ${AUDITLOGDATE}" -a "/scripts/db_reconfiguration/logs/Audit_Process_${AUDITLOGDATE}_$1.log" -r ${MAIL_RECEIPIENT}
###########################################
# Copy final audit xml file to S3 Bucket
###########################################
gzip ${AUDIT_DIR}/${AUDITLOGDATE}_$1.xml
aws s3 cp ${AUDIT_DIR}/${AUDITLOGDATE}_$1.xml.gz ${S3BUCKET}
rm -f ${AUDIT_DIR}/${AUDITLOGDATE}_$1.xml.gz
#####################################################################
## Get Database Endpoint Name
#####################################################################
EPNAME=`aws rds describe-db-instances --db-instance-identifier $1 | grep -i "Address" | cut -d '"' -f4`
echo $EPNAME
####################################################################
## Get Database Name for given Instance
####################################################################
DBNAME=`aws rds describe-db-instances --db-instance-identifier $1 | grep -i "DBName" | cut -d '"' -f4`
echo $DBNAME
###################################################################
# Purge XML files from RDS Instance
##################################################################
sqlplus -s ${DBMANAGER}/${DBPASSWORD}@$EPNAME/$DBNAME <<EOF
set pages 0
set echo off
set lines 1000
set feedback off
set termout off
spool ${SCRIPTS_DIR}/purgexml.sql
select 'exec utl_file.fremove (''ADUMP'','||''''||filename||''''||');' from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('ADUMP')) where to_char(mtime,'DD-MON-YYYY') <= trunc(sysdate)-1;
spool off
@${SCRIPTS_DIR}/purgexml.sql
EOF
sqlplus -s ${DBMANAGER}/${DBPASSWORD}@$EPNAME/$DBNAME <<EOF
BEGIN
sys.DBMS_AUDIT_MGMT.clean_audit_trail(
audit_trail_type => sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML,
use_last_arch_timestamp => true);
END;
/
BEGIN
sys.DBMS_AUDIT_MGMT.clean_audit_trail(
audit_trail_type => sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp => true);
END;
/
EOF
###################################################################
# Remove Xml file
##################################################################
rm -f ${AUDIT_DIR}purgexml.sql
exit 0
Then analyze2.py looks like this, as you see I have filtered out the names of user that I don't want to report, you may keep your own username as required.
#!/usr/bin/python
import sys
import collections
from tabulate import tabulate
try:
import xml.etree.cElementTree as ET
except ImportError:
import xml.etree.ElementTree as ET
results = dict()
elements_done=0
fname = sys.argv[1]
for action,elem in ET.iterparse(sys.argv[1]):
if elem.tag == 'AuditRecord':
if (elem.find('OS_User').tag == 'OS_User' and elem.find('OS_User').text != 'SYSTEM' and elem.find('OS_User').text != 'tomcat' and elem.find('OS_User').text != 'dsadm' and elem.find('OS_User').text != 'rdsdb' and elem.find('OS_User').text != 'rdshm' and elem.find('OS_User').text != 'pscs' and elem.find('OS_User').text != 'dbmanager' and elem.find('OS_User').text != 'root'):
if elem.find('OS_User') != None:
osuser = elem.find('OS_User').text
else:
osuser = 'Unknown'
if elem.find('Object_Name') != None:
obj = elem.find('Object_Name').text
else:
obj = 'Unknown'
if elem.find('DB_User') != None:
usr = elem.find('DB_User').text
else:
usr = 'Unknown'
key=osuser + obj + usr
if not key in results:
results[key] = {'name': osuser, 'object': obj,'dbuser': usr, 'updcnt':0, 'delcnt':0, 'altcnt':0, 'inscnt':0, 'grtcnt':0, 'drpcnt':0, 'trcnt':0}
record = results[key]
if ((elem.find('SesActions') != None and elem.find('SesActions').tag == 'SesActions' and elem.find('SesActions').text == 'S---------------') or (elem.find('Action') != None and elem.find('Action').tag == 'Action' and elem.find('Action').text == '15')):
oname = elem.find('Object_Name').text
record['altcnt'] += 1
if ((elem.find('SesActions') != None and elem.find('SesActions').tag == 'SesActions' and elem.find('SesActions').text == '---S------------') or (elem.find('Action') != None and elem.find('Action').tag == 'Action' and elem.find('Action').text == '7')):
oname = elem.find('Object_Name').text
record['delcnt'] += 1
if ((elem.find('SesActions') != None and elem.find('SesActions').tag == 'SesActions' and elem.find('SesActions').text == '----------S-----') or (elem.find('Action') != None and elem.find('Action').tag == 'Action' and elem.find('Action').text == '6')):
oname = elem.find('Object_Name').text
record['updcnt'] += 1
if ((elem.find('SesActions') != None and elem.find('SesActions').tag == 'SesActions' and elem.find('SesActions').text == '------S---------') or (elem.find('Action') != None and elem.find('Action').tag == 'Action' and elem.find('Action').text == '2')):
oname = elem.find('Object_Name').text
record['inscnt'] += 1
if elem.find('Action') != None and elem.find('Action').tag == 'Action' and elem.find('Action').text == '17':
oname = elem.find('Object_Name').text
record['grtcnt'] += 1
if elem.find('Action') != None and elem.find('Action').tag == 'Action' and elem.find('Action').text == '12':
oname = elem.find('Object_Name').text
record['drpcnt'] += 1
if elem.find('Action') != None and elem.find('Action').tag == 'Action' and elem.find('Action').text == '85':
oname = elem.find('Object_Name').text
record['trcnt'] += 1
elements_done += 1
header_row = '''
User \tSchema Name \tObject \tINSERTS\tDELETES\tUPDATES\tALTER \tGRANTS \tDROPS \tTRUNCATE
-------------- \t-------------------------------\t----------------------- \t-------\t-------\t-------\t-------\t-------\t-------\t--------'''
output_line = '%-15.15s\t%-30.30s\t%-30.30s\t%7d\t%7d\t%7d\t%7d\t%7d\t%7d\t%7d'
current_user = ''
row_count = 0
page_size = 100
for key in collections.OrderedDict(sorted(results.items())):
record = results[key]
if current_user != record['name']:
name = record['name']
current_user = record['name']
if (row_count % page_size) == 0:
name = record['name']
print
print header_row
print output_line%(name, record['dbuser'], record['object'], record['inscnt'], record['delcnt'], record['updcnt'], record['altcnt'], record['grtcnt'], record['drpcnt'], record['trcnt'])
row_count += 1
name=''
And then crontab your script, this way
## Oracle Audit Logs Download and Copy to S3 Bucket and give instance name and num of days 00 07 * * * sh /scripts/db_reconfiguration/audit/Download_Audit_Files.sh rdsinstancename 1
Download Scripts from Here
Hope this helps you to write your own when needed.
-Thanks
Suresh
Follow Me!!!