Solved

Modify unix script

Posted on 2007-03-20
18
565 Views
Last Modified: 2013-12-27
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!
0
Comment
Question by:sikyala
  • 10
  • 8
18 Comments
 
LVL 22

Expert Comment

by:Steve Wales
ID: 18766611
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
 

Author Comment

by:sikyala
ID: 18766660
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
 
LVL 22

Expert Comment

by:Steve Wales
ID: 18766826
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
 

Author Comment

by:sikyala
ID: 18877216
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
 
LVL 22

Expert Comment

by:Steve Wales
ID: 18877627
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
 

Author Comment

by:sikyala
ID: 18877783
I am referring to this section of your script:

egrep -i 'ALTER|INSERT|UPDATE|DELETE|CREATE|DROP'
0
 

Author Comment

by:sikyala
ID: 18877793
From what I understand that you are saying these words don't appear in the logs.
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 18877811
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
 

Author Comment

by:sikyala
ID: 18877813
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:sikyala
ID: 18877861
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
 
LVL 22

Expert Comment

by:Steve Wales
ID: 18877899
Yes, you will need a restart.
0
 

Author Comment

by:sikyala
ID: 18878093
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
 
LVL 22

Expert Comment

by:Steve Wales
ID: 18878100
What file are you looking at?  Where is it from?

0
 

Author Comment

by:sikyala
ID: 18878138
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
 
LVL 22

Expert Comment

by:Steve Wales
ID: 18878211
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
 

Author Comment

by:sikyala
ID: 18878717
How does your database write to audit log files when your audit_trail is set to DB?
0
 

Author Comment

by:sikyala
ID: 18879005
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
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 18879096
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now