Modify unix script

sjwales:

I modified some portions of your script to our clients environment. See below:

#!/usr/bin/ksh
#
# Description:
# This script examines Oracle Audit logs for all instances.  Anomalies
# are reported to the DBA's for research.
#
# Usage:
# oracle_audit_scanner.sh

OUTFILE=/tmp/oracle_audit_outfile.log
> $OUTFILE

cd $AUDIT_DIRECTORY

# Create a list of all files created in the last 1 day

find . -name "*.aud" -mtime -1 > /tmp/$$.1

# Process each file

cat /tmp/$$.1 | while read LINE
do
  cnt=`egrep -i 'ALTER|INSERT|UPDATE|DELETE|CREATE|DROP' $LINE | egrep -iv 'alter sess|close normal|dismount|alter_|backup controlfi
le|begin backup|end backup|startup_time|:0|  mount|orasnap|base open|add dataf|add tempf|resize|timestamp# < sysdate-30|created|crea
te more' | wc -l`

  if [ cnt -gt 0 ]
  then
    echo "Processing $LINE :" >> $OUTFILE
    egrep -i 'ALTER|INSERT|UPDATE|DELETE|CREATE|DROP' $LINE  | egrep -iv 'alter sess|close normal|dismount|alter_|backup controlfile
|begin backup|end backup|startup_time|:0|  mount|orasnap|base open|add dataf|add tempf|resize|timestamp# < sysdate-30|created|create more' >> $OUTFILE
  fi
done

# Create an empty temporary file

> /tmp/$$.2
HOSTNAME=`hostname`
echo "The following Audit errors on Host $HOSTNAME have been detected. " >> /tmp/$$.2
echo "Please review ASAP" >> /tmp/$$.2
echo " " >> /tmp/$$.2

SENDIT=1

if [ -s $OUTFILE ]
then
 echo "All OK" > /dev/null
else
  SENDIT=0
  echo "No errors detected today." >> $OUTFILE
fi

cat /tmp/$$.2 $OUTFILE > /pdffiles/audit_review.txt


However, I believe we are auditing more items then what is listed in the egrep portion of the script. I don't know much about how to write Unix scripts. Could you or someone please provide input on how I would modify the script to check for all the things our client is auditing?
See list below:

AUDIT ALL;
      AUDIT ALL PRIVILEGES;
      AUDIT SYSDBA;
      AUDIT SYSOPER;
      AUDIT ALTER SEQUENCE;
      AUDIT ALTER TABLE;
      AUDIT COMMENT TABLE;
      AUDIT GRANT DIRECTORY;
      AUDIT GRANT PROCEDURE;
      AUDIT GRANT SEQUENCE;
      AUDIT GRANT TABLE;
      AUDIT GRANT TYPE;
      AUDIT RENAME ON DEFAULT BY ACCESS;
audit ADMINISTER DATABASE TRIGGER;
      audit ALTER ANY CLUSTER;
      audit ALTER ANY DIMENSION;
      audit ALTER ANY INDEX;
      audit ALTER ANY INDEXTYPE;
      audit ALTER ANY LIBRARY;
      audit ALTER ANY OUTLINE;
      audit ALTER ANY PROCEDURE;
      audit ALTER ANY ROLE;
      audit ALTER ANY SEQUENCE;
      audit ALTER ANY SNAPSHOT;
      audit ALTER ANY TABLE;
      audit ALTER ANY TRIGGER;
      audit ALTER ANY TYPE;
      audit ALTER DATABASE;
      audit ALTER PROFILE;
      audit ALTER RESOURCE COST;
      audit ALTER ROLLBACK SEGMENT ;
      audit ALTER SESSION;
      audit ALTER SYSTEM;
      audit ALTER TABLESPACE;
      audit ALTER USER;
      audit ANALYZE ANY;
      audit AUDIT ANY;
      audit AUDIT SYSTEM;
      audit BACKUP ANY TABLE;
      audit BECOME USER;
      audit COMMENT ANY TABLE;
      audit CREATE ANY CLUSTER;
      audit CREATE ANY CONTEXT;
      audit CREATE ANY DIMENSION;
      audit CREATE ANY DIRECTORY;
      audit CREATE ANY INDEX;
      audit CREATE ANY INDEXTYPE;
      audit CREATE ANY LIBRARY;
      audit CREATE ANY OPERATOR;
      audit CREATE ANY OUTLINE;
      audit CREATE ANY PROCEDURE;
      audit CREATE ANY SEQUENCE;
      audit CREATE ANY SNAPSHOT;
      audit CREATE ANY SYNONYM;
      audit CREATE ANY TABLE;
      audit CREATE ANY TRIGGER;
      audit CREATE ANY TYPE;
      audit CREATE ANY VIEW;
      audit CREATE CLUSTER;
      audit CREATE DATABASE LINK;
      audit CREATE DIMENSION;
      audit CREATE INDEXTYPE;
      audit CREATE LIBRARY;
      audit CREATE OPERATOR;
      audit CREATE PROCEDURE;
      audit CREATE PROFILE;
      audit CREATE PUBLIC DATABASE LINK;
      audit CREATE PUBLIC SYNONYM ;
      audit CREATE ROLE;
      audit CREATE ROLLBACK SEGMENT;
      audit CREATE SEQUENCE;
      audit CREATE SESSION;
      audit CREATE SNAPSHOT;
      audit CREATE SYNONYM;
      audit CREATE TABLE;
      audit CREATE TABLESPACE;
      audit CREATE TRIGGER;
      audit CREATE TYPE;
      audit CREATE USER;
      audit CREATE VIEW;
      audit DROP ANY CLUSTER;
      audit DROP ANY CONTEXT;
      audit DROP ANY DIMENSION;
      audit DROP ANY DIRECTORY;
      audit DROP ANY INDEX;
      audit DROP ANY INDEXTYPE;
      audit DROP ANY LIBRARY;
      audit DROP ANY OPERATOR;
      audit DROP ANY OUTLINE;
      audit DROP ANY PROCEDURE;
      audit DROP ANY ROLE;
      audit DROP ANY SEQUENCE;
      audit DROP ANY SNAPSHOT;
      audit DROP ANY SYNONYM;
      audit DROP ANY TABLE;
      audit DROP ANY TRIGGER;
      audit DROP ANY TYPE;
      audit DROP ANY VIEW;
      audit DROP PROFILE;
      audit DROP PUBLIC DATABASE LINK;
      audit DROP PUBLIC SYNONYM;
      audit DROP ROLLBACK SEGMENT ;
      audit DROP TABLESPACE;
      audit DROP USER;
      audit FORCE ANY TRANSACTION;
      audit FORCE TRANSACTION;
      audit GLOBAL QUERY REWRITE;
      audit GRANT ANY PRIVILEGE;
      audit GRANT ANY ROLE;
      audit MANAGE TABLESPACE;
      audit QUERY REWRITE;
      audit RESTRICTED SESSION;
      audit UNLIMITED TABLESPACE;      

Thanks!
sikyalaSenior Database AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve WalesSenior Database AdministratorCommented:
There's two types of audit "output" - one that is written to $ORACLE_HOME/rdbms/audit and is the output of setting the audit_sys_operations parameter.  The other is the output from the Oracle AUDIT command options.  This output is written to SYS.AUD$ and will not ever be picked up by the script I gave you in that earlier question.

For that, you need to query SYS.AUD$ and make sense of the output.
0
sikyalaSenior Database AdministratorAuthor Commented:
I think I may have confused you. The audit logs are being generated at the OS level not in the database. I only pasted a list of the things we are auditing to give you an idea what I need to check for in the audit logs. Your script does work. I just need to make sure it is grepping for all possible key words that may show up in those OS logs.
0
Steve WalesSenior Database AdministratorCommented:
The only advice I can offer here is to manually examine a sample of your files from time to time.  That's how I built the list of what I was trying to report on and then the sublist of what I could exclude.

The Audit logs at the OS level probably will not show commands you are trying to trip with the audit statements above if they are run by a non SYSDBA user - a create table statement issued by user SCOTT isn't going to appear in the OS level log as far as I'm aware - that only going to appear in SYS.AUD$ if you have turned on auditing with AUDIT CREATE TABLE.

So I don't know that what you're trying to trap is even in the files to begin with...
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sikyalaSenior Database AdministratorAuthor Commented:
you are right. What I am trying to scan for doesn't appear in the audit logs. apparently there is according to the translation of what appears in the logs a number that represents each action (i.e. alter, rename etc). It is confusing. How is your script working against the OS audit logs without those words actually appearing in the logs?
0
Steve WalesSenior Database AdministratorCommented:
You wrote: "How is your script working against the OS audit logs without those words actually appearing in the logs"

I'm sorry, I don't understand what you mean here?
0
sikyalaSenior Database AdministratorAuthor Commented:
I am referring to this section of your script:

egrep -i 'ALTER|INSERT|UPDATE|DELETE|CREATE|DROP'
0
sikyalaSenior Database AdministratorAuthor Commented:
From what I understand that you are saying these words don't appear in the logs.
0
Steve WalesSenior Database AdministratorCommented:
Ah, now I understand.

What I'm asking is, if you admit that it's not in the audit logs, there's nothing I can give you that's going to allow you to find information.

These words are appearing in my audit logs:

Excerpt from one of them:

Mon Apr  9 05:00:02 2007
ACTION : 'CONNECT'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL:
STATUS: 0

Mon Apr  9 05:00:02 2007
ACTION : 'alter database backup controlfile to trace'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL:
STATUS: 0


This information is being generated solely from setting the parameter "audit_sys_operations = true"

Steve
0
sikyalaSenior Database AdministratorAuthor Commented:
Oracle puts a action code or number in the audit logs. For example I altered a table and Oracle generated a log and the action code appeared instead of ALTER Table. The code was 15. These codes are defined in the audit-actions table.
0
sikyalaSenior Database AdministratorAuthor Commented:
OOH so if I set "audit_sys_operations = true" then I will see what I am looking for. So do I need to shutdown the database and restart it after I change the value of this parameter?
0
Steve WalesSenior Database AdministratorCommented:
Yes, you will need a restart.
0
sikyalaSenior Database AdministratorAuthor Commented:
I changed the value of that parameter and my audit logs are still giving me codes:

Mon Apr  9 15:31:07 2007
SESSIONID: "80822" ENTRYID: "2" STATEMENT: "9" USERID: "IATAC" TERMINAL: "pts/2" ACTION: "15" RETURNCODE: "0" OBJ$CREATOR: "IATAC" OBJ$NAME: "TEST2" OS$USERID: "oracle"

What I did was alter a test2  table ( dropped a column). I really want my logs to look like yours does. I must be missing a configuration somewhere.
0
Steve WalesSenior Database AdministratorCommented:
What file are you looking at?  Where is it from?

0
sikyalaSenior Database AdministratorAuthor Commented:
These are my audit parameter settings:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u03/audit/iatac
audit_sys_operations                 boolean     TRUE
audit_trail                          string      OS
transaction_auditing                 boolean     TRUE

I am looking at the files in  /u03/audit/iatac directory. They have a .aud extention.
0
Steve WalesSenior Database AdministratorCommented:
The only difference between your settings and mine is the audit_trail parameter (audit_file_dest is different too, of course).

Mine is set to DB.  This sends DDL auditing to sys,aud$ and I have a third party program to interrogate that data.
0
sikyalaSenior Database AdministratorAuthor Commented:
How does your database write to audit log files when your audit_trail is set to DB?
0
sikyalaSenior Database AdministratorAuthor Commented:
I was looking at some audit dictionary tables as sysdba and later I checked the audit logs and notice this:

Mon Apr  9 17:16:00 2007
ACTION : 'select count(*) from DBA_AUDIT_TRAIL'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/2
STATUS: 0

This is exactly what I want. However, when I am logged in as a regular user I don't get the same information in the audit logs. Is there a specific configuration that would give me the same output for a user?
0
Steve WalesSenior Database AdministratorCommented:
No.

There are two types of audit information generated via the settings you now have.  There's the operations performed by the privilieged users (SYS, SYSTEM, anyone with the DBA role) that are always written to operating system.  Doesn't matter what you set the audit parameters to, if you're logging sys operations, these are always logged to the OS.

The other set of audit operations logged are the more user related stuff - the creates, deletes, audits (which is the stuff you're talking about).

When our internal auditors wanted us to start tracing some of that stuff, we turned them on and got management to spend a few thousand dollars on a tool called DB-Audit and it's companion software Alert Center ( http://www.softtreetech.com/dbaudit/ ) - this software goes and grabs all the information I need out of the audit tables and emails it to me daily.

It's a little clunky at times and I had quite a bit of trouble getting it configured, but with the help of the vendor we got it set up.

This was a whole lot easier than me working out manually everything you've spent the last several weeks trying to work out :)

Hope this clears things up for you a whole lot more.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Unix OS

From novice to tech pro — start learning today.