All about Database Administration, Tips & Tricks

Hello,

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