Home » Infrastructure » Unix » script for audit reporting (Oracle 10.2.0.4 runnning on SunOS tmolab03 5.9 )
script for audit reporting [message #438226] Thu, 07 January 2010 11:00 Go to next message
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
Senior Member
Hi All,

I need to put in place a UNIX shell script that calls three sql scripts & reports to the DBAs.
I already have the three sql scripts in place & they perform the following database auditing actions:

1. actions.sql
This script queries the DBA_AUDIT _TRAIL table to look for database user actions within a specific time
frame.
If the user action involves create, alter, drop, truncate, replace, grant, revoke, analyze, audit,
comment, it should shoot an email to the DBAs.

2. non-existent.sql
This script queries the DBA_AUDIT_SESSION & DBA_USERS tables to look for users that logged onto the database,
but are not in the DBA_USERS table.
If any records are found, it should shoot an email to the DBAs specifying that a non-database user logged
into the database.

3. usershareacct.sql
This script queries the DBA_AUDIT_SESSION table to check for situations whereby the same user account is logged
on more than one user terminal.
If that's the case, the DBAs should be notified by email.

So, the shell script should call the three sql scripts & report to the DBAs as per the specifications
outlined above.

Your help will be highly appreciated.

Regards,

- divroro12 -
Re: script for audit reporting [message #438227 is a reply to message #438226] Thu, 07 January 2010 11:12 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10613
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Quite simple.
It would be something like this.
sqlplus -s user/[email]pass@yourDB[/email] <<EOF

spool spool_for_first.
your sql_format_options
@your_first_sql
spool off
spool spool_for_second
@your_second_sql
Spool off.

#repeat for third

#use mailx or other tool to send email.
mailx -s "this subject" [email]to_this_user@domain[/email] < this_spool_file.
#repeat
EOF

note:
use unix commands test or grep to check for specific entries in spool file.

[Updated on: Thu, 07 January 2010 11:12]

Report message to a moderator

Re: script for audit reporting [message #438228 is a reply to message #438227] Thu, 07 January 2010 11:14 Go to previous messageGo to next message
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
Senior Member
Mahesh,

note:
use unix commands test or grep to check for specific entries in spool file.[/code][/quote]

This is where my problem is; the unix commands to perform the logic...

- divroro12 -
Re: script for audit reporting [message #438236 is a reply to message #438228] Thu, 07 January 2010 11:39 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10613
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
post your sample spool file.
Just create the sql script that will generate an entry in spool file only when your conditions meet.
Use test -s to check if filesize is greater than 0.
Something like this

#spool file that has entries
Kaapi:ora magvivek$ cat thisSpoolFile
some entry

#spool file without any entry

Kaapi:ora magvivek$ cat anotherSpoolFile
Kaapi:ora magvivek$ 
Kaapi:ora magvivek$ 
Kaapi:ora magvivek$ if test -s thisSpoolFile; then echo SomeEntriesFound.Emaile this; else echo NoEntriesFound.No Email sent; fi
SomeEntriesFound.Emaile this
Kaapi:ora magvivek$ if test -s anotherSpoolFile; then echo SomeEntriesFound.Emaile this; else echo NoEntriesFound.No Email sent; fi
NoEntriesFound.No Email sent



Or
Use grep to search multiple patterns.
Kaapi:ora magvivek$ cat fileOne
first create
second entry
third drop
Kaapi:ora magvivek$ cat fileTwo
some entry we are not intrested
Kaapi:ora magvivek$ grep -E 'create|drop' file*
fileOne:first create
fileOne:third drop
Re: script for audit reporting [message #438248 is a reply to message #438226] Thu, 07 January 2010 12:14 Go to previous message
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
Senior Member
Thanks Mahesh,

Will see what i can come up with from here...

- divroro12 -
Previous Topic: Telnet configuration
Next Topic: Attachment at Shell script
Goto Forum:
  


Current Time: Thu Apr 24 17:18:04 CDT 2014

Total time taken to generate the page: 0.08617 seconds