Script for Oracle user activity auditing

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 -

something like this:

PW_DBN='whatever_auth/passwd@database_nm'

maillist_dba='fred@bedrock.com;barney@bedrock.com'

sqlplus $PW_DBN @$FSQL1 > fsql1.out

egrep -i '(create|alter|drop|truncate|replace|grant|revoke|analyze|audit|comment)' fsql1.out > fsql1.out2

if [[ ! -s fsql1.out2 ]]; then
  cat fsql1.out2 | mailx $maillist_dba
fi

sqlplus $PW_DBN @$FSQL2 > fsql2.out
if [[ ! -s fsql2.out ]]; then
  cat fsql2.out | mailx $maillist_dba
fi

etc...

The 3rd query should print out those users by including a "HAVING COUNT(*) > 1"
clause in your SQL.

HTH.

Thanks,

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

• divroro12 -