OraFAQ Forum: Unix » script for audit reporting

Home » Infrastructure » Unix » script for audit reporting (Oracle 10.2.0.4 runnning on SunOS tmolab03 5.9 )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

script for audit reporting [message #438226]

Thu, 07 January 2010 11:00

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 -

Report message to a moderator

Re: script for audit reporting [message #438227 is a reply to message #438226]

Thu, 07 January 2010 11:12

Mahesh Rajendran
Messages: 10708
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

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 -

Report message to a moderator

Re: script for audit reporting [message #438236 is a reply to message #438228]

Thu, 07 January 2010 11:39

Mahesh Rajendran
Messages: 10708
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

Report message to a moderator

Re: script for audit reporting [message #438248 is a reply to message #438226]

Thu, 07 January 2010 12:14

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 -

Report message to a moderator

Previous Topic:	Telnet configuration
Next Topic:	Attachment at Shell script

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 13 11:02:20 CST 2025